Managing queries based on other queries

[Fred Boer]

Hello!

I am engaged in a bit of spring cleaning in my little application. I am
working on organizing my queries... you know, throwing out queries that I
don't actually use anymore, adding comments to their description fields to
show what forms use which queries, etc.. I have found that I have a number
of queries which are used by various forms, in different places (such as the
data source for one form, or a row source for a combobox on another form).

I am concerned that, having used the original query in a number of different
places, if I ever happen to modify the original query, I might
unsuspectingly break things in other places. Is it bad practice to use
queries in this way? Should I have created a new query for use in each
individual circumstance? This would lead to a lot of queries, I suspect, but
I suppose with a good naming convention it might be easier to keep track of
them all - and if I ever edited a query I would know that it would affect
only the existing control.

Or would it be better practice, for example, to only use saved queries as
data sources for forms, and for comboboxes, use embedded SQL statements? I
actually was experimenting with replacing *all* my saved queries with
embedded SQL statements when this issue began to nag at me...

Thanks!
Fred Boer

 

[Allen Browne]

Hi Fred. Another good question.

There is no right answer, of course, but our practice is to use the
Description property of the QueryDef to document where it is used. So, if we
use the query as the source for a form, and then you reuse it for a report,
the Description reads:
Source for Form2 and Report1
If you are consistent, that does assist in maintenace later, but it's not
the perfect answer.

We very rarely use saved queries in code. It is almost always easier to
create the SQL statement on the fly, and also makes the code independent of
the saved queries (e.g. if you copy it to another database).

We always use saved queries as the RowSource of combos. We have found that
this is the simplest way to ensure that all the Client combos (for example)
are consistent. User decides later that they want to change the sort order
(e.g. to include inactive clients but sort them to the bottom), we have only
one saved query to update and all combos in the application are consistent.
Might sound silly for combos from very simple lookup tables, but consistency
is the key to maintenance. (The only exception is combos that have their
RowSource set dynamically by code in events.)

For forms, we are lazy enough to bind directly to the table where we can.
(Shock! Horror! Duck and run.) If the RecordSource involves multiple tables
or calculated fields, we generally use a saved query. Again, the exception
is the form where we will be dynamically reassigning the RecordSource in
code.

For reports, we normally use a saved query as you almost always need
multiple tables. Again the exception is the report that will have its
RecordSource assigned in Report_Open, in which case we save it unbound.

Filtering subreports present another challenge. A saved query that picks up
the value from a form usually does the trick, but occassionally we will
reassign the SQL property of the (saved) QueryDef before opening the main
report.

Hope that is useful.

 

[Fred Boer]

Hello Allen:

Hi Fred. Another good question.

I specialize in questions. I outsource answers!

There is no right answer, of course, but our practice is to use the
Description property of the QueryDef to document where it is used. So, if we
use the query as the source for a form, and then you reuse it for a report,
the Description reads:
Source for Form2 and Report1
If you are consistent, that does assist in maintenace later, but it's not
the perfect answer.

Yes, I was actually starting to do just this when I began to wonder about
the different ways my queries and forms interacted...

We very rarely use saved queries in code. It is almost always easier to
create the SQL statement on the fly, and also makes the code independent of
the saved queries (e.g. if you copy it to another database).

Good point...

We always use saved queries as the RowSource of combos. We have found that
this is the simplest way to ensure that all the Client combos (for example)
are consistent. User decides later that they want to change the sort order
(e.g. to include inactive clients but sort them to the bottom), we have only
one saved query to update and all combos in the application are consistent.
Might sound silly for combos from very simple lookup tables, but consistency
is the key to maintenance. (The only exception is combos that have their
RowSource set dynamically by code in events.)

<Chuckle> And I was thinking just the opposite... use saved queries for
forms, 'cause you might need to change the data behind them, but who would
ever change comboboxes? <g> I hadn't considered the combobox issue as you
describe it, since my little app is small enough that I don't think I have
the same combobox used more than once.

For forms, we are lazy enough to bind directly to the table where we can.

Forgive me if this is a stupid question, but would you have forms where you
don't want to set the order? Or do you use some other process to set an
order for the data when bound directly to a table?

Hope that is useful.

Of course! Thanks!

Fred

 

[Allen Browne]

Yes, you need a query where you wish to sort the items other than primary
key order.

Sometimes the order is irrelevant, esp. if the form provides other
navigation options (such as a combo in the header to jump to a record).
Other times we actually want p.k. order, e.g. we usually use natural keys (a
text field) for lookup tables (categories/types of thing).