Is this what you are trying to do?
Month Revenue Labor Cost
January, 2009 $2,000.00 500.00
February, 2009 $2,000.00 500.00
March, 2009 $2,000.00 500.00
April, 2009 $2,000.00 500.00
If not, please give an example
Actually what I want it to look like is as follows:
Location (School Name - there are five schools in our district that serve food)
Month : October, 2009
Labor Cost 2,000.00
This needs to be repeated for each month and for each location. After I figure this out I will need to add Food cost as a second cost item. I have developed calculations that summarize the Revenue by location and month and that seems to show up on this "profit and loss" layout just fine. I also can generate reports for labor costs that summarize labor costs by location and month as well. I just can't seem to get the information from both on one report and then do a calculation on these summarized fields.
Thanks for your help. I am really frustrated...
Can't you keep all types of transactions in a single table?
It would become quite unmanageable. Revenue is based upon 20 -25 different factors that need to be calculated and labor costs have a totally separate set of variables that need to be calculated. I could try that... but it would be very hard to keep the components separate.
thanks for your feedback. It is another approach.
It's an approach that makes the report you specify much simpler to create. What I posted was an approach that could use a table of "month" records that use aggregate functions such as SUM() to compute monthly totals and such from each of your different tables. That approach lends itself to the tabular format I posted as all your subtotals for a given month are computed from a single record.
Creating the format you specify would simply require re-arranging the field locations.
Just keep in mind that you have a trade-off here. Complexities from keeping all your transactions in a single table (with more flexibility) or complexities (and less flexibility) in setting up this report with a "months" table to compute the various sub-totals.
I lean toward putting all the transactions in a single table if at all possible.
Can you elaborate a bit more on the approach of creating a table of "month" records that use aggregate functions? Would my data base have two tables, one that has one record for each month and another with all the revenue and cost information in it??
I will play around with this approach a bit. Can you give me an example of how one record in the month table would look?
thanks for the advice. I am beginning to lean in that direction as this report needs to get done:))
Another approach entirely. If you had a table which had a record with the Year|Month (200912, 201001, etc.)* and the Location, a single record for each unique instance, then you could create a calculation (relational) to sum the revenue and one for costs. In the 2 target tables you would need a calculation for the Year_Month, as well as one for the Location (really should be an ID field).
The relationships from the Monthly Reports table would be based on YearMonth and Location. The calculation(s) would be: Sum (relationship::amount). You only need 2 relationships to the 2 data tables, and 2 calculations (in the YearMonth_Locations table).
So it's not really a "subsummary" report, but it looks much the same and has the same numbers. The only tricky part is to create those records. If the Locations never change, you could just create a bunch of records for the next few years with a Loop. Or you could be a bit more complex, and test for the existence of the month/year/locations needed and create them on the fly if needed when you went there.
The above would produce dynamic totals, ie., if someone edited the original data in the data entry tables, the totals would change. If this is for historical data, and editing earlier entries is not allowed, then you could set the totals into regular number fields, for more speed. I'd keep the relational totals also, for troubleshooting.
The Yearly could be just another 2 relationships and 2 calculations. Alternatively you could do the year as Summary fields. But I'd do the relationships. It could appear in a Sumsummary part however, so it only appears once per a year.
* Year(date) & Right ("0" & Month(date); 2), result number or text, stored
Thanks for the additional approach. I can see a long night ahead of me... :)) These are such good ideas, but as a relatively new user, I will need to work through them pretty methodically.
Fenton is describing the same basic approach as I am. I'd use the following date calculation instead though: DateField - Day(DateField) + 1 set to return a Date result type. This calculation produces an actual date for the first of each month.