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

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

Re: VBA code to dynamically create an SQL query for Access db

Show all headers | View raw

On 18/07/2012 12:54, colmkav wrote:
> 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" .....

Would something like a crosstab query help in this case?

Oh, and multiple tables like that containing the same type of data went 
out of fashion ages ago.
Why not a simple table as such:
Client-ID	RuleNum
1233		0
6725		20
4545		1
6725		7
1233		19

-- 
Deanna Earley (dee.earley@icode.co.uk)
i-Catcher Development Team
http://www.icode.co.uk/icatcher/

iCode Systems

(Replies direct to my email address will be ignored. Please reply to the 
group.)

Back to comp.lang.basic.visual.misc | Previous | Next — Previous in thread | 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