Need expert help producing totals from a cross tab query

  • Thread starter Thread starter Tony
  • Start date Start date
T

Tony

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 £300.00 100
1.7A £378.73 1
1.7A £379.18 1
ICT £382.97 1
ICT £385.00 2
ICT £400.00 100

How do I return totals like so...

Measure Applications Amount
1.7A 102 £30,757.91
(300x100+378.73x1+379.18x1)
ICT 103 £41,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 £300.00 100
1.7A £378.73 1
1.7A £379.18 1
ICT £382.97 1
ICT £385.00 2
ICT £400.00 100

How do I return totals like so...

Measure Applications Amount
1.7A 102 £30,757.91
(300x100+378.73x1+379.18x1)
ICT 103 £41,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;
 
Gab,

Cool solution, worked a treat.
Many thanks

Tony
-----Original Message-----
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;


-----Original Message-----
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 £300.00 100
1.7A £378.73 1
1.7A £379.18 1
ICT £382.97 1
ICT £385.00 2
ICT £400.00 100

How do I return totals like so...

Measure Applications Amount
1.7A 102 £30,757.91
(300x100+378.73x1+379.18x1)
ICT 103 £41,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
.
Click to expand...
.
Click to expand...
 
Back
Top