Joining Two Queries

[Guest]

I have one query that calculates the amount of work an employee has booked. I
have another query that calculates the amount they have been paid. I now want
to create a query that calculates the amount they are owed (booked - paid).

Here are my queries:

qryBookedByEmployee:

SELECT qrySumLineTotalsByEmployee.EmployeeID,
qrySumLineTotalsByEmployee.TransactionTypeID,
qrySumLineTotalsByEmployee.BookedInPaidOut AS Booked
FROM qrySumLineTotalsByEmployee
WHERE (((qrySumLineTotalsByEmployee.TransactionTypeID)=1));

qryPaidToEmployee:

SELECT qrySumLineTotalsByEmployee.EmployeeID,
qrySumLineTotalsByEmployee.TransactionTypeID,
qrySumLineTotalsByEmployee.BookedInPaidOut AS Paid
FROM qrySumLineTotalsByEmployee
WHERE (((qrySumLineTotalsByEmployee.TransactionTypeID)=2));


I think this could be done straight from qrySumLineTotalsByEmployee, but
I'll need the above two queries anyway so I may as well use them here. Any
ideas how I can calculate the amount owed? There can be many transactions of
the same type (Booked or Paid) and there will not always be an equal number
of types (maybe 3 booked transactions and only 1 paid transaction).

Thanks

Dave

 

[Jeff Boyce]

David

If you can have multiple records per employee, consider using a Totals query
to get ... a total. Group by the EmployeeID and Sum the amount.

If you do that in each of your two queries, you'll have total booked per
employee and total paid per employee.

If you create a third query that joins the two on EmployeeID, you can see
the "matched" records (booked/paid). If you use a directional join (all of
one, any matching of the other), you can cover the situation I'm guessing
will occur ... work can be booked before payment is made.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Guest]

Thanks, I got it working with an INNER JOIN. Here's the SQL:

SELECT qryBookedByEmployee.EmployeeID, qryBookedByEmployee.Booked,
qryPaidToEmployee.Paid, [Booked]-[Paid] AS Owed
FROM qryBookedByEmployee INNER JOIN qryPaidToEmployee ON
qryBookedByEmployee.EmployeeID=qryPaidToEmployee.EmployeeID
GROUP BY qryBookedByEmployee.EmployeeID, qryBookedByEmployee.Booked,
qryPaidToEmployee.Paid;