Customer Queries

Lesson 9: Build & Sort Customer Queries by Criteria

S M L XL FS | Slo Reg Fast 2x | Bookmark

In Lesson 9, we will focus on building customer queries, including how to add tables and fields, run a query, sort by multiple fields, and apply query criteria. We will walk through creating a query to display customers from New York sorted by last name, demonstrate saving queries for future use, show how to copy and modify queries for different states, and discuss the difference between queries and tables. We will also look at how to edit, move, or hide fields, explain the function of the show checkbox and star symbol, and briefly discuss parameter queries and other advanced query types.

Navigation

Prev: Lesson 08
Next: Lesson 10

Keywords

Access Beginner, customer queries, query design, add tables, add fields, run query, sort multiple fields, query criteria, save query, filter records, delete query field, parameter query, permanent sort, duplicate query, refresh query, query criteria row,

Subscribe to Customer Queries
Get notifications when this page is updated

Intro

Transcript

In lesson 9 we are going to learn how to build customer queries. We will see how to add tables to your query, add fields from the table to the query. We will see how to run the query, then we will learn how to sort by multiple fields and add query criteria. In this lesson we are going to learn how to work with queries.

Now we have the same mission that we had in the last lesson. The boss wants to see a list of all the customers from New York sorted by last name. More importantly, I want to create a query to do this so I can pull it up at a moment's notice at any time in the future without having to redo all the steps.

So come up top and click on create and right here you will see a queries section. There is a query wizard and query design. Now the query wizard is okay for building some of the more advanced queries. But right now we are just going to build a simple query and I want to teach you how to do it from scratch. So click on query design. You will see a blank query window opens up right here. This is query 1.

Over on the right-hand side you will see add tables. This pane appears. If you do not see add tables over here, click on this little add tables button. Now a query can get its data from a table.

Here we only have the customer T in our database. Other queries? We do not have any. Or you can get your data from links. Now links are linked tables. That is basically tables in other databases. We do not cover this for a while so do not worry about links right now.

So let's go over here and click on tables. The only table we have in our database is our customer T so make sure that is selected and come down here and click on add selected tables. You will see that brings the table over into the query right over here.

Later on, when we have more tables in our database and we build some other queries, we can actually make queries based on multiple tables and/or other queries. We will talk about that in a more advanced class when we get into the expert series. But now we are done adding tables so I can close the add tables pane right there with that X. That gets rid of that.

Now notice how the ribbon has changed since I am building a query. It says query tools, design and there are all kinds of new buttons here. Make table, append, update, cross tab, all this stuff. We will talk about this stuff in the future.

But for today we are going to make a simple select query. This is where you select data from a table. The table or tables, in our case just one, that are part of the query will show up up here in this big area. Down here in these columns will be the fields that we want to see.

I will tell you that this is not intuitive. This is one of the stranger things you are going to see in Access. This interface can be confusing so it will take a little while to get to know how it works. But it is not that bad once you get used to it.

Up here in this table you will see the different fields: first name, last name, company name, and so on. We can make this bigger by simply grabbing and dragging the edge. We add fields to the query by clicking on them and dragging them down to these columns down here.

So let's say I want to see first name and last name in this query. So click on first name and then click and drag it and drop it right down here in that first column. See that? Now do the same thing with last name. Click and drag and drop it right there in that second column.

So now your query consists of the customer table showing the first name and the last name. That is all you have to do to see a list of first names and last names.

Now to see the results of your query, to see the actual data, come over here on the ribbon and click on the run button. There you go. There are all the customers in our customer table displayed in this query. You have got a list exactly as you asked for it. There is first name and last name and that is it.

Now it is not sorted yet and we are seeing all of the customers from all of the states. But that is exactly what we told Access to give us. We have not put a filter on and we have not sorted it yet.

Now this list is going to be for phone numbers. So let's say I want to add phone number to this list. Let's go back to design view. Click on the little design icon over here. That will put us back in design view. Let's find the phone number field right there. Click and drag and drop it in column three. Now let's run the query again. There we go. There is every first name, last name, and phone number in our customer table.

