Solved

Show only records that are from yesterday in a daily query

Posted on 2004-09-29
13
589 Views
Last Modified: 2012-06-21
There is a query that runs daily to fetch certain records from a linked table and appends them to a cumulative table.  The query runs to find all records for the 29th (today) and appends them to a table that has been queried for the entire month.  Unfortunately, sometimes today's query will contain some of yesterday's records.  For example, if you ran the cumulative query for the 29th (includes all records from the 1st to the 29th) and then just the daily query for the 29th (supposed to show only records for the 29th) you will see some records from the 28th.  These records from the 28th didn't show up on the cumulative query for the 28th but showed up overnight when the query is run in the morning for the 29th.  (Don't know why! It runs on a linked table that we have no control over).  How would I make just a simple query that shows all the records that were from yesterday but just didn't show up on yesterday's cumulative query: One that compares the cumulative query to the daily query. Is the unmatched query from the wizard right?
0
Comment
Question by:chrisamuel
  • 6
  • 6
13 Comments
 
LVL 12

Expert Comment

by:pique_tech
Comment Utility
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.
0
 

Author Comment

by:chrisamuel
Comment Utility
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?
0
 
LVL 12

Expert Comment

by:pique_tech
Comment Utility
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?
0
 

Author Comment

by:chrisamuel
Comment Utility
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?
0
 
LVL 12

Expert Comment

by:pique_tech
Comment Utility
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.
0
 
LVL 4

Expert Comment

by:leslynf
Comment Utility
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 :)
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:chrisamuel
Comment Utility
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.

0
 
LVL 12

Expert Comment

by:pique_tech
Comment Utility
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.
0
 

Author Comment

by:chrisamuel
Comment Utility
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?
0
 
LVL 12

Expert Comment

by:pique_tech
Comment Utility
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.
0
 

Author Comment

by:chrisamuel
Comment Utility
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));
0
 
LVL 12

Accepted Solution

by:
pique_tech earned 250 total points
Comment Utility
You don't need the tbl_ABCD_cumulative2 in the FROM clause, I think that's what's causing your problem.  Having it there gives you a CROSS JOIN or a CARTESIAN PRODUCT (pretty concise explanation here: http://www.devx.com/dbzone/Article/17403/0/page/6)

So your statement should just be:

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
WHERE (tbl_ABCD_daily.AppDt < Date() AND tbl_ABCD_daily.LoanNum  Not In (SELECT tbl_ABCD_cumulative2.LoanNum FROM tbl_ABCD_cumulative2));
0
 

Author Comment

by:chrisamuel
Comment Utility
Awesome! Thanks so much for putting up with me!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now