Two Data Series in CrossTab Query

[Atif]

is it possible to display 2 data series in crosstab Query i.e.

Manager USD
AAAA 100
BBBB 125
AAAA 75
BBBB 150
BBBB 100

I want to display Sum and Cound of USD for each Manager

Sum Count
AAAA 100 2
BBBB 375 3

 

[Michel Walsh]

You don't use a crosstab for that, but a total query:

SELECT manager, COUNT(*), SUM(usd)
FROM tableNameHere
GROUP BY manager



A crosstab query creates new fields from individual values picked in a
field. As example, it can create AAAA and BBBB new fields, out of the
actual values under the actual Manager field.
A total query creates statistics fields, as a standard SELECT query can
generate EXPRESSION, but a total query does not otherwise creates new
fields.

You can see if your problem mostly need a crosstab or a total query by
simply examining these differences. Do you need new fields from created
based on actual VALUES under a field? if so, a crosstab, else, a total
query.



Vanderghast, Access MVP

 

[Atif]

thanks Michel,

Actually I want creat a Pivot Chart for Analysis, I believe only Crosstab
Query provide that feature.