Cross tab query help

[deanaldo]

Hi, I am trying to set up a cross tab query to show the best performing advert per area. I want it to show up like this:

Advert England Scotland Wales
Website 45 21 8
Word of Mouth 189 75 24
Newspaper 124 14 5

etc.

Instead it comes out like this:

Advert England Scotland Wales
Website 45 0 0
Website 0 21 0
Website 0 0 8
Word of Mouth 189 0 0
Word of Mouth 0 75 0
Word of Mouth 0 0 24
Newspaper 124 0 0
Newspaper 0 14 0
Newspaper 0 0 5
*Except no "0" are shown, the field is just blank

Here is my SQL used:

TRANSFORM Count(Tbl_Source.Advert) AS CountOfAdvert
SELECT Tbl_Source.Advert
FROM Tbl_Country INNER JOIN (Tbl_Source INNER JOIN Tbl_Enquiry ON Tbl_Source.AdvertID = Tbl_Enquiry.AdvertID) ON Tbl_Country.CountryID = Tbl_Enquiry.CountryCode
GROUP BY Tbl_Source.Advert, Tbl_Enquiry.AdvertID, Tbl_Enquiry.CountryCode
PIVOT Tbl_Country.CountryDescription;

Any help would be appreciated, thanks.

 

[stopher]

You were grouping on more fields than necessary. Try this:

TRANSFORM Count(Tbl_Source.AdvertID) AS CountOfAdvertID
SELECT Tbl_Source.Advert
FROM Tbl_Country INNER JOIN (Tbl_Source INNER JOIN Tbl_Enquiry ON Tbl_Source.AdvertID = Tbl_Enquiry.AdvertID) ON Tbl_Country.CountryID = Tbl_Enquiry.CountryCode
GROUP BY Tbl_Source.Advert
PIVOT Tbl_Country.CountryDescription;

hth
Chris

 

[DCrake]

Have you tried reversing the rows and columns? Do you get the same effect?

 

[deanaldo]

You were grouping on more fields than necessary. Try this:

TRANSFORM Count(Tbl_Source.AdvertID) AS CountOfAdvertID
SELECT Tbl_Source.Advert
FROM Tbl_Country INNER JOIN (Tbl_Source INNER JOIN Tbl_Enquiry ON Tbl_Source.AdvertID = Tbl_Enquiry.AdvertID) ON Tbl_Country.CountryID = Tbl_Enquiry.CountryCode
GROUP BY Tbl_Source.Advert
PIVOT Tbl_Country.CountryDescription;

hth
Chris

Thanks, this worked great