"Phil" <pb****@med.usyd.edu.auwrote in message
news:11**********************@b28g2000cwb.googlegr oups.com...
Is it possible to swap rows and columns in select query output so that
each record's data is displayed in a column? I want to collect data
each day and display it in a query with each day's date along the top
row and the data items below it. This would give a better appreciation
of changes over time. I apologise if I'm missing something obvious.
For a Table with three Fields: DateOfData, IdentificationOfTheItem, and the
ValueOfTheItem.
Create a Query, using the QueryBuilder, with DateOfData,
IdentificationOfTheItem, and the ValueOfTheItem in the Grid. Then, in the
menu, Query | Crosstab Query, and in the Crosstab row of the grid, choose
"Column Heading" for DateOfData, "RowHeading" for IdentificationOfTheItem,
and "Value" for ValueOfTheItem.
Leave the "Total" row as "GroupBy" for DateOfData and
IdentificationOfTheItem, and the appropriate type for ValueOfTheItem.
Run the Query. Save the Query. You can use it as RecordSource of a Form or
Report if you want. You can add criteria to the Fields, if you want (and
you will, in time, as you will end up with a lot of dates unless you limit
them).
Or, if you are using Access 2002 or later, you can just create a select
Query with the three Fields, then select View, and choose Pivot Table, then
drag the Field from the Field list to the Column, Row, and Value areas -- if
all you want to do is, once in a while, review and analyze the information.
Larry Linson
Microsoft Access MVP