OK. Here is my table structure
Employee: empID, empNo, empName, empRestStatus, empRestDate
1, 12345, Thomas Johnson, True, 4.1.2009
2, 67890, Tom Mantia, False, Null
3, 54321, JohnTest, False Null
Dept: deptID, dept
1, Engineering
2, Yard
3, Sandblast
DeptTrans: deptTransID, deptID, empID, transDt, transEndDt
1, 1, 1, 1.1.09, Null
2, 2, 1, 12.1.08, 12.31.08
3, 3, 2, 1.2.09, Null
4, 3, 3, 1.3.09, Null
(One employee may have transferred departments many times)
(One employee may be assigned vacation time many times)
VacationTime: vacaID, empID, empEarnedTime, vacaTimeAwardDt
1, 1, 8, 2.1.09
2, 2, 0, Null
3, 3, 4, 2.1.09
(One employee maybe have many incidents)
EmpIncidents: incidentID, empID, incidentTYpeID, incidentDt
1, 1, 2, 12.2.08
2, 2, 2, 1.4.09
(One employee can have many types of incidents)
IncidentTypeId: incidentTypeID, incident (the only incidents that
allow an employee and his dept to lose time are types 2 and 3)
1, NonRecordable
2, Recordable
3, Lost-Time
I hope this is thorough enough. If you need more information please
let me know.
Thanks for any assistance you can provide
"How" really depends on "what" ... the data/table structure you have (or
could have) will constrain "how" you might do this.
If you'll post more information about your data structure (?and some example
data?), folks here should be able to help.
Jeff Boyce
Microsoft Office/Access MVP
(hint: take a look at Access HELP on "Totals queries" for how to addup all
the times ...)
OK. So let's say I do it your way and have it as a calculated value,
as opposed to a stored table value...what values would it use to do
the calculation? And how would I initialize the amount of leave each
employee already has? after all, vacaTimeEarned would have to be added
to the amount each employee initially has.
And there is another thing; having stored the vacaTimeEarned, I input
what each employee already had an added to that what they earned...It
seems like i would need a field in the table to represent what they
had initially, if i follow your thought process...
I am storing the vacaTimeEarned. I feel it should be stored bc I
would need to increment when leave is added and decrement when leave
is used. I can't see how I would be successful unless it is stored.
I use my query to calculate the value after I initialize what the
employee's initial leave was...then each month run the query to update
the vacaTimeEarned value.
It sounds like you're trying to use Access like a spreadsheet, and
trying to
store the total VacationTimeEarned. Don't do that!
Use a query to derive (i.e., calculate) this value.
Regards
Jeff Boyce
Microsoft Office/Access MVP
I understand, thanks for clearing that up. I have a question
though...it seems as if i should be appending, but also updating bcI
have to update the vacaTimeEarned each time new leave is earned
(monthly). Is that possible using the append query? I tried to turn
my update into an append, but I couldn't figure out how to increment
the vacaTimeEarned by 4. I have posted the update query.
UPDATE Employee INNER JOIN vacationTime ON Employee.empID =
vacationTime.empID SET vacationTime.vacaTimeEarned = [vacationTime].
[vacaTimeEarned]-4, Employee.empRestStatus = No,
vacationTime.vacaTimeAwardDt = Date()
WHERE (((vacationTime.vacaTimeEarned)=[vacaTimeEarned]+4) AND
((vacationTime.empID) Not In (SELECT DISTINCT Employee.empID FROM
(department INNER JOIN DeptTrans ON Department.deptId =
DeptTrans.deptID) INNER JOIN Employee ON DeptTrans.empID =
Employee.empID WHERE DeptTrans.deptTransDt <= Date() AND Nz
(DeptTrans.deptTransEndDt, Date()) >= Date() And Department.deptId IN
(SELECT DISTINCT Department.deptId FROM ((department INNER JOIN
DeptTrans ON Department.deptId = DeptTrans.deptID) INNER JOIN Employee
ON DeptTrans.empID = Employee.empID) INNER JOIN EmpIncidents ON
Employee.empID = EmpIncidents.empId WHERE DeptTrans.deptTransDt <=
incidentDt AND Nz([DeptTrans].[deptTransEndDt],Date())>=
incidentDt))));
Thank you
Update does that (replace what's there).
If you need to keep a history to show every update that was done,
that's
appending new records.
Regards
Jeff Boyce
Microsoft Office/Access MVP
What I'd like to do is keep track of when the update statement is
ran...so I guess that would be run the update first but then I would
need to append to the history table is what I was thinking.
I have an update statement that is ran once a month to add leave....but
i wanted to keep track of that which is why i was asking should an
append query be ran after that to add to the history table. I am
asking bc I noticed that after my update is ran, it just replaceswhat
was there intially, not create new records each time after the update.
To do what?
Append queries add records.
Update queries modify existing records.
What do you want to accomplish?
Regards
Jeff Boyce
Microsoft Office/Access MVP
HELLO:
I am inquiring about whether it would be more appropriate to use
an
update or append query. I am working on assigning leave. to assign
leave, I have an update query that updates my table and increments
each employee that should receive leave by 4. The issue I noticed
with that is that I wasn't keeping a history but just replacing
the
same record. So then I thought that if I wanted to keep trackof
when
it was awarded, I would need to keep a history, which might mean i
needed an append query. Or maybe I need both?
Any assistance would be appreciated.
what I'd
