Developer Forum

I have designed a crosstab query to produce counts on
specific values, like so (scaled down version of actual
query).


Measure Amount SumOf1_7A SumOfICT
1.7A 00.00 100
1.7A 78.73 1
1.7A 79.18 1
ICT 82.97 1
ICT 85.00 2
ICT 00.00 100

How do I return totals like so...

Measure Applications Amount
1.7A 102 0,757.91
(300x100+378.73x1+379.18x1)
ICT 103 1,152.97
(400x100+382.97x1+385.00x2)

For the life of me I cannot solve it, really need some
expert help.
Do I create another query based on this or can my cross
tab produce the results?

Regards

Tony

You can create a query on the Crosstab query, where you GROUP by the Measure
field, and SUM the Amount field.

--
Steve Clark, Access MVP
FMS, Inc.
www.fmsinc.com/consulting



I have designed a crosstab query to produce counts on
specific values, like so (scaled down version of actual
query).


Measure Amount SumOf1_7A SumOfICT
1.7A 00.00 100
1.7A 78.73 1
1.7A 79.18 1
ICT 82.97 1
ICT 85.00 2
ICT 00.00 100

How do I return totals like so...

Measure Applications Amount
1.7A 102 0,757.91
(300x100+378.73x1+379.18x1)
ICT 103 1,152.97
(400x100+382.97x1+385.00x2)

For the life of me I cannot solve it, really need some
expert help.
Do I create another query based on this or can my cross
tab produce the results?

Regards

Tony

SELECT
YourTable.Measure,
Sum(nz([Sumof1_7a],0)+nz([SUmofICT],0)) AS Applications,
Sum([YourTable].[Amount]*(nz([Sumof1_7a],0)+nz
([SUmofICT],0))) AS Amount
FROM YourTable
GROUP BY YourTable.Measure;



>1.7A 00.00 100 >>1.7A 78.73 1 > >1.7A 79.18 1>
>ICT 82.97 >
>ICT 85.00 >2
>ICT 00.00 >10>
>
>How do I return totals like s>..>
>
>Measure Applications Am>unt
>1.7A 102 0,75>.91
>(300x100+378.73x1+379.>8x1)
>ICT 103 1,1>2.97
>(400x100+382.97x1+385>00>2)
>
>For the life of me I cannot solve it, really need>some
>expert h>lp.
>Do I create another query based on this or can my >ross
>tab produce the re>ul>s?
>
>R>ga>ds
> >>To>y

Gab,

Cool solution, worked a treat.
Many thanks

Tony
>>1.7A 00.00 100 >>>1.7A 78.73 1 >>>>1.7A 79.18 1>> >>ICT 82.97 >>
>>ICT 85.00 >>
>>ICT 00.00 >>00>>>>
>>How do I return totals like s>>..>>>>
>>Measure Applications Am>>nt
>>1.7A 102 0,75>>91
>>(300x100+378.73x1+379.>>x1)
>>ICT 103 1,1>>.97
>>(400x100+382.97x1+385>>0x>>
>>
>>For the life of me I cannot solve it, really need>>ome
>>expert h>>p.
>>Do I create another query based on this or can my >>oss
>>tab produce the re>>lt>>
>>
>>R>>ar>>
>>
>>Ton>> >>.