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

Access 2010 the missing manual
	more ...			more ...

... into this

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