Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Mar 2006
    Location
    Kitchener, Ontario, Canada
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Importing data to multiple tables (2003)

    Hello
    Up to this point data I've imported has been from an Excel spreadsheet into one Access table. The data sets in the spreadsheet have been small --2 to 4 fields and the spreadsheet fields matched the table fields exactly. In most cases copying from the spreadsheet and pasting into the table works well as the records generally number less than 200 per import operation. The databases are working well and the procedures are easily taught to my staff.

    I am now working on a database with 5 tables. Designing the database with one table would create repeated and null values and therefore be very inefficient.

    I'm struggling with the import procedure. I no longer have an exact spreadsheet to table situation.

    Would it make sense to import all the data to one table then create a series of append queries to update the individual tables?

    Or

    Is there a more sensible approach?
    Part of my goal in designing databases is to create tools that others can use easily. I am trying to avoid complex situations which require my ongoing intervention.
    I'm going for effective and simple so others can easily use the tools I develop.

    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing data to multiple tables (2003)

    You could create a link to the Excel table in your Access database and design a series of queries that transfer records to the appropriate Access tables. The queries would have to be executed in a specific order.

    To make this easy for the end user, you can create a macro that executes the queries, or (even better) design a form with a command button whose On Click event procedure executes the queries.

  3. #3
    Star Lounger
    Join Date
    Mar 2006
    Location
    Kitchener, Ontario, Canada
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing data to multiple tables (2003)

    I've tested my database and I have 4 tables: subscriber table which is the main table where the new records are being appended
    and
    Complaint Table (a list of complaint codes)
    District Manager Table (District Manager's Name, Initials)
    Route Table (a list of routes)

    Ive built a select query to test the relationships and data is appearing as I'd expect.
    I've read the linking help information (which is quite detailed), and created a link to an Excel spreadsheet.

    Now I'm stumped.

    I want to append records to the Subscriber table. All other tables are basically look-up tables with repeating data like route numbers or complaint codes. This information is in each record in the spreadsheets, but the index information is not in the Excel spreadsheat.

    I don't want to append anything to the lookup tables, but I need to match the information in spreadsheet to the information in the tables and I'm not sure how to use the indexes to get the information I need.

    Instead of including the route number from the spreadsheet, I need the corresponding index from the Route Table.
    Any Idea how I can accomplish this?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing data to multiple tables (2003)

    If I understand you correctly, you don't want to import data to multiple tables after all. You want to import to one table (the subscriber table) and use index values from three other tables. You can do this as follows:
    <UL><LI>Create a query based on the linked (or imported) Excel table and on the three lookup tables (complaint, district manager, route).
    <LI>Link the Excel table to the lookup tables on the appropriate fields, e.g. to the route table on the route number.
    <LI>Add the fields you need for the subscriber table to the query grid, but where applicable, use the index from the lookup table instead of the link field, e.g. add the route index from the route table instead of the route number from the Excel table.
    <LI>Select Query | Append Query.
    <LI>Specify the subscriber table as target.
    <LI>If necessary, adjust the fields to append to.[/list]If you need more assistance, it would be helpful if we could see what you are working with. See <post#=401925>post 401925</post#> for instructions on hpw to attach a stripped down copy of a database.

  5. #5
    Star Lounger
    Join Date
    Mar 2006
    Location
    Kitchener, Ontario, Canada
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing data to multiple tables (2003)

    Thanks Hans
    I just finished creating a make-table query to replace the data fields with index fields and then I created the append query.
    It works great.
    Hopefully this will help me build an efficient file.

    Thanks again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •