SQL wizards

[JethroUK©]

I'm quite good at queries, but i'm crap at agregate queries (counting) - I'm
trying to produce the simplest (i thought) list for the receptionist so she
doesn't overbook my classroom - the final list (my aim) includes just two
fields,

1 produces a list of future start dates from dates she has already booked
some people on to

2 produces a count of all clients already attending the class by session
(morning or afternoon) + all the ones she has booked to join it

e.g. :

Query1 Start Date CountOfStart Date
26/04/2006 11:30:00 18
12/04/2006 15:30:00 16
12/04/2006 11:30:00 16
12/04/2006 11:30:00 18



i have managed to produce the correct list - but only after creating 3
queries and basing each one on the previous one:

1/ list ('bookings') of all current clients + clients booked in to start

SELECT CLIENTS.[Start Date], CLIENTS.Session
FROM CLIENTS
WHERE CLIENTS.[Start Date]>Now() OR CLIENTS.[L1 Enrolled] Is Not Null AND
CLIENTS.[L1 Submitted] Is Null OR CLIENTS.[L2 Enrolled] Is Not Null AND
CLIENTS.[L2 Submitted] Is Null;

2/ count of clients ('count') attending + booked onto a session

SELECT Count(bookings.[Start Date]) AS [CountOfStart Date], bookings.Session
FROM bookings
GROUP BY bookings.Session;

3/ a list of all future booking dates + counts from previous query

SELECT DISTINCT bookings.[Start Date], count.[CountOfStart Date]
FROM bookings INNER JOIN [count] ON bookings.Session = count.Session
WHERE bookings.[Start Date]>Now()
ORDER BY bookings.[Start Date] DESC;


i suspect these could be condensed into one simply query - but i cant figure
it - any clues?

 

[[MVP] S.Clark]

Many times I take the approach that you have in that I'll create 2 or more
queries to get the job done. You can make queries with subqueries if you
like, but they're very difficult to debug.