Display missing records?

  • Thread starter Thread starter Keith
  • Start date Start date
K

Keith

I have an employee enter sales information for each day for my business. At
the end of the month I get a report that shows just the total sales for the
month. I have found out that my employee has missed or forgotten to enter a
day or two every once in a while. Is there a way that I can run a query to
tell me which dates/records have not been entered?

As an example: Every day will be entered from 1/1/2001 to 12/31/2001 except
for a day or two here or there. Is there any way to run a query that will
show there is no record for 1/12/2001.......2/15/2001........5/7/2001......so
on and so forth? That way I know immediately which days I need to input.
(There is a date field for each record).

Thanks in advance!
Keith
 
On Mon, 22 Feb 2010 19:53:01 -0800, Keith

Yes. You would create a table with all dates of the year, and then run
an outer-join query looking for missing records. Or a "where not in"
query like this:
select * from myYearTable where myDate not in (select mySalesDate from
mySalesTable)
(of course you replace myObjectNames with yours)

-Tom.
Microsoft Access MVP
 
Create a table named CountNumber with field CountNUM with numbers 0 through 30.
Use the table in q query with your data table like this --
SELECT
DateAdd("d",[CountNumber].[CountNUM],DateAdd("m",-1,DateSerial(Year(Date()),Month(Date()),0))+1) AS LastMonthDates
FROM CountNumber
WHERE
(((DateAdd("d",[CountNumber].[CountNUM],DateAdd("m",-1,DateSerial(Year(Date()),Month(Date()),0))+1))
Between DateSerial(Year(Date()),Month(Date()),0) And
DateAdd("m",-1,DateSerial(Year(Date()),Month(Date()),0))+1));

MissingDates --
SELECT LastMonthDates.LastMonthDates AS MissingDates
FROM YourTable RIGHT JOIN LastMonthDates ON YourTable.SalesDate =
LastMonthDates.LastMonthDates
WHERE (((YourTable.SalesDate) Is Null));
 
Keith said:
I have an employee enter sales information for each day for my business.
At
the end of the month I get a report that shows just the total sales for
the
month. I have found out that my employee has missed or forgotten to enter
a
day or two every once in a while. Is there a way that I can run a query
to
tell me which dates/records have not been entered?

As an example: Every day will be entered from 1/1/2001 to 12/31/2001
except
for a day or two here or there. Is there any way to run a query that will
show there is no record for
1/12/2001.......2/15/2001........5/7/2001......so
on and so forth? That way I know immediately which days I need to input.
(There is a date field for each record).

Thanks in advance!
Keith
Click to expand...
 
Back
Top