Synchronizing or comparing two tables

[bonusmarch]

I am in serious trouble. Please help.

I am working on a database that needs to compare two tables. Table 1 is a repository table that contains all the permanent records. Table 2 contains some records. The two tables should have virtually the same datatype, field width, and etc.

Eg:

Table 1 fields: FirstName, LastName, AreaNo...
Table 2 fields: Firstname, Lastname, AreaNo..

My question is, am I able to take each of the record from table 2 and compare it with table 1 records. If there is a match, access will perform a certain task (like display it on a report with matching records). I wonder if there are any vb coding out there that can accomplish such task. Or is there any SQl statements I could implement in Access that could perform a similiar task.

thanx
Alex

 

[Maquis]

Do these tables have primary keys? If so you should just be able to create a query which can find the records which are in both tables. (Use the find duplicates query wizard if you are not comfortable with creating your own queries).

Is table 2 a temporary table or a permanent part of your database? If it is a permanent table then you should really get rid of it if it only holds the same data as table 1. You could add the table 2 records to table 1 and add an extra field to table 1 which would distinquish the records.

Hope this helps some. Maq B-)
<insert witty signature here>

 

[tempclerk]

Not sure the Find Duplicates query wizard will work on two separate tables without a Union query (which is pretty simple if your fields are already named the same - Select * from table 1, Union Select * from table 2), but the Find Unmatched query wizard will find the records in table 2 that aren't already in table 1, which is more useful anyway if your object is to add the new ones to table 1.

 

[tlbroadbent]

You can easily find records that are exactly the same based on the columns you choose to compare by creating an INNER JOIN query.

The following query will list all records on table2 that exactly match table1 on the columns in the JOIN criteria.

Select table2.*
From table2 Inner Join table1
On table2.FirstName=table1.FirstName
And table2.LastName=table1.LastName
And table2.AreaNo=table1.AreaNo Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[bonusmarch]

Thanks for all the tips. I will give them a try.

This is what being done to my database.

Table 1 contains all the repository(permanent records).
Table 2 is a table that imports all the data from a text file. This text file will change every month, so I need to program it to look for changes everyone matching w/ table 1. Recall that the two tables have the same datatype, field width, and etc. Will it be able to detect the duplicate records with this scenario. Can I also select the fields that will be used for comparison? After it found a match, it will create a report showing all the duplicate records. And when a duplicate record is found, it will add a note to a field on table 1 (e.g. Table 2 found 3 identical records, it will add asterisks &quot;*&quot; in table 2 under field name update.

thanks for you help, you guys are a life saver. I need to get this program done by the end of the month.