Path: csiph.com!usenet.pasdenom.info!gegeweb.42!gegeweb.eu!nntpfeed.proxad.net!feeder1-2.proxad.net!proxad.net!feeder1-1.proxad.net!198.186.190.251.MISMATCH!news-out.readnews.com!transit4.readnews.com!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail From: colmkav Newsgroups: comp.lang.basic.visual.misc Subject: VBA code to dynamically create an SQL query for Access db Date: Wed, 18 Jul 2012 04:54:57 -0700 (PDT) Organization: http://groups.google.com Lines: 60 Message-ID: <1e0600a4-e7ef-4eff-9001-45c41482ec1b@googlegroups.com> NNTP-Posting-Host: 87.213.36.165 Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Trace: posting.google.com 1342612623 18174 127.0.0.1 (18 Jul 2012 11:57:03 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Wed, 18 Jul 2012 11:57:03 +0000 (UTC) Complaints-To: groups-abuse@google.com Injection-Info: glegroupsg2000goo.googlegroups.com; posting-host=87.213.36.165; posting-account=cpFJtQoAAACE5G50eepA_WYY8wpcKnn7 User-Agent: G2/1.0 Xref: csiph.com comp.lang.basic.visual.misc:1363 I have a query which contains a subquery (Rule composite) that is a union o= n 20 rule tables (rule01 to rule20). The query is a series of left joins on= the rule tables with the subquery (so that it basically returns a resultse= t of fields specifying which clients exist in which rule table =20 ie a table like: =20 Client-id credit manager risk manager Rule00 rule01....rule20=20 1233 CK FJ 1 0 ... 0 6725 JP FS 0 0 ... 1 4545 CK FS 0 1 ... 0 =20 I have a static query at the moment as follows. I would like to dynamically= create this via VBA code so that if I add new rules I dont have to keep ch= anging my static query. How can I do this? I think my main issue is all the= brackets in this query just after the "FROM" ..... =20 SELECT DISTINCT [Rule Composite].[Client-id], TmpReport1.[Client-name], Tmp= Report1.[Credit Manager], TmpReport1.[Risk Manager], IIf([tblRule00].[Clien= t-id] Is Null,0,1) AS Rule00, IIf([tblRule01].[Client-id] Is Null,0,1) AS R= ule01, IIf([tblRule02].[Client-id] Is Null,0,1) AS Rule02, IIf([tblRule03].= [Client-id] Is Null,0,1) AS Rule03, IIf([tblRule04].[Client-id] Is Null,0,1= ) AS Rule04, IIf([tblRule05].[Client-id] Is Null,0,1) AS Rule05, IIf([tblRu= le06].[Client-id] Is Null,0,1) AS Rule06, IIf([tblRule07].[Client-id] Is Nu= ll,0,1) AS Rule07, IIf([tblRule08].[Client-id] Is Null,0,1) AS Rule08, IIf(= [tblRule09].[Client-id] Is Null,0,1) AS Rule09, IIf([tblRule10].[Client-id]= Is Null,0,1) AS Rule10, IIf([tblRule11].[Client-id] Is Null,0,1) AS Rule11= , IIf([tblRule12].[Client-id] Is Null,0,1) AS Rule12, IIf([tblRule13].[Clie= nt-id] Is Null,0,1) AS Rule13, IIf([tblRule14].[Client-id] Is Null,0,1) AS = Rule14, IIf([tblRule15].[Client-id] Is Null,0,1) AS Rule15, IIf([tblRule16]= .[Client-id] Is Null,0,1) AS Rule16, IIf([tblRule17].[Client-id] Is Null,0,= 1) AS Rule17, IIf([tblRule18].[Client-id] Is Null,0,1) AS Rule18, IIf([tblR= ule19].[Client-id] Is Null,0,1) AS Rule19, IIf([tblRule20].[Client-id] Is N= ull,0,1) AS Rule20, IIf([tblRule21].[Client-id] Is Null,0,1) AS Rule21 FROM (((((((((((((((((((((TmpReport1 INNER JOIN ([Rule Composite] LEFT JOIN= tblRule00 ON [Rule Composite].[Client-id] =3D tblRule00.[Client-id]) ON Tm= pReport1.[Client-id] =3D [Rule Composite].[Client-id]) LEFT JOIN tblRule01 = ON [Rule Composite].[Client-id] =3D tblRule01.[Client-id]) LEFT JOIN tblRul= e02 ON [Rule Composite].[Client-id] =3D tblRule02.[Client-id]) LEFT JOIN tb= lRule03 ON [Rule Composite].[Client-id] =3D tblRule03.[Client-id]) LEFT JOI= N tblRule04 ON [Rule Composite].[Client-id] =3D tblRule04.[Client-id]) LEFT= JOIN tblRule05 ON [Rule Composite].[Client-id] =3D tblRule05.[Client-id]) = LEFT JOIN tblRule06 ON [Rule Composite].[Client-id] =3D tblRule06.[Client-i= d]) LEFT JOIN tblRule07 ON [Rule Composite].[Client-id] =3D tblRule07.[Clie= nt-id]) LEFT JOIN tblRule08 ON [Rule Composite].[Client-id] =3D tblRule08.[= Client-id]) LEFT JOIN tblRule09 ON [Rule Composite].[Client-id] =3D tblRule= 09.[Client-id]) LEFT JOIN tblRule10 ON [Rule Composite].[Client-id] =3D tbl= Rule10.[Client-id]) LEFT JOIN tblRule11 ON [Rule Composite].[Client-id] =3D= tblRule11.[Client-id]) LEFT JOIN tblRule13 ON [Rule Composite].[Client-id]= =3D tblRule13.[Client-id]) LEFT JOIN tblRule12 ON [Rule Composite].[Client= -id] =3D tblRule12.[Client-id]) LEFT JOIN tblRule14 ON [Rule Composite].[Cl= ient-id] =3D tblRule14.[Client-id]) LEFT JOIN tblRule15 ON [Rule Composite]= .[Client-id] =3D tblRule15.[Client-id]) LEFT JOIN tblRule16 ON [Rule Compos= ite].[Client-id] =3D tblRule16.[Client-id]) LEFT JOIN tblRule17 ON [Rule Co= mposite].[Client-id] =3D tblRule17.[Client-id]) LEFT JOIN tblRule18 ON [Rul= e Composite].[Client-id] =3D tblRule18.[Client-id]) LEFT JOIN tblRule19 ON = [Rule Composite].[Client-id] =3D tblRule19.[Client-id]) LEFT JOIN tblRule20= ON [Rule Composite].[Client-id] =3D tblRule20.[Client-id]) LEFT JOIN tblRu= le21 ON [Rule Composite].[Client-id] =3D tblRule21.[Client-id];