Solved

Naming convention for many related queries

Posted on 2009-07-16
18
383 Views
Last Modified: 2013-11-29
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?
0
Comment
Question by:billb1057
  • 8
  • 5
  • 3
  • +2
18 Comments
 
LVL 6

Expert Comment

by:Yadtrt
ID: 24871949
which version of access you r using?
0
 
LVL 2

Author Comment

by:billb1057
ID: 24871967
2003
0
 
LVL 39

Expert Comment

by:thenelson
ID: 24871969
I would probably name them based on what they do. Example :
qwyCombineTableA&TableBForReportC
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) earned 105 total points
ID: 24871992
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
 
LVL 75
ID: 24872012
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
 
LVL 75
ID: 24872030
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
 
LVL 38

Assisted Solution

by:Jim P.
Jim P. earned 20 total points
ID: 24872681
>> 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
 
LVL 75
ID: 24872819
": 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
 
LVL 2

Author Comment

by:billb1057
ID: 24872824
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 2

Author Comment

by:billb1057
ID: 24872882
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
 
LVL 75
ID: 24872980
"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
 
LVL 75
ID: 24873064
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
 
LVL 2

Author Comment

by:billb1057
ID: 24873082
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
 
LVL 75
ID: 24873102
"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
 
LVL 38

Expert Comment

by:Jim P.
ID: 24873148
>> .... 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
 
LVL 2

Author Comment

by:billb1057
ID: 24873167
Thanks for your help, guys -- I'll try something like that.
0
 
LVL 75
ID: 24873204
"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
 
LVL 38

Expert Comment

by:Jim P.
ID: 24873255
Glad to be of assistance. May all your days get brighter and brighter.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now