what is the adp equivalent for crosstab??

  • Thread starter Thread starter Margaret D via AccessMonster.com
  • Start date Start date
M

Margaret D via AccessMonster.com

We are using SQLServer 2k with Access XP. I actually found code on
SQLServerCentral to simulate using actual data values as column headings, but
after a lot of time trying to discover how to accomplish it in adp for a
report. I am going to use the dynamic reporting suggestions from here and
ADH. I would just like to know for the future if there is an easier way and
if MS provided an equivalent in adp.

Thanks,
Peg
 
Peg:

I can't give you a good answer on how to do dynamic crosstabs in SQL, it
seems to me that you'd have to create the sproc dynamically based on the
columns you want at run time, which of course you could do using VB code,
possibly opening a view on the data to determine what's there first and then
use CreateProcedure..... However, that said.....

---------------------

MS Access MVP Mary Chipman posted this some time back ... perhaps it might
help
you out with the cross tabs ...

--------------------------------------------------------------------------------
Transact-SQL doesn't support the syntax for creating crosstab queries.
if you create a crosstab query in access, you can always run it by
using the OpenQuery syntax from sql server if you have linked your
access database as a linked server. in the following example AccessDB
is the name of the linked server:

SELECT *
FROM OPENQUERY(AccessDB,
'TRANSFORM Count(tblOrder.OrderID) AS CountOfOrderID
SELECT [LastName] & ", " & [Firstname] AS Name
FROM tblCustomer
INNER JOIN tblOrder ON tblCustomer.CustomerID = tblOrder.CustomerID
GROUP BY [LastName] & ", " & [Firstname]
PIVOT Format([OrderDate],"mm/yyyy")')
--------------------------------------------------------------------------------

What it is doing is actually pretty nifty. The data resides on SQL Server.
SQL
Server is calling back to Access to do a cross tab. Access can do the cross
tab, because
it has the data in SQL Server available as a linked table. In short, SQL
Server
is calling Access which in turn is calling SQL Server. No idea of what the
performance will be however.

To fully understand it, you'll going to have to do research in the SQL
Server
Books Online on "OPENQUERY" and "linked servers".

----------end chipman post-------------------------------------

This of course assumes you are using linked tables mdb and not an ADP,
however, you could use an mdb as an adjunt to your ADP (if that's the format
you are using) to generate the crosstabs. Note that when doing it this
way, you are going to create a lot of network traffic unless the mdb is run
by an instance of jet on the server, rather than locally......
 
Back
Top