If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Too many queries
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? |
#3
|
|||
|
|||
Too many queries
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? |
#4
|
|||
|
|||
Too many queries
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? |
#5
|
|||
|
|||
Too many queries
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? |
Thread Tools | |
Display Modes | |
|
|