Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Naming convention for many related queries

Posted on 2009-07-16
18
Medium Priority
?
402 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 420 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 80 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
 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

610 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