Update Query Overwriting Data with First Record

[EnginerdUNH]

Hi,

I have written three queries which update three separate but related tables in my database. There is the main table which lists the main record information and two let’s call them “sub tables” which contain related information but need to be stored separate from the main table because while the main table contains one row per unique item, the sub tables contain 1 or more rows for each record in the main table, with the tables relating to each other on one of fields which is common between the three tables.

I wrote three update queries to update the tables with information that is pulled into the database and stored in temporary tables and everything works fine when I do the initial run of the query. But if for whatever reason I have already run the query and the data from the temp tables is now stored in the main tables, the update query for the main table works fine but for the sub tables, every record for which the related field is equal all update to the values for the first record with that value. Can anyone tell me why this is happening and how I can fix it?

[HansV]

I assume that the two sub tables have an additional identifier, apart from the identifier that links them to the main table.
Do you include this additional identifier in the update queries?

[EnginerdUNH]

The only fields in the update query that I don’t include is the ID auto number field. Is this something I should be including?

[HansV]

There has to be a way to specify which record in the sub tables should be updated. Just the primary key of the main table is not enough.

[EnginerdUNH]

I'm trying to attach a stripped down version of the database that only has the tables, queries, and forms needed for you to be able to see my problem but no matter how much I try to remove from the database, I'm still getting a file that is too big to upload when I send it to a compressed zip file. As a workaround, I will include below the SQL code for the delete, append queries that I've written as well as I've attached a copy of the file I'm using to pull in the data from.

the delete queries

Code: Select all

DELETE VIRMainInfo.*
FROM VIRMainInfo;

DELETE VIRDWGInfo.*
FROM VIRDWGInfo;

DELETE VIRPOInfo.*
FROM VIRPOInfo;

the append queries

Code: Select all

INSERT INTO tblMainInfo ( VIRIssueShortDesc, VIRFinalVTS, VIRFinalPriority, VIREBReceived, VIRSupplier, VIRIssueLongDesc, VIRCRCN, VIRFinalDisposition, VIRReason, VIRActualCompDate, VIRNumber )
SELECT VIRMainInfo.VIRIssueShortDesc, VIRMainInfo.VIRFinalVTS, VIRMainInfo.VIRFinalPriority, VIRMainInfo.VIREBReceived, VIRMainInfo.VIRSupplier, VIRMainInfo.VIRIssueLongDesc, VIRMainInfo.VIRCRCN, VIRMainInfo.VIRFinalDisposition, VIRMainInfo.VIRReason, VIRMainInfo.VIRActualCompDate, VIRMainInfo.VIRNumber
FROM VIRMainInfo LEFT JOIN tblMainInfo ON VIRMainInfo.VIRNumber = tblMainInfo.VIRNumber
WHERE (((tblMainInfo.VIRNumber) Is Null));

INSERT INTO tblDWGAffected ( VIRDrawingNumber, VIRDrawingPN, VIRNumber )
SELECT VIRDWGInfo.VIRDrawingNumber, VIRDWGInfo.VIRDrawingPN, VIRDWGInfo.VIRNumber
FROM tblDWGAffected RIGHT JOIN VIRDWGInfo ON tblDWGAffected.VIRNumber = VIRDWGInfo.VIRNumber
WHERE (((tblDWGAffected.VIRNumber) Is Null));

INSERT INTO tblPOsAffected ( VIRPOLIs, VIRPO, VIRNumber )
SELECT VIRPOInfo.VIRPOLIs, VIRPOInfo.VIRPO, VIRPOInfo.VIRNumber
FROM VIRPOInfo LEFT JOIN tblPOsAffected ON VIRPOInfo.VIRNumber = tblPOsAffected.VIRNumber
WHERE (((tblPOsAffected.VIRNumber) Is Null));

the update queries

Code: Select all

