DB Normalization Issue

[Ravenmike]

I have a problem trying to normalize data that has been imported from an
Excel spreadsheet. I am hoping someone can point me in the right direction.

The sheet has a fuel type column, and a corresponding price per gallon(PPG)
column. There are 10 sets of these columns (Type 1-10 & PPG 1-10)
Each fuel "type" indicates a fuel quanity to be purchased to receive the
corresponding PPG.

Each record (vendor) has multiple types (Type 1 =1, Type 2=200), and each
vendor can be different...(Type 1 for vendor A could be 1 while Type 1 for
vendor B could be 200)

I am trying to set up a query that will show PPG comparisions between vendors
for a given "type", but the types vary greatly between vendors...and the
comparisons are not proper (apples to oranges)

For example:
Vendor Type1 PPG1 Type2 PPG2
A 1 2.00 100
1.95
B 200 1.95 400 1.
80

What can I do to normalize the data to allow for better reporting. The
vendors have already been set in a different table with a one to many
relationship...
Any help would be much appreciated.

 

[Guest]

Use a union query to normalize you data into a new table.

SELECT Ravenmike.Vendor, Ravenmike.Type1, Ravenmike.PPG1
FROM Ravenmike
UNION SELECT Ravenmike_1.Vendor, Ravenmike_1.Type2, Ravenmike_1.PPG2
FROM Ravenmike AS Ravenmike_1;

 

[Ravenmike via AccessMonster.com]

Karl,
Thanks for the info. I had not thought of a Union query...
But one question. Your SQL statement looks like it is pulling from one table.
The sets of Type-PPG are all on a single table.
Do I need to seperate the Type-PPG sets into seperate tables, with
relationships to the vendors?

Use a union query to normalize you data into a new table.

SELECT Ravenmike.Vendor, Ravenmike.Type1, Ravenmike.PPG1
FROM Ravenmike
UNION SELECT Ravenmike_1.Vendor, Ravenmike_1.Type2, Ravenmike_1.PPG2
FROM Ravenmike AS Ravenmike_1;

I have a problem trying to normalize data that has been imported from an
Excel spreadsheet. I am hoping someone can point me in the right direction.

[quoted text clipped - 23 lines]

relationship...
Any help would be much appreciated.

 

[Guest]

My query is how you would pull the data from your current table into one that
looks like this.
Vendor Type1 PPG1
A 1 2

Your also could append the data using multiple queries, one for each
type/ppg column combination.

Karl,
Thanks for the info. I had not thought of a Union query...
But one question. Your SQL statement looks like it is pulling from one table.
The sets of Type-PPG are all on a single table.
Do I need to seperate the Type-PPG sets into seperate tables, with
relationships to the vendors?

Use a union query to normalize you data into a new table.

SELECT Ravenmike.Vendor, Ravenmike.Type1, Ravenmike.PPG1
FROM Ravenmike
UNION SELECT Ravenmike_1.Vendor, Ravenmike_1.Type2, Ravenmike_1.PPG2
FROM Ravenmike AS Ravenmike_1;

I have a problem trying to normalize data that has been imported from an
Excel spreadsheet. I am hoping someone can point me in the right direction.

[quoted text clipped - 23 lines]

relationship...
Any help would be much appreciated.