Too many queries

[Evi]

My database is finished but now I seem have a lot of queries. Most of them
are based directly on the tables and have very similar functions in them. If
I decide to add or update a function, I find that I have to go into each
query to edit their version of the function.
One updatable query contains just about all my functions
Instead of having lots of queries based on tables, what if I had most of my
queries based directly on this main query? It would make it easier if I
added or updated a function. What are the pros and cons of doing this and
anything I should have to watch out for?

Evi

 

[Allen Browne]

The idea of basing a query on another one to save maintenace sounds like a
good one. Assuming appropriately designed tables (nomralized, indexed, etc),
there may be no perceptible difference in performance, and it may be much
easier to maintain -- well worth the effort.

If you have lots of queries that are very similar, there may be other ways
to achieve the end. For example, if you created a query for January sales,
another for Feb sales, and so on, it would be a much better idea to create
an inferface where you can enter the limiting criteria and apply a filter to
your form/report rather than using saved queries. Similarly, you can often
build a SQL statement in VBA code rather than have the code relying on a
saved query.

 

[Evi]

Thanks for answering my performance concerns, Allen.
I was also concerned that giving a query 'too much work' could result in db
corruption. What are the circumstances where I would need a different
uery - for instance, if, for some strange reason, I had 2 subforms within a
mainform, both based on the same query, could both subforms run from the
same query, or would I be safer having them run from 2 different queries

How is indexing affected when a query is built on a query? I'm hardly aware
of indexing except when I want to set up a Unique index in a table.
Evi

 

[Allen Browne]

2 subforms based on the same query is no problem. In fact this makes good
sense if the LinkMasterFields/LinkChildFields causes different filtering.

JET should be smart enough to use the index through the query. The Fields in
a QueryDef have no trouble selecting the right SourceTable, so it's going to
use the indexes.

I can't recall any scenario where stacked queries affects corruption in any
way, other than crashes caused by yes/no fields that are null (e.g. on the
outer side of a join:
http://allenbrowne.com/NoYesNo.html
Avoid that situation, and you should be fine IME.

 

[Evi]

Thanks Allen, Good advice.
Evi

2 subforms based on the same query is no problem. In fact this makes good
sense if the LinkMasterFields/LinkChildFields causes different filtering.

JET should be smart enough to use the index through the query. The Fields in
a QueryDef have no trouble selecting the right SourceTable, so it's going to
use the indexes.

I can't recall any scenario where stacked queries affects corruption in any
way, other than crashes caused by yes/no fields that are null (e.g. on the
outer side of a join:
http://allenbrowne.com/NoYesNo.html
Avoid that situation, and you should be fine IME.