Creating new records in access table based on records held in another

[Norris900]

ok this is my first post though i like to think i have learnt quite a bit from reading previous posts here.

I expect this is a foolish and simple question but i have found an answer yet. I have an imported table with a large number of records (20k) the problem being that a large number of the records contain identical data in most of the columns. When i break this table into smaller tables i am left with thousands of identical copies of the same data in a table.

My first thought was to use a query to find the new unique records from the thousands of duplicates and only add these to a table. This is where my problems begin as i can figure out how (or if you can) to add a refence to these now condenced and duplicate free records depending on what is there prior to removing the duplications

table1
uniqueData copies reference
1 A 1A
2 B 2B
3 B 2B
4 B 2B

table2
copies reference
A 1A
B 2B

basically i need help figuring out how to write an append query that will create the reference column in table1 when the data in the table1.copies column matches that in the table2.copies column.

Can it be automated in a query since i have thousands of records and doing it by hand would take more time than i have??

hope its clear as im a fool oh and im limited to using MS Access which can be a problem.

 

[Crowley16]

insert into ... select ... from table1.copies inner join table2.copies ...

--------------------
Procrastinate Now!

 

[Norris900]

Ok i am trying this but i haven't got it working properly yet (been busy)
Would i have to run this for each value in copies A then for B or is there a way to get it to cycle through a list creating the references all A's all B's then all C's and so on in one query.

 

[Norris900]

Sorry im not very clear after re-reading my posts hope this clarifies what i'm trying to do a little.

I have one table with all the imported data which i'm trying to split into several smaller tables to remove the duplication between records.
I can split the data without a problem and remove all duplication but i need to create a new ID for the split data so i can link the smaller tables together and keep the referential integrity after having removed all the duplicate records. Remove duplicates then reassign ID's to refer the records linked to the duplicates to the now unique record.

The Imported data is updated monthly so it needs to be an easerly repeatable query

 

[Norris900]

Dont worry i understand the answer above now, i cant believe i was being so dumb
Thanks a million Crowley16
all i need now is a little syntax