A bit of background on this db:
Once a month a check comes in that is for the previous month and distributed to the various counties 'accounts'.
Disbursements (spending the funds) will be varied here and there and are by county as well, with a 15% admin fee for each disbursement.
tblIncoming: *DateRecd, EffectiveDate, CheckAmount
tblIncomingByCounty: *InID, County, EffectiveDate, AmountIn
tblCounties: *County
tblDisbursements: *DisID, County, TransactionDate, PeopleServed, Notes
Here is what I'm after: (couldn't figure out how to upload a pic, and this forum doesn't keep spacing - so I hope it makes sense)
_________Incoming ___Disbursements__Admin Fee___People Served
County __Month_YTD ___Month _YTD __Month _YTD __Month _YTD
I have scoured the web for something like this (month & YTD columns), but was shocked to find nothing. I have read two different ways to handle date criteria - one is within the query, the other is using openReport with the parameters. I am not sure what would work best here.
Can openReport (from a form with a month and year selected) yield these results? (with the month and year being used for the month column, and the YTD column yeilding Jan 1 of that year to the end of the selected month) If so any examples.
Or... do I need a separate month and YTD query?
This is the main query 'behind' the report:
SELECT tblCounties.County AS tblCounties_County, Nz([qryIncomingByCounty]![SumOfAmount],0)-Nz([qryAdminFeeByCounty]![SumOfTransactionAmount],0)-Nz([qryAdminFeeByCounty]![AdminFee],0) AS CurrentBalance, Val(Nz([qryIncomingByCounty]![SumOfAmount],0)) AS SumOfAmount1, Nz([qryAdminFeeByCounty]![SumOfPeopleServed],0) AS SumOfPeopleServed1
FROM (tblCounties LEFT JOIN qryIncomingByCounty ON tblCounties.[County] = qryIncomingByCounty.[County]) INNER JOIN qryAdminFeeByCounty ON tblCounties.County = qryAdminFeeByCounty.County;
Thanks for any direction you can give me