Back to design view. Let's add credit limit to the query. Now here is a trick. Instead of clicking and dragging, you can just double click. Watch this. Click click. It will automatically add to the next open column in your query. It is a little faster than clicking and dragging.

Run the query again. There we go. There are all the credit limits. The boss sees this and says wait a minute. I do not want that. I do not want credit limits showing up in the query. Get rid of that. How do you delete something out of the query? Go back to design view.

This is a little tricky. Take your mouse and move it right there over the top of that column. See I have got that downward-pointing black arrow. Click right there. That selects that column. Now press delete on the keyboard. That is how you delete something out of the query. That is how you delete a field from the query.

So if I accidentally added num and employee, come over here. Find that spot. Not this spot. This allows you to drag that little barrier window up there. That is not the spot you want to be on. You have to be right there. That little black arrow. If you click on it, you actually lose the black arrow. Come over here, you click, that selects that column.

You can actually select multiple columns by clicking and dragging like that. But we just want the one. So click right there. Now let it go. Press delete on the keyboard. That is how you select and delete a column out of your query.

How do I sort this information? If you are in datasheet view, you can apply sorts. If you go back to datasheet view, or if you run the query, it is the same thing, by the way. Going to datasheet view or running the query, if it is a select query, will talk about these different types of queries in future classes, but this is the same thing. It just switches over to datasheet view.

Now you can perform these sorts up here like we learned about with tables. But these are not permanent and they are easily changed by other users. What we want to do is apply a permanent sort and in a few minutes, a permanent filter to the query itself in design mode. So go back to design mode. Notice down here there is a row that says sort. Sort. Click inside the sort box under the first name. You will see a little drop down box appears or a little combo box. Click on that. We have ascending, descending, not sorted. Pick ascending and now run the query. Look at that. It is sorted now by first name.

Go back to design view. Now let's add a second sort by last name. Ready? Drop this down and pick ascending and now run it. That is not quite what I want. Notice I have got two Jows in here and they are now sorted. First name, then last name. But usually when you do a sort, you want it last name, then first name.

How do we do that? In order to do that, we have to rearrange these columns. Access queries will sort left to right. So whatever you want sorted first has to be on the left side.

So how do we do that? We just learned how to select the column. Move right there. Get that black arrow. Click. Let it go. Take your hand off the mouse.

Now notice I have got a white pointer arrow right there. Now click and drag that line and put it right there. That is how you move a column. Let's practice. Let's move phone out in front. Ready? Click. Let it go.

Now I can move my mouse freely. Now grab that same spot. Now click and drag to the left. See that. But I do not want phone out there. I am going to put phone back where it was. This is practicing. So click right there. Let it go. Click and drag to the right. See the line moving around. That is where it is going to drop. There is where you want to drop it.

So that is how you move these things around. Now if I run it, now it is sorted by last name and then first name. We have two Smiths there. Joe and Peter and they are now sorted. Last name, first name. I am surprised the first one I got in here is a J. That is like half the alphabet missing.

Back to design view. Now it is time to save this query. So I am going to hit control S on my keyboard for save. Control S as in save.

Query name. Let's call this customer Q. My customer query. Then hit OK or press enter. Now notice it is over here in my navigation pane. There is customer Q. Now I can close this query. Go about my business. Do what I want to do. Go have lunch. Go watch some more of my videos.

Then when you come back later and you open this guy up by double clicking on it, there is your query exactly as you saved it. You have the fields you want. You have the sort order that was in there. That is the benefit of queries.

You can make different queries to display data in different ways. This way you do not have to keep reinventing the wheel. Unlike in Excel, where you have to resort this and change that and move this around, in Access, you can have multiple different queries to view your data in different ways.

More importantly, you can set these up for people that do not know Access. If they want to see a specific set of data, like we are going to see in a second how to do a list of customers from New York with a credit limit under $1000, you can set up a special query for people. All they have to do is open up the query and there is the data that they need. They do not have to know how to use Access. They do not have to know how to build queries. You build it for them and just give them a link to it.

Later on in future classes we will build full menu systems where you just have them click on a button and there is their query.

But we are not done yet. The boss said that he only wants to see customers from New York. So let's go back to design view.

To filter based on a field, you have to have that field in the query. So find state, double click on it. Now it is over here. Run the query now and you will see there are all the states.

Back to design view, which, by the way, you can also get there by right clicking here on the title bar of the query. You can switch between design view, SQL view, and datasheet view. There is datasheet view. SQL view is a little more advanced. I cover this in my more advanced classes.

That is basically a programming language to write queries. It is very powerful. We will cover that in future lessons.

So now we have the state in our query. There it is. We know about the sort row. There is a criteria row. Come into the criteria row underneath state. Here is where you are going to put your filter or the criteria for this particular field.

So I am going to type in New York. NY then press enter or tab. Now notice that Access put quotes around it. I will zoom in there so you can see it. Notice it put quotes around it. In Access, whenever we are dealing with text values, that is called a text string. A string of characters.

We have to make sure we put them inside of quotes. Now usually Access realizes that is a text string and puts the quotes there for us. But keep that in mind. You should not always rely on Access to do that. I just showed you as an example, but you should type in quote New York quote.

Every time you put in your criteria there, if it is a text string, sometimes you will have numbers, sometimes you will have currency values or dates. Those do not get text strings. We will see that as we go along.

Now that we have our criteria there under state, let's run the query again and look at that. Now I am only seeing customers from New York.

So I have got it sorted and I have got it properly filtered with criteria. That is now a permanent part of this query. Notice there is no filter on here. It says no filter. That is in the design of the query and the SQL underneath it.

Now I want to save this query so I can pull it up again at any time in the future. But do not just hit control S or click on the little floppy disk icon over there. Otherwise you will overwrite the original customer Q. I do not know how many times I have done that. I have done it a million times. Especially with Word or Excel.

I have got a Word template, like a document that I use, and I do not have it set as an actual template, so it is read only. I will open it up and I will start making changes. I will hit control S. Save. I just overwrote my old document. Happens all the time. So what I am going to do in this case is I am going to go to file and save as.

Now you can save the whole database. That is not what I want. I want to save this object. Save object as. Hit the Save As button.

This will allow you to save customer Q to right copy of customer Q or whatever you want to call it. I am going to call it customer New York Q.

Remember singular. I try to keep all my objects, table names, query names, field names, singular if possible. Someone actually called me out that notes is technically plural. I know. But I have been doing notes forever. Notes is my exception. Hit OK.

Now this query has been saved as customer New York Q. Now if I close this guy and open up the other one, there is the original query. I can open up the customer New York Q now too. There are both of them. They overlap each other like that. Just move more out if you want to. You know how to use windows. If not, go take my windows class. We are not going to cover windows stuff here. You can even open up the table if you want to get them all open up here.

Close that. Close that. Close that.

Let's say in addition to New York, we also do business in Pennsylvania. So I want a similar query, but I want customers from Pennsylvania. Now I know ahead of time I am going to be making changes to this query, and I want to have to open it up and then go file save as, save object. Let's just copy this guy ahead of time. Right-click on it. Go control C, control V. Copy, paste my keyboard shortcuts. Control C, control V.

Change the name. We will call this one customer PA. Hit OK. Now let's open customer PA up. It still says New York. Why is that? I have not actually changed the query yet. Have I? Let's go to design view. Right-click. Design view. Let's change our criteria to PA. Save it. Control S.

Save on the keyboard. Now let's run it. Nobody in there. Well, I do not have any customers from Pennsylvania. Let's open up the customer table and make sure. There are my states. No Pennsylvania.

Now let's go in and update. Let's change a couple customers and make them from Pennsylvania just for class.

So go back into customer table. Let's change this guy here. Let's make him from Pennsylvania. Let's change this guy here. Pennsylvania. Close the customer table.

What is going on? Why did my query update? It is important to note that a query will not update its records unless you either close it and reopen it or click the refresh all button. If you hit the refresh all button, it will reload its results, or you can close it and then reopen it again. It will not just update itself if it is sitting there open.

