Show only records that are from yesterday in a daily query

I'm assuming that you have some way to tell the records from each other, such as a primary key or ID field, and a create date/time or some relevant date field.  If that is true, then it seems you want to find those records created yesterday that were not copied to the destination table.  You could use the Access query wizard, but I'm not sure how to tell you to make that work right.  What I'd suggest is something like this:
SELECT * FROM LinkedTable
WHERE CreateDateTime Between Date() - 1 And Date()
AND PrimaryKeyField NOT IN (SELECT PrimaryKeyField FROM LocalCumulativeRecords)

Of course, you'd have to substitute the right field names and table names into the pseudo-SQL above to get it to work right for you.

Let us know if this isn't clear and we can try to explain a little better.  If you don't have some way to tell the records from each other (such as a primary key or ID field), then it will be a little trickier to accomplish what you want.

There is a date field and there is a primary key field.  Would CreateDateTime be appropriate if there's already a date field?  Also, what's the best way to word the query if I wanted to quickly run it everyday?  If I ran the query above would I have to change the sql above everyday and insert the right dates or isn't there a way where a parameter box will pop up, I enter in the relevant dates for the day and it shows the "lost" records?

If there's already a date field that contains the date you're concerned about, then no, a create date time field wouldn't strictly be necessary.  Let's call your date field DateField.

I didn't realize you'd want the query to be dynamic, but making it so is not difficult:

SELECT * FROM LinkedTable
WHERE DateField Between [Enter Date to find missing records for] And [Enter Date to find missing records for] + 1
AND PrimaryKeyField NOT IN (SELECT PrimaryKeyField FROM LocalCumulativeRecords)

So let's say you run this query.  You'll be prompted for
   Enter Date to find missing records for:
and you enter 9/24/2004

Then the query will return all the records where DateField is 9/24/2004 but which are not already in the LocalCumulativeRecords table.

Does that help?

Almost....The records that didn't post from yesterday show up in the current daily query with yesterday's date.  They're not erroneously masked with today's date.  For example, instead of showing all of 9/29 in a single daily query, it will include some 9/28 records that didn't append to yesterday's cumulative query.  Also, do I include [Enter Date to find missing records for] exactly like that in the sql statement?

Ok, I think I understand, but that doesn't make sense to me.  What criteria are you using in your query that produces this behavior?  Can you post the SQL of the current daily query, and a little more detail about the structure of the data?  The only way I can imagine what your describing is if there already is a create date field in addition to the a posting date field.

One thing you need to remember is that using

between SomeDate and SomeOtherDate

is that it includes all records that match each date

Basically, from what I am reading in your question, you only want the ones from yesterday. If this is correct, you need only include Date()-1 in the criteria for the field you are checking the date on.

Otherwise you will get records for more than one date.

Leslyn :)

This query was made by someone else, but it's a simple one.  The problem with the dates I think is out of our control because the query runs off linked tables which we don't have access to.  I think it has something to do with the timing of when data is entered.  What I'm trying to do is make another query which just shows all the records with the dates that didn't post yesterday to the table.  Here's the sql for the daily append query:

INSERT INTO tbl_ABCD_daily ( LoanNum, Fico, PhaseCd, Status, AppDt, ABC )
SELECT AppStat.LoanNum AS Expr1, AppStat.Fico AS Expr2, AppStat.PhaseCd AS Expr3, AppStat.Status AS Expvbr4, AppStat.AppDt AS Expr5, AppStat.FSL AS Expr6
FROM AppStat, ATS_ABCD
WHERE ((([AppStat].[Fico])>=620) AND (([AppStat].[PhaseCd])<4) AND (([AppStat].[Status])<>"CAN") AND (([AppStat].[AppDt])=[Date]) AND (([AppStat].[ATS_Source])="ABCD"));

**AppStat.AppDt** is the date field.  The above query should produce all records for a single date entered in...(the current date).

Here's the sql for the cumulative query:

