How to Update Microsoft Access Records from Another Database by Unique ID
Question details
The user wants to overwrite existing records in a Microsoft Access database table using data from another linked database table, matching them by a unique ID without inserting any new records that only exist in the source.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Updating records in one destination table with corresponding information from a second source table where the unique IDs match exactly.
- Observed behavior
- Records with matching unique IDs are updated accurately, and non-matching new records from the source table are ignored. Manual sorting is bypassed as the query handles the ID match dynamically.
Before creating your update query, ensure that both databases are accessible and that you have successfully established a linked table connection to the source database within your destination Access file.
Create an UPDATE Query Using an Inner Join
Use a standard SQL UPDATE query via the Access Query Design view to overwrite existing fields based on a matching Unique ID.
This method links the destination table to the source table directly within the query. By strictly using an UPDATE query rather than an INSERT or Append query, you guarantee that only existing records are modified and no unmatched source records are added to your destination database.
Open your destination Microsoft Access database, navigate to the 'External Data' tab on the ribbon, and choose 'New Data Source' to link the table from your other database.
Navigate to the 'Create' tab and select 'Query Design' from the ribbon to open a new query workspace.
Add both your destination table and the linked source table to the query workspace. Click and drag the 'Unique ID' field from the source table to the 'Unique ID' field in the destination table to create an Inner Join relationship.
Click 'Update' in the Query Type group on the ribbon. This will change the query grid to include an 'Update To' row.
Drag the fields you want to modify from your destination table down to the query grid. In the 'Update To' row for each field, type the corresponding field name from the source table enclosed in brackets (e.g., [LinkedSourceTable].[FieldName]).
Click the 'Run' button (red exclamation mark) on the ribbon. Click 'Yes' on the warning prompt to confirm the update of the matched records.
Switch to WPS Office for a Streamlined Experience
While Microsoft Access is a powerful database tool, many data management and processing tasks can be handled efficiently using a robust spreadsheet application. WPS Office offers a highly compatible, lightweight, and completely free alternative to the Microsoft Office suite. Enjoy seamless editing, powerful data analysis functions, and a familiar interface without the high subscription costs.
- 1. Download WPS Office: Download and install WPS Office for free from the official website to access WPS Spreadsheet.
- 2. Import database records: Export your Microsoft Access data to an Excel format (.xlsx) and open it seamlessly within WPS Spreadsheet.
- 3. Match records dynamically: Use the VLOOKUP function in WPS Spreadsheet to match unique IDs and quickly pull updated record data from other tables without complex queries.
Frequently Asked Questions
Why shouldn't I use an Append query for this task?
An Append (or INSERT) query is designed specifically to add entirely new records from your source table into your destination table. Since you only want to update existing matched records by their Unique ID without adding source-only data, an UPDATE query is the correct function to use.
Do I need to sort my records by Unique ID before running the update?
No, manual sorting is completely unnecessary. As long as you establish an Inner Join between the two tables on the Unique ID field in the Query Design view, Access will automatically match the corresponding records regardless of their logical or physical order.
What happens if a Unique ID exists in the destination table but is missing in the source table?
If a Unique ID is missing from the linked source table, the Inner Join condition will simply not be met for that specific record. As a result, the UPDATE query will bypass it entirely, leaving your destination record untouched and unchanged.
