Compare two similar tables

[ggodwin]

Hey folks I am getting started on this project and I am very new to Access. The database that I want to make in Access is one that I have done in Excell. However, it is not a user friendly format and needs to be converted over to Access.

One of the main road blocks for me is comparing two similar tables.

Each day I will do a download from a Customer website. However, the customer data is clean but does not tell me when something has changed.

I need to learn the varios methods and approaches to compare two tables so that I can determine a best practice for this application.

Please direct me in some directions that you have used in the past.

KEYPOINTS:
» I need to identify NEW records and DELETED RECORDS.
» I also need to be able to analyze existing records for fields that may have changed.

 

[Premy]

Well normally your FTP software can tell u that (presuming u have FTP access to the customer data).

HTH

 

[ggodwin]

This customer web portal actually has a download function that dumps the data into an excel file.

 

[The_Doc_Man]

This is a messy problem.

You have a table in the database that is a reference table of some sort. This spreadsheet contains some or all fields in common with the reference table. You want to know in detail what has changed.

To make this work in your lifetime (much less daily) depends on some important factors. The biggie that will instantly be make or break is whether the inbound spreadsheet has a field that could be used as a primary key (PK) if you imported it into Access. If the answer is NO, you should give up now and report that the problem is insoluble.

OK, assuming you have a candidate for PK, this is what I would do.

First, import the spreadsheet to a temporary table. For discussion, let's call the reference table "tblR" and the new daily import from a spreadsheet "tblS." To make your life easier later, assure that the field names in tblS are similar to the names in tblR but (perhaps) you add one letter to the field names in tblS. So if you have a field in tblR called SeqNo, have a corresponding field called SSeqNo in tblS.

Go look up the "NOT IN" operator. Your NEW and DELETED records will depend on it.

First, you want to write a query that tells you every record in tblR for which the corresponding PK is NOT IN tblS. That gives you deleted records.

Then, you want to write a query that tells you every record in tblS for which the corresponding PK is NOT IN tblR. That gives you new records.

Now, the next item depends on a lot of factors. If you don't have too many fields, you can write a query to do an INNER JOIN as tblS JOIN tblR on {the PK field}. OK, from this query you can see every field in both tables.

In the simplest case, you would write a query with a bunch of IIFs such as

..., IIF( tblR.SeqNo = tblS.SSeqNo, " ", "X" ) As SeqNo, ...

In this case, if you then built a report based on this query, you would see an X in the report for every column that changed.

If you need more details than this, you need to be more specific as to exactly what you wanted to see. But given what I've shown you above, you should be able to figure that out for yourself, at least partly.

 

[ggodwin]

Doc man you and I are on the same page. I think these thread is dead because after a few checks I have determined that there is no true PK.

The data I thought would be a good PK candidate is not. I have to create PK before I can advance this discussion.

See this link.

http://www.access-programmers.co.uk/forums/showthread.php?t=161085

 

[WayneRyan]

g,

Doc is right, you need a primary key:

Deletions:

Select O.PK
From   OldTable As O Left Join NewTable As On
       O.PK = N.PK
Where  N.PK Is Null

Additions:

Select N.PK
From   NewTable As O Left Join OldTable As On
       N.PK = O.PK
Where  O.PK Is Null

Modifications:

Select N.PK
From   NewTable As O Inner Join OldTable As On
       N.PK = O.PK
Where  N.Field1 <> N.Field1 Or
       N.Field2 <> N.Field2 Or
       N.Field3 <> N.Field3 ... <-- Add all non PK columns here

I have a very rough sample here:

http://www.access-programmers.co.uk/forums/showthread.php?t=127628

Wayne

 

[ggodwin]

OK, So I got the Primary key all cleared up. The data looks good and there are no duplicates.

My first step is to Identify the new records and deleted records.

Wayne,
In your examples above what kind of query are these?

 

[WayneRyan]

g,

Sorry for the non-response, but I've been on vacation for a week. No TV,
Internet or even newspapers.

The queries are just select queries, but they could easily be altered to
Insert data into tables.

Need more information,

Wayne