Hi,
I’m using Access 2003 and need a little help with a query. Please excuse my ignorance (I’m not too hot with access and certainly not SQL), looked in the guides etc, have tried searching the forums and found myself here.

I have a data set: Top row are field names.

"Sample-ID" "Analysis-code" "Qualifier" "Result"
1 0010 > 50
1 0020 > 0.22
1 0030 > 5000
2 0010 < 75
2 0020 < 0.85
2 0030 > 450
3 0010 > 80
3 0020 > 0.85
3 0030 > 225

I need to arrange the data so each sample has all the results associated with it like this. Effectively the column headings produced would be the sample-ID the qualifier / result for each of the analytes associated with it.

"Sample-ID" "Qualifier" "0010" "Qualifier" "0020" "Qualifier" "0030"
1 > 50 > 0.22 > 5000
2 < 75 < 0.85 > 450
3 > 80 > 0.85 > 225

The qualifier needs to be associated with the result but in it’s own column.

So far I’ve tried a crosstab query but could only get the results for each analyte but not a column showing the associated qualifier.

I’ve also made a query to concatenate the qualifier with the result and then crosstabed that to get the appearance of what I need but obviously, the results where there is a > or < are now text fields and I need to be able to do calculations on the resulting table.

Has anyone any suggestions please!!

Many thanks,
Pete

Look at "DemoCrosQA2000.mdb" (attachment, zip).
Run "Query1Crosstab".
I think it is what you need.

Hi MSte,

Thanks for the suggestion.
It's half the way but the Qualifier (less or greater than sign) is effectively attached to the result in your solution.

I need the qualifier to be in a column by the side of the result it refers to so I can actually use the results for calculations.

Thanks anyway,
Regards,
Pete

OK, Look at a New mdb (attachment, zip).
Run "Query1"
Important; "Qualifier 0010", "Q..0020", "Q..0030", (in Query1), Format is FIXED.
tblNew1 ignore.

Hi MStef,
Thank you so much!
Kind regards,
Pete