[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
>