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

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

VBA code to dynamically create an SQL query for Access db

Back to article view | Back to comp.lang.basic.visual.misc

Contents

  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

#1363 — VBA code to dynamically create an SQL query for Access db

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

[toc] | [next] | [standalone]

#1365

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.)

[toc] | [prev] | [next] | [standalone]

#1368

> 
> 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
> 
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.

[toc] | [prev] | [next] | [standalone]

#1370

On 18/07/2012 16:10, colmkav wrote:
>>
>> 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
>>
> 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.

-- 
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.)

[toc] | [prev] | [next] | [standalone]

#1371

On Wed, 18 Jul 2012 08:10:03 -0700 (PDT), colmkav
<colmjkav@yahoo.co.uk> wrote:

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

[toc] | [prev] | [next] | [standalone]

#1374

"ralph" <nt_consulting64@yahoo.com> wrote in message 
news:d2rd08p72cqps5ilngj46j74n91jltgktr@4ax.com...
> 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

[toc] | [prev] | [next] | [standalone]

#1381

On Wednesday, July 18, 2012 7:24:11 PM UTC+2, ralph wrote:
> On Wed, 18 Jul 2012 08:10:03 -0700 (PDT), colmkav
> < wrote:
> 
> >> 
> >> 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
> >> 
> >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

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.

[toc] | [prev] | [next] | [standalone]

#1389

On Thu, 19 Jul 2012 01:24:05 -0700 (PDT), colmkav
<colmjkav@yahoo.co.uk> wrote:

>On Wednesday, July 18, 2012 7:24:11 PM UTC+2, ralph wrote:
>> On Wed, 18 Jul 2012 08:10:03 -0700 (PDT), colmkav
>> &lt; wrote:
>> 
>> &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; Why not a simple table as such:
>> &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
>> deserves honorable mention. &lt;g&gt;)
>> 
>> 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

[toc] | [prev] | [standalone]

Back to top | Article view | comp.lang.basic.visual.misc

csiph-web