This usually is not a problem if you are the only person using the database. But sometimes, if you have a multi-user database, and we are going to get into that in future classes, you have multiple people working on the same set of records at the same time. Someone might change something and it does not appear to change right away on your screen because you have to refresh the data, either close it and reopen it or hit the refresh button.

If someone says, I just changed a record a few minutes ago, and you are not seeing the data change, close it and reopen it or hit the refresh button. We will talk a lot more about this when we get into multi-user databases, and that is coming up later in the expert classes.

Another thing that is important to remember is that queries themselves do not have any data in them. This is just a way to look at data that is stored in the tables. Let me repeat that. All of the data in your database is stored in your tables. This is just three different ways to look at this set of data. The queries themselves have no data in them.

The query is just showing you what is stored in the table. It is not a copy of the data. It is just a live view of the data in the table.

On the other hand, keep in mind that this is a live view of data in the table. If you change it in the query, it will also change in the table.

For example, I come in here and I change these people to Florida. Notice I am able to edit them here. If I hit the refresh all button or close this and reopen it, look what happens. They go away. Why? Because that is live data. I just changed those people to Florida. There is one of them. I remember that one from Jamestown. Jamestown. This one up here too. These were the Pennsylvania. Now they are from Florida.

I will put this one back to Pennsylvania. Jamestown should be Texas.

Close that. Now open up Pennsylvania again. There is your one person from Pennsylvania.

Keep those two things in mind. One, queries themselves have no data in them. They are just showing you data that is stored in the tables. Two, any changes you make while you are viewing a query get saved in the table.

Can you make queries read-only so that people do not have the ability to change the data? Yes, you can. Personally, I would prefer giving your coworkers forms or reports to work with instead of having them work directly in your queries. We will see how forms and reports work in just a few minutes.

So now we have two queries from two different states. What if you wanted to have a query for each state? Would you have to make 50 separate queries? The answer there is no.

There is something called a parameter query where you can make it so the user types in the state when the query is run. That is a little more advanced. I cover parameter queries in Access Beginner Level 5. I do also have a tip video on that. I will put a link down below in the links section if you want to learn how to do that. It is a little more advanced, but it is not too bad.

Remember, today we are just learning the basics of query design. We have to walk before we run. But my students ask this stuff every time I teach this class. I just want to let you know that yes, it is possible. If you really want to learn how to do it now, go check out the links down below. You want to find the parameter query video.

Another popular question I get asked all the time is what is this little asterisk up here? That little star? The star is so that you can add all of the fields to the query without having to bring them all in individually. So if you double click on that star, for example, you will see it says customer T dot star. That says all of the fields.

Now, we have to have first name and last name in here individually because we want to put sorting on them. Or if you have the state field in here to do a criteria, you have to add that in. But if you just want to see all the rest of the fields, just bring down the star. Now when you run it, you will see there are all the rest of the fields. That is really handy if you want to have them all on the query, and you have lots and lots of fields like we have up here.

Another thing I get asked is what are these show boxes for? If you do not want to see a particular field, even though you have to have it in the query, you can hide it.

For example, let's get rid of the customer T dot star. Remember earlier, I accidentally brought in the credit limit. If I run this, you can see everybody and their credit limits. Let's say the boss wants to see a phone list of everybody who has a credit limit of less than $1000. Maybe you are going to be calling them and see if they want to upgrade their credit limit or whatever.

So in the criteria row down here, I can put a criteria in of less than $1000. We are going to do a lot more with all these different types of criteria in future classes. I am just giving you a sneak preview.

If I run this now, you will see everybody in this list now is under $1000. But you might not want your employees seeing what their actual credit limit is. So you can come in here and hide that column. Now, when you run this, you know this is a list of everybody that has less than $1000 credit limit, but you do not know what the actual credit limit is. It is a little more discreet. So that is what the show box is for.

There are tons of things you can do with queries. I have literally just scratched the surface today. You can make queries out of multiple tables. You can make queries that prompt the user for information, like I mentioned before with the parameters. You can make queries that edit records in a table. Make table queries will create whole new tables based on the sets of data. Append queries can add records onto a table. You take them from one table to another. Update queries change information. Cross tab queries look like little spreadsheets, delete queries delete information. There are all kinds of things you can do with queries.

If I put together all of my query lessons from all my different courses, I probably have hundreds of hours of stuff on just queries. This is literally just to give you a taste of what queries can do. This is the basics of a select query.

A lot of books are written that way. Books are usually written in more of a reference type format where they teach you everything there is to know about tables first, and then they teach you everything there is to know about queries. Next, I do not do that. I do not go depth first. I go breadth first with my teaching. I will teach you a little bit of this, then a little bit of that, like a little bit of tables, a little bit of queries, a little bit of forms. Then in the next class, we will go over a little bit more with tables, a little bit more with queries. I think you will learn better that way.

Tutorials are not really designed to be reference material. I want you to watch this and apply a little bit of this lesson, then a little bit of that lesson, and it will stick in your brain a little better. Some people learn better with books. If you like book learning, then great. Go buy a book. There are lots of books I recommend. The Access Bible series is really good. I like those.

But lots of people always ask me, why did you not cover this and this and this and this about queries? Because I am only giving you a little bit. We are already over 20 minutes for this lesson.

Now we are going to move on to something else. We are going to do forms in the next lesson. I am going to teach you a little bit about forms. You want to learn more about queries? I have got tons of query lessons on my website. Go to my site, search for queries. You will find all kinds of stuff.

I give you a little bit at a time so you digest it. Now I am going to give you some form stuff, then some report stuff.

In the next lesson, we are going to learn how to build a form to make a nice user-friendly interface for our end users. So let's go to the next lesson.

Quiz

Q1. What is the primary purpose of creating a query in Microsoft Access?
A. To view and filter data from tables in a customizable way
B. To permanently delete unwanted records
C. To automatically generate reports for printing
D. To back up data from one table to another

Q2. When building a new query from scratch, which option should you choose from the Create menu for full manual control?
A. Query Wizard
B. Query Design
C. Form Design
D. Report Wizard

Q3. What step must you complete before you can add fields to your query?
A. Add one or more tables to the query
B. Set up primary keys in the table
C. Save the query file
D. Create a report layout

Q4. How do you add a field to a query design grid faster without dragging it?
A. Double-click the field name
B. Right-click the field and select Add
C. Use Shift + Click on the field
D. Type the field name in a blank column

Q5. If you want your query to always display customers sorted by last name and then first name, how should their columns be arranged in the query design grid?
A. First name to the left of last name
B. Phone number to the left of names
C. Last name to the left of first name
D. The order does not matter in Access

Q6. What does the criteria row in the query design grid allow you to do?
A. Format how data appears in the results
B. Filter records based on specific values
C. Change the table relationships
D. Calculate totals on numeric fields

Q7. After modifying a query, how can you avoid overwriting the original when saving?
A. Use Save Object As and enter a new name
B. Click Save and type over the old name
C. Use Export to make a backup
D. It automatically saves as a new file each time

Q8. According to the lesson, if no customers from a specific state show up in your query even after updating the table, what should you do?
A. Refresh the query or close and reopen it
B. Delete the query and create a new one
C. Reinstall Microsoft Access
D. Reboot your computer

Q9. Where is the actual data stored in Access?
A. In the tables only
B. In the query objects
C. In the forms and reports
D. In linked spreadsheets

Q10. What happens if you edit values directly in a query datasheet?
A. The corresponding table data is updated
B. Only the query is updated, not the table
C. No changes are saved anywhere
D. The data is automatically protected as read-only

Q11. What is the purpose of the asterisk (*) field in the table field list?
A. Adds all fields from the table to the query
B. Deletes all records in the table
C. Applies a filter to all fields
D. Sets all fields as read-only

Q12. What does unchecking the Show box for a field in a query do?
A. Hides the field in the query results
B. Deletes the field from the table
C. Makes the field a primary key
D. Duplicates the field in the output

Q13. If you want a query where the user can input the state when running it, what is this called?
A. Parameter query
B. Cross tab query
C. Append query
D. Make-table query

Q14. By saving queries for different views of the data, what is a major benefit for users who do not know Access?
A. They can just open a query to get the data they need
B. They can edit the table structure easily
C. They can write custom SQL code on their own
D. They do not have to back up their database

