VBA code to dynamically create an SQL query for Access db

[colmkav]

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];

[Stefan Ram]

Post by colmkav
I think my main issue is all the brackets in this query just after the "FROM" .....

Sub Example()
Dim t As String
Dim i As Integer
For i = 0 To 3
t = "(" & t & i & ")"
Next i
Debug.Print t
End Sub

[Deanna Earley]

Post by colmkav
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
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

[colmkav]

Post by Deanna Earley
Oh, and multiple tables like that containing the same type of data went
out of fashion ages ago.
Client-ID RuleNum
1233 0
6725 20
4545 1
6725 7
1233 19

The purpose of the table is that all the rules the client (eg 1233) had breached is summarised in 1 row. ie easy for the customer to read the report.

[Deanna Earley]

Post by colmkav

Post by Deanna Earley
Oh, and multiple tables like that containing the same type of data went
out of fashion ages ago.
Client-ID RuleNum
1233 0
6725 20
4545 1
6725 7
1233 19

The purpose of the table is that all the rules the client (eg 1233) had breached is summarised in 1 row. ie easy for the customer to read the report.

Yes, that's the point of a crosstab query.

[ralph]

On Wed, 18 Jul 2012 08:10:03 -0700 (PDT), colmkav

Post by colmkav

Post by Deanna Earley
Oh, and multiple tables like that containing the same type of data went
out of fashion ages ago.
Client-ID RuleNum
1233 0
6725 20
4545 1
6725 7
1233 19

The purpose of the table is that all the rules the client (eg 1233)
had breached is summarised in 1 row. ie easy for the customer
to read the report.

I'll stick my two cents in ...

One of the first rules of database design is to keep Data separate
from Presentation. (Actually there are so many "first rules" in
design, not sure if this is THE first rule or not, but certainly
deserves honorable mention. <g>)

The second you decided to design a table to be a 'view', you were in
trouble. Logically store and normalize your data - then write Views as
needed.

-ralph

[Farnsworth]

Post by ralph
One of the first rules of database design is to keep Data separate
from Presentation. (Actually there are so many "first rules" in
design, not sure if this is THE first rule or not, but certainly
deserves honorable mention. <g>)
The second you decided to design a table to be a 'view', you were in
trouble. Logically store and normalize your data - then write Views as
needed.
-ralph

I agree with Ralph. Here is a link to show what database normalization means
in case you weren't familiar with it:

http://en.wikipedia.org/wiki/Database_normalization

[colmkav]

Post by ralph
On Wed, 18 Jul 2012 08:10:03 -0700 (PDT), colmkav
&gt;&gt;
&gt;&gt; Oh, and multiple tables like that containing the same type of data went
&gt;&gt; out of fashion ages ago.
&gt;&gt; Client-ID RuleNum
&gt;&gt; 1233 0
&gt;&gt; 6725 20
&gt;&gt; 4545 1
&gt;&gt; 6725 7
&gt;&gt; 1233 19
&gt;&gt;
&gt;The purpose of the table is that all the rules the client (eg 1233)
&gt; had breached is summarised in 1 row. ie easy for the customer
&gt; to read the report.
I&#39;ll stick my two cents in ...
One of the first rules of database design is to keep Data separate
from Presentation. (Actually there are so many &quot;first rules&quot; in
design, not sure if this is THE first rule or not, but certainly
The second you decided to design a table to be a &#39;view&#39;, you were in
trouble. Logically store and normalize your data - then write Views as
needed.
-ralph

Some of the subqueries though take a long time to run and are used by numerous queries. So is it not better to save the results in temporary tables and run the queries of these rather than rerun the subqueries each time?

How would I write my query as a crosstab query? I am not that familiar with crosstab queries but when I had a look it didnt seem compatible with what I am trying to do.

[ralph]

On Thu, 19 Jul 2012 01:24:05 -0700 (PDT), colmkav

Post by colmkav

Post by ralph
On Wed, 18 Jul 2012 08:10:03 -0700 (PDT), colmkav
&gt;&gt;
&gt;&gt; Oh, and multiple tables like that containing the same type of data went
&gt;&gt; out of fashion ages ago.
&gt;&gt; Client-ID RuleNum
&gt;&gt; 1233 0
&gt;&gt; 6725 20
&gt;&gt; 4545 1
&gt;&gt; 6725 7
&gt;&gt; 1233 19
&gt;&gt;
&gt;The purpose of the table is that all the rules the client (eg 1233)
&gt; had breached is summarised in 1 row. ie easy for the customer
&gt; to read the report.
I&#39;ll stick my two cents in ...
One of the first rules of database design is to keep Data separate
from Presentation. (Actually there are so many &quot;first rules&quot; in
design, not sure if this is THE first rule or not, but certainly
The second you decided to design a table to be a &#39;view&#39;, you were in
trouble. Logically store and normalize your data - then write Views as
needed.
-ralph

Some of the subqueries though take a long time to run and are used
by numerous queries. So is it not better to save the results in
temporary tables and run the queries of these rather than rerun
the subqueries each time?
How would I write my query as a crosstab query? I am not
that familiar with crosstab queries but when I had a look
it didnt seem compatible with what I am trying to do.

Ha. You just had to bring performance into the discussion. <g>

Any question on performance at this point is impossible to answer
intelligently, due the number of possibilities and the simple fact we
don't have all the details. Even vague generalities are likely of
little use. For example, I could state that an outer join is going to
out-perform any sub-query solution - and probably be correct most of
the time. However, because of the ways the Query Optimizer might
handle sub-queries, or even the data types involved, actual results
may be very different or even unnoticeable.

A stored or pre-optimized query should be an improvement over a
dynamic query.

Performance is not something to tackle until you have good
architecture and a logically correct query.

Supply more details. The tables and data types. The questions you need
answered (Queries). The environment - what version of MS Access (or
what database), where is this VBA code running? ...

There are some very bright database people around here. You are likely
to get good answers, but they have to have something to chew on. <g>

-ralph