How do you compare fields between tables?

[nathanstevenson]

Hi there,

I have a temporary table with 28 fields, which need to be split up into 9 different tables. The fields are grouped together according to the 9 tables, including space for a primary key in this temporary table.

How do I:
1. Check to see if entries in col 1 and col 2 in the temp table match col 1 and col 2 in the existing table, and if they do match, place the primary key from the existing table into the temp table.
2. If the col1 and col2 do NOT match any entries in col1 and col2 in the existing table, then create a new primary key no, which is then placed in the temporary table.
3. Finally once all "keys" have been added to the temp table, append the new data to each table.

Specifically what I am stuck on is how to refer to cols and "cells" in Access, as well as different tables. Also I'm not sure how to check for the existance of a record, and if not to append it.

Any help would be highly appreciated!
Nathan

 

[DougP]

This is done using VBA code.

You can open a table and get all of the fields using the QueryDef.
There is a sample in the Help.

DougP, MCP

 

[nathanstevenson]

Hi Doug,

Should I post this question in a different forum?

I realise that this has to be done in VBA, it's how that's the question.

Thanks for your pointer, will look in the help file for QueryDef.

Nathan