AnsweredAssumed Answered

Creating a monthly report with sorts

Question asked by hillbillette on May 27, 2009
Latest reply on May 27, 2009 by mrvodka


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:

ID: 5664

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!