Finding Missing dates in a query

Leesha
5StarLounger
Posts: 1126
Joined: 05 Feb 2010, 22:25

Finding Missing dates in a query

Post by Leesha »

Hi,
I have a user who imports sales / day on a monthly basis. When there is a missing day(s) they compile average net sales for those days. Currently this is all done manually which is time consuming and open to human error.

Is is possible to create a query that will show the date(s) that are missing. For example, in June store 9 had net sales date for all days but 6/9. Is there a way to have a query populate only showing the missing day?
Thanks!
Leesha

User avatar
HansV
Administrator
Posts: 71983
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Finding Missing dates in a query

Post by HansV »

You'd have to create a table, named (for example) tblDates, with a single field of type date/time, named (for example) ADate.
Populate this field with all dates from, say, 1/1/2000 to 12/31/2029.
The easiest way to do this is to generate the list of dates in Excel, where you can create a series very easily, then copy/paste it into the table.

You can then create a query based on tblDates and your sales table, say tblSales:

SELECT tblDates.ADate
FROM tblDates LEFT JOIN tblSales ON tblDates.ADate = tblSales.SaleDate
WHERE tblSales.SaleDate Is Null;

SaleDate is the name of the date of sale in the imported table.
Regards,
Hans

Leesha
5StarLounger
Posts: 1126
Joined: 05 Feb 2010, 22:25

Re: Finding Missing dates in a query

Post by Leesha »

Thanks Hans!!

Leesha
5StarLounger
Posts: 1126
Joined: 05 Feb 2010, 22:25

Re: Finding Missing dates in a query

Post by Leesha »

Hi Hans,
I finally had a chance to get back to this. I was on vacation and actually didn't work!! I think that is a first.
I set up the table the way you described. When I join it to the sales table and add the is null nothing comes up. When I take out the is null only the dates that there are sales in the table show. I am trying to find the dates that are missing in the sales table for the date range I'm testing on. In this query, the only dates that come up (when I don't have the is null) are 6/1/21 to 6/14/21. I need it to show that 6/15/21 - 6/30/21 are missing data. If I put in the is null on dbo_tblSales nothing comes up. I put in the fiter for the Store_ID to make testing easier.

Thanks,
Leesha

SELECT tblDates.ADate, Dbo_tblSales.service_date, Dbo_tblSales.Store_ID
FROM tblDates LEFT JOIN Dbo_tblSales ON tblDates.ADate = Dbo_tblSales.service_date
WHERE (((tblDates.ADate) Between [forms]![frmSwitchboard]![txtStartDate] And [forms]![frmSwitchboard]![txtEndDate]) AND ((Dbo_tblSales.Store_ID)=35953));

User avatar
HansV
Administrator
Posts: 71983
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Finding Missing dates in a query

Post by HansV »

The condition (Dbo_tblSales.Store_ID)=35953 forces the query to return only existing records from Dbo_tblSales.

Does this doe what you want?

SELECT tblDates.ADate, Q.service_date, Q.Store_ID FROM tblDates LEFT JOIN (SELECT service_date, Store_ID FROM tblDates WHERE Store_ID=35953) AS Q ON tblDates.ADate = Q.service_date WHERE (tblDates.ADate Between [Forms]![frmSwitchboard]![txtStartDate] And [Forms]![FrmSwitchboard]![txtEndDate]) AND Q.service_date Is Null
Regards,
Hans

User avatar
HansV
Administrator
Posts: 71983
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Finding Missing dates in a query

Post by HansV »

I hope you had a lovely vacation, by the way!
Regards,
Hans

Leesha
5StarLounger
Posts: 1126
Joined: 05 Feb 2010, 22:25

Re: Finding Missing dates in a query

Post by Leesha »

I did have a great vacation and may never work on vacation again! It did me a world of good.

I tried the code you sent but it's prompting me for parameters for Service_date and store_id. I put those in but then it gives me an error about being too complicated. Was "Q" supposed to refer to the original query I sent you in my code?
Thanks,
Leesha

User avatar
HansV
Administrator
Posts: 71983
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Finding Missing dates in a query

Post by HansV »

My apologies - I made a mistake.

