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"

 

[KenSheridan via AccessMonster.com]

Try the following. Don't be confused by the name of the FIRST operator; it
doesn't really do what it says on the tin, but in this case I think it should
do what's intended as the values which it returns are governed by the nature
of the outer joins rather than any aggregating property of the operator
itself. You should test the query very carefully against your data, however,
to be satisfied it reliably returns what's expected:

SELECT X1.PersonID,
FIRST(X1.BPDate) AS BPDate1,FIRST( X1.sys) AS sys1, FIRST(X1.dia) As dia1,
FIRST(X2.BPDate) AS BPDate2, FIRST(X2.sys) AS sys2,FIRST(X2.dia) As dia2,
FIRST(X3.BPDate) AS BPDate3, FIRST(X3.sys) AS sys3, FIRST(X3.dia) As dia3
FROM (X AS X1 LEFT JOIN X As X2 ON X1.PersonID = X2.PersonID
AND X1.BPDate < X2.BPDate)
LEFT JOIN X As X3 ON X2.PersonID = X3.PersonID
AND X2.BPDate < X3.BPDate
GROUP BY X1.PersonID;

Ken Sheridan
Stafford, England

 

[KenSheridan via AccessMonster.com]

PS: For an example of a multi column subreport of the type Duane describes
see:

http://community.netscape.com/n/pfx...libraryMessages&webtag=ws-msdevapps&tid=24271


The file also includes a single report solution in which the layout of the
report is amended in code at runtime to give the same effect. Using a
subreport is far easier, however. The code solution was only included to
demonstrate that in could be done that way in response to a reader's enquiry
for a magazine column written by a contact of mine.

Ken Sheridan
Stafford, England

 

[KenSheridan via AccessMonster.com]

As you'll have seen from my reply to John Spencer I wasn't convinced of the
reliability of the query I'd posted in my first reply, which is why I
stressed the need for thorough testing. Arising from another thread I did
identify a situation in which the query would fail, and was able to break it.
So its not reliable, and shouldn't be used.

The reason is that the FIRST operator doesn't do what you might think and
return the first date in order. I realise now that this was what John was
getting at, but I wasn't using it to do that in fact. I'd tried to determine
the order of the dates returned by the join criteria, and use of the FIRST
operator was arbitrary.

It turns out however that in some situations, while maintaining the correct
date order across the columns, one of the dates and the sys and dia values
accompanying it are not returned at all. This doesn't surprise me greatly,
and in retrospect I should have expressed my doubts more in my first post.
As you see from my reply to John it can be done reliably by creating three
separate queries and then joining them, but with a large number of rows in
the table performance would not be good. Note also that it does rely on
there being no more than three rows per personID, which your first post said
was the case, but John has referred to another thread of yours which seemed
to throw some doubt on this.

I had hoped it could be done efficiently by a join, but if so I've been
unable to find a reliable way. Doing it in a report/subreport makes far more
sense and is very easy to do as the demo file to which I posted a link shows.
It also allows any number of rows per personID rather than a maximum of three.


Ken Sheridan
Stafford, England