Creating a monthly report with sorts
I am currently working on creating a monthly report to display what amounts were receipted in for certain fields and then by the date.
My database is setup like this:
I have two tables that I believe I need to work with: one is called Main and the other is Receipts.
The main is connected to receipts by ID#.
In the receipts table is ID#, CostCode, ReceiptDate, ReceiptAmt.
What I am trying to do is on a separate report display by month all the receipts that were entered for the month using the receiptdate then sort by the costcode and then display a total of the ReceiptAmt to get the amount receipted in for that for that month for that costcode.
Record 1 would be:
Cost Code: SP
Receipt Date: 1/5/07
Receipt Amt: 25.00
What I would like the report to display is just the total amount for all "SP" cost codes.
I would like to be able to enter a month on the report to display this total amount.
(Enter) Month: January 2007
Cost Code Amt Received for month
SP (total amount here)
HS (total amount here)
What I have tried is an If statement with if (costcode = "SP"; sum(receiptamt;"0.00") with a calculation field. This will work but per record but I can't figure out how to do a constraint to calculate for a month basis on a different layout.
Any help would greatly be appreciated. If you need any more info let me know thanks!