Naming convention for many related queries

I have an application with about 50 queries and its easy to get confused about which query does what.  Is there a standard way to structure query names in a hierarchy?
LVL 2
billb1057Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

YadtrtCommented:
which version of access you r using?
0
billb1057Author Commented:
2003
0
thenelsonCommented:
I would probably name them based on what they do. Example :
qwyCombineTableA&TableBForReportC
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Generally, I prefix all 'application'  related queries with 'qry'.  Further, I name queries such that related queries sort together in the database window.

For my own 'system' queries which tend to be common across my apps ... I prefix them with USysqry ....

See attached jpg examples:

mx
qryNames01.gif
qryNames02.gif
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
I'm a big fan of 'sorting' for convenience ... very useful during the design phase ... and when you come back 6 months later and are trying to remember what you did.

Here is a good reference on the De-Facto Access naming conventions pioneered by Stan Leszynski and Greg Reddick back in the early 90's, which became known as the L/R Naming Conventions (aka LNC Naming Conventions):

http://www.databasedev.co.uk/naming_conv.html

And not directly mentioned, but avoid using any of these special characters in object and field names:

Avoid using the following characters: ,.~!@#$%^&*()+-=|\:;'<>?/"`[]{}  including Spaces!

mx
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Note that in the LR naming conventions ... they are not really using the sort idea per se ...because they are prefixing the query type (update, make table, etc). Unfortunately, this causes related queries NOT to be sorted together.  And besides ... the icon to the left of the name in the database window clearly identifies the type of query.

mx
0
Jim P.Commented:
>> Generally, I prefix all 'application'  related queries with 'qry'....


Just to throw in my $0.02......

Since I'm a big keyboard guy: it has never made sense to me to prefix objects with the "type" of object. I will do suffixes (_qry, _tbl, _Form) depending on the situation.  If you are naming things right, and are half observant, you know you are on the query tab. So anything under there will be a query.

And prefixing variables (str, int, ...) also makes little sense either. If you have named your variables correctly (CurrentTime, FullUserName, TotalQty) the data type should be obvious.

To me, it just makes it harder find the actual object I want to edit, because I'm having to parse off the first x number of characters in my mind.

Again -- this is my own opinion and apparently does not reflect the opinions of anyone else. ;-)
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
": it has never made sense to me to prefix objects with the "type" of object. "

Unfortunately, there are many places in the Access  UI in various drop down lists etc ... that tables and queries are mixed together. So, w/o a prefix on these objects ... it makes it *very* had to find the table or query you are looking for.  

Even in just the query designer grid ... you can drag one or more tables and/or queries onto the grid.  w/o prefixes on the objects ... it makes it difficult to tell what is what .... especially down the road ... and especially if you are a developer who has inherited a database w/o naming conventions.

mx

0
billb1057Author Commented:
DatabaseMX -- thanks for the screen shots.  Yes, that's getting to my question and the issue about sorting.  I was thinking about some kind of number and letter scheme so you can tell which queries come first and then sub-queries with letter (1a, 1b, 2a, 2b, etc) -- but then there is the function of the query (make table, append, etc) and what it does.
So, the letter prefixes (qry, tbl) are good in some ways, but if everything starts with qry then there's no sorting feature.
In the screen shots you provided, I'll guess that you already know what ZiDA and Mui mean -- but that's what I was looking for.  Some way to group them.
For example, I always have queries that do a first sort.  Then there's a second calculation and grouping, then perhaps a make-table and then union query to combine results.
0
billb1057Author Commented:
to get something to the bottom of the database window list, for example, I'll use "Z".  But then there are a bunch of Z's so I have to go back and do "ZZ".  It's the same with numbers -- so I use 01,02,03 in case at 10 it starts again.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"but if everything starts with qry then there's no sorting feature."
Sure there is ... look at my screen shots.  Everything is sort exactly the way I intended.

"but that's what I was looking for.  Some way to group them."
They are grouped.  All the "DA" stuff is grouped together; As well as "CASRI".  This is only part of the list.  

The 'Zi' for example ... the 'i' represents Import.  This is an import module for several databases. So, everything directly related to importing is together in one big group.  

But the real bottom line is this:  Create a naming convention (SOME convention) ... that works for you.  There is obviously no right or wrong way.  Consistency is the important factor.  

As an Access developer since 1.0 ... I have inherited 100's of database, the majority of which had *no* specific naming conventions ... and I can assure you that ... they become a monumental nightmare to deal with,  especially when it comes to figuring out what is a table, query ... etc ... in various Record Sources, Row Sources, etc., even with special tool I have purchased and/or built to help with this.

mx

0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
And as far as 'what the query does' ... don't forget that you can use the query's Description property to add comments.  Very handy.  In fact, since you can column sort on the Description property ... this in itself lends to addition grouping/sorting ideas.

One cool thing about the prefix USys  is that it's treated exactly the same way as MSys ... in that Tools>>Options>>View Tab ... System Objects ... if you uncheck System Objects ... all objects prefixed with USys will automatically be hidden ... w/o having to explicitly set the Hidden property of that object.

mx
0
billb1057Author Commented:
Ok, I was wondering if there was some agreed-upon way to do it and I think the answer is "no".  I've tried to come up with some conventions and they sort of work.  But then things happen and I add combinations of things or break apart categories.
It would be nice if the basic flow of a database application had some standard structure.
"But the real bottom line is this:  Create a naming convention (SOME convention) ... that works for you. "
True -- but thus far I haven't found one that works for me and that's why I was asking to see if there was some standard way or some time-tested method rather than create my own.
 "There is obviously no right or wrong way.  Consistency is the important factor. "
There's not a right or wrong -- but for example -- the use of "qry" and "tbl" are accepted for some part of this.  Thus far, I guess, nobody has proposed a method for naming queries for a sorting/function and event-placement (what happens first then second) or relationship pattern.
Probably most databases share enough in common that the basic paths could be defined and named -- that would really be a big help.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"to get something to the bottom of the database window list, for example, I'll use "Z"."

As you can see ... I'm a big fan of that also.

for example ... in the 2nd GIF .. I have 'zpqry .....'   In this case, the 'p' means 'project' ... in that these queries may or may not be in a specific app ... and, they sort near the bottom of the list.  I like all 'application specific' queries to sort at the top ... in general.  

mx
0
Jim P.Commented:
>> .... I have inherited 100's of database, the majority of which
>> had *no* specific naming conventions.....

I haven't had that many inheritances -- but I will agree that the developer should pick a standard and stick with it across all their development.

Mine is suffixes, fully named descriptive objects. The other one that I do is that any hard objects (tables, field names, queries, reports) I use an under_score to distinguish them. For variables in coding I use CamelCase. That makes it a little easier when assigning back and forth. I also use "Num" not "No" for number fields.
0
billb1057Author Commented:
Thanks for your help, guys -- I'll try something like that.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"Ok, I was wondering if there was some agreed-upon way to do it and I think the answer is "no". "
Actually, the answer is basically Yes ... the LR naming conventions I referred to above - even though I deviate somewhat from those.

"If there was some standard way or some time-tested method "
Again ... the LR conventions are the de-facto standard of the industry.

Obviously, this is a very subjective subject :-)

mx

0
Jim P.Commented:
Glad to be of assistance. May all your days get brighter and brighter.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.