Join Adam Wilbert for an in-depth discussion in this video Following naming conventions and best practices, part of Access 2010: Queries in Depth.
Before we jump deeper into queries, I wanted to take a moment to pass on some of the best practice naming conventions used by Access professionals. You will see these naming rules applied throughout this title and I wanted to point them out early so that you can recognize them and see how they are used. Keep in mind that they're by no means required by Access but they are tried and true best practice that have been standardized over several decades of use in various database systems. If I open up my Chapter 1 custom group, you will notice that we have tables and queries here. Tables are represented by tbl_ and queries are represented qry_.
This is a form of Lozanoski or Hungarian naming convention where each object is named first by the object type and then with a unique identifying name. I've used CamelCase words to improve legibility, but I have omitted spaces to prevent any issues with moving each object into another database system down the road. Now, Access gives us some keys when we are looking at our database objects to tell us what they are. Mostly, it relies on icons. So this is an icon for a table. This is an icon for a query. If I expand my Introduction section, we've got queries, forms, and reports and we can see their icons.
But there are several places within Access where we don't have the icons to help us out to tell us what object we are looking for. For instance, if I look at our Database Tools and then Relationships, we will see a bunch of tables represented but I wouldn't be able to tell these were tables except for my keys that I have at the beginning. For instance, I can right-click and say Show Table and then add a query to this list and close the window. Again, I have got my key here that tells me this is the query but without this name, I would be hard-pressed to tell the difference between this table and this query.
So, let's take a look at some of the common tags that we will be using throughout this course. We will be using tbl to denote tables. tlkp will be a lookup table, a special kind of smaller table that only gives us additional information. qry will be our queries, rpt will stand for reports, frm will denote from objects, and mcr will be our macros. We will also see cbo and cmd, which will denote combo boxes and command buttons that we will be using on our forms.
Microsoft Access is really flexible and it's designed so that you can get quite a bit accomplished without ever needing to look at the line of Visual Basic or SQL code. If you don't plan out moving your data to any other environments and you are confident that you'll never need to pass your database onto somebody else to maintain, feel free to break any or all of these practices as you see fit. Rest assured that your database will work just fine if you use long names, spaces, capital letters, whatever you want. If however, there's a slimmest of possibilities that your project will grow beyond a simple personal endeavor. then I would urge you to consider putting some of these best practices into your workflow.
Being consistent and deliberate in your methods will only help you down the road.
- Naming conventions and best practices
- Working with joins
- Using comparison operators
- Defining criteria for select queries
- Creating parameter queries
- Creating calculated fields
- Working with dates and times
- Using the Expression Builder
- Creating conditional statements
- Making, deleting and appending records
- Building reports