Query to fill in missing dates (1 Viewer)

jpierc · Jan 31, 2016

This is probably easy to do, but I am not smart enough to figure it out.

I have to create a time-tracking database for my company. For part of this, I need to create a report showing days worked and not worked. The supervisor will enter a starting date and an ending date. I have a table that contains the dates and times worked, but I need to show those days where the employee did not work on this same report. So assume John Doe worked the following schedule:

2016/1/10 - In: 7:55 ' First clock-in of the day
2016/1/10 - Out: 11:30 ' Went to lunch
2016/1/10 - In: 12:10 ' Back from lunch
2016/1/10 - Out: 16:30 ' Done for the day
2016/1/12 - In: 8:03 ' Clocked in for the day, did not work 2016/1/11
2016/1/12 - Out: 17:30 ' Clocked out - no lunch

I would like the report to show 2016/1/11 like this:

2016/1/10 7:55 11:30 12:10 16:30
2016/1/11
2016/1/12 8:03 17:30

I have the report formatted as I want. I have the query/VBA code that will create the single line for each day. The report works except for the days where the employee does not clock any time. I can't figure out how to make the missing day show up on the report. Any help would be greatly appreciated.

Thanks
James

CJ_London · Jan 31, 2016

databases cannot create data out of thin air - in this case the missing dates. So you need a datasource which lists all dates, left join it to your query and use the date from that data source.

What that data source is, is up to you. It may be your existing table has at least one record for every date, just not for this employee (in which case you could use something like 'select distinct mydate from mytable') or another table based around your scheduling although probably not a good idea for large data sets, or the fallback which it to have a separate table which is just a list of dates - but this will need to be maintained

jpierc · Jan 31, 2016

CJ_London,
That makes sense. I guess I'll create a table of some form and use it to handle all the missing dates. I hate doing it this way because now I've got a never-ending maintenance job, but at least the system will work like I want and I can come up with some auto-update routine on the date table to keep it current.

Come Monday, I'll get this fixed.

Thanks again,
James

arnelgp · Feb 1, 2016

this is an example of cartesian by product.

create 3 tables:
tblDay, fields: DayNo (Number, Byte)
tblMonth, fields: MonthNo (Number,. Byte)
tblYear, field: YearNo (Number, Integer)

fill tblDay with values 1-31
fill tblMonth with month numbers 1-12
fill tblYear with years past, present, and future, ie: 2015, 2016, 2017, 2018, etc

now create query from this 3 tables, say qryCartesian

SELECT DateSerial([YearNo],[MonthNo],[DayNo]) AS Dates
FROM tblDays, tblMonths, tblYears
WHERE IsDate([MonthNo] & "/" & [DayNo] & "/" & [YearNo]) AND (DateSerial([YearnNo], [MonthNo], [DayNo]) BETWEEN begDate AND endDate)
AND DateSerial([YearNo], [MonthNo], [DayNo]) NOT IN (SELECT DISTINCT [dateField] FROM yourTable)
ORDER BY 1;

create another query (this is the one we will use in your report).

SELECT [field1] from yourFirstQuery
UNION
SELECT [Dates] FROM qryCartesian

CJ_London · Feb 1, 2016

not seen that way of creating a list of dates - seems pretty straight forward.

jpierc · Feb 1, 2016

Arnelgp

That is a pretty impressive way to solve the problem. Thanks for the additional view. I would have never thought to do it that way (obviously).

I'll put this to work for me today.

Thanks,
James

CJ_London · Feb 1, 2016

@jpierc

Having created your date list, I would still left join the Cartesian query to your data rather than use a union query to 'fill in the blanks' and still needs to be left joined to your 'main' query. So your cartesian query would just be

SELECT DateSerial([YearNo],[MonthNo],[DayNo]) AS Dates
FROM tblDays, tblMonths, tblYears
WHERE IsDate([MonthNo] & "/" & [DayNo] & "/" & [YearNo]) AND (DateSerial([YearnNo], [MonthNo], [DayNo]) BETWEEN begDate AND endDate)

You may still want to order it, or may be faster to order further down the query chain.

CJ_London · Feb 1, 2016

Jus tried Arnelgp's solution - one change and a suggestion

1. Dateserial requires text parameters, so your fields need to be text rather than numeric

2. Alternative to using the isdate function is to just use SELECT DISTINCT

jpierc · Feb 1, 2016

CJ_London and arnelgp,
It seems the two of you are working harder to solve this than I am. I do appreciate the suggestions and all the ideas. This community is much better and more responsive than I could have hoped.

Thanks to both of you. You've saved me a lot of time doing stupid, repetitive work with these suggestions.

James

Gasman · Jul 17, 2016

Thank you Arnelgp,

I was about to post a question on how I could fill a table with a years dates (or a date range) via a query, and your code has shown me how to do that.

Galaxiom · Jul 18, 2016

CJ_London said:
Dateserial requires text parameters, so your fields need to be text rather than numeric.

DateSerial() arguments are Integer.

https://msdn.microsoft.com/en-us/library/office/gg264202.aspx

CJ_London · Jul 18, 2016

I stand corrected

Query to fill in missing dates (1 Viewer)

jpierc

New member

CJ_London

Super Moderator

jpierc

New member

arnelgp

..forever waiting... waiting for jellybean!

CJ_London

Super Moderator

jpierc

New member

CJ_London

Super Moderator

CJ_London

Super Moderator

jpierc

New member

Gasman

Enthusiastic Amateur

Galaxiom

Super Moderator

CJ_London

Super Moderator

Similar threads

Users who are viewing this thread