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

Francisco Tapia fhtapia at gmail.com
Mon May 14 12:31:16 CDT 2007


Arthur, :), it would help if you provided a link to the SP or article about
the SP :)

On 5/14/07, Arthur Fuller <fuller.artful at gmail.com> wrote:
>
> 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
>
>


-- 
-Francisco
http://sqlthis.blogspot.com | Tsql and More...



More information about the dba-SQLServer mailing list