Query type

[Jeff]

I have 2 tables of student information, one for 2006 and one for 2007.
They both with the same primary key ( DOB and last name, which I know
is not perfect).

I would like to create a query to merge this data. By merge, I would
like to:

1) merge the data from the 2 tables when I find matching keys on both
tables.
2) leave the data from the 2006 as is if there is no record in 2007
3) add the 2007 if there is no record for 2006.

I think this would be a series of queries, but I'm at a loss at how to
begin.

Thanks

Jeff

 

[KARL DEWEY]

Just append, the primary key will not allow duplicates.

Then I suggest changing the table by adding an autonumber field to be used
as primary key. You can keep your unique two field index even if it is not
primary key.

 

[Jerry Whittle]

First make a backup of the database in case something goes badly wrong.

Next make a copy of the 2006 table. Make sure that this copy still has the
combo of the LastName and DOB fields as the primary key.

Create an append query based on the 2007 table and point it towards the new
table created above. Run it. Access should say something about records not
being added due to a key violation. That's the dupe records. Say OK and it
should add the 2007 records that aren't dupes.