Transpose table

[jabarlee]

Hi everyone.



I have the following table:
tbl_LabResults

Result_ID	Result_Date	Patient_ID	LabTest_ID	LabTest_Units	LabTest_Value
1	Date_1	Pt1	Test1	yyyy	6,7
2	Date_2	Pt2	Test2	xxxx	8,5
3	Date_3	Pt1	Test2	xxxx	4,4
4	Date_2	Pt3	Test3	zzzz	3,2

What I would like to do is to transform it like that:

Result_ID	Patient_ID	LabTest_ID	LabTest_Units	Date_1	Date_2	Date_3
1	Pt1	Test1	yyyy	6,7
2	Pt2	Test2	xxxx		8,5
3	Pt1	Test2	xxxx			4,4
4	Pt3	Test3	zzzz		3,2

By searching a little, I understand that I should use a normalizing UNION ALL query and a TRANSFORM query, probably with First, or something like that.
I have tried to make it work, with no success.
Could anyone help ?

[ranman256]

that sounds like the way to to do it.
did you 1st get the Union query to work?

once you do that , then make the crosstab query (that uses the Union Qry as source). Use the qry wizard.

once that is done, alter the Crosstab query and make sure its set to FIRST, instead of Sum.

[isladogs]

UNION queries are only needed where you want to 'combine' the results from separate tables so not needed here.
A crosstab will get what you want assuming the commas in lab test value are decimal separators and not multivalue fields.

Use the first four columns as your column heading fields. Result_Date as your row heading and lab_test_value as your Values using First as you suggested
The crosstab query wizard will make this easy to do.

[jabarlee]

Thank you for your answers.

Colin, I assume that you meant to use the first (three, it does not allow more than 3) columns as row headings and Result_Date as column heading ? I tried that and it does return the result I am looking for, at least it seems so.

Since I cannot use more than 3 row headings I'll use a query to concatenate LabTest_ID and LabTest_Units

[isladogs]

The wizard only allows three row headings but you can add more in query design view.
AFAIK there is no limit if done that way

[jabarlee]

Yay! and thanx again!