Solved find missing date gaps (1 Viewer)

theDBguy

I’m here to help
Staff member
Local time
Today, 16:52
Joined
Oct 29, 2018
Messages
21,358
oopss let me re attach the tables, sorry

query working on is Query 1
Hi. I hope I understood your question correctly. See the attached modified version of your db and open the query DBG_MissingDates to see which dates were missing from your table Main. You can also open the query DBG_AllDates to see all possible dates you could have had in the table Main.

Hope that helps...
 

Attachments

  • Daventry Digital Paperwork Archive - Copy.zip
    83.5 KB · Views: 114

murray83

Games Collector
Local time
Today, 23:52
Joined
Mar 31, 2017
Messages
728
Hi. I hope I understood your question correctly. See the attached modified version of your db and open the query DBG_MissingDates to see which dates were missing from your table Main. You can also open the query DBG_AllDates to see all possible dates you could have had in the table Main.

Hope that helps...

sir that is a very elegant solution, much better than mine, thank you and to all who put in there ideas
 

theDBguy

I’m here to help
Staff member
Local time
Today, 16:52
Joined
Oct 29, 2018
Messages
21,358
sir that is a very elegant solution, much better than mine, thank you and to all who put in there ideas
Hi. You're welcome. Since we can't know, ahead of time, what is the date range for your data, I thought it might be a better approach if you could dynamically create a dates table instead of manually creating one and then making sure to keep it updated. Cheers!
 

murray83

Games Collector
Local time
Today, 23:52
Joined
Mar 31, 2017
Messages
728
and it looks epic
 

Attachments

  • thanks.png
    thanks.png
    58.6 KB · Views: 108

murray83

Games Collector
Local time
Today, 23:52
Joined
Mar 31, 2017
Messages
728
Glad we could all assist. Good luck with your project.

i know solved but have one last question in this regard

could the query you made, be altered also to look at dates missing based on today's date

as I'm a bit behind in work and haven't been updating it on time, but still says missing only up to 21/6/20 but today's the 3/7/20 and i haven't added the 1st or 2nd day data or am i dreaming of such a thing
 

Attachments

  • Daventry Digital Paperwork Archive - Copy.accdb
    664 KB · Views: 111
  • any chance.png
    any chance.png
    75.4 KB · Views: 99

theDBguy

I’m here to help
Staff member
Local time
Today, 16:52
Joined
Oct 29, 2018
Messages
21,358
i know solved but have one last question in this regard

could the query you made, be altered also to look at dates missing based on today's date

as I'm a bit behind in work and haven't been updating it on time, but still says missing only up to 21/6/20 but today's the 3/7/20 and i haven't added the 1st or 2nd day data or am i dreaming of such a thing
Hi. Not a problem; but yet again, you didn't include the tables locally. No worries, I think all you have to do is change the query DBG_MainDates to this one.
SQL:
SELECT DateAdd("d",[ID],DMin("Dt","Main")) AS AllDates
FROM tblNumbers
WHERE DateAdd("d",[ID],DMin("Dt","Main")<=Date()
Give it a try and let us know what happens. If it doesn't work, please repost your db with local data. Cheers!
 

murray83

Games Collector
Local time
Today, 23:52
Joined
Mar 31, 2017
Messages
728
Hi. Not a problem; but yet again, you didn't include the tables locally. No worries, I think all you have to do is change the query DBG_MainDates to this one.
SQL:
SELECT DateAdd("d",[ID],DMin("Dt","Main")) AS AllDates
FROM tblNumbers
WHERE DateAdd("d",[ID],DMin("Dt","Main")<=Date()
Give it a try and let us know what happens. If it doesn't work, please repost your db with local data. Cheers!

Sorry for the lack of tables again, must remember to add them back if i split em.

but yes works a charm, again thanks :)
 

theDBguy

I’m here to help
Staff member
Local time
Today, 16:52
Joined
Oct 29, 2018
Messages
21,358
Sorry for the lack of tables again, must remember to add them back if i split em.

but yes works a charm, again thanks :)
Hi. Glad to hear it worked. Cheers!
 

