I have a two tables A and B they have a date field.

TABLE A, has a date field (Short date) and for every day of year there are 4 to 6 data.
Ex:
[Date when bought Field] [Product field]
01 Jan 2008 Apple purchase
01 Jan 2008 Orange purchase
01 Jan 2008 Peanut purchase
01 Jan 2008 Apple purchase
01 Jan 2008 Orange purchase
02 Jan 2008 Apple purchase
02 Jan 2008 Orange purchase
02 Jan 2008 Peanut purchase
03 Jan 2008 Apple purchase
04 Jan 2008 Orange purchase
and on ... for every date of the year.


TABLE B, has a date field (Short date) and for every BUSINESS day (MON-FRI) of year there are 4 to 6 data.

Ex:
[Date when bought Field] [Product field]
01 Jan 2008 Apple purchase
01 Jan 2008 Orange purchase
01 Jan 2008 Peanut purchase
01 Jan 2008 Apple purchase
01 Jan 2008 Orange purchase
04 Jan 2008 Apple purchase
04 Jan 2008 Orange purchase
04 Jan 2008 Peanut purchase
05 Jan 2008 Apple purchase
05 Jan 2008 Orange purchase

and on ... for every BUSINESS DAY {from Monday to friday} of the year.

As those table are data imported from some txt file and this data is imported manually everyday; I have a feeling "because we human beings might do mistakes" that during the year I have might have skipped or forgot to import a file.

Now the problem is that I do not know when.
ex I have missed one day and for TABLE A let's suppose that there are no 2 January date transactions And for TABLE B I forgot date 4 January to import. If I do not import file for one day for that day will not be transactions at all.

I am wondering If it can be done a check to find if there are some missing dates? I tried to make some query but no result.
How can I find if I have missed any day ? It's a weird questin UH ?

this is only one idea. In excel, create a spreadsheet with one column with a header called DATE1. Fill the column with dates beginning with 1/1/2008. Import that file into Access into a table. File Get External Data Import. Create a query that joins the two tables on the date. Change the query to a LEFT join from an inner join on the Date only table. In this manner, you will see side by side where you are missing data in your current table. I suggested Excel because you can populate it very quickly and not have to do a lot of manual data entry.

Alan

Yeah thank you that's a manual solution but in fact there are two problems:

1. There is a lot of data and than this check has to be done for periods, let's say weekly.

2. Business days are Mon-Fri like 1 Jan to 5 jan and than 8 jan to 13 ect and cannot be found this way.

Anyway thanks for the suggestion.

Doable and quite easy too, just some query stuff... aircode but here goes.
Query 1: qryPrevDatesALL
SELECT BoughtDate, [BoughtDate]-1 AS PrevDate
FROM tblDatesALL;

Query2: qryMissingAllDays
SELECT PrevDate
FROM qryPrevDatesALL
LEFT JOIN tblDatesALL ON qryPrevDatesALL.PrevDate = tblDatesALL.BoughtDate
WHERE BoughtDate Is Null;

This will find all previous days... note this only works if and when there is only 1 day missing, if there are multiple days missing.... only the first day is reported...

Now business days is a lot harder... If you want ACTUAL business days, ie. no christmas and banking holidays search this forum for the 'proper' function to determain the business days. It is somewhere in the reference part of this forum.
If you only want weekdays...
qryPrevBusDatesALL:
SELECT BoughtDate, [BoughtDate]-IIf(Weekday([boughtdate],2)=1,3,1) AS PrevWeekDate
FROM tbtblDatesBus;

qryMissingWeekdays:
SELECT PrevWeekDate
FROM qryPrevBusDatesALL
LEFT JOIN tblDatesBus ON PrevWeekDate = tblDatesBus.BoughtDate
WHERE BoughtDate Is Null;

Again this works for 1 weekday missing any consequetive days missing will not be reported.

Offcourse you could expand on this and search for the NEXT day missing instead of the previous date missing. Then join that together to make a query that will show "missing from to"

Hope this helps

Greets

Thanks namliam,

I'll try this tomorrow and let u know.

Thank you for your reply I add your rep.

I'm thinking, maybe wrongly, that you are going to need to group by date before starting.

Brian

Yes brian you are right... you will want to "unify" the result either by a Distinct or group by in there someplace.

Yes yes, I did use Distinct from the beginning, it works great.
I'm not such a beginner, I am an advanced beginner :P

Thanks again.