Trouble with duplicate records

[Kemidan2014]

One of my users attempted to follow my work instruction on how to update the database (which is based off of a linked excel table) some how managed to add the SAME complaint record in the table over and over again from Record ID 1205 through 2403

is there a way i can cut out all these duplicates so to only keep the singular record that needs to be there. right clicking and to delete record can only be done 1 at a time it seems.

the linked excel sheet customer complaint ID is a non repeating value the excel linked file is CORRECT and has the correct amount of complaints in it. i run an UPSERT query to add new complaints that done exist. he apparently did do this nor can he explain what he did to get this result.

 

[Kemidan2014]

I withdraw a portion of my question. I just discovered on my own by HOLDING the shift key after i highlight records and rightclicking it does not break the selection so i was able to successfully delete the unessecary information.

the 2nd portion of my question though, how can I in the future guard against duplicate customer complaint numbers from being allowed to be appended to the table. is it just as easy as changing\adding to my primary key(s)?

incase its useful here is the SQL code for the UPSERT query that appends the main table to add new recordsfrom the linked excel file

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

 

[welshgasman]

Seriously?, how is anyone supposed to make any sense of that?

UPDATE LinkTableData
LEFT JOIN MainData ON LinkTableData.[QIMS#] = MainData.[QIMS#]

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

You have no criteria in that all?

You might have overwritten a lot of records?, not appended the same one multiple times.
Set a unique index on your data is one way.

Did you thoroughly test that query? with different permutations of data?
I have never had to use such a query. I would probably do it in two steps anyway so I can test everything.

 

[Micron]

how can I in the future guard against duplicate customer complaint numbers from being allowed to be appended

Set table field(s) to not allow dupes? If required, then create a composite index if you need to allow
A | A
A | B
A | C
A | D
B | A
etc.
but not A | A again.