Too many queries

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

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Evi" wrote in message
...
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?

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" wrote in message
...
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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Evi" wrote in message
...
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?

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Evi" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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?

Thanks Allen, Good advice.
Evi

"Allen Browne" wrote in message
...
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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Evi" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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?