SQL Join Statement not grouping totals as expected 1

[maxflitom]

Hello everyone,

I am attempting to create a SQL statement to summarize revenues in an Access 2002 database. In my database structure I have an Events, EventDetail, MenuItems, and a RevenueCategory Tables. I am attempting to create a join to group the details on the Revenue Categories stored in the RevenueCategory table so I can sum the totals per Revenue Category. For example:

MenuItemID ExtendedPrice RevenueCategory
42 500.00 Food
42 600.00 Beverage
42 10.00 Equipment

Here is my SQL statement:
SELECT EventDetail.EventID,_
Sum(EventDetail.Price*EventDetail.Quantity) AS ExtendedTotal, RevenueCategories.RevenueCategory
FROM (EventDetail INNER JOIN MenuItems ON EventDetail.MenuItemID=Menuitems.MenuItemID)_
INNER JOIN RevenueCategories ON MenuItems.RevenueCategoryID=RevenueCategories.RevenueCategoryID
WHERE EventDetail.EventID=42
GROUP BY EventDetail.EventID, EventDetail.Price, EventDetail.Quantity, RevenueCategories.RevenueCategory, EventDetail.EventID
ORDER BY RevenueCategories.RevenueCategory;

The values are there, however the totals are listed individually and not grouped together:

MenuItemID ExtendedPrice RevenueCategory
42 250.00 Food
42 250.00 Food
42 300.00 Beverage
42 300.00 Beverage
42 5.00 Equipment
42 5.00 Equipment

How can I modify my SQL statement to group the totals together by Revenue Category?

Thanks for any and all suggestions!!!

Tom

 

[BGrego]

"SELECT Sum(EventDetail.Price*EventDetail.Quantity) AS ExtendedTotal, RevenueCategories.RevenueCategory
FROM (EventDetail INNER JOIN MenuItems ON EventDetail.MenuItemID=Menuitems.MenuItemID)_
INNER JOIN RevenueCategories ON MenuItems.RevenueCategoryID=RevenueCategories.RevenueCategoryID
WHERE EventDetail.EventID=42
GROUP BY RevenueCategories.RevenueCategory,
ORDER BY RevenueCategories.RevenueCategory;

 

[maxflitom]

BGrego,

Thank you for your post. I see my mistake. I modified your/my SQL to include the EventID and RevenueCategory fields in the SELECT section and removed the Price and Quantity from the GROUP BY section and it works as it should.

Thank you for helping me understand what I was not doing correctly!!!! I have spent too much time with this, but I feel good that I now understand my mistake.

Tom