Home	Excel	Access
| Feedback | Adding total rows to a query |

Transposing data in an Access query

If you summarise an Access table or query, it may result in a wide record with many fields. This may be inconvenient for being directly displayed or for copying and pasting into a document.

It can be useful to change this ...

UserGroup summary p1: Select Query
	User Group	DaysOut	DistanceKm	NumHires	AvgOfInvoice_Amount
	CHELSFIELD GIRL GUIDES	2.333	242	3	60.93

Would you like to learn more?

Microsoft Office Access 2003 Step by Step (with CD)

... into this

UserGroup summary p2: Select Query
	Descr	Values
	Group name	CHELSFIELD GIRL GUIDES
	Average Days	2.3
	Average Distance	242 Km
	Average value	60.93
	Number of Bkgs	3

In this example, a row has been transposed into columns and better descriptive narratives have been applied against each item.

The first stage is to create an Access select query that will summarise a table of details for the selected group name into a single record.

UserGroup summary p1: Select Query
Field:	USERGROUP_NAME	DaysOut: Avg([date_return]-[date_out]+1)	DistanceKm: Avg([km_return]-[km_start])	...
Table:	Bookings
Total:	Group by	Expression	Expression	...
Sort:
Show:	Y	Y	Y	Y
Criteria:	[Select a group name]
Or:

The first summary is then read by a second query. The KeyData table has been added. This could be any table that contains a field (RecordNo) containing the numbers one to five (i.e. five records). There is no common field and so the tables are not joined. This will cause the query to run five times.

UserGroup summary p2: Select Query
Field:	Descr: Choose([recordno],"Group name", "Average Days", "Average Distance", "Average value", "Number of Bkgs")	Values: Choose([recordno], [usergroup_name], Format([Daysout],"0.0"), Format([Distancekm],"0") & " Km", Format([Avgofinvoice_amount],"£0.00"), Format([numhires],"0"))	RecordNo
Table:			Keydata
Sort:
Show:	Y	Y	N
Criteria:			Between 1 and 5
Or:

Both of the fields use the CHOOSE() function to select a particular value, depending on an initial value. The RecordNo field provides a value of 1, 2, 3, 4 or 5 and the fields are populated with a different description or content for each value. The FORMAT() function has been applied to some of the values in order to smarten their appearance.