Crosstab query

[Deutz]

Hi and thanks in advance,

I'm using Access 2003.

I'm trying to formulate a crosstab query and having a bit of difficulty.

I have a table with fields: Owner, Area & Status

Data looks like:

OwnerAreaStatusJackArea1RedJackArea5RedJackArea3GreenJackArea2AmberJackArea6AmberBillArea9RedBillArea8RedBillArea4GreenBillArea7GreenBillArea10GreenTonyArea11RedTonyArea12Amber

An Area can only belong to a single Owner.

I'm trying to produce a query to rearrange the data for a report where data will be displayed as

OwnerRedGreenAmberJackArea1Area3Area2Area5Area6BillArea9Area4Area8Area7Area10TonyArea11Area12


My SQL works to some extent as it puts a 1 into the Red Green and Amber cols but not the Area itself ...

TRANSFORM Count(Data.[Area]) AS [CountOfArea]
SELECT Data.[Owner], Data.[Area]
FROM Data
GROUP BY Data.[Owner], Data.[Area]


PIVOT Data.Status;


Thanks
Deutz

[Deutz]

Sorry about the long lines of data.

I foolishly thought I could copy from Excel cells into the post.

I'll see what I can do to reformat it and post again

[Deutz]

My data is stored in a single table like this:

Owner.....Area.......Status
Jack.......Area1......Green
Jack ......Area2......Amber
Tony......Area7......Green
Tony......Area10.....Red
Tony......Area5.......Red
Bill.........Area4.......Amber

I'm trying to produce a query for a report that will look like this:

Owner........Green........Amber.......Red....
Jack...........Area1........Area2................
Tony..........Area7.......................Area10
Tony.........................................Area5
Bill.............................Area4............ ....