How do I write a query in Access 2007 to display dates missing in sequence

Posted on 2009-02-23
Medium Priority
Last Modified: 2012-05-06
I have a table that gets updated every night when a store runs a report.  Sometimes the stores forget and that row of the table will not have that dates information.  So I guess I need a formula for example if 2/1/09 -2/21/09 rows are updated but missing 2/5 and 2/8  I need for the report to show me the dates 2/5 and 2/8 as being missing.  I know how to put the date range in the criteria but I don't know a formula for checking for missing dates in the sequence.
Question by:flaggkort
LVL 34

Accepted Solution

Mike Eghtebas earned 500 total points
ID: 23715227
make table tDates. This table has one field called DateVal (date/time). For now, populatet it with one worth of the data like

knowing that there is a missing data for an store. Later, this table will be automatically filed in. You don't have to do it manually.
Then make a query with an Outer Join and critria for days in your data table set to:
Is Null
This will show the missing dates. This is just a test for you to see if you like it. For now, also add store id for a particular store. If you make a query of your data table and post its SQL here I can help you wuth the new query we discussed above.
LVL 52

Expert Comment

by:Gustav Brock
ID: 23715305
Splendid article here about finding missing values in a sequence - also for dates:


LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 23729757

You can also use some pretty simple things to prevent missing values in the first place.
(Like checking for a missing value when you try to enter a new record)

Try this simple, basic example:


Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

840 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