Re: SQL wizards

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.


--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

"JethroUK©" <reply@xxxxxxxxx> wrote in message
news:gChYf.12144$NN4.10194@xxxxxxxxxxxxxxxxxxxxxxx
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: 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)
  • SQL wizards
    ... I'm quite good at queries, but i'm crap at agregate queries - I'm ... 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 figure ...
    (microsoft.public.access.queries)
  • 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: Clients - internal and external, how design and process?
    ... Embedding "hidden" information in a field is not good design practice as ... This you have one table to create queries from and you ... clients are people outside the company. ...
    (microsoft.public.access.tablesdbdesign)
Quantcast