Results 1 to 4 of 4
  1. #1
    colby is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2011
    Posts
    2

    Import Excel Data to Multiple Access Tables

    I have an Excel spreadsheet that is relatively simple. It has the following structure:
    • User Name
    • Subordinate
    • Home Server
    • Department
    A user may have multiple subordinates, and multiple users will be on the same server, so multiple values for user, subordinate and server must be allowed.



    I would really like to import this into three linked tables, a user table that links to both a subordinate table and a server table. If possible, I would also like to link the subordinate table to the server table to add that data in later.

    I can get a simple import, and then using the analyzer tool, break the relationships out. The issue is when the analyzer starts checking the data, it wants me to replace approximately 13,000 of the 14,000 users with alternate data, and to click on each to say leave as is very impracticle.

    Is there a better way to be doing this?

    Cheers

  2. #2
    RayMilhon is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,067
    Yes there is. Analyze your data and design the structure of your new tables. Create Group by queries to populate the new tables.

    Based on the 4 fields you provided I would use the structure as follows

    tbl_User
    userID autonumber
    User Name Text
    Department Text

    tbl_Home_Server
    HSID Autonumber
    Home_Server Text

    subordinate
    Mgr_ID Number
    User_ID Number

    the hard part is populating the subordinate table. I'm assuming that the Subordinate Field you mentioned is text for the subordinate's User Name. You would have to retrieve the UserID for the User name and the User ID for the subordinate and save those 2 elements in each record of the subordinate table. Create an append query with the imported file and the 2 of the user table 1 for mgr and 1 for subordinate
    Link the import Username to the mgr table and the Subordinate to the subordinate table Append the userid from both tables into your subordinate table.

  3. #3
    colby is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2011
    Posts
    2
    Ray,

    Thanks for the quick reply.

    I was thinking more along the lines of:

    tbl_User
    userID - autonumber
    User Name - text
    Department ID - number (linked to Department table)
    Subordinate ID - number (linked to subordinate table)
    HomeServer ID - number (linked to Home Server table)

    tbl_Department
    departmentID - autonumber
    Department Name - text

    tbl_Subordinate
    subordinateID - autnumber
    Subordinate Name - text

    tbl_HomeServer
    serverID - autonumber
    HomeServer Name - text

    I will do some digging on the Create Group by queries options to populate things.

    Cheers
    Dave

  4. #4
    RayMilhon is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,067
    The only issue I can see is that if a user has multiple subordinates you will duplicate the user data. What you need is a 1 to 1 or 1 to many relationship and your design doesn't have that. that's why I recommend the user table I did although I did forget to include the Home Server ID. a question can a user be in multiple Departments? If the answer is no then you don't need a separate table for the department. If yes then you need both a department table and a linking table from user to department

    Take a look at the 2 JPEGS attached to this.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Import Data from Excel into Access
    By sauce1979 in forum Import/Export Data
    Replies: 2
    Last Post: 10-14-2011, 12:05 AM
  2. Replies: 4
    Last Post: 06-14-2011, 07:19 PM
  3. Splitting an Excel File into Multiple Access Tables on Import
    By TheWolfster in forum Import/Export Data
    Replies: 4
    Last Post: 04-29-2010, 04:52 AM
  4. Replies: 0
    Last Post: 04-29-2009, 04:27 PM
  5. Automate Excel Import to Access Related Tables
    By KramerJ in forum Programming
    Replies: 6
    Last Post: 04-04-2009, 04:24 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums