Crosstab with SQL Backend Problem

[pete.trudell]

I'm very new to SQL, and having problems transfering our old Access
2003 Database to a new Access 2007 Frontend- SQL server Backend. I
have figured over the last several months many of the Query
conventions that make most of my new database work, however I'm realy
stumped with crosstabs.

I have a CallLog Table & Login Table entries during the day of the
type of calls they receive, The database collects the staffs
dbo.Login.LName, dbo.Login.FName dbo.CallLog.RDate, and
dbo.CallLog.RTime, and dbo.CallLog.CType (call type, eithe Claims,
Inquiry, Tele-Serve, etc. etc.) Their are 7 types of calls in total.

I want to repeat the 2003 crosstab report I made out each morning of
call activity:

Staff Claims Inquiry Tele-
Serv Totals
Brown Les 5 15
1 21
Gordon Mary 15 10
5 30

I've read about using SQL to set up the report and TRANSFORM, SELECT,
etc. but I'm not sure what goes where.

Appreciate any assistance.

Thanks

 

[Lord Kelvan]

you can still build the same queries in access using an odbc
connection from sql server if this is what you are asking

Regards
Kelvan

 

[pete.trudell]

you can still build the same queries in access using an odbc
connection from sql server if this is what you are asking

Regards
Kelvan

I no longer have the traditional Access Query Grid to enter the fields
as I normally do with access 2003, and I can't go back to 2003. The
set-up with SQL as the Backend Database is a SQL grid and I'm
struggling to understand how to create a Crosstab using the
recommended TRANSFORM, SELECT, etc. All my attempts created multiple
errors with SQL and failure to result in sucess.

Appreciate any guidance on this procedure.

Pete

 

[pete.trudell]

you can still build the same queries in access using an odbc
connection from sql server if this is what you are asking

Regards
Kelvan

Here is my TRANSFORM Code, which will not parse in SQL- errs out :

TRANSFORM COUNT(CType)
SELECT dbo.Login.LName, dbo.Login.FName, dbo.CallLog.CType,
dbo.Login.Team
FROM dbo.CallLog INNER JOIN dbo.Login ON dbo.CallLog.LO =
dbo.Login.StdLogonID
GROUP BY dbo.Login.LName, dbo.Login.FName, dbo.CallLog.CType,
dbo.Login.Team
ORDER BY dbo.Login.Team
PIVOT(CType) IN ([Claims], [Inquiry], [Tele-Serve])

I've tried different things to adjust this and still it will not
work.

Need help, Thanks

Pete

 

[Bob Barrows [MVP]]

you can still build the same queries in access using an odbc
connection from sql server if this is what you are asking

Regards
Kelvan

Here is my TRANSFORM Code, which will not parse in SQL- errs out :

TRANSFORM COUNT(CType)
SELECT dbo.Login.LName, dbo.Login.FName, dbo.CallLog.CType,
dbo.Login.Team
FROM dbo.CallLog INNER JOIN dbo.Login ON dbo.CallLog.LO =
dbo.Login.StdLogonID
GROUP BY dbo.Login.LName, dbo.Login.FName, dbo.CallLog.CType,
dbo.Login.Team
ORDER BY dbo.Login.Team
PIVOT(CType) IN ([Claims], [Inquiry], [Tele-Serve])

I've tried different things to adjust this and still it will not
work.

No, you can only use JetSQL against Jet tables or linked tables. You
cannot, for instance, use TRANSFORM in a passthrough query: it's a
JetSQL keyword that does not exist in Transact-SQL. In order to use
TRANSFORM, you will need to use linked tables. Is the problem that you
don't know how to create linked tables? Access pretty much walks you
through the process ...

SQL 2005 does have new PIVOT functionality if you are using that
version. It, however, still does not use TRANSFORM.

 

[pete.trudell]

Thank you Bob, for responding.

I am trying to use a Stored procedure as a Crosstab, because I don't
have the old Query Grid anymore as I did with Access 2003. We are
trying to mirrow our faithful Access 2003 Database we have used for
nine years. State Office upgraded everyone to Office 2007, and
suggested- strongly- that I move to a SQL backend (however, they were
not in aposition to help me with this project), I'm on my own. This
internal Database tracks our leave, Training, Call Activity, etc.
general Operational stuff.

When I started this project, we could not use a Migration Tool as our
databse was too complicated. The Migration Boomed. So I've had to
start from scratch, feeling my way along, until I reached this stumb-
Crosstab Queries.

