SQL wizards

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?


.

Relevant Pages

  • Re: MySQL Connections buendeln
    ... Slow query log zeigt die ueblichen Queries wo halt viele dort drin stehen ... ich werd' das mal reduzieren um die Load lieber auf die Clients ...
    (de.comp.datenbanken.mysql)
  • Re: How To Steamline My Queries
    ... Just select a month in the listbox, and the query will "read" ... > different queries which return anniversary dates. ... > FROM Clients ...
    (microsoft.public.access.formscoding)
  • Re: SQL wizards
    ... You can make queries with subqueries if you ... produces a count of all clients already attending the class by session ... FROM bookings INNER JOIN ON bookings.Session = count.Session ... i suspect these could be condensed into one simply query - but i cant ...
    (microsoft.public.access.queries)
  • RE: Double Joins
    ... Have one query called qryClient with the SQL Statement: ... Then add both of those queries into another query called "qryFinalCall". ... clients table (clients and consignees are stored in the clients ... both the client and the consignee using a single query. ...
    (microsoft.public.access.queries)
  • Re: Cross tab query construction with Subqueries
    ... Perhaps the single Crosstab Query is more efficient than several nested Select Queries, but to determine how much so you'd probably need to run your own tests. ... So I attached it to [Bookings], since it made no sense to me to have 3 [Revenue] records on a line in the datasheet and only one of the attached fields. ...
    (microsoft.public.access.queries)
Quantcast