How to import a file into database and split into two related tables (1 Viewer)

JudyHNM

Registered User.
Local time
Yesterday, 22:41
Joined
Oct 5, 2006
Messages
37
I have an MS Excel file in which I have keep purchases, i.e., inventory. I want to import this file into Access and split it into two tables. I want one table to have a description of the item and where purchased. I want the related (linked) table to have purchase date, cost , etc. That way I can keep one record for the inventory item and display multiple purchases through a subform. How can I split the record on import?

Note: I am using Accessl 2016 and know how to import files from Excel to Access but don't know how to split the records.

Thank you, Judy
 

Ranman256

Well-known member
Local time
Today, 00:41
Joined
Apr 9, 2015
Messages
4,339
link the excel file as an external table.
build 2 queries. 1 for each table. put both queries in a macro.
then when you get a new file, overwrite the old one,
run the macro.
 

JudyHNM

Registered User.
Local time
Yesterday, 22:41
Joined
Oct 5, 2006
Messages
37
I think I understand what you are saying. I will see if I can figure it out. Thanks for your response.

Judy
 

Cronk

Registered User.
Local time
Today, 15:41
Joined
Jul 4, 2013
Messages
2,770
Ranman has given you the short answer. The first query should select and group on those fields you want in the primary table which should have an autonumber PartID field together with whatever unique identifier is used in Excel to identify the part.

The second query should join the first Access table to the Excel data using the Excel unique identifier and insert the PartID (to provide the foreign key) as well as the Excel data relating to the purchases of that part, into the second related Access table.
 

Users who are viewing this thread

Top Bottom