Update Query or Append Query

[The Grim Discovery]

Hello Access People

Wondering if there was any quick and dirty advice you can give an Access noob.

I've created two basic tables A and B. They've each got 18 sets of data linked by a primary key.
I've created an update query so that Table B matches up with any changes I make to Table A. It's great... But when I add a 19th row of data to Table A and then run the update query it doesn't show in Table B. I've tried using an append query instead but this tells me there are 18 (not 19) Key Violations.

If nothing else could you tell me which query type I should be using to get this 19th row of data from Table A to Table B.

Thanks in advance

 

[Joe4]

I've tried using an append query instead but this tells me there are 18 (not 19) Key Violations.

If you are trying to do an Append Query, you should only be adding the one new record. It sounds like you have it set to try to add ALL 19 records.
You should do an Unmatched Query first between the two tables to identify the new record, and then build the Append Query off of that, so it is only adding the one new record.

Taking a step back, your data structure sounds a little odd to me (the needed interaction and behaviot between tables). It could be indicative of a design flaw.
Also note that you can set up Relationships bertween your two tables with Cascading Updates and Deletes (see: https://support.office.com/en-gb/ar...ionships-30446197-4fbe-457b-b992-2f6fb812b58f).

 

[The Grim Discovery]

Joe - thanks for your guidance - just the sort of thing I was after.

 

[Joe4]

You are welcome.
Glad I was able to help.

 

[The Grim Discovery]

hi
Is this the correct process for getting tblB to match up with tblA?
　
Run unmatched data query (between tblA and tblB).
Turn unmatched query into tblC.
Append tblC to tblB.

I ask because it's not working.
bw

 

[Joe4]

You don't need the intermediate step. You should be able to just:
1. Create an unmatched query that returns all records in tblA that are not in tblB
2. Convert that unmatched query from step 1 above into an Append Query, electing to add the new records to tblB
(make sure you correctly identify which fields you want to add, and what other fields you may need to hard-code to tblB).
3. Run the append query just created

So all you will have when finished is one new Append Query.

If it is not adding the new records, please do the following:
1. Tell us what is happening (any error messages? if so, what do they say?)
2. Post the SQL code for your Append Query (switch the query to SQL View and copy and paste the code here)
3. Provide some small samples of data from tblA and tblB
4. Let us know what you Primary Key field is in tblB

 

[The Grim Discovery]

I'll give that a go today Joe. Thanks for your sharing your knowledge and time. It's proving invaluable in what is a huge learning experience for me.

 

[The Grim Discovery]

Well, that nailed it which feels like a great success. Cheers. The only catch I can see with following steps 1 to 3 is that subsequent runs of the query duplicates the data that transfers from Table A to Table B. I'm trying to work this out but I'd appreciate any advice.

 

[stumac]

You should be able to do this all in one query, by excluding the records that already exist in table 2 within your query it will mean subsequent runs will not duplicate data.

[COLOR=#006400]INSERT INTO Table2 ( ID, MyField )[/COLOR] [COLOR=#0000cd]SELECT ID, MyField FROM Table1[/COLOR] [COLOR=#0000cd]WHERE[/COLOR] [COLOR=#0000ff]ID [/COLOR]Not In ([COLOR=#006400]SELECT ID FROM Table2[/COLOR])

in the above anything in Blue is referring to table 1 and anything in Green is table 2.

 

[Joe4]

The only catch I can see with following steps 1 to 3 is that subsequent runs of the query duplicates the data that transfers from Table A to Table B. I'm trying to work this out but I'd appreciate any advice.

It shouldn't, because the Append Query has the Unmatched Query built in (you should only have one query when you are done). So every time it runs, it should only add the new records. If they unmatched portion of the query is written correctly, it should be impossible to add/create duplicates.

If you are having trouble figuring it out, post the SQL code for your query (swtich to SQL View and copy and paste the code here), and include small samples of the data from each query, and show us an example where things are being duplicated.