DB Normalization Issue "Ravenmike" <u27275[ at ]uwe> 28.09.2006 21:11:13 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.

RE: DB Normalization Issue KARL DEWEY 28.09.2006 22:11:02 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" wrote: [Quoted Text] > 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. > >

RE: DB Normalization Issue "Ravenmike via AccessMonster.com" <u27275[ at ]uwe> 29.09.2006 16:05:26 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 DEWEY wrote: [Quoted Text] >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. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/200609/1

RE: DB Normalization Issue KARL DEWEY 29.09.2006 16:28:02 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" wrote: [Quoted Text] > 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 DEWEY wrote: > >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. > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/200609/1 > >