Transpose rows and columns in reports

  • Thread starter Thread starter David P. Lurie
  • Start date Start date
D

David P. Lurie

Access 2003

How can rows and columns be transposed in reports:

example : TableA with 4 columns - id (Autonumber), date (date), name (text),
value (number)

Standard detail section for report based on query for single id, sorted by
date

date1 value1
date2 value2
date3 value3
 
Without knowing more about your table struckture, have you
considered using a crosstab query?
Hope this helps.
Fons
 
Fons Ponsioen said:
Without knowing more about your table struckture, have you
considered using a crosstab query?
Click to expand...

Crosstabs won't work, as I don't need any summary functions. Actually going
to use for display of laboratory data, where comparison is easier with
records displayed as individual columns rather than rows, sorted by date:

table cbc, with just a few fields shown for example:

id (autonumber)
chart_number (int)
date (date)
wbc (number)
hb (number)
hct (number)
plt (number)

Query retrieves rows for specified individual for specified dates:

select date, wbc,hb hct,plt from cbc where
((chart_number = 12345) and (date between #2/1/2004# and #3/31/2004#))
order by date desc;

Conventional report:

Date WBC Hb Hct Plt

3/14/2004 7.4 12.9 39.8 179
2/1/2004 9.6 13.5 42.5 158
 
Your data is not stored in a normalized form and that makes it very difficult to
work with.

One method is to use a UNION query as the source of the crosstab, as long as
there aren't TOO many fields.

SELECT [Date], "WBC" as TestType, WBC
FROM CBC
WHERE Chart_Number = 12345 AND
[Date] Between #2/1/04# and #3/31/04#
UNION ALL
SELECT [Date], "HB" as TestType, HB
FROM CBC
WHERE Chart_Number = 12345 AND
[Date] Between #2/1/04# and #3/31/04#
UNION ALL
....

Then using that as the source of the crosstab query

TRANSFORM Max(WBC) AS [The Value]
SELECT [Date]
FROM YourUnionQuery
GROUP BY [Date]
ORDER BY [Date]
PIVOT TestType;


You would have been better off with a table for the tests that looked like

TblTests
Chart_Number
TestDate
TestType
TestResult

Then you could do the crosstab query directly. You would never need to change
the table structure when a new test came along.
 
John Spencer (MVP) said:
Your data is not stored in a normalized form and that makes it very difficult to
work with.

You would have been better off with a table for the tests that looked like

TblTests
Chart_Number
TestDate
TestType
TestResult

Then you could do the crosstab query directly. You would never need to change
the table structure when a new test came along.
Click to expand...

I'm still in the table design stage, when modifications will be easier. A
lab encounter table with demographics and other related data linked to a lab
detail table would normalized, and certainly best.

Our lab performs three general categories of studies, with heterogeneous
data types. Each study generates one or more data points. Data validation
would cumbersome with a single subform for each category to enter detail
items, which led me to try separate tables for each category.

Separate subforms for each category to enter sets of data generated by a
particular study might work, and I'll give that a try.

Thanks,

David P. Lurie
 
Back
Top