Best way to normalize and store data

DougStroud

Well-known Member
Joined
Aug 16, 2005
Messages
2,966
One of the projects I have been thinking about doing is creating a master file that stores product data for future and current items that I will purchase for my store. The pertinent data is the same from all the companies, but the structure in which I receive it is always different, not consistent structure.
Initially, I am looking for suggestions of how to approach this rather than a specific answer right at the moment.
Suggestions on which way to handle this and then on how-- e.g. All in Excel or incorporate Access. I have extremely limited exposure to Access, so if Access is the way to go, I will need a little more help once execution begins, the how question details: on whether to store the data in one main file w/ all the companies, part numbers, part names, color, sizing, pricing, by season, or store the data in individual files by company name w/ part numbers, part names, color, sizing, by season, stored in the same sub-directory.
Three different examples below that will demonstrate what I am provided by the companies initially to build the file I wish to store the data in.
I had to add these into different posts to allow to upload, sorry if this makes this hard to read.

Also thanks to everyone who reads and contributes...

Doug
DVS BTS 2006.xls
ABCDEFGHIJKLMNOPQRSTUVWXYZ
6SHOP:PHONE:
7ADDRESS:ORDERED BY:
8P.O.#SHIP DATE:
9PRICE SHEET / SUMMER 2006 / DOMESTIC
10MENS SUMMER 2006 SHOE STYLES
11STYLECOLORCODEITEM #NEW-RE-C/OAVAIL5667788991010111112131415UNITSPRICETOTAL
12TAYLOR SECTBLACK LEATHERBLKFS/TAYLOR SECTNEW5/1011112121111$40.00$ 440.00
13TAYLORWHITE LEATHERWHITS/TAYLOR SMNEW5/10-$36.00$ -
14TAYLORBLACK NUBUCKBLKTS/TAYLOR SMNEW5/101111111119$36.00$ 324.00
SHOES PAGE 1
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

DougStroud

Well-known Member
Joined
Aug 16, 2005
Messages
2,966
2nd Example:
0716%OrderForm.xls
ABCDEFGHIJ
1Dealer NamePO Number
200
3
4UPC#ItemNPDescriptionCost16%QTYTotalRubberAccessory
5
674861016021862001575NPABS/PLAT 135 07-ZEUS/HGE S435.00435.00$ -
774861016022562001576NPABS/PLAT 135 07-ZEUS/HGE M435.00435.00$ -
874861016023262001577NPABS/PLAT 135 07-ZEUS/HGE L435.00435.00$ -
974861016024962001578NPABS/PLAT 135 07-ZEUS/HGE XL435.00435.00$ -
1074861016025662001579NPABS/PLAT 141 07-ZEUS/HGE L435.00435.00$ -
1174861016026362001580NPABS/PLAT 141 07-ZEUS/HGE XL435.00435.00$ -
1274861016027062001581NPABS/PLAT 141 07-ZEUS/HGE XXL435.00435.00$ -
1374861016028762001582ABS/PLAT 135 07-BLANK W/FINS384.00322.56$ -
1474861016029462001583ABS/PLAT 141 07-BLANK W/FINS384.00322.56$ -
1574861016030062001584ABS/PLAT 135D07-BLANK W/FINS353.28296.76$ -
1674861016031762001585ABS/PLAT 141D07-BLANK W/FINS353.28296.76$ -
1774861016032462001586NPABSOLUTE 135 07-FUSE S350.00350.00$ -
07CWB
 

DougStroud

Well-known Member
Joined
Aug 16, 2005
Messages
2,966
Loaded Skateboards 08-08-06.xls
ABCDEFGHIJK
10Bill To:Ship To:
11EastCoast Boardco.
12
13
14
15
16email:email:
17phone #phone #
18fax #
19
20Payment TermsPmt. MethodCheck #Ship ViaFOBInvoice #
21N-30UPSLos Angeles
22ItemDescriptionMSRPQtyWholesaleAmount
23Vanguard Bamboo CompleteVanguard Flex 5 (Lime Logo), 80-140+ lbs, L: 38 WB:31"$ 264.00$ 140.00$ -
24Vanguard Flex 4 (Black Logo), 120-170+ lbs, L: 38" WB: 31"$ 264.00$ 140.00$ -
25Vanguard Flex 3 (Maroon Logo), 150-200+ lbs, L: 38" WB: 31"$ 264.00$ 140.00$ -
26Vanguard Flex 2 (Blue Logo), 150-210+ lbs, L: 42" WB: 35"$ 264.00$ 140.00$ -
27Vanguard Flex 1(Green Logo), 175-230+ lbs, L: 42" WB: 35"$ 264.00$ 140.00$ -
28Vanguard Bamboo PremiumWith Abec 11 Gumballs (76mm, 75a) FLEX: _______$ 298.00$ 158.00$ -
Sheet1
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,304
Office Version
  1. 365
