Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.basic.visual.misc > #1363

VBA code to dynamically create an SQL query for Access db

Show all headers | View raw

I have a query which contains a subquery (Rule composite) that is a union on 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 resultset of fields specifying which clients exist in which rule table
 
ie a table like:
 
Client-id credit manager risk manager Rule00 rule01....rule20 
1233          CK             FJ          1     0   ...   0
6725          JP             FS          0     0   ...   1
4545          CK             FS          0     1   ...   0
 
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 changing my static query. How can I do this? I think my main issue is all the brackets in this query just after the "FROM" .....
 
SELECT DISTINCT [Rule Composite].[Client-id], TmpReport1.[Client-name], TmpReport1.[Credit Manager], TmpReport1.[Risk Manager], IIf([tblRule00].[Client-id] Is Null,0,1) AS Rule00, IIf([tblRule01].[Client-id] Is Null,0,1) AS Rule01, 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([tblRule06].[Client-id] Is Null,0,1) AS Rule06, IIf([tblRule07].[Client-id] Is Null,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].[Client-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([tblRule19].[Client-id] Is Null,0,1) AS Rule19, IIf([tblRule20].[Client-id] Is Null,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] = tblRule00.[Client-id]) ON TmpReport1.[Client-id] = [Rule Composite].[Client-id]) LEFT JOIN tblRule01 ON [Rule Composite].[Client-id] = tblRule01.[Client-id]) LEFT JOIN tblRule02 ON [Rule Composite].[Client-id] = tblRule02.[Client-id]) LEFT JOIN tblRule03 ON [Rule Composite].[Client-id] = tblRule03.[Client-id]) LEFT JOIN tblRule04 ON [Rule Composite].[Client-id] = tblRule04.[Client-id]) LEFT JOIN tblRule05 ON [Rule Composite].[Client-id] = tblRule05.[Client-id]) LEFT JOIN tblRule06 ON [Rule Composite].[Client-id] = tblRule06.[Client-id]) LEFT JOIN tblRule07 ON [Rule Composite].[Client-id] = tblRule07.[Client-id]) LEFT JOIN tblRule08 ON [Rule Composite].[Client-id] = tblRule08.[Client-id]) LEFT JOIN tblRule09 ON [Rule Composite].[Client-id] = tblRule09.[Client-id]) LEFT JOIN tblRule10 ON [Rule Composite].[Client-id] = tblRule10.[Client-id]) LEFT JOIN tblRule11 ON [Rule Composite].[Client-id] = tblRule11.[Client-id]) LEFT JOIN tblRule13 ON [Rule Composite].[Client-id] = tblRule13.[Client-id]) LEFT JOIN tblRule12 ON [Rule Composite].[Client-id] = tblRule12.[Client-id]) LEFT JOIN tblRule14 ON [Rule Composite].[Client-id] = tblRule14.[Client-id]) LEFT JOIN tblRule15 ON [Rule Composite].[Client-id] = tblRule15.[Client-id]) LEFT JOIN tblRule16 ON [Rule Composite].[Client-id] = tblRule16.[Client-id]) LEFT JOIN tblRule17 ON [Rule Composite].[Client-id] = tblRule17.[Client-id]) LEFT JOIN tblRule18 ON [Rule Composite].[Client-id] = tblRule18.[Client-id]) LEFT JOIN tblRule19 ON [Rule Composite].[Client-id] = tblRule19.[Client-id]) LEFT JOIN tblRule20 ON [Rule Composite].[Client-id] = tblRule20.[Client-id]) LEFT JOIN tblRule21 ON [Rule Composite].[Client-id] = tblRule21.[Client-id];

Back to comp.lang.basic.visual.misc | Previous | Next — Next in thread | Find similar | Unroll thread

Thread

VBA code to dynamically create an SQL query for Access db colmkav <colmjkav@yahoo.co.uk> - 2012-07-18 04:54 -0700
  Re: VBA code to dynamically create an SQL query for Access db Deanna Earley <dee.earley@icode.co.uk> - 2012-07-18 15:39 +0100
    Re: VBA code to dynamically create an SQL query for Access db colmkav <colmjkav@yahoo.co.uk> - 2012-07-18 08:10 -0700
      Re: VBA code to dynamically create an SQL query for Access db Deanna Earley <dee.earley@icode.co.uk> - 2012-07-18 17:54 +0100
      Re: VBA code to dynamically create an SQL query for Access db ralph <nt_consulting64@yahoo.com> - 2012-07-18 12:24 -0500
        Re: VBA code to dynamically create an SQL query for Access db "Farnsworth" <nospam@nospam.com> - 2012-07-18 15:03 -0400
        Re: VBA code to dynamically create an SQL query for Access db colmkav <colmjkav@yahoo.co.uk> - 2012-07-19 01:24 -0700
          Re: VBA code to dynamically create an SQL query for Access db ralph <nt_consulting64@yahoo.com> - 2012-07-19 11:00 -0500

csiph-web