UPDATE VIRMainInfo INNER JOIN tblMainInfo ON VIRMainInfo.VIRNumber = tblMainInfo.VIRNumber SET tblMainInfo.VIRNumber = [VIRMainInfo].[VIRNumber], tblMainInfo.VIRIssueShortDesc = [VIRMainInfo].[VIRIssueShortDesc], tblMainInfo.VIRFinalVTS = [VIRMainInfo].[VIRFinalVTS], tblMainInfo.VIRFinalPriority = [VIRMainInfo].[VIRFinalPriority], tblMainInfo.VIREBReceived = [VIRMainInfo].[VIREBReceived], tblMainInfo.VIRSupplier = [VIRMainInfo].[VIRSupplier], tblMainInfo.VIRIssueLongDesc = [VIRMainInfo].[VIRIssueLongDesc], tblMainInfo.VIRCRCN = [VIRMainInfo].[VIRCRCN], tblMainInfo.VIRFinalDisposition = [VIRMainInfo].[VIRFinalDisposition], tblMainInfo.VIRReason = [VIRMainInfo].[VIRReason], tblMainInfo.VIRActualCompDate = [VIRMainInfo].[VIRActualCompDate];

UPDATE tblDWGAffected INNER JOIN VIRDWGInfo ON tblDWGAffected.VIRNumber = VIRDWGInfo.VIRNumber SET tblDWGAffected.VIRNumber = [VIRDWGInfo].[VIRNumber], tblDWGAffected.VIRDrawingNumber = [VIRDWGInfo].[VIRDrawingNumber], tblDWGAffected.VIRDrawingPN = [VIRDWGInfo].[VIRDrawingPN];

UPDATE tblPOsAffected INNER JOIN VIRPOInfo ON tblPOsAffected.VIRNumber = VIRPOInfo.VIRNumber SET tblPOsAffected.VIRNumber = [VIRPOInfo].[VIRNumber], tblPOsAffected.VIRPO = [VIRPOInfo].[VIRPO], tblPOsAffected.VIRPOLIs = [VIRPOInfo].[VIRPOLIs];

the code to pull it all in

Code: Select all

'Deletes info previously imported into VIRListImport
        DoCmd.OpenQuery "qryDeleteVIRMainInfo"
        DoCmd.OpenQuery "qryDeleteVIRDWGInfo"
        DoCmd.OpenQuery "qryDeleteVIRPOInfo"
        
        Dim strFolder As String
        Dim strFile As String
        strFolder = "C:\Users\" & User & "\Documents\VIR Database Imports\"
        strFile = Dir(strFolder & "*xlsm")
        If strFile = "" Then
            MsgBox "There is no .xlsm file in the folder!", vbExclamation
            Exit Sub
        End If
        DoCmd.TransferSpreadsheet TransferType:=acImport, _
        SpreadsheetType:=acSpreadsheetTypeExcel12Xml, _
        TableName:="VIRMainInfo", _
        FileName:=strFolder & strFile, _
        HasFieldNames:=True, _
        Range:="tblMain$"
        
        DoCmd.TransferSpreadsheet TransferType:=acImport, _
        SpreadsheetType:=acSpreadsheetTypeExcel12Xml, _
        TableName:="VIRDWGInfo", _
        FileName:=strFolder & strFile, _
        HasFieldNames:=True, _
        Range:="tblDWG$"
        
        DoCmd.TransferSpreadsheet TransferType:=acImport, _
        SpreadsheetType:=acSpreadsheetTypeExcel12Xml, _
        TableName:="VIRPOInfo", _
        FileName:=strFolder & strFile, _
        HasFieldNames:=True, _
        Range:="tblPO$"
        
        DoCmd.OpenQuery "qryAppendtblMainInfo"
        DoCmd.OpenQuery "qryAppendtblDWGAffected"
        DoCmd.OpenQuery "qryAppendtblPOsAffected"
        DoCmd.OpenQuery "qryUpdatetblMainInfo"
        DoCmd.OpenQuery "qryUpdatetblDWGAffected"
        DoCmd.OpenQuery "qryUpdatetblPOsAffected"

[HansV]

I think the update queries should look like this:

UPDATE tblDWGAffected INNER JOIN VIRDWGInfo ON tblDWGAffected.VIRNumber = VIRDWGInfo.VIRNumber AND tblDWGAffected.VIRDrawingNumber = VIRDWGInfo.VIRDrawingNumber SET tblDWGAffected.VIRDrawingPN = [VIRDWGInfo].[VIRDrawingPN];

UPDATE tblPOsAffected INNER JOIN VIRPOInfo ON tblPOsAffected.VIRNumber = VIRPOInfo.VIRNumber AND tblPOsAffected.VIRPO = VIRPOInfo.VIRPO SET tblPOsAffected.VIRPOLIs = [VIRPOInfo].[VIRPOLIs];

[EnginerdUNH]

That is exactly what I needed!