Francisco Tapia
fhtapia at gmail.com
Mon May 14 13:33:52 CDT 2007
ok, found it http://www.simple-talk.com/sql/t-sql-programming/creating-cross-tab-queries-and-pivot-tables-in-sql/ On 5/14/07, Francisco Tapia <fhtapia at gmail.com> wrote: > > 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... -- -Francisco http://sqlthis.blogspot.com | Tsql and More...