Results 1 to 7 of 7
  1. #1
    Pawtang's Avatar
    Pawtang is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2021
    Posts
    91

    Question Splitting big Excel table data into two normalized related tables from import

    Hey folks,



    I'm trying to build a better system to manage customer returns. In the Excel table, all of the data for one record is kept in one row. One return is an RMA which has a unique RMA Number, and contains many parts, which have part numbers and serial numbers (in our application, referred to as date codes).

    In the DB, I split this into two major tables; RMAs and Parts Returned. I use RMA Number as the primary key because they are the unique identifier, and I prefer to have meaningful PK when possible rather than auto-number when it should be strictly enforced. I attached my table relationship diagram for more details.

    My main question is, how would I go about taking data imported from the Excel and appending it to each of these tables? Can I write a single query that will first append relevant columns to the RMA table, and then append the other relevant columns to the Parts Returned table, and somehow match the foreign key on the Parts Returned table back to the RMA number of the RMA Table? I haven't touched SQL in a few years but am going to dive back in with this; just curious if I have the right approach.

    Hope what I'm saying is clear enough; if not I'm happy to try to clarify and provide more info.

    RMA Database Diagram.pdf
    Last edited by Pawtang; 03-10-2021 at 12:20 PM. Reason: Typo in title

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I don't see it being done with a single query. I assume the various fields for multiple parts are multiple columns in the spreadsheet? It would help to see the spreadsheet, but I envision 2 append queries, one for the RMA table and a second for the parts table. The second would be based on a UNION query that pulled the various columns of "part1, part2" into a single dataset.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    You can probably create the normalised tables data automatically in Excel using the transform aspect of a Power Query.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  4. #4
    Pawtang's Avatar
    Pawtang is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2021
    Posts
    91
    Some googling led me to end up doing the following (just needed the confidence to try):

    1. Link each page of the Excel source worksheet to database as external data source
    2. Create first query, which appends RMA information to RMA table
    3. Create second query, which appends parts returned information to Parts Returned table

    That's pretty much it. Those two tables are already related one-to-many, so no other processing is needed in the query.

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Glad you got it working.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I would suggest you stop and remove any and all spaces in object names.
    Don't use special characters (Warehouse Code#) or punctuation in object names.



    Good luck with your project.......

  7. #7
    Pawtang's Avatar
    Pawtang is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2021
    Posts
    91
    Quote Originally Posted by ssanfu View Post
    I would suggest you stop and remove any and all spaces in object names.
    Don't use special characters (Warehouse Code#) or punctuation in object names.



    Good luck with your project.......
    Thanks ssanfu

    I used this just to lay out the concept, I use camel case for anything in the Access environment

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

Similar Threads

  1. Append/Import data to normalized tables
    By margzj in forum Access
    Replies: 3
    Last Post: 08-05-2017, 12:58 PM
  2. Replies: 1
    Last Post: 06-06-2016, 06:05 PM
  3. making Normalized tables from Non-Normalized Tables
    By JrMontgom in forum Programming
    Replies: 1
    Last Post: 04-16-2015, 08:50 AM
  4. Replies: 2
    Last Post: 08-07-2014, 07:31 AM
  5. Replies: 1
    Last Post: 07-16-2012, 02:10 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
  •  
Other Forums: Microsoft Office Forums