Organisation for Queries (1 Viewer)

[GK in the UK]

Just wondering .. how do you organise your queries ? Naming conventions ? Main vs. sub ? After a form ?

Now I have more than a handful of saved queries I'm feeling the need for some kind of method of knowing how each one is used. There's nowhere to save a narrative about the query, unless I missed it.

 

[Micron]

my query and form names usually reflect the intended purpose, thus they contain similar characters that tie them together conceptually.

Right click on object in nav pane; select properties - there is a dialog for notes.
There is also the db documenter, which you can tweak to show various properties such as record source (if applicable).

 

[Gasman]

I use as prefix, qry for a Select query, qryA for an Append query, qryU for an Update query, and likely to use qryD for a delete query, though hardly ever touch those.
If I had a frmEmail, or rptEmail then the query would be qryEmail etc.

 

[The_Doc_Man]

On three different projects I've had three different naming conventions and ended up not liking each one.

I think the rule has to be "use something that makes sense to you. DON'T name queries when you have been awake more than 16 hours continuously beforehand.

 

[plog]

Depends on what the database is for. In general I have 2 sections for queries--main queries (action queries and queries forms/reports are based on) get one name and sub queries get prefixed with 'sub_' and other data.

In data processing databases (no forms or reports, just tables and queries to extract, transform and load data) for main queries I use number prefixes and then a query description so that they will always show in the correct processing order (005_QueryDescription, 010_QueryDescription, 015_QueryDescription, ...) and subqueries that feed into those main queries get prefixed with 'sub', the number of the main query they go to and then a letter (sub_015_A, sub_015_B, sub_025_A). I always increment the main queries by 5's because invariable I will need to add a query between steps sometime in the future.

In user systems I give main queries a descriptive name (LastUsedProducts, CurrentInventory) and then sub queries that feed those get 'sub_' the name and a letter (sub_LastUsedProducts_A, sub_CurrentInventory_A).

Lastly, I used to do ad hoc querying for project managers and invariably they came back to me days later for updates. These main queries I prefixed with the date (mmddyy) and the name of the project (072220_ParkingExpansion) and the subs got named similarly (sub_072220_ParkingExpansion_A). My deliverable was an Excel file and I always named that the name of the main query (072220_ParkingExpansion.xlsx). So, in 3 weeks when I received an email that said 'I need this file updated', it took me 2 seconds to get to the query.

 

[Isaac]

This is such a wide-open topic, I guess I'll just list a few principles that I can think of that I am strict with myself on:

• Choose names (this goes for ALL objects) that will be as helpful as possible to the person who comes after you. Best practice, always assume there will be a 'next' or 'additional' person who has to try to suddenly figure out your database. Choose names that help a person with minimal inside knowledge understand.
• Group similar objects with their broader membership toward the beginning (left hand side) of the name, and more specific membership toward the end of the name. This affects sorting and everything else. I.E.: qryLetters_Clients_Monthly, qryLetters_Clients_Daily, qryLetters_Sponsors_Monthly etc.
• Delete obsolete objects! How many times I have inherited a database, somewhat shocked to find 300 linked tables. Invariably the person "on their way out" will inform me that 75% of those aren't used any more. In cases where they haven't told me that and a migration project is at hand, I would have had to figure out the potential usage of hundreds of unnecessary things. Delete all your "test copies" of objects, once finished. I've actually learned to write a lot of code for the sole purpose of auto-documenting Access objects, and it's mostly because of tons of garbage left in databases.
• Simply document each object in separate documentation
• Don't use spaces or symbols/characters in object names

Doc is right - naming conventions are notoriously one of those "hindsight is 20 20" things. But knowing that up front, you can strive to avoid names that box you into a corner as much as you can.
You are right to note this as an important topic. It will affect the development and maintenance of your db for years to come. I feel even more strongly about the need for careful variable and procedure naming in code. (post 7, point 5) Great question.

 

[Micron]

Surely there are almost as many valid approaches as there are experienced developers and it's a matter of consistency coupled with knowledge of what to do and not do. I for one don't worry about defining what type a query is because when developing, I group objects by type and just look at the icons that tell me which is a select vs append query. Not that this is better, it just serves me well enough. However, I do append sub to a subform, subreport and a query that supports either. If the question about naming conventions was meant to apply to everything, then I more or less follow this

P.S. if a moderator could remove the black formatting tags from that post, it would be good. Looks like this in dark theme:

 

[MajP]

You can also put queries into custom groups, like a folder.

Customize the Navigation Pane - Microsoft Support

Create custom categories and groups in the Navigation Pane, so you can organize your Access database objects to fit your needs.

support.microsoft.com

 

[Cronk]

Over the years I've been contacted to fix or enhance a large numbers of systems developed by others. A glance at the names of objects and code variables reveals the expertise level of the creator. The good developers are those that use a consistent, clear and concise naming scheme. Whether an update query is prefixed by qudt or qryU does not worry me. I rename every control that gets referred to in a query or code or wherever but I don't bother with label names if I'm not invoking an event or changing a property.

 

[GK in the UK]

Thanks all, some good tips there which will enhance my organisation. Always good to hear how others are doing stuff. On the related subject of readable code, I find that if I review my comments some time after I wrote them, I realise I need to make things clearer (including, for myself)

 

[isladogs]

Just to add to previous answers, wherever possible I usually use SQL statements these days rather than saved queries as I prefer having all code in one place.
In the past I only used saved queries and so the number of queries could get very large.
At an extreme, in one of my largest commercial apps there are over 1000 queries before I mostly changed over to SQL statements!
Having a consistent naming convention both for my needs and for other future developers became absolutely essential.
So I used very descriptive names e.g qryAppend..., qryUpdate..., qryDelete..., qryUnion..., qryCtb... etc followed by a description of purpose.
Any temp queries that I knew would be needed only briefly were prefixed by a #.
Any queries or other items that were candidates for later deletion were prefixed with a tilde ~ and hidden in the nav pane for a month or two until I was sure they could be safely deleted.