[Last Call] Learn how to a build a cloud-first strategyRegister Now


Access Import data into several tables at once

Posted on 2011-05-10
Medium Priority
Last Modified: 2012-06-21
Dear Experts,

I have CSV file that looks something like this:

Name                    Company             Product Type
Joe                         Nike                       Shoes
Robert                  Columbia             Outer Wear
Tom                       Nike                       Outer Wear
And I have an Access database that has these tables and columns:

Sales Rep Table
Sales_Rep_ID    SR_Name

Company Table
Company_ID      Co_Name

Product Table
Product_ID         Product_Name

Each ID field is the primary key and is an autonumber field. I want to import my data from the report to look like this (with whatever ID numbers come up from the autonumber field; these numbers are just examples):

Sales Rep Table
Sales_Rep_ID    SR_Name            SR_Company     SR_Product
1           Joe                           1                            1
2           Robert                      2                            2
3           Tom                           1                           2

Company Table
Company_ID      Co_Name
1      Nike
2      Columbia

Product Table
Product_ID         Product_Name
1      Shoes
2      Outer Wear

How can this be done if the original report is long enough to want it to be automated somehow? Editing 1000+ rows by hand shouldn’t be the solution.

The first table should contain the correct foreign keys to for the child tables.  Is there a way I can set up do command that would import this flat file and populate all three tables first Company and Project tables and thent he Sales rep table to ensure the right foreign key is used?

Question by:shogun5
LVL 42

Accepted Solution

dqmq earned 1600 total points
ID: 35735438
1. Import the CSV into a table: let's tblCSV

2. Add columns SR_Company_ID and SR_Product_ID to the [Sales Rep] table

3. Insert into Company (Co_Name)
  Select Company from tblCSV group by Company

4. Insert into Product (Product_Name)
   Select [Product Type] from tblCSV group by Product

5. Insert into [Sales Rep] (SR_Name, SR_Company_ID, SR_Product_ID)
   Select [Name], max(c.company_id), max(p.product_id)
    from ((tblCSV as s
     inner join Company as c on c.co_name = s.company)
     inner join Product as p on p.[product type] = s.product)
   group by s.name

I caution you two ways. First, the above code is untested, while I'm confident it's conceptually correct, there may some typo/syntax corrections needed.  Second, not the max() aggregations in the step 5 insert.  That's a result of your table design which limits a sales rep to a single company and a single product.  I'm not too concerned about the company, but I question the limitation of 1 product per sales rep.  :>)

Assisted Solution

Andrew_Webster earned 400 total points
ID: 35746996
I now work in an enterprise data warehouse.  Our work splits into data design, ETL (extract, transform, load), and business intelligance - reporting.

You're problem is a basic ETL problem.  The normal process is to import into a staging area, then apply transformations and load into the final tables.  In other words, dgmg's process is spot on.

1. Import to staging
2. Run validation and sanity checks using SQL (i.e. any missing data, data in the wrong format - dump it into an error table for review)
3. Run a series of INSERTS parsing your data into their respective tables, starting with loading look-ups whose primary keys provide foreign keys in the data tables that are loaded last

Keep that in mind as the basic method and you'll be fine.  There's not a magic way of doing this, even the scary enterprise ETL tools follow a workflow that's based on this set of steps.

Author Closing Comment

ID: 35751015
Thanks all this helped!

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question