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?
LVL 1
mlcktmguyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
Create a table, tblAttendanceSummarize, with field ClientID and as many YearAndMonth/GasText combos you expect to meet, three in your example.

Now, in VBA, loop the records of tblAttendanceReport  sorted by ClientID and YearAndMonth.
For each ClientID, add a new record in tblAttendanceSummarize and fill first the first combo.
For the next records with the same ClientID, fill the next combos.
When moving to a record with a new ClientID, save the record in tblAttendanceSummarize and add a new.

When reaching EOF, save the last record in tblAttendanceSummarize.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mlcktmguyAuthor Commented:
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 BrockCIOCommented:
No. Except if you would accept 23 columns for year-month as to your example data.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.