Crosstab Query to count number of items in value

[Tony]

Hi All,

I've got a basic crosstab query set up that totals values by category over a
series of years. The output looks like this:

CAT1 CAT2 CAT3
2007 1 2 3
2006 3 6 9
2005 4 5 7

What I'm attempting to do now is to add additional columns (if possible)
that will show the number of items in each category for each year. The
desired output looks like this:

CAT1 CAT1COUNT CAT2 CAT2COUNT CAT3 CAT3COUNT
2007 1 1 2
2 3 2
2006 3 2 6
4 9 5
2005 4 1 5
2 7 4

In the example above (for 2007):
CAT1's total is 1 and is comprised of 1 item
CAT2'S total is 2 and is comprised of 2 items
CAT3'S total is 3 and is comprised of 2 items
2007's total is 6 and is comprised of 5 items
2006's total is 18 and is comprised of 11 items
etc...

Does anyone know if this can be done and if so, can you point me to any
examples/tutorials, etc.? I'm using Access 2000.

Thanks,

Tony

 

[Guest]

Maybe I am just slow this Monday morning but I do not understand your second
set of data.
The first has totals by category and year combination. It is the Sum of all
2007 with Cat1, Sum of all 2007 with Cat2, Sum of all 2007 with Cat3, and
then the same for 2006 & 2005.
How is the additional data derived that you are showing in the second set?

 

[Tony]

Karl,

Thanks for the reply. The second dataset is what I'm after; I don't know
how to create it. The extra columns in the second set are meant to
represent the number of 'elements' that comprise the totals. Maybe a better
example would be CAT2 for 2007 = $500.00 and is a total of $400.00 and
$100.00. What I'd like to display, next to the entry for CAT1 2007 is the
count of items totaled to make the $500.00. In this case, the value would
be 2. Does that make more sense?

Thanks,

Tony

 

[Guest]

TRANSFORM Count([Amount]) & " - " & Sum([Amount]) AS Expr2
SELECT Format([TransDate],"yyyy") AS Trans_Year, Sum(Tony.Amount) AS [Total
Of Amount]
FROM Tony
GROUP BY Format([TransDate],"yyyy")
ORDER BY Format([TransDate],"yyyy") DESC
PIVOT "Cat " & [Category];

 

[Tony]

Karl,

Thanks for the post. I'll give this a shot and see how things shake out. I
appreciate the help.

Thanks,

Tony

TRANSFORM Count([Amount]) & " - " & Sum([Amount]) AS Expr2
SELECT Format([TransDate],"yyyy") AS Trans_Year, Sum(Tony.Amount) AS
[Total
Of Amount]
FROM Tony
GROUP BY Format([TransDate],"yyyy")
ORDER BY Format([TransDate],"yyyy") DESC
PIVOT "Cat " & [Category];

--
KARL DEWEY
Build a little - Test a little

Karl,

Thanks for the reply. The second dataset is what I'm after; I don't know
how to create it. The extra columns in the second set are meant to
represent the number of 'elements' that comprise the totals. Maybe a
better
example would be CAT2 for 2007 = $500.00 and is a total of $400.00 and
$100.00. What I'd like to display, next to the entry for CAT1 2007 is
the
count of items totaled to make the $500.00. In this case, the value
would
be 2. Does that make more sense?

Thanks,

Tony