Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-12-2017, 11:42 AM   #1
JudyHNM
Newly Registered User
 
Join Date: Oct 2006
Posts: 23
Thanks: 1
Thanked 0 Times in 0 Posts
JudyHNM is on a distinguished road
How to import a file into database and split into two related tables

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

JudyHNM is offline   Reply With Quote
Old 05-12-2017, 11:54 AM   #2
Ranman256
Newly Registered User
 
Join Date: Apr 2015
Location: KY,USA
Posts: 3,327
Thanks: 0
Thanked 732 Times in 717 Posts
Ranman256 will become famous soon enough Ranman256 will become famous soon enough
Re: How to import a file into database and split into two related tables

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.
Ranman256 is offline   Reply With Quote
Old 05-12-2017, 02:49 PM   #3
JudyHNM
Newly Registered User
 
Join Date: Oct 2006
Posts: 23
Thanks: 1
Thanked 0 Times in 0 Posts
JudyHNM is on a distinguished road
Re: How to import a file into database and split into two related tables

I think I understand what you are saying. I will see if I can figure it out. Thanks for your response.

Judy

JudyHNM is offline   Reply With Quote
Old 05-14-2017, 04:45 PM   #4
Cronk
Newly Registered User
 
Join Date: Jul 2013
Posts: 2,119
Thanks: 3
Thanked 459 Times in 452 Posts
Cronk will become famous soon enough Cronk will become famous soon enough
Re: How to import a file into database and split into two related tables

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.

Cronk is offline   Reply With Quote
Reply

Tags
import

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Import Excel- append to Access Related Tables kjyoung Queries 2 01-29-2012 03:20 AM
Import excel file and create records in related tables gfultz Modules & VBA 0 06-02-2010 06:34 PM
Question Import data to multiple related tables Cobo General 3 03-04-2010 04:35 AM
Import Text File Split Database Lightwave General 3 07-24-2009 02:20 PM
Import Data into Related Tables? spacepro General 8 06-02-2009 10:47 AM




All times are GMT -8. The time now is 03:14 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World