Jürgen Welz
jwelz at hotmail.com
Tue Jan 24 00:28:01 CST 2006
Arthur: I wasn't suggesting that one dynamically set the SQL of each subreport. Each subreport, when it has data, displays the data appropriate to its relation to the parent record. A parent result that cuts across all subcategories is what I am advocating. I recently did a database for a law office and I wound up creating a single entity table that held the names of all people and corporate bodies in a single table. After all, a Client could be a corporate body or an individual. The corporate body may be a municpality. A person who is a lawyer, who would have lawyer attributes could also be a client with the attributes of a client. Similarly I had a single address table that represented addresses of corporate bodies, law firms, and properties in respect of which we were handling transactions such as sales, liens in a law suit and places to which mail might be sent by mail or delivered by hand. An address may belong to several categories at a single time, but, if I have a client's address as a mailing address and he sells the property, I do not wish to have to re-enter the address of the property, but merely add the related details of legal description, tax assessment and encumbrances. If I were shooting for raw speed, I could have denormalized by keeping addresses with a table of properties and and with a table of entities. However, I wanted to be able to enter an address and find out what connections the data had to that address no matter what the relationship. If you open the address, there is a subform on a tab with which files are related to it and a tab with parties related to the address, as husband & wife, as lawyer and his lawfirm, legal assistant. Items related to a file may be clients that include corporate bodies, other lawyers, legal assistants, realtors, municipalities, court file information, properties, each of which may require particular information in a subform for that kind of information. None of this requires dynamic SQL to fill a form with data, and the same applies to subreports. I am quite content to use dynamic SQL to build search criteria to populate a list box. I also have no problem running 5 or 6 separate queries in sequence, appending the results to an array, sorting the array and throwing the results into a list box of hits. If you use the latter approach, it is enough to use as many simple paramaterized queries as needed without any need for dynamic SQL. You can select a literal as one of the fields to indicate in which category a particular result was returned to provide a means to navigate to an appropriate type of form upon click of an item in the list. For example, if I enter 'Edm' as a 'starts with' name criteria, I may get a lawyers, municipalities and clients, both corporate and individual, and clicking on a particular result should navigate to a form that hold the data particular to the appropriate kind of parent record. I like to allow a client to choose which categories are included and excluded in the search results list and it is easy enough, using a series of rst.getrows against each chosen category to populate an array for sorting and then use a callback to fill the list. In my opinion, searching a library of books and media for results that cut across all categories seems a natural for this general kind of approach. I always prefer to show results in a list box to allow quick navigation to more information about the data and do not like to wait for a report to open and use that screen or printout as the basis for examining a variety of potential items of interest. Ciao Jürgen Welz Edmonton, Alberta jwelz at hotmail.com >From: "Arthur Fuller" <artful at rogers.com> > >Very interesting, Jurgen, as always! I am working on an inherited app in >which a table is constructed to bang out the details of interest, and there >is a bunch of code that I don't really understand that examines what the >current group header is and then makes decisions, banging this or that text >into the group header as the code sees fit. I have never in lots of years >developing in Access approached a problem in this fashion, and to be frank >I >have no idea what is going on in this code. It seems to work but I cannot >fathom what is occurring. I have always chosen the path that a query does >the grouping etc., but this approach is totally new to me. I have no idea >what the code is doing, except to say that it walks a recordset >(accumulated >from several tables) and then decides what the group header should be. It >makes NO sense to me. I seem to live on another planet. My choice would >have >been to create one or more queries that do the groups and then proceed, but >this was not the previous developer's choice, and his code works, so what >can I say except that I have no idea what he is doing. He is walking >recordsets and examining what rs1's value is, then making decisions about >what to print as the group header, then printing the detail rows for that >header, and so on. I have read the code about 110 times and I still don't >get it. I was under the impression that this is why we created >relationships. >Stuff like this is the bane of my life as a developer. I was raised under >the wing that whenever possible, one should construct a query that does >what >you intend, and that the last possible resort (necessary now and then >admittedly) is that you construct a strSQL statement on the fly and then >run >it... but that is IMO the last possible resort. I HATE going in that >direction. My choice is to create a query (assuming MDB) that invokes as >many static functions (or using JC's method, class-methods) that return the >values of interest. I HATE HATE HATE dynamically constructed SQL >statements. >They are hard to read, hard to debug, and take no advantage of the SQL >optimizer. Nuff said. >IMO, this is even more true if the BE is SQL rather than MDB, but in both >cases I think that my argument is correct. Building SQL statements, IMO, is >asinine. I want the statement to live on its own, wanting only a few >arguments. That way I can test the statement without relying on a form or >report to invoke it. I want to create a named query that accepts some >arguments (my choice is statics but c.f. JC for class-based alternatives), >such that any given query can be invoked from the immediate window, >supplied >with some arguments, and run to see if it works. >Now, back to your immediate response. Depending on file size, I am >definitely in favour of creating a Results table: gather the data and then >create a stupid flat file report that lists said results. I have no problem >at all with approaching a problem in that fashion. In fact I have >benchmarks >that demonstrate that this approach results in much quicker reporting. No >problem at all with that. My argument is against dynamic SQL. I think that >in most cases constructing the SQL statement in Access or any other front >end is foolish. (Admittedly there are a few cases in which it is almost >impossible to fire a stored query or a stored sproc/UDF, but I think that >those cases number a very, very few.) >Arthur > >-----Original Message----- >From: accessd-bounces at databaseadvisors.com >[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jürgen Welz >Sent: January 23, 2006 12:50 PM >To: accessd at databaseadvisors.com >Subject: Re: [AccessD] Virtual library management database > >Ervin: > >This is a subject on which opinions will vary along lines of what people >perceive the priorities are. A flatter file will generally provide better >performance and a more normalized one will ordinarilly be be both slower >and > >more complex to query yet provide greater flexibility. > >Generally, it is easier to query if you include general items such as Title >and Author in a single table that encompasses all categories and types of >media. Where a object may not have an attribute not universal to other >objects, it makes sense to use an unequal join to such tables to return >those attributes. A user search could then return all medai types and all >relevant attributes on a generic search on a word in the title. You can >normalize this as much as necessary by joining tables containing attributes >of specific media categories. > >For example, if you store information about an article in a magazine, your >base table containing an periodical article name and author could easily be >queried and be joined to a magazine article table that stores the >containing > >Magazine information and page references. This would allow the user to be >able to include all types of media in a single search. > >If you break your tables along media type lines, it would be necessary to >union your search results across all the tables to provide an encompassing >search. Union queries cannot (in versions of Access I am familiar with) >include memo fields that may be necessary to contain descriptive >informaion. > >Using a report as output simplifes things in that you can use a sub report >for each category, each with its own SQL source. However, my preference is >to always provide a list output where a user can click on an item and have >it open the appropriate form that contains all relevant information with >appropriate joins, perhaps with the foreign keyed data in subforms. >Although the Union restriction on memo types will not apply in the case of >search results in a list box, I find it easier to return results from a >single table and show the media type and allow futher filtering on type of >media in the list result. > >Ciao >J|rgen Welz >Edmonton, Alberta >jwelz at hotmail.com