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