I am attempting to create a SQL statement to summarize revenues in an Access 2002 database based on values in the RevenueCategory Table. In my database structure I have an Events, EventDetail, MenuItems, and a RevenueCategory Table. The Events and EventDetail have an EventID field, the EventDetail and MenuItems have a MenuItemID field, the MenuItems and RevenueCategories have a RevenueCategoryID field, and the RevenueCategories and TaxRates have a TaxRateID field. I am attempting to create a join and group the details on the Revenue Categories stored in the RevenueCategory table so I can sum the totals per Revenue Category. For example:
Food Beverage Equipment
“Event 1” 2000.00 5000.00 500.00
“Event 2” 2000.00 4000.00 400.00
Totals 4000.00 9000.00 900.00
I wish to place the totals in a DataReport (VB6) to summarize the totals by Event and by Revenues Categories for the day.
I played around with some SQL statements, however, I have not had to create a SQL this complex before.
Can anyone give some ideas on either a SQL statement or how I can place these in a DataReport to display the results?
Any and all help is greatly appreciated.
Tom
Food Beverage Equipment
“Event 1” 2000.00 5000.00 500.00
“Event 2” 2000.00 4000.00 400.00
Totals 4000.00 9000.00 900.00
I wish to place the totals in a DataReport (VB6) to summarize the totals by Event and by Revenues Categories for the day.
I played around with some SQL statements, however, I have not had to create a SQL this complex before.
Can anyone give some ideas on either a SQL statement or how I can place these in a DataReport to display the results?
Any and all help is greatly appreciated.
Tom
