Starting from creating tables and adding data to tables, we have seen the underlying nuts and bolts of an Access Database. Now, when it comes to databases, there are two sides to the whole story, there is input in the data.
We require reading that data is generally done through a query, because we would query the data so, we want to create our first-ever query. We go to the create ribbon here on the top toolbar. You will see the query has a section with two options, a query wizard and a query design; now, we’re only going to use query design because we’re going to be dead clever. Do it all ourselves, but I will quickly step through the wizard in the query wizard.
What Are the Uses of Microsoft Access Queries?
We have a choice of four types of query; Simple Query, cross tab query, defined duplicates query, and defined unmatched query. Now, the duplicates and the unmatched are actually quite useful little queries that you can use if you ever need to find any unlinked records. If you’ve got duplicate entries of any kind of cross tab, it is a little bit like a Pivot Table in Excel. Coming back to our topic, the first step is then to pick the table that your query is based on.
Simple Query Wizard
There’s a little dropdown even though we’ve only got one table, there’s still a dropdown. So, I need to choose that table and then the fields that I would like to see in my query. There is a double-headed arrow here for throwing all the fields into the right-hand side. So, on the left-hand side, there are the column fields, and from your table’s right-hand side there are the fields or columns that you’ve then made available to the query. So, I’m only going to see the id title, first name, and surname next to which title you want for your query.
The Tables Prefixed With TBL
Well, a little bit like the tables being prefixed with TBL, I would suggest some kind of prefix naming convention. Usually, it’s a query because there are only three letters and then staying with my little convention of no spaces. So, query the first query, so query the first query, and then I have two choices one to open the query. To view the information or two to modify the query design. Let’s say we’ve opened the query finish, and two things happen. Firstly, the query is saved and now appears in my navigation list over here on the left. I can see query first query, and it’s in its own little section, so I’ve got queries one thereof tables one there of this is my query. So, the result of a query is a datasheet. It looks like the table in datasheet mode.
Open All Query In Datasheet
However, you definitely don’t have as many columns because you just see the columns you have added to the query. Now that’s in datasheet view, and you could spend your life creating all your queries with the wizard. Opening them in datasheet view and will show the answer to see this in design view. Go to the Home ribbon, choose the little set square, and this is now the design of a query. I don’t need the property sheet in a query. We have an upper half and a lower half. The upper half is the input area. So, my query comes into the whole table. I’m just going to resize it, if possible.
And that’s just a personal preference, so I can see all the fields without scroll up and down sometimes with big tables. If you can’t see all the fields, you will have to scroll up and down this big horizontal bar. Here it separates the upper and the lower half so, I can just move that up and down and then in the lower half. We have separate columns for each of the fields that I would like to see in my query. When we did the wizard, we chose customer id, customer title, and customer-first name. Because of the surname, if I want to see further columns, I can add that in two ways.
For Example:
If I don’t like the town, I could go to town and double-click on it and it will jump into the next available black column. If I want another column, I can go to the next available blank column. And in the little drop list, it will show me all the fields. So, I could perhaps choose a region from within there. I could perhaps choose the postcode and date started so that I can select both of those using control-click, then I can drag across to the next blank column and let go. Both of those columns will appear so, there are three methods the double click, and it will just jump to the next available column, click and drag to the way you want it.
What are queries in MS Access? Go into the next blank one, and there’s a little drop-down arrow. For Example, I wanted a column earlier in this process. I could take my number in my customer’s lives. For Example, Drag that down-drop it here. It looks like I’m dropping on top of the customer title. But actually, when I insert it, and the customer title moves along. So, the order these columns are in here in design is the order. When I go to View, that I will see them in. Now see customer Live title surname town region etc. Notice the columns that we added a caption to show that label here at the top. So customer title first name start date shows the caption rather than the field name.
Use Of Wizard
Go into the next blank one, and there’s a little drop-down arrow. For example, I wanted a column earlier in this process. I could take my number in my customer’s lives. It looks like I’m dropping on top of the customer title. But actually, when I insert it, the customer title moves along. When I go to the View tab, I will be able to see them in the column. Now, see customer Live, title, surname, town, region, etc.
Notice the columns that we added a caption to show that label here at the top. So, the customer title, first name, and start date show the caption rather than the field name.
Create Query Without Wizard
Now, I am going to create a query in Access without the wizard just from scratch. So, while creating a query design, it pops up two boxes that I can see behind the query grid. This is the input and the output in front of that and it gives me this shown table, which lists all the tables on to base this query. I know we’ve only got one but, we can choose that ad and then close this box. Next, a little bit of tidying up so, bring this down to open that up a little bit. So that I can see all the fields and this can come back up a bit. Then I need to decide what this query will show me.
For Example:
Like Customer Title, First Name, Surname, and the Postcode Drag that drop down here. They appear when I go to view what I see from the table. The query will allow me to filter out the number of columns; however, I don’t need to see all the columns. When I am at the table, I need to see all the columns. When I am in the query, I only need to see the columns that I want to see. This is called the select query because it selects records from the table. It effectively works the same as a table. It’s a view of the table, so if I were to do anything here, like add another person, Mr. Jim price postcode yy789.
When I tab out it and then add in Mr. Jim’s price, now if I have a quick look at the table, I’ll see that it’s added in, but only the columns that have access. Because I don’t see the address information in that query, it’s just a view of the data. But it’s an editable view of the data. The query then gets saved in my list over here on the left. So, I can create hundreds of queries in Access that ask different questions and show your different fields.
Enter Specifications In Customers Data
If we keep an eye on their sally bell close that goes back to the table, come down to sally bell. Let’s change her surname, she’s married. So, I have changed the source data, close to the tables, and re-run my query. But it’s by double-clicking here on the left-hand side and, sally is now sally bacaro. So, the query does not remember the data. Otherwise, these grids could get quite large. If you have quite a large table, it doesn’t remember anything at all.
Fix The Query Table
As far as, the result you see in front of you, now what it remembers is the question. So, it remembers what you have asked to see. In design, I have asked the C to title the first name the surname, and the postcode for all the customers in my TBL Customers. And that’s what it remembers the actual questions when you re-run it re-asks. The questions with any change in the data are then shown in the query.
So, the query will always show you the latest data. This is the basics of creating a select query. We go to create, we are not really bothering with the wizard, although we run through its query design. Choose the table or tables that you’re going to extract data from. Then put the fields you want into the bottom half. Because the top is the input, so everything coming into this query, but the bottom half is what we would like to see out. We choose those fields that we would like to see maybe I have nearly seen town and postcode, but that’s the town postcode for everybody at my table.
Sort The Data In Order
The start of the next stage that we’re going to move on to is the ability to then filter out. Sort the data that you extracted, so it presents us the data in the same order that it’s seen in the table. This effectively is the primary key order, but we can overwrite that in our query. So our queries can be sorted differently our queries don’t need to show all the records. We can add some filtering criteria so, that’s the next stage of our select queries.
