RE: Find missing Working Days

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.



"Evi" wrote:

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



.

Relevant Pages

  • Re: Query to find amissing number
    ... Here I need to write a query to find out that number 7 is missing in the ... given sequence. ... giving you a quick way to determine if any gaps exist. ...
    (comp.databases.sybase)
  • Re: find missing numbers
    ... This will report the first missing number of each sequence. ... similar subquery to find the high end of each missing sequence. ... For a complete query in my response, provide your table name and column ...
    (microsoft.public.access.queries)
  • Re: finds gaps in sequential numbers?
    ... I too am looking for a way to determine missing numbers in a sequence. ... "Tom Ellison" wrote: ... This allows the query to look at the ...
    (microsoft.public.access.queries)
  • Re: Query to find a missing number
    ... Here I need to write a query to find out that number 7 is missing in the ... given sequence. ... from tab1 b ...
    (comp.databases.ms-sqlserver)
  • Re: Find missing Working Days
    ... numbers and then let the query do the rest.That's got so many uses. ... contains one field (intNumbers) and the values of 0 through 9. ... Can anyone think of a query that can check if any days are missing? ... results into a table if a number from 1 to 5 is out of sequence but I ...
    (microsoft.public.access.queries)