SELECT AppStat.LoanNum AS Expr1, AppStat.Fico AS Expr2, AppStat.PhaseCd AS Expr3, AppStat.Status AS Expr4, AppStat.AppDt AS Expr5, AppStat.FSL AS Expr6 INTO tbl_ABCD_cumulative
FROM AppStat, ATS_ABCD
WHERE ((([AppStat].[Fico])>=620) AND (([AppStat].[PhaseCd])<4) AND (([AppStat].[Status])<>"CAN") AND (([AppStat].[AppDt]) Between [Date1] And [Date2]) AND (([AppStat].[ATS_Source])="ABCD"));

So what happens is that the daily will (but not supposed to) show some of yesterday's records that didn't post to the daily query pulled yesterday.  (So some 9/28 records that didn't appear in the cumulative query from yesterday will show up in the daily 9/30 query run today, with the date 9/28).  I just need a query i can run everyday that says: these are the "late" 9/28 records that didn't post yesterday.

I use linked tables all over the place all the time and I never experience the kind of latency you're describing, so while I can't rule that out as your problem, it seems very unlikely.

I have experienced some minor latency occasionally with REPLICATED data, but not to the extent you're describing.

Is it possible to enter loan apps into your system without an app date, or to back-date them--i.e., can a person entering this data decide "I'm too busy to enter all these today, but I'll enter them tomorrow or the next day but give them the application date of when I actually received them"?  That might explain what you're seeing.  Or similarly, can the loan apps be entered without an app date, the the app date later be changed to some prior day?  That would also cause them to appear "missed" in you extraction process.  One last thought:  is everyone in the same time zone, and where is the app date set (i.e., on the client or at the server, by the database or the application).

I cannot think of a technology/database reason why you'd be experiencing what you are.  But I've actually encountered the process problems I just described.

I was also thinking that it might be a backdate issue, because of the timing of different workers is very erratic. But I'm not sure because I've just started with this project and don't have access to the people that are working with the tables or any other info.  But I definitely need a quick band-aid like the query mentioned above.  Would your first sql example work to show all the missed records or would I need to use Date-1?

If there should only be one record in the local data table (tbl_ABCD_daily  ?), and if LoanNum is unique, then there's a fairly straightforward fix but it's not very discerning:
SELECT *
FROM AppStat {I think this is your remote "source" table}
WHERE AppDate < Date() AND LoanNum Not In (SELECT LoanNum FROM tbl_ABCD_daily)

This would return all records from the source table with an application date before today that aren't already in the local table.  That might provide the immediate band-aid you need...

If there was some time in the past before which you didn't copy the records from the source to the destination, or you only cared about relatively recent app dates, then you could modify slightly:
SELECT *
FROM AppStat {I think this is your remote "source" table}
WHERE AppDate Between #1/1/2004# AND Date()-1 AND LoanNum Not In (SELECT LoanNum FROM tbl_ABCD_daily)

But you're right, to really solve the problem, you'll have to understand the application logic and the business process.

I tried out the sql statement like below and it's doing the weirdest thing.  It does show the four missing records but it seems like it keeps looping.  It shows them continuously.  The four records just keep repeating themself in the table the query produces.  And when you try to go to the last record, Access just seems to hang and I have to CTRL ALT DEL to get out.  What's going on?

SELECT tbl_ABCD_daily.LoanNum, tbl_ABCD_daily.Fico, tbl_ABCD_daily.PhaseCd, tbl_ABCD_daily.Status, tbl_ABCD_daily.AppDt, tbl_ABCD_daily.FSL
FROM tbl_ABCD_daily, tbl_ABCD_cumulative2
WHERE (tbl_ABCD_daily.AppDt < Date() AND tbl_ABCD_daily.LoanNum  Not In (SELECT tbl_ABCD_cumulative2.LoanNum FROM tbl_ABCD_cumulative2));

Awesome! Thanks so much for putting up with me!