1 Reply Latest reply on May 27, 2009 9:55 AM by mrvodka

    Creating a monthly report with sorts

    hillbillette

      Title

      Creating a monthly report with sorts

      Post

      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.

       

          Example....

      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.

       

      Example

      (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! 

       

       

       

       

        • 1. Re: Creating a monthly report with sorts
          mrvodka
            

          What you want is pretty much a straight subsummary report. A subsummary report is based off the current found set, therefore you should perform a find for the records that you want. In this current case where the code = "SP". I would also recommend creating a calculation with date result that has a formula like: Date ( Month ( Receipt Date ); 1; Year ( Receipt Date ) ). This will take your receipt date and give it a generic first day of the month, so now you can group by that date field.

           

          In your subsumary report, you can now group by date, then cost code. You can delete out the BODY part since you dont want any of the detail.

          If you are unfamilar with subsummary reports, there a numerous examples of them on the net.