Force Column Headings in a Crosstab Query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Force Column Headings in a Crosstab Query

Author

Topic

pepig
Starting Member

10 Posts

Posted - 2007-08-29 : 19:44:26

I'd like to force the column headings to be the same as the values of a certain field in another table. Let me take an example.

I have a "Cities" table giving me names of cities.
I am creating a crosstab table where rows are names of travelers and columns are the names of all the cities in the "Cities" table. in each (travelerName, cityName) case, I store a number saying how many times a traveler has been in that city.

Since the "Cities" may vary, I'd like to make sure that all cities are there in the crosstab table (even though there could be one column with null because nobody has visited the city yet)

I only know how to force the column headings manually in SQL: by putting ' "city1", "city2", "city3" ' in the "IN" tsatement of the crosstab query:

TRANSFORM Count(CityVisited) AS CountOfCityVisited
SELECT Travelers.travName
FROM Travelers JOIN Cities USING(cityID)
GROUP BY Travelers.TravName
PIVOT Cities.cityName IN ("city1", "city2", "city3");

That works. Now I would like to automize this so that if I add city4 in the "Cities" table, I don't have to change the SQL statement manually.

I built a function called ForcedColumnHeadings() that scans the "Cities" Table and returns a string : ' "city1", "city2", "city3" '.

In the SQL statement of the crosstab, at the very end, I tried calling that function in the "IN"-statement:

TRANSFORM Count(CityVisited) AS CountOfCityVisited
SELECT Travelers.travName
FROM Travelers JOIN Cities USING(cityID)
GROUP BY Travelers.TravName
PIVOT Cities.cityName IN (ForcedColumnHeadings());

but it doesn't regognize it as a VBA Function...

Any advice ?

pepig
Starting Member

10 Posts

Posted - 2007-08-31 : 12:21:00

If anyone interested, the solution I found is to have a VBA procedure create the query. Here's the interseting piece of code:

Dim strSQL AS String
strSQL = "TRANSFORM Count(CityVisited) AS CountOfCityVisited SELECT Travelers.travName FROM Travelers JOIN Cities USING(cityID) GROUP BY Travelers.TravName PIVOT Cities.cityName IN" &_
ForcedColumnHeadings() &";"
dbCurrent.CreateQueryDef(Queryname,strSQL)

See help by typing "DAO Queries" in he Access help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources