Query / Pivot table question

[mmohon]

I have a table that looks like this:

patientNo diagNo diagCode
------------------------------------------------
1 dx1 2500
1 dx2 V358
2 dx1 4010


I want to rearrange it to look more like:

patientNo dx1 dx2
-------------------------------------------------
1 2500 V358
2 4010 (blank)

Not all patients have the same number of DiagNo's occuring, one might
have 3, one might have 23.

So far I have this SQL statement

SELECT patientNo, diagCode as DX1
FROM mytable
WHERE diagNo="DX1"

I figured it would be followed by

UNION ALL
SELECT diagCode as DX2
FROM mytable
WHERE diagNo="DX2"

but that doesn't work.
anyone got any suggestions for me??

 

[David Cressey]

I have a table that looks like this:

patientNo diagNo diagCode
------------------------------------------------
1 dx1 2500
1 dx2 V358
2 dx1 4010


I want to rearrange it to look more like:

patientNo dx1 dx2
-------------------------------------------------
1 2500 V358
2 4010 (blank)

Not all patients have the same number of DiagNo's occuring, one might
have 3, one might have 23.

So far I have this SQL statement

SELECT patientNo, diagCode as DX1
FROM mytable
WHERE diagNo="DX1"

I figured it would be followed by

UNION ALL
SELECT diagCode as DX2
FROM mytable
WHERE diagNo="DX2"

but that doesn't work.
anyone got any suggestions for me??

Try using the "crosstab query wizard". A crosstab query in Access is like a
pivot table in Excel.

 

[mmohon]

Sweet, that worked like a charm