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"
<MSAccessSche...@discussions.microsoft.com> wrote in message
news:BAC02CF7-B127-4C9C...@microsoft.com...

[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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201001/1

[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/forum.aspx?tsn=1&nav=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

--
Message posted via http://www.accessmonster.com

[John Spencer]

I wonder if that will work as expected. For one thing there is no guarantee
that the first date returned for X1 is going to be the earliest date for that
person. I think you could end up with (in theory) the same row for all three
sets of readings.

I proposed the following solution to an earlier posting by MS Access Scheduler
Database. I got no feedback on whether or not it did (or did not) work. The
original posting did say it wanted the top 3 out of 1 to many (implying more
than 3 was possible).

Step 1 as proposed by Jerry Whittle

SELECT X1.PersonID,
X1.BPDate,
X1.sys,
X1.dia
FROM X AS X1
WHERE X1.BPDate In
(SELECT TOP 3 X2.BPDate
FROM X AS X2
WHERE X2.PersonID=X1.PersonID
ORDER BY X2.BPDate DESC)
ORDER BY 1,2 DESC;

My addition to Jerry Whittle's post was:

The easiest way to handle this in a report would be to use a sub-report to
show the results. The main report would show the personId and not have any
other fields in it. Based on your table "X" the source for the main report
would be

SELECT Distinct PersonID
FROM X

The sub-report would use the query that Jerry Whittle has proposed (without
the final Order By clause. Set up the subreport as a multi column Across then
down (3 columns) report with the three fields BPDate, Sys, and Dia.

If you really, really need to do this in a query then Jerry Whittle's proposal
is a start on the queries I can envision to do this and the performance will
probably be terrible.

The next query will rank the three (or less) records returned by Jerry
Whittle's query (call it qJW) and then you will join this query (qRanked) to
itself three times and use the rank to return record 1, 2 and 3 for each PersonID.

SELECT A.PersonID, A.BPDate, A.Sys, A.Dia, Count(B.BPDate) as Rank
FROM qJW as A LEFT JOIN qJW as B
ON A.PersonID = B.PersonID
AND A.BPDate<B.BPDate
GROUP BY A.PersonID, A.BPDate, A.Sys, A.Dia

Now use that query (qRanked) in a query that looks like the following:
SELECT A.PersonID, A.BPDate, A.Sys, A.Dia
, B.BPDate, B.Sys, B.Dia
, C.BPDate, C.Sys, C.Dia
FROM (qRanked as A LEFT JOIN qRanked as B
On A.PersonID = B.PersonID
AND A.Rank = B.Rank-1)
LEFT JOIN qRanked as C
On A.PersonID = C.PersonID
AND A.Rank = C.Rank-2

I did forget to add one bit at the end to preclude multiple rows being
returned for each personID
WHERE A.Rank = 0

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

[KenSheridan via AccessMonster.com]

I too had serious doubts about the theoretical reliability of this approach.
What I was trying to explore was a means of doing it by joins rather than
subqueries, the former almost always being a more efficient approach. In
practice, however, I've found that whatever values I throw at it the result
has been 100 percent reliable. This is on the basis that there are a maximum
of three rows per personID in the table as the OP states in this thread – I
didn't see the earlier thread. It wouldn't surprise me in the least if it
could be broken, though, hence my exhortation to the OP to test it very
carefully against real data.

In reality, though, I can't envisage any circumstance when I'd ever do this,
as doing the same thing in a report/subreport is so simple, reliable and
would cater for any number of rows per personID. It can even be done in a
single report as my demo file shows, but again I can't conceive any reason
for doing it that way. The single report solution was devised solely in
response to a specific request for such a solution by a magazine reader,
merely to show that it was possible. Surprisingly the use of a subreport
seemed not to have occurred to the reader, nor to the author of the column,
who's a friend of mine.

Using subqueries I'd have thought the easiest way would be to create one
query for each of the three possible dates per personID, so for the first
date:

SELECT PersonID, X1.BPDate AS BPDate1, X1.sys AS sys1, X1.dia AS dia1
FROM X AS X1
WHERE BPDate =
(SELECT MIN(BPDate)
FROM X AS X2
WHERE X2.PersonID = X1.PersonID);

for the second:

SELECT PersonID, X1.BPDate AS BPDate2, X1.sys AS sys2, X1.dia AS dia2
FROM X AS X1
WHERE BPDate =
(SELECT MIN(BPDate)

FROM X AS X2
WHERE X2.PersonID = X1.PersonID

AND BPDate >
(SELECT MIN(BPDate)
FROM X AS X3
WHERE X3.PersonID = X2.PersonID
AND (SELECT COUNT(*)
FROM X AS X4
WHERE X4.PersonID = X3.PersonID)>=2));

and for the third:

SELECT PersonID, X1.BPDate AS BPDate3, X1.sys AS sys3, X1.dia AS dia3
FROM X AS X1
WHERE BPDate =
(SELECT MAX(BPDate)

FROM X AS X2
WHERE X2.PersonID = X1.PersonID

AND (SELECT COUNT(*)
FROM X AS X3
WHERE X3.PersonID = X2.PersonID)=3);

Its then just a case of outer joining them:

SELECT qryBP1.PersonID,
qryBP1.BPDate1, qryBP1.sys1, qryBP1.dia1,
qryBP2.BPDate2, qryBP2.sys2, qryBP2.dia2,
qryBP3.BPDate3, qryBP3.sys3, qryBP3.dia3
FROM (qryBP1 LEFT JOIN qryBP2 ON qryBP1.PersonID = qryBP2.PersonID)
LEFT JOIN qryBP3 ON qryBP2.PersonID = qryBP3.PersonID;

As with my original query this works with one, two or three rows per personID
in X, but no more, so both are reliant on the OP's statement that a patient
'may have up to 3 BP readings' being correct.

However its done, given that a report is so easy to constrict, a query
solution is reminiscent of Samuel Johnson's famous dictum that " it is like
a dog walking on its hinder legs. It is not done well; but you are surprised
to find it done at all".

Ken Sheridan
Stafford, England

>> 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

>[quoted text clipped - 15 lines]
>> 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

--
Message posted via AccessMonster.com

http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201002/1