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