Access SQL Subquery Problem

[dfenton21]

Hi All,



I have an unbelievably frustrating problem that I've been looking at for the last hour but sadly can't resolve.

The problem is with the first subquery below called BookedPlaces. The purpose of the entire code is to return a list of records where Bookings.Status = 'Request'. The BookedPlaces subquery adds another field to the query to show the total number of bookings for that session (i.e the second field: Bookings.SessionID)

I can't figure out how to get the subquery to count only the bookings where Bookings.SessionID = the SessionID of each record returned by the entire code. At the moment, it's counting every booking for every session (the number is the same for every record). Basically, the clause should be WHERE Bookings.SessionID = "the second field".

I hope I've been clear, but please let me know if you have any questions.

Any help would be greatly appreciated. I'm using Access 97 (unfortuately)

SELECT 

Bookings.BookingID,
Bookings.SessionID,  
Employees.StaffName, 
iif(Sessions.SessionCategory = 'Stand Alone', Sessions.SessionType & ' ' & 'Session', Sessions.SessionType & ' ' & 'Programme') AS SessionDescription, 
Sessions.SessionDate, 
(SELECT 
COUNT(BookingID) 
FROM Bookings 
WHERE Bookings.Status = 'Booked') AS BookedPlaces, 
Sessions.SessionPlaces-BookedPlaces as AvailablePlaces, 
Employees.StaffStartDate, 
(SELECT 
COUNT(SessionDate) 
FROM Sessions 
LEFT JOIN Bookings ON Sessions.SessionID = Bookings.SessionID 
WHERE Bookings.StaffID = Employees.StaffID AND Sessions.SessionType = 'Epos Session' AND Bookings.Attendence = 'Attended') As EposAttendence 

FROM 
Sessions 

INNER JOIN (Employees INNER JOIN Bookings ON Employees.StaffID = Bookings.StaffID) ON Sessions.SessionID = Bookings.SessionID 

WHERE 
Bookings.Status = 'Request' 

GROUP BY
Bookings.BookingID, 
Employees.StaffName, 
Sessions.SessionPlaces, 
Sessions.SessionCategory, 
Sessions.SessionType, 
Sessions.SessionDate, 
Employees.StaffStartDate,
Employees.StaffID,
Bookings.SessionID

[pbaldy]

I might join to a totals query, but try

(SELECT
COUNT(BookingID)
FROM Bookings AS Alias

WHERE Alias.Status = 'Booked' AND Alias.SessionID = Bookings.SessionID) AS BookedPlaces,

[rpeare]

Is there any chance you can attach a copy of your database with non sensitive information and what you want your output to look like?

It's hard to give you coding advice without knowing table structure etc.