[dba-SQLServer] [AccessD] Cross Tab Query Help Requested

Eric Barro ebarro at verizon.net
Mon May 14 10:59:09 CDT 2007


Arthur,

Do you have the link to the article?

Eric 

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Arthur
Fuller
Sent: Monday, May 14, 2007 7:19 AM
To: Access Developers discussion and problem solving
Cc: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] [AccessD] Cross Tab Query Help Requested

In my experience, the easiest solution was to use static functions in the
first query, and then base the crosstab query on that result set.

For the SQL Server folks on this list, there is an amazing stored procedure
available at Simple-Talk (www.simple-talk.com) that dynamically generates
cross-tabs as complex as you'd like. One of the big problems with cross-tabs
is that the number of columns might change from run to run. This stored
procedure gets around that.

In the interests of transparency, let me admit that I write for Simple Talk,
although I did not write this article and wish that I had. The stored
procedure is amazing.

Arthur

On 5/14/07, Gustav Brock <Gustav at cactus.dk> wrote:
>
> Hi Thomas
>
> Here's an article on using ADO and parameters:
>
> http://support.microsoft.com/kb/225897/en-us
>
> Also, look up in the archives subject "ADO code stopped working" from 
> early February this year.
>
> If it works now with DAO, I would leave it except, of course, if this 
> is a learning experience.
>
> /gustav
>
> >>> ewaldt at gdls.com 14-05-2007 13:10 >>>
> I am running into a problem with a cross tab query.
>
> I have a report based on a parameter query, which is in turn based on 
> a cross tab query (which is based on the same parameter), and ADO 
> doesn't seem happy. On MSFT's site, they use DAO in their (very 
> complex) example, and they show how to specify a parameter (qdf = a 
> QueryDef, and
> qdf.Parameters(xxx) = yyy). I really prefer to use ADO (trying to 
> learn it), but I don't see how to specify a parameter's value in ADO. 
> I'm assuming that's the problem , because Access keeps saying that I'm 
> not specifiying required info. Also, when I went through and replaced 
> all instances of parameters in the queries (query based on queries 
> based on
> queries) with solid numbers, it worked. In the actual queries, the 
> parameter is:
>
> [Forms]![frmWeeklyData]![fraMonths]
>
> This simply refers to a frame containing option buttons so that I can 
> specify the month I'm interested in. The month's number is then used 
> by the queries.
>
> Running the queries without the report works just fine. However, since 
> a cross tab query is involved, and there can be varying numbers of 
> columns, I have to use dynamic columns in the report, and that's where 
> complexity rears its ugly head. The parameter query (that calls the 
> cross tab query) is necessary because I have information in addition 
> to the cross tab query itself which is needed in the report.
>
> Here's the code portion that Access highlights:
>
> rst.Open _
>    Source:="qfrmWeeklyData", _
>    ActiveConnection:=CurrentProject.Connection, _
>    Options:=adCmdTable
>
> I'd greatly appreciate any help with this.
>
>
> Thomas F. Ewald
> Stryker Mass Properties
> General Dynamics Land Systems
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com

No virus found in this incoming message.
Checked by AVG Free Edition. 
Version: 7.5.467 / Virus Database: 269.7.0/803 - Release Date: 5/13/2007
12:17 PM
 




More information about the dba-SQLServer mailing list