Need Help with Crosstab Queries

I'm a database newbie and I need to create queries (I think crosstabs) using SQL Server. One query needs to take a count and another needs to take a sum. I've never done these type of queries before, so I tried doing them first in Access using the wizard. The queries work just fine in Access. So I copied the SQL code that was generated in Access and pasted it as a stored procedure in SQL Server. But I'm running into several problems that I don't know how to fix.

Here's what my table (ContactMethods) looks like:
(I used dashes so that the data would line up under the column.)

ID- ContactHelpDesk
1 - email
2 - walk
3 - form
4 - phone
5 - email
6 - walk
7 - form

My ultimate goal is to be able to find out the total number of people who selected email, walk, form, and phone (in the ContactHelpDesk field). I also need to have email, walk, form, and phone to be column headings. I need it in this format so that I can take a sum for each column and refer to the column by name when using ColdFusion (a scripting language).

My first query (called ContactHelpDeskCrosstab) does a count:

TRANSFORM Count(ContactMethods.ContactStudent) AS CountOfContactStudent
SELECT ContactMethods.ID, Count(ContactMethods.ContactStudent) AS [Total Of ContactStudent]
FROM ContactMethods
GROUP BY ContactMethods.ID
PIVOT ContactMethods.ContactHelpDesk;

The result looks like this:

ID- Total of ContactStudent- email- form- phone- walk
1 - 1 ---------------------- 1
2 - 1 ------------------------------------------ 1
3 - 1 ----------------------------- 1
4 - 1 ----------------------------------- 1
5 - 1 ---------------------- 1
6 - 1 ------------------------------------------ 1
7 - 1 ----------------------------- 1

This is exactly what I need so that I can take a sum.

Next, I take a sum for each column (email, form, phone, walk). Here's the query that takes the sum:

SELECT DISTINCTROW Sum(ContactHelpDeskCrosstab.email) AS EmailTotal, Sum(ContactHelpDeskCrosstab.form) AS FormTotal, Sum(ContactHelpDeskCrosstab.phone) AS PhoneTotal, Sum(ContactHelpDeskCrosstab.walk) AS WalkTotal
FROM ContactHelpDeskCrosstab;

The result looks like this:

EmailTotal- FormTotal- PhoneTotal- WalkTotal
2 --------- 2 -------- 1 ---------- 2

This is exactly what I need. Now that it is in this format, I will be able to use ColdFusion to print out the total for each answer.

However, when I copy the SQL code generated in Access from the first query and paste it to a new stored procedure, I receive two error messages from SQL Server:

• Incorrect syntax near TRANSFORM.
• Incorrect syntax near PIVOT.

When I copy the SQL gode generated in Access from the second query and paste it to a new stored procedure, I receive this error message from SQL Server:

• Incorrect syntax near ContactHelpDeskCrosstab. (I'm not sure if something is wrong with the syntax or if it's throwing that message because it can't find a query by that name. As I mentioned, it won't let me create that query because of the syntax problem.)

I'd appreciate any help with how I can fix these queries so that they will work with SQL Server. Thanks.

Unfortunately SQL Server 2000 does not support crosstab queries like Access.
There are a few freeware procedures that claim they mimc the crosstab query behavior
but I have not used them.

This is why you get errors with keywords Transform etc.

Depending on what exactly you are doing
you may have to use temp tables or write a series of queries to get the information you need.
I.e. I would suggest going back to the drawing board....or link the tables into access database and do your query there.

Smartbs
www.accessneeds.com

I had the same problem. I simply Linked a front end Access database to it, used the same tables (now in Access). I still had a problem doing crosstabs with a parameter but if you don't have any parameters it is easier.

I then created a make-table query with my parameters and then based my crosstab query on the newly created table. If you do not have any parameters to address simply create the crosstab query using the tables in Access.

Harold