|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Query Criteria By Richard Rost 
6 years ago
How to use Criteria in your Microsoft Access Queries? In this video, I'll show you how to use criteria to set up a query in Microsoft Access to display exactly the information you want. Jennifer from Mississippi (a Gold Member) asks, "I'm brand new to Access. I set up my customer list, but I need to see a list of people who are in Florida and have been customers for at least 30 days. How can I do that?" MembersI'll show you how to specify OR conditions in queries (like show me all the customers who are from Florida OR have been customers at least 30 days), how to list all the birthdays / anniversaries this month, and how to use parameter queries to allow the user to type in the state when the query runs. Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today! LinksFREE Access Beginner 1, Lesson 9: https://599cd.com/Free1
IntroIn this video, I will show you how to use criteria in your Microsoft Access queries. We will work with a customer table to filter records, such as displaying only customers from Florida who have been customers for at least 30 days. I will demonstrate the step-by-step process of creating a query, adding fields, setting up criteria, and saving your query for future use. This video is a great introduction to working with query criteria to display the data you need in Microsoft Access.TranscriptWelcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.In today's lesson, we are going to learn how to use criteria in your Microsoft Access queries. Today's question comes from Jennifer from Mississippi, one of my Gold members. Jennifer asks, I'm brand new to Access. I set up my customer list, but I need to see a list of people who are in Florida and have been customers for at least 30 days. How can I do that? Well Jennifer, the easiest and best way to do this is to set up a query and use query criteria. Let me show you how. Here is my customer database, and I have a customer table in here. I have a field called state where I track what state they are from and another field called customer since where I track how long they have been a customer. Now Jennifer wants to see all of the customers from Florida who have been customers for at least 30 days. So let's set up a query. I'm going to close this table, come up to my ribbon, and pick Create, Query Design. You should see the Add Tables list over here; if not, click on Add Tables up here under Query Setup, and we are going to add our CustomerT to the query. Double click on it. You can have queries based on multiple tables. That's a little more advanced, and I cover that in my Expert classes. For now, I'm just going to close the Add Tables pane over here. I'm going to open this up a little bit so I can see more of what is inside of it. The way a query works is you bring down to these columns the information that you want to see. For example, FirstName, double click on it, LastName, State. Instead of double clicking, you can also click and drag it and drop it. And then let's find CustomerSince. Let's save this (Ctrl+S). Let's save this as MyCustomerQ. I like to end all of my queries in the letter Q, and you can see it appears over here under the Database Objects. Let's run this query. Running the query just says, show me what you have. That is what we've got. We have all the records showing up, but just the fields that I asked for: FirstName, LastName, State, and CustomerSince. Queries are good because you can set these up and then use them later without having to go through modifying your table, changing your sort, and adding filters. You can do all this right in the table, but queries are better because they save all your work, so you do not have to keep doing this over and over again. If someone else uses your database and they're not an Access expert, you can just say, open up the CustomerQ and it will show you exactly what you want. Let's add some criteria. I want to see all the customers from Florida. So I'm going to go back to Design View (right click, Design View). Down here underneath State, where you see it say Criteria, in that row, inside of quotes, put "FL" like that. Now when I run my query, you can see I only see customers from Florida. If you want to add that second criteria, which is customers that have been a customer for at least 30 days, let's go back to Design View. Right under CustomerSince, you can put less than or equal to Date() minus 30 and then tab or enter. One of the nice things about Access is that when you're dealing with dates, a unit of one equals one day. So Date - 7 is seven days ago; Date - 30 is 30 days ago. There are functions if you need exact months or quarters or things like that. Let's run our query now, and look at that. Two of those three Florida customers have been a customer for at least 30 days. That's the basics of working with criteria in your queries. Now when I save this, save it and close it, I can open it up at any time in the future and get the same exact results with whatever data happens to change. These queries are tied to the tables, so as I add customers, the query (when I run it again) will show the most recent information. Queries are useful for displaying your data in a certain way. There is lots more to learn with query criteria. I cover a good amount of material in my free Access Level 1 class. Lesson 9 is 15 minutes of just query tips: how to do things with queries, with numbers, and dates, and all kinds of stuff. Go watch that if you want to learn more about query criteria. It is a free three-hour course. There is also an extended cut edition of this video for members only. If you are a member (a Silver member or up) of my YouTube channel, I talk about OR conditions, like what if you want to see customers who are either from Florida or have been a customer for a month. I will show you how to do that. I will also cover how to do birthdays or anniversaries in a particular month. So if you want to see all the people you want to send birthday cards to, all your people who were born in September, I will show you how to do that in the extended cut. We will talk about parameter queries so you can pop up a message like you see on the screen: Enter the state. You do not have to hard code that into your query. The user can type in Florida or New York or whatever whenever they run the query. That's all covered in the extended cut for members. How do you become a member? Click on that Join button right below the video on my YouTube channel. You can get access to the extended cut TechHelp edition if you're a Silver member and up. You can access live video and chat sessions and a bunch of other perks. You'll see a list of all the options available when you click on the Join button. Don't worry, these TechHelp videos are always going to be free. I'm going to keep making them as long as you keep watching them. Make sure you like and share my video. Be sure to click on the Subscribe button and ring the bell. That's free. It just subscribes you, so whenever I release a new video, you get a notification. I like to see those subscriptions going up and it makes me want to make more videos when I get more subscribers. If you're not a subscriber already, go ahead and subscribe right now. It doesn't cost you anything. Make sure you click down below under the description where it says Show More because there are always links to other stuff that I cover. You'll find a link to that free Access Beginner 1 class. I also cover a whole hour on queries and criteria in my Access Level 5 Beginner class. That's on my website. You can find a link to that below as well. Want to see your question answered in a video like this? Visit my TechHelp page. Or you can always email me, but I prefer you use the TechHelp page. There's all my other cool stuff: my blog, Facebook, Twitter. Thanks for watching. I hope you learned something, and I'll see you next time. QuizQ1. What is the main purpose of using criteria in a Microsoft Access query?A. To filter records to show only those that meet certain conditions B. To change the design of the table structure C. To create automatic backups of the database D. To export data to Excel automatically Q2. In the video, what example did Jennifer want to accomplish using query criteria? A. Find customers with overdue payments B. List customers in Florida who have been customers for at least 30 days C. Display all customers who have not made any purchases D. List customers from New York with birthdays in September Q3. Where do you enter the criteria for a field in the query design grid? A. In the table view only B. In the Criteria row under the specific field C. In the Sort row above the field names D. In the Description property of the field Q4. How do you specify that you want records for customers from Florida in the State field? A. Typing Florida in the Criteria row B. Entering "FL" (with quotes) in the Criteria row C. Leaving the Criteria row blank D. Entering "USA" in the Criteria row Q5. If you want to find customers who have been customers for at least 30 days, what criteria would you use? A. Date() + 30 B. Greater than Date() - 30 C. Less than or equal to Date() - 30 D. Equal to 30 Q6. Why are queries in Access considered better than simply filtering data in a table? A. Queries are slower but more reliable B. Queries automatically backup the database C. Queries save your criteria and results can be reused easily D. Queries are required for exporting data Q7. After saving a query with criteria, what happens when you run it again after new customers are added to the table? A. The query shows only the original records B. The query will not include new data until it is recreated C. The query automatically includes any new records that match the criteria D. The query can only be used once Q8. What happens if you add criteria to more than one field in the same row in Access query design? A. Records have to meet all criteria in that row (AND condition) B. Records will meet any one of the criteria (OR condition) C. Only the first criterion is used D. Access will show an error Q9. What function does "Date()" perform in Access queries? A. Returns the system time B. Returns the current date C. Returns the date 30 days ago D. Returns the user's birthday Q10. What is a parameter query in Access? A. A query that sorts by a specific field B. A query that prompts the user to enter a value each time it runs C. A query that only administrators can use D. A query with no criteria set Q11. What is the name recommended by Richard for saving your queries? A. Always end with "Table" B. Always end with "Q" C. Always start with "My" D. Always end with "Report" Q12. Where can viewers find additional free training on queries and criteria according to the video? A. In Access Help files only B. On a different website unrelated to AccessLearningZone.com C. In the free Access Level 1 class lesson and other course links provided in the video description D. No additional training is available Answers: 1-A; 2-B; 3-B; 4-B; 5-C; 6-C; 7-C; 8-A; 9-B; 10-B; 11-B; 12-C 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. SummaryToday's video from Access Learning Zone focuses on how to use criteria in your Microsoft Access queries to filter your data efficiently. I recently received a question from a viewer who set up a customer list and wanted to know how to display only those customers who are in Florida and have been with the company for at least 30 days.To address this, the best method is to set up a query using specific criteria. In my example, I use a customer database containing a field for the state and another for the date each customer joined, called CustomerSince. If you want to see all customers from Florida who have been customers for at least 30 days, you should begin by creating a query. When setting up the query, make sure to add the relevant table, in this case, CustomerT. Then, select the fields you wish to see in the query results. For this scenario, you might include FirstName, LastName, State, and CustomerSince. After adding the necessary fields, it is helpful to save your query with a name that ends in 'Q' to identify it as a query. Running the query at this stage will show all customers, but you want to narrow this down further. This is where criteria come in. Queries are very versatile since you can set up the filtering once and use it repeatedly without having to modify your tables or apply filters each time. It's especially useful if others who are not Access experts use your database, as they can simply open the prebuilt query. To filter for customers from Florida, go into Design View and enter "FL" as the criteria under the State field. Running the query now will display only those customers whose state is Florida. To add a second condition for customers who have been with you for at least 30 days, enter a criteria under CustomerSince that checks if the date is at least 30 days in the past. In Access, date calculations are straightforward since each unit corresponds to a day, so subtracting 30 from the current date gives you the date 30 days ago. When you run the query now, you will see only those customers from Florida who have been customers for 30 days or more. As you add new customers to your table, running this query in the future will always reflect the most current information. Queries are incredibly valuable for displaying your data according to specific requirements and save you time since you do not have to recreate your filtering and sorting preferences. If you want more information about query criteria, I cover a lot in my free Access Level 1 class. Lesson 9, in particular, focuses specifically on queries and offers many useful tips for working with numbers, dates, and other types of criteria. For those interested in advanced techniques, I also have an extended cut edition of this video available exclusively to members. In the extended cut, I cover topics such as using OR conditions to find customers who either live in Florida or have been customers for a month, how to filter by birthdays or anniversaries that occur in a specific month, and how to create parameter queries that prompt the user to enter a value, such as a state name, when running the query. These topics can be very helpful if you want to make your queries even more flexible and user friendly. To access the extended cut and other exclusive content, you can become a member of my YouTube channel. Membership unlocks these advanced videos along with live video sessions and additional perks for Silver members and above. Even if you are not a member, you can always access my free TechHelp videos, and I will continue making these as long as there is interest from viewers. If you find these tutorials helpful, remember to subscribe to my channel and share the videos with others. Subscribing is free and ensures you will receive notifications whenever I post new content. In the video description, you'll find links to the free Access Beginner 1 class and my Access Level 5 Beginner class, which contains even more material on queries and criteria. If you have your own questions or ideas for future topics, visit my TechHelp page or send me an email. For a complete video tutorial with step-by-step instructions on everything discussed here, you can find it on my website at the link below. Live long and prosper, my friends. Topic ListSetting up a basic query in Microsoft AccessAdding tables to a query using Query Design Selecting fields to display in a query Saving queries with custom names Running a query to view filtered results Using criteria to filter by specific text values Filtering records by state using criteria Filtering records by date using criteria expressions Using Date() function and date arithmetic in criteria Combining multiple criteria in a query Understanding how queries update with table changes Benefits of using queries over table filtering |
||||||||||||||||||||||||
|
| |||
| Keywords: TechHelp Access access query criteria queries PermaLink Query Criteria in Microsoft Access |
