MeadInKent
| Feedback | Adding total rows to a query | Various tasks for 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 ...

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

Access 2010 the missing manual

 
UK more ...   USA more ...

... into this

2 UserGroup summary p2: Select Query ox
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 ox
  data source
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
  data source   data source
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.



file: acctranspose.htm Page last modified apr14 MeadInKent.co.uk 2014