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".

  • 1
    While I am also not particularly conversant in "Access-eze", I'd upload the file to a new Access table, and then update each of the other tables, matching rows to the new table based on the manufacturer's part IDs, using Access's query language (which I believe is a SQL variant, probably related to T-SQL by this point).. That's where I can no longer provide specifics; since I believe Access is related to SQL Server at some level, you may simply be able to do an INNER JOIN between the old table and the newly-loaded table.
    – RDFozz
    Jun 27 '18 at 20:13
  • 1
    Access can directly query Excel files. You can just use an UPDATE query, and join the data from the Excel file to the Access table. Access even allows outer joins in UPDATE queries which inserts new rows for unmatched rows.
    – Erik A
    Jun 28 '18 at 14:31

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

  • Thank you. As you said, I will need to work through this, but it looks like a path I can follow. I used to do a lot with MySQL and PHPMyAdmin, but that was about 1999 and I haven't refreshed that DRAM in a while. Perhaps it will start looking familiar once I get into it. Jun 28 '18 at 18:44

Not the answer you're looking for? Browse other questions tagged or ask your own question.