Results 1 to 3 of 3

How to Import One-to-Many Relationships from Excel

  1. #1
    MarkMeer is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Posts
    1

    Question How to Import One-to-Many Relationships from Excel

    I'm wondering if there is a way to import a one-to-many relationship from an Excel spreadsheet into an Access database.



    Presently, the data is represented in Excel by a semi-colon separated list in the cells of a column, representing the OtM relationship.
    For example, a cell in the Excel spreadsheet might contain "3;20;23", indicating a relationship to another table's rows with IDs (primary keys) of 1, 20, and 23.
    NOTE: this appears to be the same format when an existing Access one-to-many relationship is exported from Access to Excel.

    I'm struggling to find a way to import this Excel data into Access.

    Can anyone kindly advise (or link to documentation) as to how I would setup my Access database, and format the Excel data to import such data into Access?

    Many thanks in advance.
    MM.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    7,697
    import the master table data,
    then import the child table data.

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,436
    Hmmmmm, you might be able to use VBA code, but you have not provided enough info to be able to be confident about using code.

    I have never seen a spreadsheet with this format.
    From the spreadsheet, what is the one table - name, structure, data??
    From the spreadsheet, what is the many table - name, structure, data??


    Quote Originally Posted by MarkMeer View Post
    a cell in the Excel spreadsheet might contain "3;20;23", indicating a relationship to another table's rows with IDs (primary keys) of 1, 20, and 23.
    To me "3;20;23" would represent the one table PK of 3 and the many table FK records of 20 and 23. But how do you know that PK record 3 is related to FK records 20 and 23??


    Maybe post the Access dB and spreadsheet?
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

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

Similar Threads

  1. Replies: 6
    Last Post: 09-08-2019, 03:18 PM
  2. Replies: 2
    Last Post: 02-28-2017, 01:34 AM
  3. Replies: 5
    Last Post: 07-18-2016, 11:10 AM
  4. Replies: 10
    Last Post: 06-16-2014, 08:37 AM
  5. Saved Import for Table with Relationships
    By garygoodguy in forum Import/Export Data
    Replies: 3
    Last Post: 04-21-2013, 08:07 PM

Tags for this Thread

Posting Permissions

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