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

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 to rearrange the data for a report where data will be displayed as

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

An Area can only belong to one Owner.

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

 

[stopher]

I don't think you can do what you ask without some code. However, take a look at this:

TRANSFORM Max(Data.Area) AS MaxOfArea
SELECT Data.Owner
FROM Data
GROUP BY Data.Owner
PIVOT Data.Status;

If you run it you'll see it nearly works. What you need to do is combine the two Tony/Red records into a single record where the two areas are concatenated together. Do a search on concatenating records and you should find some methods how to do this.

hth
Chris

 

[Deutz]

Thanks Chris,

I'll see what I can find on concat records.

 

[stopher]

I think I was wrong when I said it couldn't be done as a query.

Try this:

TRANSFORM Max(Data.Area) AS MaxOfArea
SELECT Data.Owner
FROM Data
GROUP BY DCount("owner","Data","[Owner]='" & [data].[Owner] & "' AND [Status]='" & [data].[Status] & "' AND [Area]<='" & [data].[Area] & "'"), Data.Owner
PIVOT Data.Status

It seems to produce exactly the result you want.

Chris

 

[Deutz]

I must say, I'm very impressed. That works perfectly! I just added a sort by owner to group the owner rows. You saved me a lot of mucking about with extra queries or recordsets.

It will take me some time to figure out how the SQL works. Are you able to provide a brief explanation of how the group by DCount bit works so I will be able to implement this type of thing in future applications?

Thanks heaps Chris

Deutz

 

[stopher]

Sure....

The problem is the one I highlighted in post 2 in that the transform in Post 2 isn't able to handle the fact that there are two Tony/Red recrods (for every other owner/status combination there is only one record). So what we need to do is make the records look like this:

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

Notice how the Suquence field number the occurences of each owner/status combination. Once we are able to do this then the transform is easy to write (see how Sequence has been added in the grouping):

TRANSFORM Max(Area) AS MaxOfArea
SELECT Owner
FROM Data
GROUP BY Owner, Sequence
PIVOT Status

So how do we get a Sequence field? The principle is for each record (owner/status combination), to count the number of occurences of that combination up to that record. Hence:

Tony.....Red......count of Tony/Reds up to this record =1
Tony.....Red......count of Tony/Reds up to this record =2

One way to generate the sequence is using a subquery i.e. for each record, a subquery is performed to profide the count for that record. Run this query and see the effect:

SELECT Data.Owner, Data.Area, Data.Status, 
    (SELECT Count(Owner) AS CountOfOwner
     FROM Data as d
     WHERE d.Area<=Data.Area
     GROUP BY d.Owner, d.Status
     HAVING d.Owner=Data.Owner AND d.Status=Data.Status) 
AS Test
FROM Data

However, Transform (Pivot) queries and subqueries don't mix well together at all. If there's a way to get them to work together, I haven't found it. So instead of using a subquery, I've used the DCount function. DCount is one of many domain functions which allow you to return a single value from a table against a set of criteria. See here for for explanation.
So the above changes to:

SELECT Owner, Area, Status, DCount("[Owner]","Data","[Area]<='" & [Area] & "' AND [Owner]='" & [Owner] & "' AND [Status]='" & [Status] & "'") AS Sequence
FROM Data

Literally, the DCount says... count the number of records where we have the same owner and status as the current record and the area is less than or each to the area given by the current record.

Again you can run the above to confirm is produces the same list we are after.

You can then either create a Transform query based on this query, or alter this query to display as a transform (which is what my solution does).

Note running a DCount (or subqeury for that matter) in this fashion isn't very efficient. For every record, Access has to perform a count. If you need speed then another approach is advisable.

hth
Chris

 

[Deutz]

Thanks again Chris. That is a very comprehensive and informative description of how the query works and I'm going to file it away for future applications.

Cheers
Deutz