[AccessD] Dynamic Report Headers (Huh?)

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




More information about the AccessD mailing list