DB Normalization Issue

  • Thread starter Thread starter Ravenmike
  • Start date Start date
R

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


KARL said:
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.
Click to expand...
[quoted text clipped - 23 lines]
relationship...
Any help would be much appreciated.
Click to expand...
Click to expand...
 
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.

Ravenmike via AccessMonster.com said:
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?


KARL said:
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.
Click to expand...
[quoted text clipped - 23 lines]
relationship...
Any help would be much appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top