Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Q and A _ Naming Versions Of Queries During Development

Posted by: home70 Nov 4 2019, 11:06 AM

First, I'll say, because maybe it matters, that at this point I am a plodder at writing queries that combine multiple tables in multiple ways and/or use any but the more basic commands. I don't create db's much, so writing queries does not come naturally to me at all (yet. Maybe one day, I hope, bc I do love Access's abilities.).

My question is, what is the best way to name my queries as I am developing and testing and building on them?

I tend to want to save each version in case I want to refer back to it. But I can't find a way to name them that tells what it does because what it does really cannot be specified without looking at the entire text of it.

Is there a good system I could use? Or does anyone have advice on this process?

Thanks!

Posted by: Daniel_Stokley Nov 4 2019, 11:18 AM

Hello,

I like to start my query names with a two part prefix, then a short description, then, if necessary, a version number.

For example: qry_A01_BuildTableA: qry is the first part of the prefix; A01 is the second part of the prefix; BuildTableA is a short description.

In the case of that example, if I decide I want to replace it, I will make a copy and name the copy qry_A01_BuildTableA_V1. The active or current version doesn't have a version number; only earlier versions. That way, if another query uses qry_A01_BuildTableA as a source, I will not have to rewrite that other query.

HTH, Daniel



Posted by: DanielPineault Nov 4 2019, 11:21 AM

This is entirely a personal preference.

I like to always include a date, sometime time, stamp so they can be easily sorted.

The critical thing isn't the exact format, but more that you stick with whatever you choose.

Posted by: GroverParkGeorge Nov 4 2019, 11:28 AM

Each person probably has their own preferences, although there are some commonly adopted approaches. Here are some thoughts.

I prefer full names over abbreviations and acronyms.

"apdNewProjectDetails", not "apdNewPD", and so on.

While you may remember that PD is short for ProjectDetails today, three weeks from now, that may be less clear.

I use prefixes to sort queries according to type:

"apdXXXXX" for append queries
"updXXXXX" for update queries
"delXXXXX" for delete queries
"selXXXXX" for select queries

Others prefer to use a suffix instead so that queries sort by related names:

"ProductCatalogapd"
"ProductCatalogupd"
"ProductCatalogdel"
"ProductCatalogsel"

I also like to prefix obsolete queries with a "Z" so that they sort to the bottom of the list, but don't delete them until I am really sure they are no longer useful.

If you have queries that need to run in a specific sequence when called from a VBA process, I like to prefix with the number:

"1delOldRecordsFromTempTable"
"2apdNewRecordstoTempTable"



Posted by: nuclear_nick Nov 4 2019, 12:15 PM

I'm pretty much with GPG.

I use prefixes... qapp (append), qupd (update), qdel (delete), qry (select), qfrm (form source), qsfrm (sub form source), qrpt (report source), qsrpt (sub report source), z_ to put outdated queries at the bottom of the list and _ (underline) to put queries in development but not complete, so that they sort at the top of the list until complete.

Part of the reason for the form and report source naming is that the rest of the name matches the form, subform, report or subreport, so I can search for the form "Associates" and get "qfrmAssociates", "qsfrmAssociates", "qrptAssociates", and "qsrptAssociates" all right there.

So it indeed is personal preference, but at the same time, it's really handy to keep it consistant… not only to help you, but anyone else who has to work on your database in the future!

Good night, and good luck!

Posted by: home70 Nov 8 2019, 08:00 PM

Thanks so much everybody! It really helps to hear what experienced people use, and why.

Posted by: WildBird Nov 11 2019, 05:07 PM

Agree with the sentiment, it is personal, however there are better practices than others. As long as it is consistent is the main thing, and meaningful.

I personally dont use the query type, I generally just use 'qry'. Reason being I can see the icon or I can query the type easy enough in code. Someone pointed out numbering them, this is good, however I prefix them consistently, and start numbering with 3 digits, like 'qry001DoSomething', qry002DoSomethingElse. Means you can group together on the prefix, and have multiple queries, if you are doing a migration or something, this is very useful.

Backups - obviously back up the whole database, but as for individual queries, I also just take a quick copy, and delete when I am sure I no longer need them. For my last role, I kept an audit trail of everything that was exported with the SQL string of the queries used.