How to link 2 queries

[niccig]

Please Help - I have to get this database finished before I begin maternity leave, and this is the LAST hurdle.

I have a report that is based on a query PayDetails from two main tables: tbPayPeriod, tbHours -this gives me 233 records. The query gives me the pay details:
* PayID
* StudentID
* FacultyID
* ProjectID
* Hours
* PayRate
* ProjectCost (PayRate*Hours)
* Sum of Project Cost (separate query based on Faculty ID so can sort)
* Sum of Project Hours (separate query based on Faculty ID so can sort)

For a few records, expense transfers need to be recorded and subtracted from the Project Cost for each FacultyID and StudentID as my department doesn't pay the expense amount. I have a separate table tbexpenses to keep track of these.
* Expense ID
* PayPeriodID
* FacultyID
* StudentID
* ExpenseAmt
* FundingType

I have a query (FacultyExpenses) that calculates the SumOfExpenseAmt for each Faculty ID

I want to add the Faculty Expenses query to the PayDetails query so I can subtract the SumOfExpenseAmt from SumOfProjectCost to get the final cost for our deparment. But it only shows complete pay details for faculty that have a record in tbexpenses. I want all 233 records, to show and only to subtract SumOfAmt from SumOfProjectCost for those Faculty IDs that have it.

Help...I'm close to finishing this and I really want it completed before I leave. Otherwise, I'll have to work on it from home.

Thanks in advance, Nicci

 

[Jon K]

To return all 233 records, you can use an outer join (in query SQL view, if query PayDetails appears on the left in the From Clause, change the word INNER to LEFT, otherwise change it to RIGHT.)

You may also need to use the Nz() function to convert Null values to zeros in the calculation, that is

SumOfProjectCost - Nz(SumOfExpenseAmt)
.