How can I merge data from an Excel file into an access database with multiple tables? I have an access database with many internal tables.
Each table is a category of electronic components (resistor, capacitor etc)
In all tables, there is a manufacturer part number and a currently empty field for our internal part number.

I have an excel file containing the internal part numbers and the matching manufacturers part numbers.

How can I fill in the internal part number field for all tables from my excel file, matching on the manufacturer part number?

I'm sure this is simple, but I don't use access much, and I don't speak "accesseze".
To update your tables as described requires multiple steps that can't be answered in detail for this single question.  I'll give what I hope is a useful outline, but you will need to search for more specific help for each step (in this forum, on Stack Overflow, or on the web).

I am using Access 2013, but these steps should be identical/similar for versions back to Access 2007.


Import or Link to the Excel file:


In Access, go to the External Data ribbon (i.e. toolbar tab).
Select the Import Access Database icon.
Follow the import wizard steps.


Linking to the Excel file creates a link "table" in Access.  On the surface it behaves and looks like another Access table, but it reads the data directly from Excel. Whether you link to the file or import the data depends on your preference and use.  If the Excel file data changes often AND the file location and format remain the same, then linking can avoid having to re-import into a new table, handling duplicates, etc.
However, if the Excel file is not formatted simply (i.e. basic columns with headers) or there are other complications, importing the data into a standard Access table can avoid some headaches.


Write multiple update queries that joins a table to the Excel data and updates the part number.


Because you do not speak "accesseze", perhaps you also don't know SQL.  In that case, you can use the Query wizards in Access to accomplish the updates.  However, it is much more convenient to share queries using SQL in these forums, hence the following example SQL.  The convenient thing about Access is that it allows switching between SQL View and the visual Design View
The following example will of course only update records with exact matches.  It is prudent to write data validation queries which look for missing part numbers, ill-formed part numbers, etc.  Such queries will ultimately require other types of joins between the tables, looking for Null values, etc.
Example SQL with made-up names since your question didn't include more details about the data:


The WHERE Component_Table.InternalPartNo Is Null clause is there to avoid inadvertently overwriting existing data.  Only remove this criteria if you are certain that you want to overwrite existing numbers, but refer back to my previous hint about data validation.






UPDATE Component_Table
INNER JOIN Excel_Data ON Component_Table.ManufacturerID = Excel_Data.ManufacturerID
SET Component_Table.InternalPartNo = Excel_Data.InternalPartNo
WHERE Component_Table.InternalPartNo Is Null