murray83

Games Collector
Local time
Today, 23:52
Joined
Mar 31, 2017
Messages
728
last time i promise this time, but after i added the missing dates have been bugged by people cant you put what day that was IE was it a weekend if so that is fine if not then where is the data

so i have googled and looked at W3 and tech on the net and have tried and have almost got it but all days are Mondays, which isn't right by a long shot, what am i missing
 

Attachments

  • added.png
    added.png
    7.2 KB · Views: 97
  • result.png
    result.png
    11.8 KB · Views: 105

Gasman

Enthusiastic Amateur
Local time
Today, 23:52
Joined
Sep 21, 2011
Messages
14,048
Well your value is always 1?

 

murray83

Games Collector
Local time
Today, 23:52
Joined
Mar 31, 2017
Messages
728
Well your value is always 1?


have read that but i have the number for the day in different column and I try and reference it in the query i get func error see
 

Attachments

  • #func.png
    #func.png
    12.3 KB · Views: 95

Isaac

Lifelong Learner
Local time
Today, 16:52
Joined
Mar 14, 2017
Messages
8,738
Just to add on to Gasman's reply, so where you have 1 you should probably have [AllDates]
PS - I think the general operation and flow of the forum works better if you start new threads when you have new questions. One question (well-titled), per issue, creates a highly functional archive for posterity.

Edit - correcting myself...where you have 1 you should have something like Weekday([AllDates])
 

Gasman

Enthusiastic Amateur
Local time
Today, 23:52
Joined
Sep 21, 2011
Messages
14,048
have read that but i have the number for the day in different column and I try and reference it in the query i get func error see
So why not combine the functions?

In fact you can refer?

Code:
SELECT Transactions.TransactionDate, Weekday([TransactionDate],1) AS t1, WeekdayName([t1],True,2) AS Expr1
FROM Transactions;
 

murray83

Games Collector
Local time
Today, 23:52
Joined
Mar 31, 2017
Messages
728
found this

this great site and used this

Code:
  DayOfWeek:Format([YourDateField],"dddd")

and BAM
 

Attachments

  • BAM.png
    BAM.png
    28.9 KB · Views: 102

Isaac

Lifelong Learner
Local time
Today, 16:52
Joined
Mar 14, 2017
Messages
8,738
found this

this great site and used this

Code:
  DayOfWeek:Format([YourDateField],"dddd")

and BAM
That is cool.
Just remember you lose the optional arguments about start of week, etc. But if your wishes correspond to whatever the Format is doing, then great
 

CJ_London

Super Moderator
Staff member
Local time
Today, 23:52
Joined
Feb 19, 2013
Messages
16,553
I provided this solution recently which just looks at the data and does not need a table with numbers or dates. Appreciate it is not a list of each missing date but provides a potentially much shorter list of 'from - to'

Code:
SELECT S.MissingFrom, Min([SDate]-1) AS MissingTo
FROM (SELECT S.EDate+1 AS MissingFrom
FROM tblDates AS S LEFT JOIN tblDates AS E ON S.EDate+1= E.SDate
WHERE (((E.sDate) Is Null)))  AS S, tblDates
WHERE (((tblDates.SDate)>=[MissingFrom]))
GROUP BY S.MissingFrom;

which looks at a table with start and end dates and needed to find the gaps where a start date did not equal the previous record end date+1

If this requirement is based on a table with a single date field then simply change S.EDate to S.SDate in two places
Code:
SELECT S.MissingFrom, Min([SDate]-1) AS MissingTo
FROM (SELECT S.SDate+1 AS MissingFrom
FROM tblDates AS S LEFT JOIN tblDates AS E ON S.SDate+1= E.SDate
WHERE (((E.sDate) Is Null)))  AS S, tblDates
WHERE (((tblDates.SDate)>=[MissingFrom]))
GROUP BY S.MissingFrom;
 

Users who are viewing this thread

Top Bottom