[AccessD] MDB to SQL Server

Hi Jürgen

Maybe that is part of your problem. I often use one a main query joined with different subqueries, each saved as a new query that the report pulls data from - which query to be used is set by the code than runs the report.

However, often this can be boiled down to 1) define the selection of ID to pull, 2) pull the "real" data for that selection of IDs.
A lot of time can be saved if the selection if IDs is written to a temp table which then is inner joined to select and extract the real data.

I remember on example on reporting taxes to the authorities on liquors delivered, based on both percentage of alcohol and the type of liquors as well as rates varying over time. A nice setup of queries fixed it perfectly but it took "ages" to run. By using one temp table I cut minutes to seconds.
I know many refrain from temp tables because they are scared of bloating the mdb (avoid that by using a separate local temp database you recreate each time the user launches the app) but most (all?) SQL server engines feature automatic maintenance of temp tables.

/gustav

>>> jwelz at hotmail.com 22-02-2007 04:05:23 >>>

..  Same report but involving joins, and sometimes equal and other times 
unequal joins to an indeterminate variety of tables.  Sometimes 1 table, 
sometimes 5 or 6, sometimes with parameters on some fields, at other times 
with parameters on other fields.  I couldn't imagine doing this with saved 
queries.