Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Queries / June 2006

Tip: Looking for answers? Try searching our database.

return a list of dates when nothing happens

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
news.shaw.net - 30 Jun 2006 18:14 GMT
I have a database of events booked at three halls.

I'm trying to design a query that will return a list of Saturdays when
each hall is NOT booked.

What criteria should I use for the Date?

Thanks for your help....
MGFoster - 30 Jun 2006 18:49 GMT
> I have a database of events booked at three halls.
>
> I'm trying to design a query that will return a list of Saturdays when
> each hall is NOT booked.
>
> What criteria should I use for the Date?

Usually, you set up a calendar table of all Saturday dates in the range
you are interested in.  Then compare the booking table against the
calendar table.  Sorta like this:

CREATE TABLE calendar (saturday_date DATETIME NOT NULL);

[Just example dates - not actual Saturday dates]

INSERT INTO calendar VALUES (#1/1/2006#);
INSERT INTO calendar VALUES (#1/8/2006#);
INSERT INTO calendar VALUES (#1/15/2006#);
... etc. ...

Then the query looks like this:

SELECT c.saturday_date
FROM calendar As c LEFT JOIN booking as b
     ON c.saturday_date = b.book_date
WHERE b.book_date IS NULL

The LEFT JOIN combined w/ the WHERE clause produces all dates in the
Calendar table (Saturday dates) that are not in the Booking table.
Signature

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

news.shaw.net - 30 Jun 2006 23:03 GMT
Thank you so much.

You just saved me hours of reading.

Lester

>> I have a database of events booked at three halls.
>>
[quoted text clipped - 28 lines]
> The LEFT JOIN combined w/ the WHERE clause produces all dates in the
> Calendar table (Saturday dates) that are not in the Booking table.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.