<div>
Could you provide an example?
</div>


<div>
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. &nbsp;I've selected the field 'CountOfRole' and I'm not sure which option to select - avg, count, var, first etc.
</div>


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.

<div>
<div class="content wysiwyg-content">
Dear all,<br />
<br />
I have a table in the below format: <br />
<br />
M &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Y &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;District &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Role &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;CountOfRole<br />
1 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2010 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;18 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Admin &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1<br />
1 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2010 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;19 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;IT &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1<br />
2 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2010 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;18 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Admin &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2<br />
2 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2010 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;19 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;IT &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1<br />
<br />
<br />
I need to change the format to:<br />
<br />
M &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Y &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;District &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Admin &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;IT<br />
1 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2010 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;18 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1<br />
2 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2010 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;19 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1<br />
<br />
Is this possible in a query? Apologies for the alignment of example data.
</div>
</div>


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.

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

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.