Having looked at tables and creating tables and adding data to tables. We've seen the underlying nuts and bolts of the start of an Access Database. Now when it comes to databases, there are two sides to the whole story, There is input in the data. So, getting data into the tables storing away until eventually.

We require to read that data read the 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 query design now we're only going to really 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 Quries In Ms Access 1

What Are Queries In MS Access & It's Use


We want to click, we have a choice of four types of query simple Query a cross tab query find duplicates query. The defined unmatched query now the duplicates one of the unmatched are really actually quite. Useful little queries that you can come and do if you ever need to find any unlinked records. But if you complete this whole course, that shouldn't happen to you find duplicates very useful to see. If you've got duplicate entries of any kind cross tab is a little bit like a Pivot Table In Excel. If you use Pivot Tables, But we want to stick with for the moment the simple query Wizard okay. The first step is then to pick the table that your query is based on.

What Are Quries In Ms Access 2

Simple Query Wizard


What Are Queries In MS Access? 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, Then which fields would I like to see in my query. I don't like the id The title First name, the surname If I happen to want all the fields. There is a double-headed arrow here for throwing all the fields into the right-hand side. So, on the left-hand side are the columns fields from your table right-hand sides 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 the surname next to which title you want for your query.

What Are Quries In Ms Access 3

What Are Queries In MS Access? 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.

What Are Quries In Ms Access 4
What Are Quries In Ms Access 5

Open All Query In Datasheet

But obviously, you 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 and 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, into my query comes the whole table, so I'm just going to resize that I tend to resize if possible. To see all the fields, you'll see the vertical scroll bars disappear. There if I go a bit smaller, it appears to go a bit bigger it disappears.

And that's just a personal preference, so I can see all the fields without scroll up and down sometimes with big tables. You can't see all the fields, and you will have to scroll up and down this big horizontal bar. Here 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 customer first name. Because of the surname, if I want to see further columns, I can add that in two ways.

For Example:

I don't like the town, I could go to town, and I could double-click 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, so that's two different ways I can, even multiple select. So, I could perhaps choose the postcode and date started. So, I can select both of those using control-click, and then I can grab drag across to the next blank column and let go. And 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.

What Are Queries In MS Access? & Use Of Wizard

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.

Create Query Without Wizard

Now, if I create a query without the wizard just from scratch. So, create query design it pops up two boxes I can see behind the query grid. So, that's the input and the output in front of that, It gives me this shown table which lists all the tables that I would like to base this query on. I know we've only got one but, we can choose that ad and then close this box. Then a little bit of tidying up so, bring this down open that up a little bit. So, 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:

What Are Queries In MS Access? Like customer title first name surname and the postcode drag that drop down here. They appear when I go to view what I then see from the table. The query will allow me to filter out the number of columns I don't need to see all the columns. When I'm at the table, I need to see all the columns. When I'm 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 to speak, so if I were to do anything here, like add another person, Mr. Jim price postcode yy789. 

When I tab out it, then adds 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. If I saved the query noticed the default. There is query 1 what our imagination so, I'm going for query customers. And okay, that query then gets saved in my list over here on the left. So, I can create I'm going to say hundreds you could create hundreds of queries that ask different questions show your different fields. So, that all the queries stores through are not the data, It's just the question.

Enter Specifications In Customers Data 

 
What Are Queries In MS Access? 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 got married, so sally is going, to became sally of the current. So. I have changed the source data close to the tables 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. The postcode for all the customers in my TBL Customers. And that's what it remembers the actual questions that when you rerun it re-asks. The questions any change to the data is 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 close. That 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.

What Are Queries In MS Access? 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.