No I haven't. What you are saying is that I should use valueList to build a full list from all values in the getActiveWorks query and then listQualify to see if any variable matches. Thanks David! I will give it a shot. Does anyone else know of any other ways? David G. Moore, Jr. UpstateWeb. LLC> Subject: RE: Query Too Complex for Access?> From: [EMAIL PROTECTED]> To: cf-talk@houseoffusion.com> Date: Wed, 20 Aug 2008 13:27:34 -0500> > Have you tried:> > AND Works.ThisInventory not in> (#listQualify(valueList(getActiveWorks.ThisReference),"'")#)> > ??> > Dave> -----Original Message-----> From: David Moore [mailto:[EMAIL PROTECTED] > Sent: Wednesday, August 20, 2008 1:18 PM> To: CF-Talk> Subject: Query Too Complex for Access?> > I know I am setting myself up for another "Query too complex" issue, so> before I start I thought I would ask for suggestions. I run into this when I> have to reference two different Access databases that are Client imposed> (don't ask). Basically, I have to use one for active data and one to show> available date (minus the active data). This is, of coures, where the> problem comes in. The queries will help:> > <cfquery name="getActiveWorks" datasource="#DSN#">> SELECT * > FROM Works > WHERE Works.PageReference = #FORM.ThisPage#> AND Works.TypeReference = '#FORM.ThisType#'> </cfquery>> > <cfquery name="getWorks" datasource="#DSN2#">> SELECT *> FROM Works, Artists > WHERE Artists.ArtistNumber = Works.ArtistNumber> AND Works.Type = '#FORM.ThisType#'> <cfloop query="getActiveWorks">> AND Works.ThisInventory <> '#getActiveWorks.ThisReference#'> </cfloop></cfif>> ORDER BY Works.Title Asc> </cfquery>> > Where the cfloop is is where the problem is going to come into play when the> "Active Works" get to a certain level and the Query becomes "Too Complex".> What is the Best way to handle this? > > I am using CF8, Windows Server 2003, and MS Access w/Unicode ODBC Connector.> > > >
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311310 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
