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

    Splitting a malformed table into a junction table and two normalized tables

    I am trying to bring an "excel" database into a well-formed relational database format; it's a BOM, so I have SKUs and each of their components and nesting depths enumerated in this big table. I want to accomplish three things, I'm not quite sure how to go about them:

    1. Copy all unique SKUs to a new table and AutoNumber
    2. Copy all components and their related data to a new table and AutoNumber
    3. Create a junction table that matches up the SKU ids with the Component ids based on the original table's relationships between them. This seems to be the trickiest.

    1. and 2. I'm not sure if I can accomplish in one query or if it requires two each.


    3. I'm, clueless

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    You need one query per table - so 3 queries in total from what you have described

    would need to see some example data with required output to be clearer. But as a guide, populate the two primary tables tables first then in the third, join these to your excel table on the appropriate fields (SKU to SKU for example) and select the appropriate auto number

  3. #3
    Pawtang's Avatar
    Pawtang is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2021
    Posts
    91
    Here is a little tabular diagram of what I'm hoping to accomplish, the first table is the format I have data in now, I have determined best approach would be one table of components and their descriptions and another that just ties components together in parent-child relationships with quantities of children. Let's ignore that the initial table is missing the quantity and cost for now.

    We also have superfluous data in the original table - we can ignore the columns that describe "immediate parent", only need to pull from the ones that have immediate child.

    I supposed I could write something like INSERT INTO Table2 (Parent, Child) SELECT Component, Immediate Child from Table WHERE Immediate Child IS NOT NULL

    I need to make sure I only have distinct pairs of foreign keys, I could either do like an ON ERROR DO NOTHING or perhaps define the selection as distinct?

    Is this the right approach?

    Click image for larger version. 

Name:	Screenshot 2022-05-09 123550.png 
Views:	14 
Size:	27.8 KB 
ID:	47768

  4. #4
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Can you upload the database?
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

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

Similar Threads

  1. Replies: 11
    Last Post: 02-06-2022, 11:31 PM
  2. Replies: 6
    Last Post: 04-09-2021, 08:33 AM
  3. Replies: 12
    Last Post: 05-09-2020, 03:38 PM
  4. Replies: 1
    Last Post: 06-24-2014, 01:31 PM
  5. Populate Junction Table related to 3 Tables
    By Daoud1987 in forum Access
    Replies: 5
    Last Post: 12-11-2013, 12:13 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