SELECT tblDates.ADate, Q.service_date, Q.Store_ID FROM tblDates LEFT JOIN (SELECT service_date, Store_ID FROM Dbo_tblSales WHERE Store_ID=35953) AS Q ON tblDates.ADate = Q.service_date WHERE (tblDates.ADate Between [Forms]![frmSwitchboard]![txtStartDate] And [Forms]![FrmSwitchboard]![txtEndDate]) AND Q.service_date Is Null
Regards,
Hans

Leesha
5StarLounger
Posts: 1126
Joined: 05 Feb 2010, 22:25

Re: Finding Missing dates in a query

Post by Leesha »

It not longer asks for parameters but it comes up and invalid data error. I'm confused as to what "Q" refers to or how it gets the data.

I should probably explain what I ultimately need to set up. I have a query that shows me which stores were not open for the total days in the month. I need to append the sales table (dbo_tblsales) with the missing days for each store that does not have the full compliment of days. I'm not sure if there is a way to show the store_id in the query you are helping me with or if I need to set up some type of loop process that will append the days for each stores with missing days.
Thanks!
Last edited by Leesha on 31 Jul 2021, 22:09, edited 1 time in total.

User avatar
HansV
Administrator
Posts: 71983
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Finding Missing dates in a query

Post by HansV »

I'm afraid it'll be difficult for me to help you without seeing (a copy of) the database...
Regards,
Hans

Leesha
5StarLounger
Posts: 1126
Joined: 05 Feb 2010, 22:25

Re: Finding Missing dates in a query

Post by Leesha »

It's too big to strip down so will see what I can come with.

Leesha
5StarLounger
Posts: 1126
Joined: 05 Feb 2010, 22:25

Re: Finding Missing dates in a query

Post by Leesha »

Here is a sample of the DB.

frmSwitchboard is where the date range is entered. 6/1/21 to 6/30/21.

qryEstimatedSalesMissingDays shows the stores that have less than the total days in June. These are stores that I’m trying to find which days are missing. In reality in the real table that could be hundreds of stores. qryFindMissingSalesDays is the query with the sql you gave me.

There are total of 3 stores in the sample. Store 39712 has the full 30 days of data. I put in one store with the total days to be able to test with. Please note 35953 is not in this sample. I was only using that to test with.

What I am trying to achieve (and probably going about it in the wrong way) is to have dbo_tblSales updated with the missing days data for 6/28/21. I need to have that date appended to dbo_tblSales and 438 needs to have 6/22, 6/23, 6/24, 6/25, 6/26, 6/27, 6/28, 6/29 and 6/30 appended to dbo_tblSales. Store 39712 would have nothing done.

Once this piece works I should be able to update the missing net_sales data with the average net sales/day.

I hope this makes sense!

Thanks,
Leesha
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 71983
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Finding Missing dates in a query

Post by HansV »

Thanks!

See the attached database.
I created the following queries:

qryStores returns a list of unique Store_IDs. If you already have a table or query for that, you can use that instead, of course.

qryAllStoresDates is based on qryStores and tblDates. It returns all possible combinations of a Store_ID and a date in the range specified on frmSwitchboard. So with 3 stores and 30 days, this query will return 3*30 = 90 records.

qryMissingSalesDates is based on qryAllStoresDates with a left join to dbo_tblSales on store and date. I returns the Store_IDs and dates in the specifed range that have no sales.

Finally, qryAppendMissingDates appends the records from qryMissingSalesDates to dbo_tblSales.

Hans73121.zip
You do not have the required permissions to view the files attached to this post.
Regards,
Hans

Leesha
5StarLounger
Posts: 1126
Joined: 05 Feb 2010, 22:25

Re: Finding Missing dates in a query

Post by Leesha »

OMG Hans! This is perfect! I so rarely set up queries without a join between then that it never occured to me to do that the way you did with qryAllStoreDates. I'm doing the Snoopy Dance of Joy around the house!
Thanks so much,
Alicia

User avatar
HansV
Administrator
Posts: 71983
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Finding Missing dates in a query

Post by HansV »

Snoopy.jpeg
You do not have the required permissions to view the files attached to this post.
Regards,
Hans

Leesha
5StarLounger
Posts: 1126
Joined: 05 Feb 2010, 22:25

Re: Finding Missing dates in a query

Post by Leesha »

The pic made my day! It's how I feel!
Have a great one!
Leesha