Query help - Records don't exist

  • Thread starter Thread starter Tara
  • Start date Start date
T

Tara

I need help creating a query based on 2 tables. One table (tblEmployee)
contains all Employees, their EmployeeID, and BucketEarned (the amount of
expense money they have earned). The second table (tblBucketRequests)
contains expense reimbursement requests from those employees. I need the
query to calculate the amount of money available for each employee based on
what they've earned vs. what they've used. The problem is that not all
employees have made requests, therefore records in tblBucketRequests don't
exist for everyone. So the query only pulls data for those that have made
requests. I need it to show everyone. For those that don't have any records
in tblBucketRequests, I want to show the value from the BucketEarned field
instead.

Thanks for any help!
 
Tara said:
I need help creating a query based on 2 tables. One table (tblEmployee)
contains all Employees, their EmployeeID, and BucketEarned (the amount of
expense money they have earned). The second table (tblBucketRequests)
contains expense reimbursement requests from those employees. I need the
query to calculate the amount of money available for each employee based on
what they've earned vs. what they've used. The problem is that not all
employees have made requests, therefore records in tblBucketRequests don't
exist for everyone. So the query only pulls data for those that have made
requests. I need it to show everyone. For those that don't have any records
in tblBucketRequests, I want to show the value from the BucketEarned field
instead.
Click to expand...


Use an outer join between the two tables. This is done by
right clicking the line between the two table's ID fields
and choosing the all records from tblEmployee and any
matching records from tblBucketRequests.

Then you can modify the calculated field to use the Nz
function to deal with Null values in the request field.
 
Back
Top