Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
When creating queries using calculated or filtered records, it's always a good idea to properly document your workflow so that your results will be easily understood by other users. One way to do this is to rename the field headers in your queries to give them a more meaningful and accurate name. Let's go ahead and build a query that will demonstrate this. We'll go up to the Create tab and in the Queries section click on Query Design. We'll add our Products table by double- clicking and then we can say Close Show Table. I'm going to go ahead and open this up and let's add a bunch of fields from our table to our query.
I'll double-click on ProductName, Ounces, Cost, Price, MarkupDollars and MarkupPercent. Now these field names come directly from the table headers. ProductName, Ounces, Cost, Price, MarkupDollars, MarkupPercent. You will notice that they are written with no spaces and they are not exactly in the most human-readable format. We can change that by using a field alias and it works like this. If we click in the very beginning of the name, you could type in an alias followed by a colon and Access will use that in the table of results from the query.
So instead of ProductName as all one word we can say Product Name with a space followed by colon. Ounces, well this is our size field and Ounces is a little bit confusing. So we'll say Size in ounces followed by a colon. Our Cost? Well this is really our wholesale cost so I'll write in Wholesale Cost followed by colon. The Price field represents our customers' price.
So I'll click in the beginning and write Customers Price, followed by a colon. MarkupDollars and MarkupPercent are both markup fields but they have different units. So MarkupDollars I'll specify as dollars with a dollar sign and MarkupPercent, I'll specify the units as percent with a percent sign followed by a colon. Now if I go ahead and run this query, you'll see that we have new headers at the top of our columns.
Product Name is 2 words. Sizes is with ounces specified. Let me double-click here just to widen that. Wholesale Cost, Customers Price, Markup in dollars and Markup in percent. Later on in this course, you'll see that when we create a calculated field Access is automatically going to add an alias Expr1, Expr2, etcetera to denote an Expression 1 or Expression 2. Most of the time those are not going to be helpful at all so we'll be using these aliases to redefine Access's default.
By clearly identifying the fields returned in your queries you'll aid other users that will be working with your database and will help clearly communicate the meaning of the field in forms and reports.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 64867 Viewers
80 Video lessons · 124403 Viewers
52 Video lessons · 60327 Viewers
59 Video lessons · 46153 Viewers