Q15. What is the main difference between a query and a table in Access?
A. A query only displays data, but the table stores it
B. A table only filters data, but queries store it
C. Queries cannot be sorted but tables can
D. Only queries allow data entry

Answers: 1-A; 2-B; 3-A; 4-A; 5-C; 6-B; 7-A; 8-A; 9-A; 10-A; 11-A; 12-A; 13-A; 14-A; 15-A

DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.

Summary

Today's video from Access Learning Zone will walk you through the essentials of building customer queries in Microsoft Access. Our focus will be on developing a query that can display a list of all customers from New York, sorted by last name, so that you can quickly access this information in the future without having to recreate your work.

To begin, you need to create a new query. In Access, you have options like the query wizard or query design. For our purposes, I prefer to use query design to give you a clear understanding of how the query structure works from the ground up. Once you start a new query in design view, you will be presented with a blank workspace and an Add Tables pane. If you do not see this pane, you can add it manually. Queries draw data from tables, and in our example, we will use the only table in our database, customer T.

After adding the customer T table to the query, it appears in the upper part of your query design window. The table displays all its available fields, such as first name, last name, company name, and more. You can resize this table to better see all the fields. To build the query, drag the fields you want to view down to the columns in the design grid below. For our scenario, let's start with first name and last name. Click and drag each of these fields down into the grid. This immediately sets up a query that will display the first and last names of all customers.

To view results, use the run button, and your query will show all existing customers with the specified fields. Right now, the list is unsorted and contains customers from every state, but we will refine that soon.

Suppose you want to show phone numbers in your results. You return to design view and add the phone number field to your query grid alongside first name and last name. Running the query now includes phone numbers in the output. If you want to add credit limit, you can simply double-click on the credit limit field to add it quickly to your grid.

If the boss decides that credit limit is not needed, you can remove a field from the query. In design view, select the field's column by clicking above it until you see a downward-pointing black arrow, then press delete on your keyboard. You can also remove accidental additions in the same way.

Sorting is another important aspect. While sorting can be done in datasheet view, these sorts are temporary. To establish a permanent sort that stays as part of your query's design, set sorting options in design view. For example, under the last name column, select ascending sort order. If you want to sort by more than one field, the leftmost field is sorted first. So if you plan to sort by last name and then by first name, arrange the columns accordingly. Rearranging columns is done by selecting and dragging the column to the desired location.

After arranging your sort order and confirming everything looks correct, it's a good time to save your query. Use control S to save, and give your query a name, such as customer Q. Saving lets you reuse the query later, maintaining the same layout and sort criteria.

At this point, you have a reusable query showing all customers, sorted by last name and first name. Queries in Access can be customized for different viewpoints of the same data, which is incredibly helpful compared to tools like Excel that require constant sorting and adjustments.

Next, let's filter the query so it only shows customers from New York. To do this, you must include the state field in your query grid. Add state to the query, then in the criteria row beneath state, enter the value "New York". Access will automatically enclose text strings in quotes. After entering the criteria, running the query will display only customers from New York.

To save this specific version without overwriting your general customer Q, use 'Save object as' under the File menu, and give this refined query a new name like customer New York Q. This way, you preserve your earlier work and add a specialized query for New York customers.

Suppose you want to create a similar query for another state, like Pennsylvania. You can copy the New York query, rename the copy to customer PA, and modify its criteria to look for Pennsylvania instead of New York. If you realize there are no Pennsylvania customers, you can add some for testing, and then refresh the query to see updated results. Remember that queries reflect whatever is currently in the tables, so changes made in the table will be reflected when you refresh or reopen the query.

It's important to understand that queries themselves do not store any data. They are simply ways to view and manipulate the information that is stored in your tables. Any edits you make in a query are actual changes to your table data, so be careful when allowing users to work directly within queries. In general, to prevent accidental changes, it is better to provide users with forms or reports rather than direct access to queries. We will discuss this further in upcoming lessons.

If you need similar queries for all 50 states, you do not have to create 50 separate queries. Access supports parameter queries, which prompt users to enter criteria (like the state) when the query runs. I cover parameter queries in detail in my beginner level 5 course, with additional tip videos available for those who want to learn more at this stage.

