By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,327 Members | 2,661 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,327 IT Pros & Developers. It's quick & easy.

Crosstab query Totals

P: n/a
jim
I have a cross tab query:

TRANSFORM Count(tblRegionalExperience.ConsultantID) AS CountOfConsultantID
SELECT tblRegionalExperience.RegionID
FROM tblRegionalExperience
GROUP BY tblRegionalExperience.RegionID
PIVOT tblRegionalExperience.Experience;

Which is nice & gives me something like this:

RegionID 1 2 3
++++++++ ++ ++ ++
AFRICA 20 10 4
ASIA 10 10 3

etc.

but what i would really like is this

RegionID 1 2 3 TOTAL
++++++++ ++ ++ ++ +++++
AFRICA 20 10 4 34
ASIA 10 10 3 23
i have managed to do by creating another query:

SELECT Count(tblRegionalExperience.ConsultantID) AS CountOfConsultantID,
tblRegionalExperience.RegionID
FROM tblRegionalExperience
GROUP BY tblRegionalExperience.RegionID;

then joining this to the first query by changing it to:

TRANSFORM Count(tblRegionalExperience.ConsultantID) AS CountOfConsultantID
SELECT tblRegionalExperience.RegionID, qryOvRegTotals.CountOfConsultantID
FROM tblRegionalExperience INNER JOIN qryOvRegTotals ON
tblRegionalExperience.RegionID=qryOvRegTotals.Regi onID
GROUP BY tblRegionalExperience.RegionID, qryOvRegTotals.CountOfConsultantID
PIVOT tblRegionalExperience.Experience;

Yet it feels like there should be an easier way to do this???

any thoughts much appreciated..


Oct 26 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You just need a COUNT() in the SELECT clause:

TRANSFORM Count(ConsultantID) AS CountOfConsultantID
SELECT RegionID, COUNT(*) As Total
FROM tblRegionalExperience
GROUP BY RegionID
PIVOT Experience

--
MGFoster:::mgf00 <atearthlink <decimal-pointnet
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRUBERoechKqOuFEgEQLupQCfdeRfKUKKg8FV5u41XCdFK2 RPMeQAoJNR
CspNy/WUqvrGNiE6HeIJQT3t
=6nF8
-----END PGP SIGNATURE-----
jim wrote:
I have a cross tab query:

TRANSFORM Count(tblRegionalExperience.ConsultantID) AS CountOfConsultantID
SELECT tblRegionalExperience.RegionID
FROM tblRegionalExperience
GROUP BY tblRegionalExperience.RegionID
PIVOT tblRegionalExperience.Experience;

Which is nice & gives me something like this:

RegionID 1 2 3
++++++++ ++ ++ ++
AFRICA 20 10 4
ASIA 10 10 3

etc.

but what i would really like is this

RegionID 1 2 3 TOTAL
++++++++ ++ ++ ++ +++++
AFRICA 20 10 4 34
ASIA 10 10 3 23
i have managed to do by creating another query:

SELECT Count(tblRegionalExperience.ConsultantID) AS CountOfConsultantID,
tblRegionalExperience.RegionID
FROM tblRegionalExperience
GROUP BY tblRegionalExperience.RegionID;

then joining this to the first query by changing it to:

TRANSFORM Count(tblRegionalExperience.ConsultantID) AS CountOfConsultantID
SELECT tblRegionalExperience.RegionID, qryOvRegTotals.CountOfConsultantID
FROM tblRegionalExperience INNER JOIN qryOvRegTotals ON
tblRegionalExperience.RegionID=qryOvRegTotals.Regi onID
GROUP BY tblRegionalExperience.RegionID, qryOvRegTotals.CountOfConsultantID
PIVOT tblRegionalExperience.Experience;

Yet it feels like there should be an easier way to do this???
Oct 26 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.