Append Query with Criteria from Linked Excel Tables

[Kemidan2014]

All examples I have seen on the internet handle brand new data where existing table does not have any of the same information as the Excel data they want to import

When we Download our Data from customer to update new complaints we get an excel file with all the old + the new
My current table has 1156 records, the new Linked table has 1164 records. in my mind an append query should only append 8 rows but its telling me it wants to update 1164 records. I aborted because i do not want to clean up that mess if theres duplicates.

Field 1 called SerialNum is imported value that is serialized and never replicated.
What expression can i Add to the Criteria in this Feild of the Append query so that only NEW Rows of Data are filled into for NEW serial numbers?

After this procedure i plan to also run an UPDATE query so that certain values that we know change over time when we down load will be updated to for all previous records that have had change in status.

if there is a combined method to do this That would also be beneficial.

 

[Micron]

Set the unique value fields in the table (or create a compound index using multiple fields). If you manually attempt to run your query it should run but you'll get a long message regarding not adding some records due to field violations. That you can ignore. Or you can write code to suppress that message. In either case, the records that can be added will be and the rest will be discarded.

Another way is to create an Upsert query (research ms access upsert query) if your tables are suited to that. This would append new and update the existing at the same time.

What expression can i Add to the Criteria in this Feild of the Append query so that only NEW Rows of Data are filled into for NEW serial numbers?

Impossible to tell without seeing and understanding the data to be able to identify what makes anything unique. You are probably the best judge of that.

 

[Kemidan2014]

I am going to attempt Both for my own education. and maybe is my sheer lack of skill with SQL, but I tried this already


Old SQL of Append Query

INSERT INTO MainData ( [QIMS#], DocType, Rank, SupplierCode, PartName, PartNumber, OverallStatus, NCDDescription, QEName, Model, LTCMPlanDue, OriginalLTCMActualDueDate, RevisedLTCMActualDueDate, [Close], InitialIssuanceDate, OfficialIssuanceDate, LTCMPlanSubmitted, LTCMPlanRejectionDate, LTCMActualSubmitted, LTCMActualAcceptedDate, LTCMActualRejectionDate, NAMC, Standard, Actual )
SELECT LinkTableData.[QIMS#], LinkTableData.[Doc Type], LinkTableData.Rank, LinkTableData.[Supplier Code], LinkTableData.[Part Name], LinkTableData.[Part Number], LinkTableData.[Overall Status], LinkTableData.[NCD Description], LinkTableData.QEName, LinkTableData.Model, LinkTableData.[LT CM Plan Due], LinkTableData.[Original LTCM Actual Due Date], LinkTableData.[Revised LTCM Actual Due Date], LinkTableData.Close, LinkTableData.[Initial Issuance Date], LinkTableData.[Official Issuance Date], LinkTableData.[LTCM Plan Submitted], LinkTableData.[LTCM Plan Rejection Date], LinkTableData.[LTCM Actual Submitted], LinkTableData.[LTCM Actual Accepted Date], LinkTableData.LTCMActualRejectionDate, LinkTableData.NAMC, LinkTableData.Standard, LinkTableData.Actual
FROM LinkTableData;

INSERT INTO MainData ( [QIMS#], DocType, Rank, SupplierCode, PartName, PartNumber, OverallStatus, NCDDescription, QEName, Model, LTCMPlanDue, OriginalLTCMActualDueDate, RevisedLTCMActualDueDate, [Close], InitialIssuanceDate, OfficialIssuanceDate, LTCMPlanSubmitted, LTCMPlanRejectionDate, LTCMActualSubmitted, LTCMActualAcceptedDate, LTCMActualRejectionDate, NAMC, Standard, Actual )
SELECT DISTINCTROW LinkTableData.[QIMS#], LinkTableData.[Doc Type], LinkTableData.Rank, LinkTableData.[Supplier Code], LinkTableData.[Part Name], LinkTableData.[Part Number], LinkTableData.[Overall Status], LinkTableData.[NCD Description], LinkTableData.QEName, LinkTableData.Model, LinkTableData.[LT CM Plan Due], LinkTableData.[Original LTCM Actual Due Date], LinkTableData.[Revised LTCM Actual Due Date], LinkTableData.Close, LinkTableData.[Initial Issuance Date], LinkTableData.[Official Issuance Date], LinkTableData.[LTCM Plan Submitted], LinkTableData.[LTCM Plan Rejection Date], LinkTableData.[LTCM Actual Submitted], LinkTableData.[LTCM Actual Accepted Date], LinkTableData.LTCMActualRejectionDate, LinkTableData.NAMC, LinkTableData.Standard, LinkTableData.Actual
FROM LinkTableData;

and i did not recieve the error message you were describing only the same "you are about to append 1164 rows, do you want to continue?"

 

[Kemidan2014]

Update I Took your advice to research Upsert query and THAT worked. however Testing it with a back up copy of my main data base the ID numbers (autonumber field) increased consecutively but when i made the corrections to the same query because i backed it up just prior to test running to the SQL in my actual DB it skipped like THOUSANDS. so in my Table i go from 1156 next record is 8141. what am i overlooking?
Back up DB i tested first

Original DB i tested 2nd after correcting SQL

 

[Kemidan2014]

Oh geesh nm I see what i did, i left it as an INNER Join in my original DB and forgot to ajust to Left...

 

[Micron]

FYI - autonumbers should NOT be used for meaningful data if that' what you're doing. If a record append begins but fails, that number is "used up" (in your case, that's about 7K records). The upsert should prevent that though. IIRC, putting the constraints at the table level should prevent that as well, so did you ever do that?

 

[Kemidan2014]

Oh i wasnt i was just using it as a reference to understand if the records were being added correctly i am using a seperate key field to control it. While i have your attention. Is it possible to add more tables to this qeury meaning - I want to update Table MainData (Table A) with not only LInkDatatable (Table B) but an additional relational table for the sake of simplicity call it TableC as i have 3 fields in that relational table that i would like to autopopulate 3 fields in my MainData based on Part number.

 

[Micron]

Sorry, don't know and don't have anything handy that I can test with. Perhaps if you can join the third table to the first in the same manner that you joined the second to the first. If not, then probably would have to use 2 separate upserts. If you chain the tables I don't think you'll be successful. It may appear to work at first (if it does at all) but chaining them would make the last table dependent upon the middle one so likely to return incorrect results at some point if it ever works at all.