Query to change format of table - field value to columns (new fields)

Posted on 2013-08-27
Medium Priority
Last Modified: 2013-08-29
Dear all,

I have a table in the below format:

M      Y      District      Role      CountOfRole
1      2010      18      Admin      1
1      2010      19      IT      1
2      2010      18      Admin      2
2      2010      19      IT      1

I need to change the format to:

M      Y      District      Admin      IT
1      2010      18      2      1
2      2010      19      1      1

Is this possible in a query? Apologies for the alignment of example data.
Question by:AndyC1000
  • 2
LVL 11

Assisted Solution

by:Deepak Lakkad
Deepak Lakkad earned 600 total points
ID: 39444450

Yes, you can do it with Cross Tab Query.

In Access, there is an option for creating Cross Tab Query in Query Wizard.

- Deepak Lakkad

Author Comment

ID: 39447825
Could you provide an example?

Author Comment

ID: 39447849
I've tried to use the cross tab query wizard, the issue I'm having is at the 'what number do you want calculated for each column and row intersection.  I've selected the field 'CountOfRole' and I'm not sure which option to select - avg, count, var, first etc.
LVL 41

Accepted Solution

Sharath earned 1400 total points
ID: 39448137
Can you try this?
       MIN(District)                         AS district, 
       MAX(IIF(Role = 'Admin', CountOfRole)) AS admin, 
       MAX(IIF(Role = 'IT', CountOfRole))    AS it 
  FROM your_table 

Open in new window


Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question