Query help - Records don't exist

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!
 
M

Marshall Barton

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.


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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top