You may notice a star symbol among your table fields. This star allows you to display all fields from a table in your query results, instead of dragging each field down individually. However, if you want to apply sorting or specific criteria, you still need to include those fields separately in your grid.

Another feature in queries is the Show checkbox below each field in the design grid. If you want to apply a filter or criteria but do not want that field to appear in your query results, you can uncheck this box. For instance, if you only want to see customers with a credit limit under $1000 but want to hide the actual credit limit values, you can add credit limit to your grid, specify the criteria, and uncheck Show. The results will only display the chosen fields, still filtered according to your criteria.

This overview just scratches the surface of what queries can do in Access. You can create queries based on multiple tables, set up user prompts, perform batch updates, append or remove data from tables, and much more. These advanced topics will be covered in future lessons, as I believe it's best to progress steadily, layer by layer, rather than covering every detail of a single topic all at once.

If you are more comfortable with books, there are many excellent references like the Access Bible series, but my tutorials are designed to help you apply new skills step by step and absorb concepts more effectively.

In the next lesson, I will demonstrate how to create forms, providing user-friendly interfaces for your end users to work with data safely and efficiently.

You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.

Topic List

Adding tables to a query in Access
Adding fields to a query using drag and drop
Using query design view
Running a query to view results
Sorting query results by multiple fields
Rearranging columns to change sort order
Deleting fields from a query
Saving queries with custom names
Copying and modifying existing queries
Filtering query results using criteria
Using text criteria in queries
Making queries based on specific field values
Understanding that queries are live views of table data
Refreshing query results after table changes
Hiding fields in query results
Using the show checkbox in queries
Using the asterisk to add all table fields
Editing data through queries
Making queries read-only recommendations
Difference between tables and queries in Access

Article

In this lesson, you will learn how to build customer queries in Microsoft Access. Queries are a powerful feature that lets you sort, filter, and display data from your tables in different ways. By the end of this article, you will know how to add tables to a query, select the fields you want to see, sort your results, filter them with criteria, and save your queries for future use.

Let us start by creating a query to give the boss exactly what they need: a list of customers from New York, sorted by last name. The advantage of doing this as a query instead of as a one-time search is that you can pull up the result any time you want it, without going through all the steps again.

To begin, go up to the top menu and click on Create. In the Queries section, you will see two options: Query Wizard and Query Design. While the wizard can be useful for more complex queries, for now it is better to learn how to build simple queries from scratch, so click on Query Design. This opens a blank query window.

On the right side, you will see the Add Tables pane. If you do not see it, click the Add Tables button. A query in Access gets its data from tables, and in your database you might only have one customer table now, called customerT. Select that table, then click Add Selected Tables. The table will appear in the upper part of the query window. When your database grows, you can bring in multiple tables or even other queries for more advanced scenarios, but for now we are just using the customer table. You can close the Add Tables pane once you are done.

Notice that, since you are editing a query, the ribbon at the top has changed to show Query Tools and the Design tab. You will see buttons for make table, append, update, cross tab, and more, but for today, we will just focus on the basics.

A select query is a type of query that shows you data from your tables. The upper part of the window shows which tables are involved, and the bottom area has columns for the fields you want to see in your query results.

To add fields to your query, look at the list of fields in your customer table, such as first name, last name, company name, and more. You can resize the table window by clicking and dragging the edge. To add a field, click and drag its name down into the first empty column in the lower panel. For example, to see first name and last name, drag each field into the first and second columns. Now your query will show those fields.

To see the results of your query, go to the ribbon and click Run. Access will show you a list of all the customers, displaying the first name and last name for each.

As it stands, nothing is sorted, and you are seeing all customers from all states because no filters or sorts have been applied yet. You can add more fields if needed. For example, if you need to show phone number, go back to Design View (by clicking the View button), find the phone number field, and drag it to the next column. Run the query again, and phone numbers will be displayed as well.

