Path: csiph.com!v102.xanadu-bbs.net!xanadu-bbs.net!news.glorb.com!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail From: colmkav Newsgroups: comp.lang.basic.visual.misc Subject: Re: VBA code to dynamically create an SQL query for Access db Date: Thu, 19 Jul 2012 01:24:05 -0700 (PDT) Organization: http://groups.google.com Lines: 35 Message-ID: References: <1e0600a4-e7ef-4eff-9001-45c41482ec1b@googlegroups.com> <141ae15f-9c79-44e1-bfef-d894b1f96715@googlegroups.com> NNTP-Posting-Host: 87.213.36.165 Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 X-Trace: posting.google.com 1342686245 25968 127.0.0.1 (19 Jul 2012 08:24:05 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Thu, 19 Jul 2012 08:24:05 +0000 (UTC) In-Reply-To: Complaints-To: groups-abuse@google.com Injection-Info: glegroupsg2000goo.googlegroups.com; posting-host=87.213.36.165; posting-account=cpFJtQoAAACE5G50eepA_WYY8wpcKnn7 User-Agent: G2/1.0 Xref: csiph.com comp.lang.basic.visual.misc: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.