Report with Query and 3 tables

[iewnhoff]

Hi everyone, first time here and it's been a while since I used access.
I have a Datbase that for this purpose has 3 Tables.
Table 1 - Events (Overall Information about a specific Event including Date)
Table 2 - Menu Items (A List of Available Menu Items which is simply the Name of the Item and It's Unit Cost)
Table 3 - Link Table (For Each selection of a Menu Item for an Event there is a record. There is the Primary Key of the Event, the Primary Key of the Menu Item, the Quantity, and a Calculated Field as to the Total Cost of the selection)

Table 3 will have multiple records of a single event with the individual menu items selected, the quantity, and the calculated total for that item.

All is well. Except.


I am struggling on creating a report that shows, for a given year and month (we are good here) a list of each event (from the event table) and the summed value of the ordered items (table 2 and table 3)

I think I need some multiple FROM statements, but I keep getting errors when I try to group by my Event ID.
Any help is greatly appreciated.

Bill

[June7]

Why don't you provide database for analysis? Follow instructions at bottom of my post.

Should be fairly simple to group by Event and sum the cost with expression in group footer textbox: =Sum(TotalCost)

Apply year/month filter to report when opening.

[madpiet]

Hi everyone, first time here and it's been a while since I used access.
I have a Datbase that for this purpose has 3 Tables.
Table 1 - Events (Overall Information about a specific Event including Date)
Table 2 - Menu Items (A List of Available Menu Items which is simply the Name of the Item and It's Unit Cost)
Table 3 - Link Table (For Each selection of a Menu Item for an Event there is a record. There is the Primary Key of the Event, the Primary Key of the Menu Item, the Quantity, and a Calculated Field as to the Total Cost of the selection)

Table 3 will have multiple records of a single event with the individual menu items selected, the quantity, and the calculated total for that item.

All is well. Except.
I am struggling on creating a report that shows, for a given year and month (we are good here) a list of each event (from the event table) and the summed value of the ordered items (table 2 and table 3)

I think I need some multiple FROM statements, but I keep getting errors when I try to group by my Event ID.
Any help is greatly appreciated.

Bill

Events---(1,M)--EventMenuItems[Quantity]--(M,1)--MenuItems [UnitCost]

then create a query and add a calculated column LineTotal:EventMenuItems[Quantity] * MenuItems[UnitCost]

then sum that in your report.