Two queries into one (Income minus Expenses)

[alanrcooke]

I have two queries which lists (datasheet) all income for a tax year - with calculated total. The other lists all expenses for the tax year - with calculated total.

Currently I simply note down each total and calculate my net income.

The tables and queries are not related in any way.

What I want to know is if there is a way I can combine the two queries in such a way that I can output the net income both as datasheet and hopefully a report as well for my records.

Thanks in advance

Alan

PS if required I can provide the SQL for both queries.

 

[theDBguy]

Hi. Yes, please provide the SQL and sample data plus the formula to calculate the net income.

 

[alanrcooke]

Thanks for quick response.

This is the SQL to output 'gross income':

SELECT tblClient.fldClient, tblWorkCompleted.fldInvoiceNumber AS Invoice, qryWorkCompTransDetails.fldWorkDate, tblWorkCompleted.fldInvoiceDate, qryWorkCompTransDetails.fldDescription, qryWorkCompTransDetails.fldHours, qryWorkCompTransDetails.fldHoursRate, Sum((Nz([fldHours]*[fldHoursRate]))) AS [Total Hours], Sum(qryWorkCompTransDetails.fldDayRate) AS [Total Day Rate], qryWorkCompTransDetails.fldMiles AS [Miles Done], qryWorkCompTransDetails.fldMilesRate AS [Rate per Mile], Sum((Nz([fldMiles]*[fldMilesRate]))) AS [Total Travel], Sum(qryWorkCompTransDetails.fldMisc) AS [Total Misc], Sum((Nz([fldHours]*[fldHoursRate])+(Nz([fldMiles]*[fldMilesRate]))+(Nz([fldDayRate]+(Nz([fldMisc],0)))))) AS [Total Invoiced], Sum(((Nz([fldHours]*[fldHoursRate])+(Nz([fldMiles]*[fldMilesRate]))+(Nz([fldDayRate]+(Nz([fldMisc],0))))))/12) AS [Month Avg]
FROM tblClient INNER JOIN (tblWorkCompleted LEFT JOIN qryWorkCompTransDetails ON tblWorkCompleted.[fldTransactionsPK] = qryWorkCompTransDetails.[fldWorkCompletedID]) ON tblClient.fldClientPK = tblWorkCompleted.fldAccountID
GROUP BY tblClient.fldClient, tblWorkCompleted.fldInvoiceNumber, qryWorkCompTransDetails.fldWorkDate, tblWorkCompleted.fldInvoiceDate, qryWorkCompTransDetails.fldDescription, qryWorkCompTransDetails.fldHours, qryWorkCompTransDetails.fldHoursRate, qryWorkCompTransDetails.fldMiles, qryWorkCompTransDetails.fldMilesRate, tblWorkCompleted.fldAccountID, tblWorkCompleted.fldAccountNumber, qryWorkCompTransDetails.fldWorkDate
HAVING (((tblWorkCompleted.fldInvoiceDate)>="06/04" & "/" & [Enter start year] And (tblWorkCompleted.fldInvoiceDate)<="05/04" & "/" & [Enter end year]) AND ((qryWorkCompTransDetails.fldDescription) Like "Work Completed - " & "*"))
ORDER BY tblWorkCompleted.fldInvoiceNumber DESC;


This is the SQL to output the 'Expenses'

SELECT tblExpenses.fldDate AS [Exp Date], Sum(tblExpenses.fldExpense) AS [Total Exp], tblExpenses.fldNotes, tblClient.fldClient AS Client, tblAllocation.fldAllocation AS Allocation, tblVendors.fldVendor AS Vendor, tblExpenses.fldExpensesPK
FROM tblAllocation INNER JOIN (tblVendors RIGHT JOIN (tblProject INNER JOIN (tblClient INNER JOIN tblExpenses ON tblClient.fldClientPK = tblExpenses.fldClientFK) ON tblProject.fldProjectPK = tblExpenses.fldProjectFK) ON tblVendors.fldVendorPK = tblExpenses.fldVendorFK) ON tblAllocation.fldAllocationPK = tblExpenses.fldAllocationFK
GROUP BY tblExpenses.fldDate, tblExpenses.fldNotes, tblClient.fldClient, tblAllocation.fldAllocation, tblVendors.fldVendor, tblExpenses.fldExpensesPK, tblExpenses.fldTaxDeductable, tblProject.fldProject, tblAllocation.fldAllocation
HAVING (((tblExpenses.fldDate)>="06/04" & "/" & [Enter Start Tax Year] And (tblExpenses.fldDate)<="05/04" & "/" & [Enter End Tax Year]) AND ((tblExpenses.fldTaxDeductable)=False));


I am unable to provide sample data due to the sensitivity of the info but hopefully the above will assist.

As mentioned each query provides a 'tax year' of data along with an annual total for each. I work out the net total manually.

 

[Pat Hartman]

Is there some reason you can't join the two queries on client?

I would use a left join of income to expenses just in case there are are no expenses.

 

[theDBguy]

Hi. I'm with Pat on this. If you want to post a copy of your db, you can remove the "sensitive" data and replace them with "dummy" data.