Apologies if this has been answered elsewhere, haven't used the right search term?
I'm going to receive multiple copies of an excel file. The same sheet was emailed to several people. They will fill out and email back. In general, I think it will be straightforward, but thinking about it, seems risky under various scenarios to simply run an update query since may wipe out data from original table or from person A getting replaced with person B file and would like suggestions. For now my thought process is: as I receive the spreadsheet link in and then view a query that joins the two tables (the access table and the linked table containing the updates), query shows only the rows where data are null in original or <>. If the new data looks like is ok, then run an update query to replace the old data. Repeat steps with each file that is received. For example Document Number 124 shows open, Person A sends me file indicating Document Number 124 is now closed, and table gets updated to reflect that change. Person B sends file, obviously, that copy still shows open, so we don't want to change 124 back to open. Do I need to add more columns to the table to indicate when the data got updated and the source of that update so I know not to change closed back to open? Another possibility that could happen - Person B sends back and says 124 is pending closure, so then I will have to get confirmation about whether to use Person A's response or Person B's. Would there be a vba solution, recordsets or something to assist in the process? There are two columns that are going to be updated. Below is the query I am setting up to screen the data when it arrives.
I'm going to receive multiple copies of an excel file. The same sheet was emailed to several people. They will fill out and email back. In general, I think it will be straightforward, but thinking about it, seems risky under various scenarios to simply run an update query since may wipe out data from original table or from person A getting replaced with person B file and would like suggestions. For now my thought process is: as I receive the spreadsheet link in and then view a query that joins the two tables (the access table and the linked table containing the updates), query shows only the rows where data are null in original or <>. If the new data looks like is ok, then run an update query to replace the old data. Repeat steps with each file that is received. For example Document Number 124 shows open, Person A sends me file indicating Document Number 124 is now closed, and table gets updated to reflect that change. Person B sends file, obviously, that copy still shows open, so we don't want to change 124 back to open. Do I need to add more columns to the table to indicate when the data got updated and the source of that update so I know not to change closed back to open? Another possibility that could happen - Person B sends back and says 124 is pending closure, so then I will have to get confirmation about whether to use Person A's response or Person B's. Would there be a vba solution, recordsets or something to assist in the process? There are two columns that are going to be updated. Below is the query I am setting up to screen the data when it arrives.
Code:
SELECT tblLetters.DocumentNo, tblLetters.Originator, [LETTERSNT].Originator, tblLetters.BIStatus, [LETTERSNT].[BI Status],
tblLetters.ResponseExpected, [LETTERSNT].Response
FROM [LETTERSNT] INNER JOIN tblLetters
ON [LETTERSNT].[Document No] = tblLetters.DocumentNo
WHERE ((([LETTERSNT].[BI Status])<>[tblLetters].[BIStatus] Or [tblLetters].[BIStatus] Is Null)
AND (([LETTERSNT].[BI status]) Is Not Null)) OR ((([LETTERSNT].Response)<>[tblLetters].[ResponseExpected]
Or [tblLetters].[ResponseExpected] Is Null)
AND (([LETTERSNT].response) Is Not Null));