Platform
  1. Windows
Doug

What data do you actually want to store?

What data is common between the suppliers?

Are you always dealing with the same suppliers?

If you were to move to Access I would suggest at least 2 tables - one with details of the supplier and another for, say, stock.

PS I've actually thought numerous times of suggesting that you switch to Access, but I've never really been able to quite work out an overview of what you are actually doing.
 

DougStroud

Well-known Member
Joined
Aug 16, 2005
Messages
2,966

ADVERTISEMENT

Hi Norie,
The specifics are Product number, Product name, Color, Sizing, Price. No unfortunately the suppliers will change like the weather, adding, deleting and intermittent.
Changing to Access prior probably would not have worked. As your commented about getting an overview of what I am doing... I am working out these things as I go along and Access is a natural progression, at least my feeling on it, I might be totally wrong though.
That is an interesting thought, a table for the supplier details-- that is. That might not be too important. This data will be used almost exclusively internal, but might be worthwhile as I progress the project.

For background information purposes: I deal w/ hundreds of suppliers several times a year w/ products changing every quarter and not much reorder opportunity-- so always changing.

So when you have more time, if you can share your thoughts, I am grateful for your advice and time.

Doug
 

DougStroud

Well-known Member
Joined
Aug 16, 2005
Messages
2,966
Norie,
And BTW: I will be pulling the data back into Excel once it is stored to either write orders, import into my point of sale software or update my online store....
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,304
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Doug

I'm still unclear on what you are actually doing, and I've read most of your posts here and elsewhere.:)

I understand you are getting data from different sources in different formats, but I'm still not sure exactly what you are doing with the data.

By the way if you do move to Access why would you need to pull the data into Excel?

Does your POS software or online store require that you use Excel?

Another by the way, a lot of the things you've posted on before could perhaps have been achieved in Access using simple queries rather than code, though code would probably be needed at some point if the structure of the data is as diverse as it seems.
 

DougStroud

Well-known Member
Joined
Aug 16, 2005
Messages
2,966
I understand you are getting data from different sources in different formats, but I'm still not sure exactly what you are doing with the data.
I need to do many different things w/ the data, 1) populate my point of sale software w/ the items ordered through elimination of data entry, 2) a reference database of the items and pertinent data, item number, price etc..., 3) write immediate and reorder purchase orders, 4) populate my online store database, which is a completely different data structure, and all this is done w/ Excel.

So an example of how I see myself using the data would be:
Receive the order form from a company (which is the basis for populating my database), it has say 100 items that could be ordered from it, I might order 10 items now, and some later for reorders or for new items. Once the items arrive, which can be immediately or could be 6 months later, as we have to pre-order a great deal many things, the items must be entered into our point of sale. I do not want to rely on where the order form resides to gather the data, it could have been written on my computer from home, a computer at work or my laptop. I want to upload the data to one centrally located file. This contains all the items available from the company including the ones I did not order, so pricing and colors and sizing are immeditately available, I spend an inordinate amount of time tracking this stuff down manually, "the amount of wasted time and resources is killing me". Then my stock person will have all the necessary data to correctly create the stock item in our POS.

By the way if you do move to Access why would you need to pull the data into Excel?
One reason that I need to pull the data back into Excel is that I want a standard form to submit my orders on to the companies. This cannot be an Access file, the industry I am in is lucky enough just to use Excel.


Does your POS software or online store require that you use Excel?
Now to populate the POS (point of sale) I work outside the normal program for data entry interface for efficency and correctness, my POS is DOS based using Cobol and I have had since 1988. I have written a procedure in Excel that helps eliminate typing errors, incorrect department and category placement, pricing to name a few. This procedure creates the item record, item name, qty, cost, price, dept, cat, and imports the item record directly. This has to be done in a Csv file format.
The online store-- yes. The interface that is built is an Excel sheet.

Thanks....

Doug
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,304
Office Version
  1. 365
Platform
  1. Windows
Doug

You can easily create CSV files using Access.
 

Forum statistics

Threads
1,141,705
Messages
5,707,973
Members
421,539
Latest member
zuniBM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top