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

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

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

Show all headers | View raw

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.

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