Access Import data into several tables at once

Posted on 2011-05-10
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

    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 =
         inner join Product as p on p.[product type] = s.product)
       group by

    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.  :>)
    LVL 8

    Assisted Solution

    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

    Thanks all this helped!

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    734 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

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now