asked on

Pivot/Cross Tab Table Query

This is an Access 2013 application. I've been using Access for a long time but never had occasion to use pivot or cross tab queries.

Im my application I have tblAttendanceReport The pertinent fields are:
ClientRecID Int,
YearAndMonth Short Text (6) For Example: 201402, 201809, etc...
GasText ShortTExt (250)

There may be 1 or more YearandMonth/DistTExt records for a given client.

Client ID YearAndMonth Gas Text
6683 201207 $20 Gas Card
6925 201401 Z8 Monthly
6925 201404 Z7 Monthly
6925 201405 Z1 Monthly
7010 201209 Z1 Monthly
7010 201310 Z1 Monthly

As the output from this query I would like one line for each client, with the respective Year and Month and Enties text entries to the right. Sorted in descending sequence by YearAndMonth. So the most recent year and month would be of the immediate left of the clientID.

The GasText entry on each line may or may not be the same as other line for the same client.

Output Desired
6683 201207 $20 Gas Card
6925 201405 Z1 Monthly 201404 Z7 Monthly 201401 Z8 Monthly
7010 201310 Z1 Monthly 201209 Z1 Monthly

Is it possible for a query to accomplish my objective? If so, what would it look like?

ASKER CERTIFIED SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mlcktmguy

ASKER

Thanks Gustav.
I had an idea that might make this possible in a query. I created a query on the original table that combines YearandMonth and GasText into one field.

SELECT tblAttendanceReport_Local.ClientRecID, [YearAndMonth] & ", " & [GasText] AS YYYYMM_And_Distribution
FROM tblAttendanceReport_Local
ORDER BY tblAttendanceReport_Local.ClientRecID, tblAttendanceReport_Local.YearandMonth DESC;

Open in new window

The result of this query has two fields on each line, ClientID and YYYYMM_and_Distribution

Would it be possible to make a pivot/crosstab query as I prefer from this query?

Gustav Brock

No. Except if you would accept 23 columns for year-month as to your example data.