Crosstab/Pivot results in horizontal lists below columns

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello

I have a table containing a list of UK area codes together with the name of the service centre responsible for the customers in that zone. I want to display this list in a crosstab type format with the zone as the column heading and the area codes in that zone listed underneath

The data looks like this

Code Zon
01223 Zone
01438 Zone
01763 Zone
01638 Zone
01276 Zone
01338 Zone

I want to get to something like this

Zone 1 Zone 2 Zone
01223 01438 0163
01763 0127
0133

Can anyone help? Thanks in advance

Adam
 
Hi,


You need to rank (first,second, 3, 4, 5,....) the code values, by zone,
and then, use that rank as GROUP.


SELECT a.Code, a.Zone, COUNT(*) As Rank
FROM myTable As a INNER JOIN myTable As b
ON a.zone=b.zone AND a.code >= b.code
GROUP BY a.code, a.zone


save that as query1, as example, then run the XTab wizard, based on query1.


Rank Zone 1 Zone 2 Zone 3
1 01223 01276 01638
2 01338 01438
3 01763

(or similar)





Hoping it may help,
Vanderghast, Access MVP


Adam Carpenter said:
Hello,

I have a table containing a list of UK area codes together with the name
Click to expand...
of the service centre responsible for the customers in that zone. I want to
display this list in a crosstab type format with the zone as the column
heading and the area codes in that zone listed underneath.
 
Back
Top