select statement with conditions for each field

[Guest]

I have an access table with laboratory test results. The table consists of
three fields(see below for a sample table):
Sample Number, Lab test ID, and Test Result.
1 1 54
1 1 65
1 1 69
1 2 45
1 2 48
1 2 44
1 3 89
1 3 85
1 3 87
For one of our tests multiple results can be entered for that test and these
results are used to compile a graph and other info.

Is it possible to query the above table so that results (for a particular
sample number) for test 1 are in column 1,result for test 2 in column 2 etc.
(see below for example)
Lab test id 1 lab test id 2 lab test id 3
54 45 89
65 48 85
69 44 87

Thanks in advance for your help

 

[John W. Vinson]

Is it possible to query the above table so that results (for a particular
sample number) for test 1 are in column 1,result for test 2 in column 2 etc.
(see below for example)
Lab test id 1 lab test id 2 lab test id 3
54 45 89
65 48 85
69 44 87

A Crosstab query will do this for you. Use the query crosstab wizard, and use
First as the aggregate function (it'll try to get you to sum the lab test
results).

John W. Vinson [MVP]

 

[Guest]

There may be a better way but try these two queries --
Awill_1 --
SELECT Awill.[Sample Number], Awill.[Lab test ID], Awill.[Test Result],
(SELECT Count(*)
FROM Awill As X
WHERE X.[Lab test ID] = Awill.[Lab test ID]
And X.[Test Result] <= Awill.[Test Result]) AS Rank
FROM Awill;

SELECT IIf([Awill_1].[Rank]=1,[Awill_1].[Test Result],Null) AS [Lab test id
1], IIf([Awill_1_1].[Rank]=2,[Awill_1_1].[Test Result],Null) AS [Lab test id
2], IIf([Awill_1_2].[Rank]=3,[Awill_1_2].[Test Result],Null) AS [Lab test id
3]
FROM (Awill_1 INNER JOIN Awill_1 AS Awill_1_1 ON (Awill_1.[Lab test ID] =
Awill_1_1.[Lab test ID]) AND (Awill_1.[Sample Number] = Awill_1_1.[Sample
Number])) INNER JOIN Awill_1 AS Awill_1_2 ON (Awill_1.[Lab test ID] =
Awill_1_2.[Lab test ID]) AND (Awill_1.[Sample Number] = Awill_1_2.[Sample
Number])
WHERE (((IIf([Awill_1].[Rank]=1,[Awill_1].[Test Result],Null)) Is Not Null)
AND ((IIf([Awill_1_1].[Rank]=2,[Awill_1_1].[Test Result],Null)) Is Not Null)
AND ((IIf([Awill_1_2].[Rank]=3,[Awill_1_2].[Test Result],Null)) Is Not Null));

 

[Guest]

Thanks for the reponse. I tried both but couldn't get it to work. I played
with the choose function and managed to get the query. The query I made is :

SELECT SampleID, TestID, choose(TestID,Result,"",""),
choose(TestID,"",Result,""), choose(TestID,"","",Result) FROM Database_2006
WHERE SampleID='20' and (TestID=1 Or TestID=2 Or TestID=3);

Thanks again for the help.

There may be a better way but try these two queries --
Awill_1 --
SELECT Awill.[Sample Number], Awill.[Lab test ID], Awill.[Test Result],
(SELECT Count(*)
FROM Awill As X
WHERE X.[Lab test ID] = Awill.[Lab test ID]
And X.[Test Result] <= Awill.[Test Result]) AS Rank
FROM Awill;

SELECT IIf([Awill_1].[Rank]=1,[Awill_1].[Test Result],Null) AS [Lab test id
1], IIf([Awill_1_1].[Rank]=2,[Awill_1_1].[Test Result],Null) AS [Lab test id
2], IIf([Awill_1_2].[Rank]=3,[Awill_1_2].[Test Result],Null) AS [Lab test id
3]
FROM (Awill_1 INNER JOIN Awill_1 AS Awill_1_1 ON (Awill_1.[Lab test ID] =
Awill_1_1.[Lab test ID]) AND (Awill_1.[Sample Number] = Awill_1_1.[Sample
Number])) INNER JOIN Awill_1 AS Awill_1_2 ON (Awill_1.[Lab test ID] =
Awill_1_2.[Lab test ID]) AND (Awill_1.[Sample Number] = Awill_1_2.[Sample
Number])
WHERE (((IIf([Awill_1].[Rank]=1,[Awill_1].[Test Result],Null)) Is Not Null)
AND ((IIf([Awill_1_1].[Rank]=2,[Awill_1_1].[Test Result],Null)) Is Not Null)
AND ((IIf([Awill_1_2].[Rank]=3,[Awill_1_2].[Test Result],Null)) Is Not Null));

--
KARL DEWEY
Build a little - Test a little

I have an access table with laboratory test results. The table consists of
three fields(see below for a sample table):
Sample Number, Lab test ID, and Test Result.
1 1 54
1 1 65
1 1 69
1 2 45
1 2 48
1 2 44
1 3 89
1 3 85
1 3 87
For one of our tests multiple results can be entered for that test and these
results are used to compile a graph and other info.

Is it possible to query the above table so that results (for a particular
sample number) for test 1 are in column 1,result for test 2 in column 2 etc.
(see below for example)
Lab test id 1 lab test id 2 lab test id 3
54 45 89
65 48 85
69 44 87

Thanks in advance for your help