Copy data from one table to another

[a7n9]

HI I've two tables.
Table 1: Original Table with many fields
Table 2: A new table with only two fields. (ID number and Maxapps)

I want to copy all the data from Table1 to Table2 for matching ID numbers using VBA. Can anyone please help me with that?

 

[Norie]

Do you actually need to use VBA?

Can't you just create a query that links the ID numbers?

 

[a7n9]

Thanks for your reply Norie. Yes, that can be done too. Can you tell me how to do the query please?

Thanks.

 

[tonnic]

Hi,

the easiest possibility is to to run an sql-query .

For Examlple:

Private sub insert_ID ()

dim tblOld as string 'original
dim tblNew as string 'new

tblOld="table1"
tblNew="table2"

docmd.runsql "Insert into " & tblNew & " (ID, Maxapps) Select ID, Maxapps from " & table1 & ";"

exit sub

The other possibilities are transferring the values really in VBA only (docmd-Object is more or less macro-programming .. )
Here you have to methods, one is to transfer all needed fields with DAO (Data Active Objects) or ADO (ActiveX Data Objects), but these ways are much more complicated ....

 

[a7n9]

Thanks for your reply Tonnic.

I think I didn't explain my question properly. The original table has duplicate records for [ID Number], the new table has unique [ID number]. Therfore, I want to match the [ID Number] from the new table to the original table and get the remaining fields from the original table for the unique [ID number]

 

[tonnic]

Okay .... that souldn't be the big Problem

Once more:

In table1 you have the duplicates, right?
In talbe2 you already have unique records?

So you want a query which shows you all records from table1 which are not in table2?

And the table1.[ID Number] is equal to the table2.[ID Number]?

 

[Norie]

Create a new query, add the two tables.

Drag the ID number from one table to the other to create a join, which will be represented by a thin black line.

Then right click on the this join and select join properties.

Here set it to show only records from the second table.

Then add the fields you want and goto datasheet view.

Now if all of that has worked and the query is giving you the results you want you could convert it into a make table query.