Format data to columns

[MS Access Scheduler Database]

I have a table named X that contains person's blood pressure reading dates and
sys/dia results. Many may have up to 3 BP readings. I would convert the
data grouped by PersonID to show the BP reading as BPdate1, BPDate2, BPdate3
and BP readings in a row.

Table example:
PersonID BPDate sys dia
1 4/3/2009 130 70
1 5/24/2009 145 80
2 4/22/2009 136 90
2 6/10/2009 136 90
2 9/23/2009 136 90


Result example:

Person ID BPDate1 sys1 dia1 BPdate2 sys2 dia2 BPDate sys3
dia3
1 4/3/2009 130 70 5/24/2009 145 80
2 4/22/2009 136 90 6/10/2009 136 90
9/23/2009 136 90

Appreciate any help.

 

[Duane Hookom]

You can get results like this with a multiple column subreport on a main
report based on the distinct PersonID only. If you really want to do this in
a query you need to first create a column that numbers the BPs based on
date. Then you create crosstab with multiple values. The multiple value
crosstab instructions can be found at
http://www.tek-tips.com/faqs.cfm?fid=4524.

The easiest is the report. If you really want to do this in a query then
come back for instructions. Is your table really named "X" or do you want to
provide your actual table name?

Duane Hookom
MS Access MVP

"MS Access Scheduler Database"