Benefit Report

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

I have a report based on a query that shows each
employee's vacation time accrued, used and balance. The
problem with the report is that it only shows employees
that have used vacation time and not those who have not.
The query uses 3 tables:

Employee TimeCard WorkHours
--------------------------------------------------
EmployeeID DateWorked JobCode
PaidVacation Hours

The query also has a calculated field:
Balance:[PaidVacation]-[Hours]

By setting the criteria for [JobCode] to "81808" the code
for vacation, I get a query that shows everyone that took
vacation time with their balance.
I would like it to include those who have not taken
vacation also.

Thanks for any advice!
 
Mike said:
I have a report based on a query that shows each
employee's vacation time accrued, used and balance. The
problem with the report is that it only shows employees
that have used vacation time and not those who have not.
The query uses 3 tables:

Employee TimeCard WorkHours
--------------------------------------------------
EmployeeID DateWorked JobCode
PaidVacation Hours

The query also has a calculated field:
Balance:[PaidVacation]-[Hours]

By setting the criteria for [JobCode] to "81808" the code
for vacation, I get a query that shows everyone that took
vacation time with their balance.
I would like it to include those who have not taken
vacation also.
Click to expand...


Look at the query in design view. Right click on the line
joining the tables together and select Join Properties.
Then, choose the option for include all records from the
employee table and only matching data in the other tables.

Once you get the query producing the right records, you'll
notice that the Vacation value is Null for those employess
that didn't have any. Use the Nz function to get your
calculation to work with the Null values:
Balance: Nz([PaidVacation], 0) - [Hours]
 
Back
Top