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)

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.

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.

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.

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)

MS Access, properly designed, will handle millions of rows with ease.

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...

PostgreSQL is free.

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.

Could probably migrate from Access to SQL Server Express (free) with little or no problem, and continue to use the Access interface.

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...