Queries management (2003)

[mbila]

I have an application with more than 300 queries. I create some groups for some of them but now I Have some trouble to know if this query is in this group or not. I have to go through each group to see which queries are in it.
Are they some other way to manage queries: add- ins, code....
Any help will be appreciated,
Regards,
Marcel

[charlotte]

The usual way to do this is through the names of the queries. Name queries you want to "group" with the same prefix.

[mbila]

So I think I will have to do this way.
Thank you for your assistance,
Marcel

[hasse]

I guess many have their own ways... but... just to give idea's & maybe start some discussion, these are some rules I try to use (all critics & suggestions welcome <img src=/S/smile.gif border=0 alt=smile width=15 height=15>:
- starting point: Reddick naming guidelines, recent Reddick's naming convention(RVBA Conventions, Maya Calendar, et.al.) (table 8) and/or a more extended outline at the on-line version of Leszinsky's Access 97 Expert Solutions (Chapter 5 & Appendix C) (1);
- limit query name length using consistent standard abbreviations in the query names, like 'Cust' for Customer;
- in the name you can include the fields combined in crosstab queries in a fixed order, e.g. qcrValue_RowTitle_ColumnTitle
- in the name you can include the fields on which data is grouped, e.g. qtotCust_Country
- with parameter queries, you can include the field(s) used as parameter(s) at the end of the query name, e.g. ..._ParYear (2);

You might also consider a table with query metadata (fields to categorise on,... but I never needed this + it might be difficult to set up & maintain properly).

(1) If anyone knows a worthy successor of this old but valuable work...
(2) Sometimes, you can reduce the amount of queries with a parameter query - form combination. For example, the series qryResults_2000, qryResults_2001, etc can be replaced by qryResults_ParYear, where the (parameter) criterium refers to a combo box on a form (e.g. when many forms, queries and reports are year-related, you can put such combo on the main form where the user selects the required year before heading further into the database).

[WendellB]

In addition to your suggestions, another way to reduce the number of queries is to store the query string as a data source for the form or report. We often take that approach where performance isn't critical, or it isn't a huge SQL statement. Doing that also protects you from prying fingers where someone decides to modify a query that is the data source for a form, and then your form breaks - using an MDE doesn't protect you from that kind of tinkering.

[hasse]

In addition to Wendell's suggestion: in case you still prefer queries as the recordsource for forms & reports as queries, you can think about naming them like 'qfrmName' and 'qrptName', with qfrm... = the form for which they serve as recordsource or (maybe - I haven't been in that situation yet) the most important form if more than one is 'served' by this query...
If you don't want to loose the info provided by the qsel/qcr/qtot/...-prefix, I suggest you could even combine both prefixes.

One other point: it might be worth thinking about the desired query sort order (in case you can only rely on the database window to manage the queries), as this is determined by your query naming logic too.

Finally, I noticed there might be some valuable further reading available on the net. To find those, you google/... on search terms like "query naming (convention OR tips)"and end up finding sites like http://www.ahinc.com/support/access.htm#Naming Conventions, etc.

Good luck ;-).