Problems Calculating Query Fields

  • Thread starter Thread starter Phillip Schmidt
  • Start date Start date
P

Phillip Schmidt

Here is my dilemna:

I have a database that tracks the quantity and reasons
that an employee takes a day off, and I am wanting to
calcualate the remaining number of days of vacation that
someone has.

Desired End Result
In a Query [RemainingVacation]....I want a calculated
field to subtract the total number of vacation days that a
person is allotted [Employees]![AvailableVacation] from
the total number of vacation days that they have used
since a given date, I think this would be, [TakenVacation]!
Sum[txtTotalAbsent]. I would like the Query to list all
employees even if they have not used any vacation for the
year or I guess be able to list it somehow as a field in a
report that summarizes of all of an employees absences.

Slightly More specific information about the database.

Table
[Employees]![AvailableVacation] contains an inputted data
value of the total number of days that an employee has

Query
I have a collection of Queries that give me a summary of
all of the people that have not been at work for a
specific reason (a list of 7), and in this case that is
[TakenVacation] which sums up the total days in
[txtTotalAbsent]

Thanks so much and please contact me if I missed a key
piece of information.

Phil Schmidt
 
Phil,

I am assuming that you have a field in your Employees table called
EmployeeID which uniquely identifies each employee. And I am assuming
that this is also the name of the field that is used to identify the
employee in the table where you record absences. And thus, I am
assuming the TakenVacation query has two fields, being EmployeeID and
txtTotalAbsent which is the total days take by each employee. If these
assumptions are wrong, let me know, but what I have described is
probably pretty much how it should be. So then, all you need to do is
make another query, which includes the Employees table and the
TakenVacation query, joined on the EmployeeID field from both with a
Left Join (so you get all employees, even if there is no record for them
in TakenVacation query). And your calculated field will look like this...
RemainingVacation: [AvailableVacation]-Nz([txtTotalAbsent],0)
 
Back
Top