Take data from columns and put in rows

[skipit]

Hello all,

I have a table that is imported from some test software we use. We do a test of three parts with a reading for each part which would make a Series of three. See below..

PartNum Reading SeriesNumber SubSeriesNum RecordDate
G1234 150.5 2212 1 10/20/03
G1234 154.6 2212 2 10/20/03
G1234 152.4 2212 3 10/20/03
R2222 200.2 5551 1 10/21/03
R2222 205.4 5551 2 10/21/03
R2222 204.1 5551 3 10/21/03

The PartNum is our identifier for our part we test. The Reading is our result for said part. The SeriesNumber is a number generated by the testing software, it is the same for all three test results for the Part. The SubSeriesNum is the number of the test in the set of three I.E. 1 means the first Reading, 2 means the second Reading and 3 means the third Reading for the Part. The RecordDate is an auto date I setup in Access.

I know this may seem redunent but the testing software we use will not export the three Readings to one row thus we have three rows for one SeriesNumber. In order to import these readings into our Statisical software I am trying to use three qrys to get the readings. The Stat software will only accept the readings if they are in a row with the PartNumber and other info in the same row. (The way the info is exported to Access it is in columns.)

So my first qry will look like this pulls the first reading from the table:

PartNum Reading SeriesNumber SubSeriesNum RecordDate
G1234 150.5 2212 1 10/20/03
R2222 200.2 5551 1 10/21/03

Second qry, this pulls the second reading from the tbl:

PartNum Reading SeriesNumber SubSeriesNum RecordDate
G1234 154.6 2212 2 10/20/03
R2222 205.4 5551 2 10/21/03

Third qry, this pulls the third reading from the tbl:

PartNum Reading SeriesNumber SubSeriesNum RecordDate
G1234 152.4 2212 3 10/20/03
R2222 204.1 5551 3 10/21/03

Now I make a fourth qry. In design view I add the three qrys above and join them all by SeriesNumber and RecordDate. My thinking by doing this is that it will show me the readings for the part number with the same SeriesNumber in one row. Similar to below:

PartNum Reading1 Reading2 Reading3 SeriesNum RecordDate
G1234 150.5 154.6 152.4 2212 10/20/03
R2222 200.2 205.4 204.1 5515 10/21/03

When I run this fourth qry instead of getting the two rows shown above from the six rows of the table I get about 20 rows with combinations of the readings mixed up not grouping by the series number or date.

I hope I explained this ok I reread it and it makes sense to me but I have been looking at the qrys for a few days now. If anyone can help me out or point me in the right direction I will be grateful. I am not sure this is the way to go but I need the three readings in three seperate fields in a row not in columns.

Thanks in advance,
Skip

 

[namliam]

If you are only doing above recordset, with above 3 querys... impossible. Why?

3 query's produce 2 records each, even without linking the queries you would get 2^3 = 8 records like so:
1 1 1
1 1 2
1 2 1
1 2 2
2 1 1
2 1 2
2 2 1
2 2 2

There would have to be another table involved and or more records. So please elaborate...

Maybe post a sample DB including the table and query with some test data in it...

Regards

The Mailman

 

[skipit]

Attached DB

Mailman and others,

Thanks for looking at this. I guess I did not explain very well. I have attached the requested zipped db A97 version. I removed all info that was not needed. I have tblGMT800 which is my table that the testing software uses to export the "Readings" which in the table are called 50% Corrected. There are three qrys named qryGMT800Result1, qryGMT800Result2, qryGMT800Result3 these retrieve the number 1 reading for the series, number 2 reading for the series and the number 3 reading for the series respectively. The final qry is the qryGMT800ALLResults this is the one I am trying to have pull the readings togehter into one row per part number. I have deleted the links I talked about in my previous post because they did not seem to work. I removed the PartNum field from the qryGMT800ALLResults as well.

I thank you for reviewing this. Let me know if I am close to the right path or is there some other way I can do this.

Thanks Skip

 

[namliam]

I dont know how you were linking but this seems to work
SELECT qryGMT800Result1.[_50_Corrected], qryGMT800Result2.[_50_Corrected], qryGMT800Result3.[_50_Corrected]
FROM (qryGMT800Result2 INNER JOIN qryGMT800Result3 ON qryGMT800Result2.Series_number = qryGMT800Result3.Series_number) INNER JOIN qryGMT800Result1 ON qryGMT800Result2.Series_number = qryGMT800Result1.Series_number;

Also maybe a pivot table is more what your looking for, try this:
TRANSFORM Sum(tblGMT800.[_50_Corrected]) AS SumOf_50_Corrected
SELECT tblGMT800.JCI_Part_
FROM tblGMT800
WHERE (((tblGMT800.[No]) Is Not Null))
GROUP BY tblGMT800.JCI_Part_
PIVOT tblGMT800.[No];

Then again... Dont use _ or any other special characters in names this will lead you down the "Dark side" (eg bad practice and such)

Regards

The (ever helpfull) Mailman

 

[skipit]

Mailman,

Thanks for the help. I will try it and see what it does.

As for the field names that is something the test software does. I am in the process of changing this over in the testing software but having 300 or more test files to change takes a while.

The test software is German made so getting in and changing things takes a little detective work to see what their terms mean.

Anyway thanks for the help, will post back if I need more help.

Thanks,
(The ever needing help.....too bad my comes from the guys with the long white coats and the straight jackets!!)
Skip

 

[namliam]

Happy to help, would be nice to know what way you went...

Regards

 

[skipit]

Mailman,

I used your first example. It does exactly what I needed. I added more fields to the qry, of some data I needed but left out of my post for typing reasons. I tried the pivot table but it gave me totals(may have set it up wrong). I have never messed with pivot tbls before. I think I am going to keep playing with it to see how it works and if I can adjust it for my needs. I am scanning through the MS knowledge db to find an article on pivot tables and after I post this reply I am going to run a search on pivot tbls.

I am not sure where I kept going wrong because I had the inner join thing going on but I guess I didn't join it properly. I plugged in your example and bingo there it was. Once again thanks for your help!!!!!!!

Thanks Skip

 

[namliam]

Keep hacking at that CrossTab, depending on what you specific needs are the CrossTab is a verry powerfull tool. Verry much worth understanding.... (even more powerfull in Excel but OK)

Good luck