Best way to normalize and store data

[DougStroud]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
6	SHOP:									PHONE:
7	ADDRESS:									ORDERED BY:
8										P.O.#									SHIP DATE:
9	PRICE SHEET / SUMMER 2006 / DOMESTIC
10	MENS SUMMER 2006 SHOE STYLES
11	STYLE	COLOR	CODE	ITEM #	NEW-RE-C/O	AVAIL	5	6	6	7	7	8	8	9	9	10	10	11	11	12	13	14	15	UNITS	PRICE	TOTAL
12	TAYLOR SECT	BLACK LEATHER	BLKF	S/TAYLOR SECT	NEW	5/10						1	1	1	1	2	1	2	1	1				11	$40.00	$ 440.00
13	TAYLOR	WHITE LEATHER	WHIT	S/TAYLOR SM	NEW	5/10																		-	$36.00	$ -
14	TAYLOR	BLACK NUBUCK	BLKT	S/TAYLOR SM	NEW	5/10						1	1	1	1	1	1	1	1	1				9	$36.00	$ 324.00
SHOES PAGE 1

 

[DougStroud]

2nd Example:

0716%OrderForm.xls
	A	B	C	D	E	F	G	H	I	J
1	Dealer Name			PO Number
2	0			0
3
4	UPC#	Item	NP	Description	Cost	16%	QTY	Total	Rubber	Accessory
5
6	748610160218	62001575	NP	ABS/PLAT 135 07-ZEUS/HGE S	435.00	435.00		$ -
7	748610160225	62001576	NP	ABS/PLAT 135 07-ZEUS/HGE M	435.00	435.00		$ -
8	748610160232	62001577	NP	ABS/PLAT 135 07-ZEUS/HGE L	435.00	435.00		$ -
9	748610160249	62001578	NP	ABS/PLAT 135 07-ZEUS/HGE XL	435.00	435.00		$ -
10	748610160256	62001579	NP	ABS/PLAT 141 07-ZEUS/HGE L	435.00	435.00		$ -
11	748610160263	62001580	NP	ABS/PLAT 141 07-ZEUS/HGE XL	435.00	435.00		$ -
12	748610160270	62001581	NP	ABS/PLAT 141 07-ZEUS/HGE XXL	435.00	435.00		$ -
13	748610160287	62001582		ABS/PLAT 135 07-BLANK W/FINS	384.00	322.56		$ -
14	748610160294	62001583		ABS/PLAT 141 07-BLANK W/FINS	384.00	322.56		$ -
15	748610160300	62001584		ABS/PLAT 135D07-BLANK W/FINS	353.28	296.76		$ -
16	748610160317	62001585		ABS/PLAT 141D07-BLANK W/FINS	353.28	296.76		$ -
17	748610160324	62001586	NP	ABSOLUTE 135 07-FUSE S	350.00	350.00		$ -
07CWB

 

[DougStroud]

Loaded Skateboards 08-08-06.xls
	A	B	C	D	E	F	G	H	I	J	K
10	Bill To:					Ship To:
11	EastCoast Boardco.
12
13
14
15
16	email:					email:
17	phone #					phone #
18	fax #
19
20			Payment Terms	Pmt. Method		Check #	Ship Via	FOB	Invoice #
21			N-30				UPS	Los Angeles
22	Item		Description			MSRP	Qty	Wholesale	Amount
23	Vanguard Bamboo Complete		Vanguard Flex 5 (Lime Logo), 80-140+ lbs, L: 38 WB:31"			$ 264.00		$ 140.00	$ -
24			Vanguard Flex 4 (Black Logo), 120-170+ lbs, L: 38" WB: 31"			$ 264.00		$ 140.00	$ -
25			Vanguard Flex 3 (Maroon Logo), 150-200+ lbs, L: 38" WB: 31"			$ 264.00		$ 140.00	$ -
26			Vanguard Flex 2 (Blue Logo), 150-210+ lbs, L: 42" WB: 35"			$ 264.00		$ 140.00	$ -
27			Vanguard Flex 1(Green Logo), 175-230+ lbs, L: 42" WB: 35"			$ 264.00		$ 140.00	$ -
28	Vanguard Bamboo Premium		With Abec 11 Gumballs (76mm, 75a) FLEX: _______			$ 298.00		$ 158.00	$ -
Sheet1

 

[Norie]

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]

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]

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]

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]

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]

Doug

You can easily create CSV files using Access.