Solved

MS Access - return list of dates with no record

Posted on 2009-05-16
2
213 Views
Last Modified: 2013-11-27
I have a SQL Server database with an Access front end.  The database is of event bookings.  I would like to design a query that lists Saturday and Sunday dates with no events booked.

The end object is to publish it to the web so I can create last minute deals.
0
Comment
Question by:les28
2 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 24404300
what you need is a calendar table, with (all) the dates.
once you have that (and believe me, that table WILL serve later also), you can fetch, from there, easily all the dates that are a saturday/sunday:
datepart(weekday, your_date_field) in (6,7)
the values 6 and 7 are if the DATE_FIRST setting is set to monday.

once you have that query, you just left join to your table.
"just" fill in your table and column names as fit:
SELECT cd.date_value
  FROM your_calendar_table cd
  LEFT JOIN your_boooking_table b
    ON b.booking_date = cd.date_value
 WHERE datepart(weekday, cd.date_value) in (6,7)
   AND b.booking_date IS NULL  ---- < this is the part that will make the query to return the dates with no bookings

Open in new window

0
 
LVL 1

Author Closing Comment

by:les28
ID: 31582284
Thanks very much.  I'll give it a try
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question