Find missing Working Days

[Evi]

My database needs a record entering for every working day. A working day is
defined as every day from Monday to Friday. This includes Bank Holidays and
other holidays, so at least that bit isn't a problem
Can anyone think of a query that can check if any days are missing? eg one
week I didn't enter a record for Tuesday? I suspect the answer will have
something to do with DatePart but I can't figure out how to find the missing
numbers. ie the

I suppose that I could cycle through the record with VBA and print the
results into a table if a number from 1 to 5 is out of sequence but I wonder
if it could be done with a query.

It wouldn't matter if the query also returned the results for the week I ran
it, if the query was run half way through the week.

The datefield is called WkDate in TblWorkRecord.

Evi

 

[Dale Fye]

I keep a table (tbl_Numbers) handy for this kind of thing. It generally
contains one field (intNumbers) and the values of 0 through 9.

I then create a query (qryNumbers) that generates numbers from zero through
999 (you can easily change this to make the range 9999).

SELECT hundreds.intNumber * 100 +
Tens.intNumber * 10 +
Ones.intNumber as intNumber
FROM tbl_Numbers as Hundreds,
tbl_Numbers as Tens,
tbl_Numbers as Ones

To do what you are looking for, I would do something like the following.
The subquery generates a list of sequential dates between two dates you
provide (you can hard code these or use a parameter query). The rest just
joins this set of sequential dates to your table on the date column (using a
left join). The WHERE clause identifies the missing dates and whether the
day of the week is M - F:

SELECT M.SeqDates AS MissingDate
FROM (Select DateAdd("d", [intNumber], #4/1/08#) as SeqDates
FROM qry_Numbers
WHERE DateAdd("d", [intNumber], #4/1/08#) < #4/30/08#) AS M
LEFT JOIN tbl_SomeDates AS T ON M.SeqDates = T.SomeDate
WHERE T.SomeDate IS NULL
AND DatePart("w", M.SeqDates, 2) < 6
ORDER BY M.SeqDates

HTH
Dale

 

[Evi]

I love that idea of your Numbers table, especially as you only type in 10
numbers and then let the query do the rest.That's got so many uses. It works
really well in sifting out the missing dates.
Thanks Dale

I keep a table (tbl_Numbers) handy for this kind of thing. It generally
contains one field (intNumbers) and the values of 0 through 9.

I then create a query (qryNumbers) that generates numbers from zero through
999 (you can easily change this to make the range 9999).

SELECT hundreds.intNumber * 100 +
Tens.intNumber * 10 +
Ones.intNumber as intNumber
FROM tbl_Numbers as Hundreds,
tbl_Numbers as Tens,
tbl_Numbers as Ones

To do what you are looking for, I would do something like the following.
The subquery generates a list of sequential dates between two dates you
provide (you can hard code these or use a parameter query). The rest just
joins this set of sequential dates to your table on the date column (using a
left join). The WHERE clause identifies the missing dates and whether the
day of the week is M - F:

SELECT M.SeqDates AS MissingDate
FROM (Select DateAdd("d", [intNumber], #4/1/08#) as SeqDates
FROM qry_Numbers
WHERE DateAdd("d", [intNumber], #4/1/08#) < #4/30/08#) AS M
LEFT JOIN tbl_SomeDates AS T ON M.SeqDates = T.SomeDate
WHERE T.SomeDate IS NULL
AND DatePart("w", M.SeqDates, 2) < 6
ORDER BY M.SeqDates

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

My database needs a record entering for every working day. A working day is
defined as every day from Monday to Friday. This includes Bank Holidays and
other holidays, so at least that bit isn't a problem
Can anyone think of a query that can check if any days are missing? eg one
week I didn't enter a record for Tuesday? I suspect the answer will have
something to do with DatePart but I can't figure out how to find the missing
numbers. ie the

I suppose that I could cycle through the record with VBA and print the
results into a table if a number from 1 to 5 is out of sequence but I wonder
if it could be done with a query.

It wouldn't matter if the query also returned the results for the week I ran
it, if the query was run half way through the week.

The datefield is called WkDate in TblWorkRecord.

Evi