how would you normalize this table?

[casey]

please, i need help, a user have been maintaining an excel spreadsheet and i
need to move them into access. i need some guidance and advice on how to
properly create re-create the table(s).

this is how the excel looks like:

prod_id prod_desc bus_line1 bus_line2 bus_line3 bus_line4
and more
1 a 1 0
0 0 0
2 b .5 .5
0 0 0
3 c .25 .25 .25
..25 0
4 d 0 0
0 0 1
and more

bus_line = contains assigned percentages

i tried to run the Table Analyzer but it was no help. if anyone have an
idea the best way to create multiple tables based on above sample. thank
you!
casey

 

[Joseph Meehan]

please, i need help, a user have been maintaining an excel
spreadsheet and i need to move them into access. i need some guidance and
advice on how to properly create re-create the table(s).

this is how the excel looks like:

prod_id prod_desc bus_line1 bus_line2 bus_line3 bus_line4
and more
1 a 1 0
0 0 0
2 b .5 .5
0 0 0
3 c .25 .25 .25
.25 0
4 d 0 0
0 0 1
and more

bus_line = contains assigned percentages

i tried to run the Table Analyzer but it was no help. if anyone
have an idea the best way to create multiple tables based on above
sample. thank you!
casey

Can you tell us what those things are and how they are related. I don't
have the foggiest idea what a "bus_line1" is.

 

[Dirk Goldgar]

please, i need help, a user have been maintaining an excel
spreadsheet and i need to move them into access. i need some
guidance and advice on how to properly create re-create the table(s).

this is how the excel looks like:

prod_id prod_desc bus_line1 bus_line2 bus_line3
bus_line4 and more
1 a 1 0
0 0 0
2 b .5 .5
0 0 0
3 c .25 .25
.25 .25 0
4 d 0 0
0 0 1
and more

bus_line = contains assigned percentages

i tried to run the Table Analyzer but it was no help. if anyone
have an idea the best way to create multiple tables based on above
sample. thank you!
casey

Without more information, it looks like you'd have a table of "Prods" --
Products? -- with the fields Prod_ID and Prod_Desc, and another table
"ProdBusLines" with the fields ProdID (foreign key to Products), LineID,
and LineValue. The primary key of ProdBusLines would be a compound key
comprising the two fields ProdID and LineID; or you might use a
separate, autonumber primary key if that's more convenient. But you
haven't really given us enough information about what real-life entities
these tables are representing to be sure of any of this.

 

[casey]

prod_id have several business_line associated with it and each have an
allocated percentage (all equal to 100%)

prod_id
bus_line1
bus_line2
bus_line3
bus_line4
etc....up to 30

NOTE: bus_line is not really what its called in the excel file, I only used
for this purpose, so its easy to understand.

sample again:
=============
prod_id = 1
bus_line1 = 0
bus_line2 = 1
bus_line3 = 0
bus_line4 = 0
prod_id = 2
bus_line1 = .5
bus_line2 = .25
bus_line3 = .25
bus_line4 = 0
prod_id = 3
bus_line1 = 0
bus_line2 = 0
bus_line3 = 0
bus_line4 = 1

 

[John Vinson]

please, i need help, a user have been maintaining an excel spreadsheet and i
need to move them into access. i need some guidance and advice on how to
properly create re-create the table(s).

this is how the excel looks like:

prod_id prod_desc bus_line1 bus_line2 bus_line3 bus_line4
and more
1 a 1 0
0 0 0
2 b .5 .5
0 0 0
3 c .25 .25 .25
.25 0
4 d 0 0
0 0 1
and more

bus_line = contains assigned percentages

This got wordwrapped pretty badly, and it's a bit hard to tell what's
what - but at a guess, you need three tables:

Products
Prod_ID <Primary Key>
Prod_Desc <text>
<other information about the product as an entity>

BusLines
BusLineNo <Integer Primary Key, 1 - 4 at the moment>
<information about the bus line, e.g. termini>

BusLineFraction
Prod_ID <link to Products>
BusLineNo <link to BusLines>
Fraction <Number Single>

You can populate these tables using a "Normalizing Union Query" like

SELECT DISTINCT Prod_ID, Prod_Desc
FROM exceltable
INTO Products;

SELECT Prod_ID, (1) As BusLineNo, Bus_Line1
FROM exceltable
WHERE Bus_Line1 IS NOT NULL
UNION ALL
SELECT Prod_ID, (2) As BusLineNo, Bus_Line2
FROM exceltable
WHERE Bus_Line1 IS NOT NULL
UNION ALL
SELECT Prod_ID, (3) As BusLineNo, Bus_Line3
FROM exceltable
WHERE Bus_Line1 IS NOT NULL
UNION ALL
SELECT Prod_ID, (4) As BusLineNo, Bus_Line4
FROM exceltable
WHERE Bus_Line1 IS NOT NULL;

Save this query and base an Append query on it.

John W. Vinson[MVP]