Add new information to existing table

[kdoran]

I am using access 2000.

I have a table - tblFieldTag

FieldTagID - primary key
Manufacturer
ModelNumber

I am trying to add and or update this table with a new table given to me by another person. I want it to overwrite any manufacturer or model number currently in tblFieldTag. If the Field tag does not exist I would like it also to add it as well. I have tried to do this with a query but not successful at it yet.

Help,

Kelly

 

[GingerR]

pretend new table name = NewTable.
pretend new table fields = NewFieldTag, NewManuf and NewModel.
you will make two queries: one to update existing records, and one to add new ones.


make a query with both tables in there.
join them on the FieldTag fields.

bring down Manufacturer and ModelNumber from current table.
make this an update query.

in the UPDATETO row, under Manufacturer put

[NewTable]![NewManuf]

and in the UpdateTo row under ModelNumber, put

[NewTable]![NewModel]

that will get existing ones updated to new data.

then you'll write a new query to add new records that aren't already there.
make a new query. make it an append query. base it on your NewTable.
at the top of the screen, when you choose APPEND query, it will want you to choose the current table (cause that's what you're appending TO).
bring down the three fields.
in each column of the query grid, choose the proper field to APPEND TO. simply run this query. since you have a primary key in the current field, it will NOT add any records that have an ID that is currently in there. it may look like it is going to ("You are about to add 2000 records&quot but when you say OK, it will then say "Access cannot add 1500 records cause it violates a key" or something like that.

 

[randysmid]

Hi,
This can be done programmatically using code. Please check this thread to see if it is helpful: thread700-545004

In that thread, I showed how to read records from one table, and write to another one.

HTH,
Randy Smith
California Teachers Association

 

[GingerR]

i guess i thought this was a one-time process so some hack-job queries would do it.