I am creating a database that tracks the orders placed by a laboratory and the grants they use to pay these orders. I have created a file called orders and a file called grants (I also have a product file and a vendor file but they are irrelevant to this discussion). In the order file I created a layout that sorts the grants by ID(there are 5 right now) and then summarizes the amount spent against each grant by summarizing the total charges for each order. I need to take this calculation one step further. I would like to have a summary within a summary so that for each grant I can also see the amount spent each month. In my mind the report looks like this:

[subsummary]

Grant 1

Amount Spent = $100

[body]

Order 1 Amount = $25 May

Order 2 Amount = $25 May

[subsummary]

Total May Charge = $50

[body]

Order 3 Amount = $30 June

Order 4 Amount = $20 June

[subsummary]

Total June Charge = $50

Grant 2

You get the picture. I have banged my head against the wall on this and I just cant figure out how to do it!! Any ideas would be gratefully appreciated!

What you need to do is to create a calculation field based on the order date that you can do a subsummary on. The field should return the month and year of the invoice in the form mmyyyy eg. 24 April 2001 returns 042001. The calculation would be:

Right("00" & Month(InvoiceDate), 2) & Year(InvoiceDate)

with a text result so you retain any leading zero.

Your layout will be:

Header

---Subsummary (when sorted by Grant)

------Body

----Subsummary (when sorted by MonthYear)

Footer

In the subsummary by MonthDate, you can put the InvoiceDate field and customise the format so it shows only the month name and the year for the label.

Sort the records by Grant and MonthYear.