Normalizing Tables

Discussion in 'Access Table Design' started by DuWayne O'Neal, Apr 11, 2012.

  1. We manufacture concrete shelters. I pull BOM data down off of our ERP system into excel, then import into Access. I have ended up with about 90 plus BOM tables with different names. I need to put all this data into a normalized table. The database has grown to 176mb, so I need to break it up. Each BOM table has a name of the shelter ie.. PACB and all have the same structure with the following field names:

    ID--given to it by access when i import.
    fbompart--(part no)
    fbomdesc--(part desc)
    ftotqty--(qty used to make shelter)
    fbommeas--(unit of measure)
    Each table has between 300-600 parts

    I also have a item master file that has a list of every part no we use with the following fields:
    fpartnoID--given to it by ERP system.
    fpartno--(same as fbompart)
    fpartdesc--(same asfbomdesc)
    fgroup--(The category of each part no)

    So my question is how do I get all this data into a few tables, maybe one called Shelters, one called Category, one called Qty and one called Unit of Measure and still link all the data to each shelter?

    I hope I have explained everything with enough details.
    Just need help.
    Thanks
    DuWayne
     
    DuWayne O'Neal, Apr 11, 2012
    #1
    1. Advertisements

  2. DuWayne O'Neal

    Dan Dungan Guest

    Hi DuWayne,

    Here's a few questions that may help you identify the entities and
    their relationships in your process:


    1. How experienced are you with normalization concepts?
    2. What is the purpose of the database?
    3. What are the questions you want this database to answer?
    4. Can the same part number be used on several structures?
    5. Is there a BOM number?

    Tables named Qty or Unit of Measure seem non-normalized to me.

    Based on your description, I see two tables

    tblGroups
    fGroupId Primary Key
    fgroup--(The category of each part no


    tblBom
    fBomId Primary Key
    fGroupId Foreign Key
    fpartnoID--given to it by ERP system.
    fpartno--(same as fbompart)
    fpartdesc--(same asfbomdesc)
    ftotqty--(qty used to make shelter)
    fbommeas--(unit of measure)

    Hope this helps,

    Dan
     
    Dan Dungan, Apr 12, 2012
    #2
    1. Advertisements

  3. Re: Hi DuWayne,Here is a few questions that may help you identify theentities

    Thanks Dan for the reply.

    1. Not to experienced.
    2. I use this database every day to order parts off the BOM, to build the shelters.
    3. I want to be able to query different category of parts through a form using the Model of the shelter, which is the BOM table name. Each table is named after each shelter Model we build.
    4. Each of the BOM's dupicate part numbers. All shelters use cement, so therefore the part number for cement is in each one, same for hundreds of othe parts in each BOM.
    5. Again I use Model of shelters to name my tables, such as BCLX10, GIPD03, SUSA20, etc.

    If i create the tables you have listed, how do I get approximatley 45000 parts transfered into the tables?
    Hope you can help further or recommend someone. I will create the tables and will get back with you.
    Thanks again
     
    DuWayne O'Neal, Apr 12, 2012
    #3
  4. DuWayne O'Neal

    Tony Toews Guest

    Re: Hi DuWayne,Here is a few questions that may help you identify the entities

    On Thu, 12 Apr 2012 20:00:23 GMT, DuWayne O'Neal <>
    wrote:

    >If i create the tables you have listed, how do I get approximatley 45000 parts transfered into the tables?


    You can use append queries. Trouble is you'll need one or two for
    each of your incoming tables. And maybe one for each of the child
    tables if any as, if you used autonumber primary keys, you might need
    to renumber the primary key of the child tables.

    This can be a large and tedious task.

    Tony

    --
    Tony Toews, Microsoft Access MVP
    Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
    Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
    For a convenient utility to keep your users FEs and other files
    updated see http://www.autofeupdater.com/
     
    Tony Toews, Apr 12, 2012
    #4
  5. DuWayne O'Neal

    Dan Dungan Guest

    Re: Hi DuWayne,Here is a few questions that may help you identify the entities

    Hi DuWayne,


    You wrote:
    > 1. Not to experienced.


    I'm still attempting to learn these concepts as well. Have you read
    http://support.microsoft.com/kb/100139? It will only take a few
    minutes to read.


    > 2. I use this database every day to order parts off the BOM, to build theshelters.

    How do you use it? What steps do you take to order parts?

    > 3. I want to be able to query different category of parts through a form using the Model of the shelter, which is the BOM table name. Each table is named after each shelter Model we build.


    I understand you want to query, but what query--what question are you
    trying to answer when you look at the table?

    > 4. Each of the BOM's dupicate part numbers.  All shelters use cement, so therefore the part number for cement is in each one, same for hundreds ofothe parts in each BOM.
    > 5. Again I use Model of shelters to name my tables, such as BCLX10, GIPD03, SUSA20, etc.
    >



    > If i create the tables you have listed, how do I get approximatley 45000 parts transfered into the tables?


    Are you familiar with append queries, as Tony mentioned?


    > Hope you can help further or recommend someone. I will create the tables and will get back with you.
    > Thanks again


    Dan
     
    Dan Dungan, Apr 13, 2012
    #5
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Fred Thomas

    Normalizing for statistical analysis

    Fred Thomas, Oct 17, 2003, in forum: Access Table Design
    Replies:
    8
    Views:
    97
  2. Jeff

    Trouble importing and normalizing tables

    Jeff, Dec 5, 2003, in forum: Access Table Design
    Replies:
    3
    Views:
    133
    Jeff Boyce
    Dec 9, 2003
  3. Steve Newton
    Replies:
    2
    Views:
    86
    Steve Newton
    Aug 16, 2004
  4. Normalizing Table - is this right?

    , Mar 26, 2005, in forum: Access Table Design
    Replies:
    3
    Views:
    77
    abqhusker
    Mar 30, 2005
  5. Jolene Updike

    Normalizing my table- too many dimensions!

    Jolene Updike, May 4, 2005, in forum: Access Table Design
    Replies:
    7
    Views:
    107
    Tim Ferguson
    May 5, 2005
  6. Lee Stafford via AccessMonster.com

    Question about normalizing a survey

    Lee Stafford via AccessMonster.com, Jul 8, 2005, in forum: Access Table Design
    Replies:
    3
    Views:
    84
    Duane Hookom
    Jul 9, 2005
  7. Sydious

    Normalizing Tables Help Needed.

    Sydious, Aug 25, 2006, in forum: Access Table Design
    Replies:
    2
    Views:
    132
    Sydious
    Aug 29, 2006
  8. Rob S

    normalizing and creating fields or related tables

    Rob S, Aug 4, 2011, in forum: Access Table Design
    Replies:
    0
    Views:
    256
    Rob S
    Aug 4, 2011
Loading...