Adding records to a table using Action Queries 1

[BajanPOET]

I have a problem where I have a table that contains some data and another table with an identical structure and the same data as the first, with additions. (For example, if Table A has dog, cat and rat, Table B has dog, cat, rat, cow, horse and snake...)

My question is, how do I design a query that will add the missing records from the second table into the first table without giving me errors? My SQL query comes back with warnings about Key violations.

This is a copy of the SQL statement:

INSERT INTO defn_system ( system, system_desc, auto_fill_inventory, system_only, trade_shop_id )

IN 'C:\Documents and Settings\rgibson\desktop\AMMS\AMMS2006.mdb'

SELECT natinv.SYSTEM, natinv.DESC, natinv.AUTOFILL, natinv.SYSONLY, TRADE.TRADE

FROM natinv LEFT JOIN TRADE ON natinv.TRADE = TRADE.TRADE

WHERE (((natinv.SUBSYSTEM) Is Null Or (natinv.SUBSYSTEM)=''))

ORDER BY natinv.SYSTEM;

This code scans the NATINV table for any records that have an entry in the SYSTEM field but not one in the SUBSYSTEM field and outputs that record. I'm trying to seperate th SYSTEM records and put them in their own table (defn_system), but that table already has some of the records from NATINV. I'm trying to append Defn_system with the others.

GOD is in charge, though men may say, "Not so!

 

[MMSMSD]

BajanPOET,
I'm not the greatest at this, so sorry if I just create more confusion with my assistance. Two things that may help to resolve the issue:

1. trade_shop_id - If this or any other field is an autonumber field in either table, you probably want to make sure you don't copy that over, since it would most likely conflict. The table will create a new number for it when the record is added.
2. Your WHERE statement should probably include a "Field A (with dog, cat and rat) does not equal Field A (in the other table)" statment, so that it doesn't copy the same data over. If any of those fields are Key fields, then that would explain your error message.

Hope this is helpful.

Michael

 

[rjoubert]

I'm assuming you want to append the RECORDS in TableB that are not in TableA. So in your first example, RECORDS for cow, horse, and snake would need to be added.

INSERT INTO TableA (fields)
SELECT fields
FROM TableA RIGHT JOIN TableB ON TableA.index = TableB.index
WHERE (((TableA.index) Is Null));

Good Luck!