I can start the design grid for a Stored procedure, add two tables
CallLog (the collected Daily data of call activity for the Center) and
Login (the list of staff with there Login IDs, which is used in
CallLog to record there call type). Then I select LName, Fname Team
from Login, and the CType (Call Type for each call). Now I'm in
trouble, because all the web searchs for Croostab and SQL sends me to
- i.e MSDN - TRANSFORM Statement, using PIVOT - IN . Another Web
Search sent me to Allen Browne's site 0n Crosstab query Techniques for
SQL - Access again TRANSFORM and PIVOT IN. However, they use examples
that are far removed from my problem, and I have attempted to use them
as guides, but every attempt has failed.

So- I have no crosstab for my Stored Procedure, and if I do it without
the SQL additions of TRANSFORM and PIVOT i get a results, but they do
not act like a crosstab, and just list the activity as separate rows,
not sums, etc.

Bottom line is the data is collected and stored in SQL, and I can get
to the data with a SDtored Procedure, but I can not set-up as a
Crosstab.

Thanks Pete

 

[Bob Barrows [MVP]]

Thank you Bob, for responding.

I am trying to use a Stored procedure as a Crosstab, because I don't

Are you talking about a SQL Server stored procedure? or an Access saved
query?

If SQL Server stored procedure, you can use a passthrough query that
executes the stored procedure as the source table for the crosstab query
wizard.

have the old Query Grid anymore as I did with Access 2003. We are

Huh? Why not? I'm definitely staying away from A2007 if they've gotten
rid of the query builder ...

I can start the design grid for a Stored procedure, add two tables

I don't get you: are you in the Access environment or SQL Server? Are
these tables linked tables?

I really cannot follow the rest of this until we get the terminology
straight.

 

[pete.trudell]

I really feel like the first man on Mars with this process-- No one
seems to understand Access 2007 and SQL, not even Microsoft has any
suggestions.

When I set-up the SQL Databse as the Back-end and Access 2007 as the
front end, the Query Grid changed to reflect a SQL Grid,


Tables ---- Tables


Column Alias Table Output Sort Type Sort
Order Criteria Or Or Or ......................


You use T-SQL code in the [Column] -Column to work with.

You DO NOT HAVE

Field
Table
Total
Criteria
OR

And you do not have a option to have a Crosstab Row.

And when you go to Microsoft to get help????? No Help. You Figure.

Frustrated - Pete

 

[Bob Barrows [MVP]]

I really feel like the first man on Mars with this process-- No one
seems to understand Access 2007 and SQL, not even Microsoft has any
suggestions.

Whom have you talked to at Microsoft?

When I set-up the SQL Databse as the Back-end and Access 2007 as the
front end, the Query Grid changed to reflect a SQL Grid,


And you do not have a option to have a Crosstab Row.

And when you go to Microsoft to get help????? No Help. You Figure.

Again, when did you go to Microsoft for help?

Frustrated - Pete

Hmm, somebody must have led you to believe that programming for SQL
Server was the same as programming for Jet, with little or no learning
curve. Well, that's just not the case. No wonder you are frustrated.

I'm afraid I'm a little (lot) out of my depth here. If Access was not in
the picture, I could probably help since I program completely in SQL.
Here is an example of creating a pivot query in SQL 2005 (I'm not even
sure that is the version of SQL you are using ... ). It comes from SQL
Server Books OnLine (BOL) and I suggest you download a copy of it from
the microsoft website:

USE AdventureWorks
GO
SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS
Emp4, [233] AS Emp5
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [164], [198], [223], [231], [233] )
) AS pvt
ORDER BY VendorID;

Here is a google search result that provides more explanations about
using pivot:

http://www.google.com/search?q=pivo...s:IE-SearchBox&ie=UTF-8&oe=UTF-8&sourceid=ie7

 

[pete.trudell]

Got It , Thanks

SELECT dbo.Login.LName, dbo.Login.FName, dbo.Login.Team,
SUM(CASE CType WHEN 'Claims' THEN 1 ELSE 0 END)
AS Claims,
SUM(CASE CType WHEN 'Inquiry' THEN 1 ELSE 0 END)
AS Inquiry,
SUM(CASE CType WHEN 'Tele-Serv' THEN 1 ELSE 0
END) AS [Tele-Serv],
SUM(CASE CType WHEN 'Adjudication' THEN 1 ELSE 0
END) AS Adjudication,
SUM(CASE CType WHEN 'Employer' THEN 1 ELSE 0
END) AS Employer,
SUM(CASE CType WHEN 'DUA' THEN 1 ELSE 0 END) AS
DUA,
SUM(CASE CType WHEN 'EUC' THEN 1 ELSE 0 END) AS
EUC,
COUNT(dbo.CallLog.CType) AS Totals
FROM dbo.CallLog INNER JOIN
dbo.Login ON dbo.CallLog.LO =
dbo.Login.StdLogonID
GROUP BY dbo.Login.LName, dbo.Login.FName, dbo.Login.Team
ORDER BY dbo.Login.Team

This works, great Thanks for your assistance.

Pete