You can add more fields quickly by double-clicking their names instead of dragging, and they will appear in the next empty column. For example, to add credit limit, double-click the credit limit field in the list and it will be added automatically. If you want to remove a field, go back to Design View, move your mouse to the top of the column until you see a downward-pointing black arrow, then click to select the column and hit the Delete key on your keyboard. This lets you remove fields you do not need in your query.

You can rearrange the order of your fields by selecting a column, clicking so it is highlighted, and dragging it left or right to the desired position. This is useful because Access will sort based on the left-to-right order of your fields when you apply sorts.

To sort the data, use the Sort row in the query design grid. For example, click underneath last name in the Sort row, choose Ascending from the dropdown, and run the query. Now your results will be sorted by last name. If you want to do a secondary sort, for example by first name, apply Ascending sort under first name as well. Remember, sorting is done by fields from left to right, so rearrange your columns accordingly. For this query, put last name before first name if you want to sort by last name first and then first name.

When you have the query just the way you want, save it by pressing Ctrl+S or clicking the save icon. Give your query a meaningful name, like customerQ. It will now appear in the navigation pane, and you can open it whenever you need to see the list again, with all the sort and filter settings preserved.

Now let us add a filter so you only see customers from New York, as the boss requested. To filter based on a field, that field must be included in the query. Add the state field by double-clicking it in the list on the table window. Now, in the Criteria row underneath the state column, type NY. After pressing Enter or Tab, Access will automatically add quotes around text criteria. If not, you can type them in yourself like "NY". Text criteria should always be in quotes, while numbers and dates are entered differently (topics for other lessons).

Run the query, and you will now see only customers whose state is New York. This filter is part of the query design, not a temporary filter, so it is always active until you change or remove it.

It is important to save your query with a new name if you want to keep both the unfiltered query and the filtered one. Do not just hit save, as that would overwrite your original customerQ. Instead, use File > Save As, then Save Object As, and give your new query a different name, such as customerNewYorkQ. Now you have two separate queries: one for all customers, one for New York only.

If you want to make similar queries for other states, copy one of your existing queries using Ctrl+C and Ctrl+V in the navigation pane, rename it, and then change the filter in design view. For example, make a customerPA query, change the criteria to PA under state, save it, and run the query to see only customers from Pennsylvania. Remember, if you edit the underlying table and add or change data, your query results may not update until you press the Refresh All button or close and reopen the query.

It is important to understand that queries do not store any data themselves. They simply display data that lives in your tables. Any edits you make through a query, such as changing a customer's state, will also change the data in the main table. If you change someone from Pennsylvania to Florida in the query, running or refreshing the Pennsylvania query again will no longer show that person.

You might want to prevent users from accidentally editing data in queries. While you can make queries read-only in some ways, it is often better practice to use forms or reports when giving end users access, which are more controlled and less likely to cause accidental changes. Forms and reports will be covered in another lesson.

If you find yourself needing a separate query for each state, you may start thinking that there should be a better way. There is: parameter queries allow you to ask the user for input, like the state they want to see, every time the query runs. These are a bit more advanced, but it is good to know they exist if you need them.

Another feature you may notice is the asterisk or star (*) at the top of the field list. Adding this to your query brings in all fields from the table, which is useful if you want to see every column without dragging them down one by one. If you have specific needs for sorting or criteria, you must add those fields individually as well.

The Show checkbox in the query design grid allows you to hide fields from the result, even if you need to use them for sorting or filtering. For example, if you only want to display customers who have a credit limit under 1000 but do not want to show the actual credit limit in the result, type <1000 in the Criteria row under credit limit, then uncheck the Show box for that column. The query will filter based on credit limit but not display it.

Queries in Access have many more powerful features. You can create queries using multiple tables, make parameter queries that prompt the user for criteria, and use special queries to create new tables, append data, update data, or even delete records. Select queries like the ones in this lesson are the foundation of working with your data in Access, and learning how to design and use them can save you a lot of time and provide customized views of your data for yourself and others.

Now that you have learned the basics of building and saving queries, you are ready to create your own to view customer information in any way you want. The next step will be to learn about forms, which will let you build easy-to-use interfaces for your end users. Queries are an essential part of working with Access, and as you get more familiar with them you can take advantage of their full power.