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.