Arthur Fuller
artful at rogers.com
Mon Jan 23 20:22:19 CST 2006
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