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
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
