Query Help

  • Thread starter Thread starter Daniel
  • Start date Start date
D

Daniel

Hello, I have made a database for resource management. I am tracking
vacation requests by employee ID number (Social Security number). I have a
table setup which has stored the amount of vacation each associate is
allowed to take. A second table with takes input from a form once an
associate requests a day off. The problem is with my query, I have designed
it to take the balance of vacation and subtract the new request, which
works, however I can have multiple requests for a particular associate. How
do I get the query to 1). subtract the new request from the vacation balance
every time there is a new request or 2). add the multiple requests by
associate and subtract its total from the total vacation remaining.

I would greatly appreciate the help or perhaps a better suggestion on how I
can do this.
Thank you,
Daniel
 
Daniel,

First thing, don't try to update the vacation balance. It
will be a nightmare to get it right, and keep it that
way. It is far better to compute it when you need it.

Without knowing your basic data structure, it is hard to
say, but I'll give you an example, and let you work from
there. Assume you have your Vacations table
(tbl_Vacation_Requests), and it contains fields EmpID,
VacationStart(Date), VacationEnd(Date), Status (text).
Then to get the number of days approved for the current
year, you would do something like:

SELECT EmpID, SUM(DateDiff("d", VacationStart,
VacationEnd) as AnnualVacation
FROM tbl_Vacation_Requests
WHERE (Status IS NULL OR Status = "Approved")
AND YEAR(VacationStart) = YEAR(Date)
GROUP BY EmpID

In reality, it would be a little more complex than this,
because you only want to consider regular work days (which
means you need to exclude weekends and holidays from the
calculation) and need to account for vacations that extend
from one fiscal year into another.

If you have more questions, post back.

HTH
Dale
-----Original Message-----
Hello, I have made a database for resource management.
Click to expand...
I am tracking vacation requests by employee ID number
(Social Security number). I have a table setup which has
stored the amount of vacation each associate is allowed to
take. A second table with takes input from a form once an
associate requests a day off. The problem is with my
query, I have designed it to take the balance of vacation
and subtract the new request, which works, however I can
have multiple requests for a particular associate. How do
I get the query to
1). subtract the new request from the vacation balance
every time there is a new request
OR
2). add the multiple requests by associate and subtract
its total from the total vacation remaining.

I would greatly appreciate the help or perhaps a better
suggestion on how I
can do this.
Thank you,
Daniel
 
Back
Top