Comparison between 2 tables

[jeremie_ingram]

I know this is going to sound simple, and will most likely kick myself later, but here goes.
I am creating a database that will track the history of certain transactions. The data will be imported on a daily basis into the tbl_CURR. I will then run a comparison to the data that was imported previously in tbl_DATA. What I am attempting to do is grab the records and create a documentible history based on various criteria (dates, owners, pass/fail). So the logic seems sound, compare yesterdays dload to todays dload, find the differences, then file them into a seperate table.
My problem is in extracting the unique records between the two tables. I have gone the route of a FIND UNMATCHED query, but the results are not what is expected and takes an extrodinary amt of time to process.
Any and all suggestion would be greatly appreciated.
Thanks

 

[dcx693]

The ease or difficulty of this will depend on exacty what differences you need to capture. but shouldn't be too hard.

Will both tables always have the same numbers of entries? Will the entries on one table always correspond to rows on another table? Exactly what info do you want to capture when you document the differences?

 

[jeremie_ingram]

Here is the basics,
I have a daily download of ALL records and need to make a comparison between that and the previous days download of all records. The record count will never be the same (it may, but rarley), and from the total record download I need to extract those that are different from the previous days. There are 3 fields which help uniquly identify each reacod (VENDOR, CLIENT, UNIT) and two fields that will change regularly (INSPSTAT, LSTINSPDTE).

If the two fields change for a record, I need to extract the information from Yesterdays table and put them into the HISTORY table. Once that is done, I need to overwrite Yesterdays table with Todays dload.

Generally speaking, the data is entered into the main dbase for out company on a daily basis. I take a dload containg everything needed each day. I try to catch the changes that were made by the data entry from the previous day. I can then use this data to generate statistical data reports that show in great detail the wht/whos/hows of the data.
I have tried the find unmatched, but it does not seem to work well for this situation.

 

[dcx693]

from the total record download I need to extract those that are different from the previous days

So what do you do if you get a record in today's download, but it wasn't in the previous day's? Is that considered a difference that you want to capture?

To capture changes for records that exist in both today's and yesterday's tables, use a query like this:

SELECT tblToday.Vendor, tblToday.Client, tblToday.Unit, tblYesterday.INSPSTAT, tblYesterday.LSTINSPDTE
FROM tblToday INNER JOIN tblYesterday ON (tblToday.Unit = tblYesterday.Unit) AND (tblToday.Client = tblYesterday.Client) AND (tblToday.Vendor = tblYesterday.Vendor)
WHERE (((tblYesterday.INSPSTAT)<>[tblToday].[INSPSTAT])) OR (((tblYesterday.LSTINSPDTE)<>[tblToday].[LSTINSPDTE]));


I've also attached a graphical picture of the query, since that's usually easier to visualize.

If that query works to pull the records that have changed between today and the previous day's download, then we can easily change it to an update query to write something to a history table and to update the main table.

 

[jeremie_ingram]

There is an issue of the possibility of orphan records between the new table and old table since new units can be added at any time. I was hoping to nail this down with a single query, but could go the route of fireing off multiple queries to get the job done. What I am attempting to do is extract the new data from the new (daily) information, putting to the history table, then overwriting the previous days data with the current days data (in preperation for the next session).

So, I will have to detect and extract both records that have no match in the previous days table, and those with new/different last inspection dates or status.

Thos will be the easy part, after I get this functioning properly its off to create report after report that will display this data is many many different ways.

 

[Pat Hartman]

Three queries would be best. One to find changes in the common records. The second to find added records. And the third to find deleted records.

I don't know what your souce for these tables is but many RDBMS applications automatically log changes. You can use these logs for reporting rather than replicating the process.