How to group records

[danb]

Hi,

I've got a database containing three tables:


=====================
tblEvent:
---------------------------
Id (PK) | Event_Title | Event_Price
=====================


=====================
tblDelegate:
---------------------------
Id (PK) | Delegate_Name
=====================


=====================
tblBookings:
---------------------------
Id (PK) | Event_Id (FK) | Delegate_Id (FK)
=====================



I need to output an HTML table with the following headings:

==============================================
Event Title | Event Price | No. Delegates Booked | Total Price
==============================================

How do I group(/join?) the tables to show this data? I'm confident with doing the recordset stuff, it's just that I have no idea how to build a SELECT string, with data grouped into events where the total number of delegates who've booked on that event will be added together.

Does this make sense?

Any help would be much appreciated, thanks!

 

[gromit]

Hi -

Try something along these lines...
SELECT tblEvent.Event_Title, tblEvent.Event_Price, Count(tblDelegate.Delegate_Name) AS NumberBooked, Sum(tblEvent.Event_Price) AS TotalPrice
FROM tblEvent INNER JOIN (tblDelegate INNER JOIN tblBookings ON tblDelegate.ID = tblBookings.Delegate_Id) ON tblEvent.ID = tblBookings.Event_Id
GROUP BY tblEvent.Event_Title, tblEvent.Event_Price;

A few (unasked for) suggestions:
1. Rename your Primary Keys to something unique for each table. E.g. BookingID, DelegateID, EventID. This will help keep things straight as you start joining tables.
2. Remove the underscores for field names. This can also help avoid some very confusing problems down the road.
3. You can prototype your queries in design view and then view the SQL to get the syntax. (The automatic generation by Access is not always the cleanest, but it can help you find your way through the thicket.)

hth,

g

 

[danb]

Thanks Gromit, that very nearly does the job.

The trouble is, I need to group the events by the Event Id, rather than Event Title, since different events may have the same title.

I tried altering the SELECT string to:

SELECT tblEvent.Id, tblEvent.Event_Title, tblEvent.Event_Price, Count(tblDelegate.Delegate_FirstName) AS NumberBooked, Sum(tblEvent.Event_Price) AS TotalPrice FROM tblEvent INNER JOIN (tblDelegate INNER JOIN tblBookings ON tblDelegate.Id = tblBookings.Delegate_Id) ON tblEvent.Id = tblBookings.Event_Id GROUP BY tblEvent.Id, tblEvent.Event_Price;

But receive the error:

"You tried to execute a query that does not include the specified expression 'Event_Title' as part of an aggregate function."



Can anyone see why this might be? My TSQL isn't very good as you can probably tell, so any pointers would be great, thanks.

 

[gromit]

SQL queries that use aggregate functions (SUM, COUNT, GROUP BY etc.) are a little fussy. All the fields that are returned need to be in one of the aggregate functions.

In this case, Event_Title is not included in any function, so it generates the error message.

Try this instead -
SELECT tblEvent.Id, tblEvent.Event_Title, tblEvent.Event_Price, Count(tblDelegate.Delegate_FirstName) AS NumberBooked, Sum(tblEvent.Event_Price) AS TotalPrice FROM tblEvent INNER JOIN (tblDelegate INNER JOIN tblBookings ON tblDelegate.Id = tblBookings.Delegate_Id) ON tblEvent.Id = tblBookings.Event_Id GROUP BY tblEvent.Id, tblEvent.Event_Title, tblEvent.Event_Price;

- g

 

[gromit]

Sorry, should have mentioned...

Grouping by Event Id AND Event Title, should preserve the separate events . Even if they have the same title, the ID should separate the totals.

 

[danb]

Perfect, thanks very much!

 

[gromit]

You're welcome!