Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2009
    Posts
    5

    Help me normalize!

    First off, I am working with an access database that consists of one enormous table with all of our sales data. I have put a description of what each item is used for. I am a business analyst with a background in database through school. However, when it comes to normalization I am lost. If possible can somebody help me normalize the table below? It's over 900k line items & sometimes takes 5-10 minutes to run a simple search query.

    Now I pull data from the database off the vendor_item number, or vendor_name, manuf_nbr, Manuf_name. I then do cost comparisons based on the time frame of the data. I get new sales data each month, so how would I go about updating everything once the table is normalized?

    After Contract_Num all the data is only in the Lawson table and does not Exist in the Cardinal table.


    NAME_OF_TABLE (The data comes from one or two tables, Lawson or Cardinal, both are setup with a simular schema)
    MO (City of Hospital)
    MONTH_OR_YEAR (Month of data feed if Lawson, if Cardinal it's the Quarter)
    COMPANY_NAME (Name of Hospital)
    COMPANY_NUM (Hospital ID)
    VENDOR_NAME (Supplier Name)
    VENDOR_NUMBER (Supplier ID)
    VENDOR_ITEM (Vendor Item Code #)
    MATERIAL_DESCRIPTION (Vendor Item Description)
    MATERIAL_DESCRIPTION_2 (Vendor Item Description 2)
    PACKAGING_INFO (QTY per UOM, Cardinal only)
    PRODUCT_DESCRIPTION (Category of Product)
    PRODUCT_SUB_DESCRIPTION (Sub Category of Product)
    ITEM_NUM (Another way to identify Vendor_Item)
    CARDINAL_NUM (Cardinals way to identify Vendor_Item)
    UOM (Unit of Measure)
    EACH (QTY per UOM)
    QUANTITY (UOM QTY Sold to the Hospital)
    Total QTY (EACH * QUANTITY)
    SALES (Total Amount of the Sales for the hosptial)
    Sales W/3.85 Dist Markup (Sales with Cardinals Distribution Markup removed, Cardinal Data only)
    EACH Price (Sales with Cardinals Distribution Markup removed / Total QTY)
    CONTRACT_COST (Cost per UOM)
    COST_VENDOR_CONTRACT (Unused)
    CONTRACT_NUM (We have several agreements were our members get cheaper prices if they are signed, not all sales with have an agreement, sometimes we search by agreement)
    AGMT_TYPE (Either committed or prefered)
    Agrmt_Descr_1 (Agreement Description, simular to Product Description)
    Agrmt_Descr_2 (Agreement Description, simular to Product Description)
    VEND_ACCT (Hospitals Account Number with Vendor)
    Manuf_Name (Manufacture Name)
    MANUF_NBR (Manufacture Code, never the main search, but sometimes checked if not found in vendor_item)
    ALT_UOM_01 Vendor_item information
    ALT_UOM_CONV_01 Vendor_item information
    ALT_UOM_02 Vendor_item information
    ALT_UOM_CONV_02 Vendor_item information
    ALT_UOM_03 Vendor_item information
    ALT_UOM_CONV_03 Vendor_item information
    ALT_UOM_04 Vendor_item information
    ALT_UOM_CONV_04 Vendor_item information
    ALT_UOM_05 Vendor_item information
    ALT_UOM_CONV_05 Vendor_item information
    ISS_ACCOUNT (Unused, in searchs but useful to those reading report)
    ACCOUNT_DESC (Unused, in searchs but useful to those reading report)

  2. #2
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    First get yourself a decent DBMS. Maintaining a million rows of sales data in Access must be a pretty painful experience. I'd expect there are other potential users of this data who could benefit from it being shared on a corporate database server instead of in a file.

    It looks like you'll need at least tables for vendor, company and sales. There's very little value in us trying to guess a table design based on your list of column names. You are in the best position to understand and analyse your data.

    Quote Originally Posted by nickhoff
    I get new sales data each month, so how would I go about updating everything once the table is normalized?
    How do you do that today? If the data source is some file or database then you could connect to that source in various ways and load the data automatically on a schedule. For instance Microsoft SQL Server has Integration Services with which you can transform almost any format that you can access through OLEDB/ODBC.

  3. #3
    Join Date
    Apr 2009
    Posts
    5
    Getting a decent DBMS is out of the question. We are a small custom office with 8 employees and I'm the only one who knows SQL.

    The end results is I'm going to want to pull all of the information back based on the vendor_item or the vendor_name, and time frame of sales data. I usually then import it to an excel spreadsheet and work on the analysis/pivot tables there.

    To update the database, I receive the excel spreadsheet and I append it to the current table. Once the database is normalized appending it to the table wouldn't work, unless I appended it to each table based on what wasn't a duplicate.

  4. #4
    Join Date
    Apr 2009
    Posts
    5
    Would some thing like this work? I'm not sure how to get the tables to relate though, they all need to relate to each other.

    Hospital
    MO (City of Hospital)
    COMPANY_NAME (Name of Hospital)
    COMPANY_NUM (Hospital ID)
    VEND_ACCT (Hospitals Account Number with Vendor)

    Vendor
    VENDOR_NAME (Supplier Name)
    VENDOR_NUMBER (Supplier ID)

    Product
    VENDOR_ITEM (Vendor Item Code #)
    MATERIAL_DESCRIPTION (Vendor Item Description)
    MATERIAL_DESCRIPTION_2 (Vendor Item Description 2)
    PACKAGING_INFO (QTY per UOM, Cardinal only)
    PRODUCT_DESCRIPTION (Category of Product)
    PRODUCT_SUB_DESCRIPTION (Sub Category of Product)
    ITEM_NUM (Another way to identify Vendor_Item)
    CARDINAL_NUM (Cardinals way to identify Vendor_Item)
    UOM (Unit of Measure)
    EACH (QTY per UOM)
    ALT_UOM_01 Vendor_item information
    ALT_UOM_CONV_01 Vendor_item information
    ALT_UOM_02 Vendor_item information
    ALT_UOM_CONV_02 Vendor_item information
    ALT_UOM_03 Vendor_item information
    ALT_UOM_CONV_03 Vendor_item information
    ALT_UOM_04 Vendor_item information
    ALT_UOM_CONV_04 Vendor_item information
    ALT_UOM_05 Vendor_item information
    ALT_UOM_CONV_05 Vendor_item information
    CONTRACT_COST (Cost per UOM)
    COST_VENDOR_CONTRACT (Unused)
    Manuf_Name (Manufacture Name)
    MANUF_NBR (Manufacture Code, never the main search, but sometimes checked if not found in vendor_item)

    Agreement
    CONTRACT_NUM (Can be Null) We have several agreements were our members get cheaper prices if they are signed, not all sales with have an agreement, sometimes we search by agreement)
    AGMT_TYPE (Either committed or prefered)
    Agrmt_Descr_1 (Agreement Description, simular to Product Description)
    Agrmt_Descr_2 (Agreement Description, simular to Product Description)

    Sales
    NAME_OF_TABLE (The data comes from one or two tables, Lawson or Cardinal, both are setup with a simular schema)
    MONTH_OR_YEAR (Month of data feed if Lawson, if Cardinal it's the Quarter)
    QUANTITY (UOM QTY Sold to the Hospital)
    Total QTY (EACH * QUANTITY)
    SALES (Total Amount of the Sales for the hosptial)
    Sales W/3.85 Dist Markup (Sales with Cardinals Distribution Markup removed, Cardinal Data only)
    EACH Price (Sales with Cardinals Distribution Markup removed / Total QTY)
    ISS_ACCOUNT (Unused, in searchs but useful to those reading report)
    ACCOUNT_DESC (Unused, in searchs but useful to those reading report)

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    MS Access, properly designed, will handle millions of rows with ease.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Apr 2009
    Posts
    5
    Sorry about the double post blindman, the website froze on me during my first post and it never went through after 5 mins of waiting, I hit the stop button on the browser and sent it again...

  7. #7
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Getting a decent DBMS is out of the question. We are a small custom office with 8 employees
    PostgreSQL is free.

    MS Access, properly designed, will handle millions of rows with ease.
    True. If you anticipate the database continuing to grow, you may want to consider a server-based solution as the back end though. You could still use Access as the front end & for reporting.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Could probably migrate from Access to SQL Server Express (free) with little or no problem, and continue to use the Access interface.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Apr 2009
    Posts
    5
    What benefits would our team receive by upgrading to SQL Server or PostgreSQL? I ended up normalizing the database and the search queries are being completed in a few seconds instead of several minutes.

    As to the conversion I need a good explaination for my boss as to why we should do it and how its going to produce more revenue...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •