Access: Multi-Table Queries (Part 2)

Goodwill Community Foundation creating opportunities for a better life in the last video we created a query that used two tables and we just wanted our query to include customers who have placed an order at our bakery now we're going to talk about why we chose the type of join that we did and we're also going to look at some examples of search criteria that you can use to narrow down your queries in this example we chose a right-to-left join because we wanted the query to pull in the records from the orders table first and then use the information in those records to retrieve the records from the customers table to understand how this works let's look at how these tables are connected every time an order is placed it's connected with a customer and if someone places multiple orders then they'll be connected with each of those orders you'll notice that not all of our customers have placed an order but all of the orders are connected with a customer and that's an important thing to keep in mind when we're choosing which type of join to use let's look at what would happen if we connected these tables with a left-to-right join first access retrieves all of the records from the customers table and then it uses this list to get all of the orders that are connected with a customer that means the query is going to include all of the customer records even if they're not connected with an order and this is not what we want instead we want to use the order records to just pull in the customers who have placed an order we can do this by choosing a right-to-left join now the query will first retrieve all of the records from the orders table and it will then use that list to find all of the customers who are connected with at least one order so whenever you're creating a query with multiple tables you'll need to decide which type of join to use you can double click the join to change it and then choose option 2 for left to right join or option 3 for a right to left join we are also narrowing down our query by using search criteria in the city and phone number fields you may remember that search criteria have to be written with a very specific syntax so that access can understand them and they'll often need to include quotation marks and parentheses in order to be correct so let's look at a few of the different syntaxes that you can use if you're looking for an exact match then you can just put your search terms in quotation marks if you want to exclude something from the results then you can use not in and you can even exclude several different things by separating them with commas if you're looking for terms at the beginning or the end of a field you'll need to use the like syntax and you'll notice that each one of these has an asterisk in it this is known as a wild-card character which just means that anything can go here for example if you're looking for phone numbers that begin with nine one nine then you'll type nine one nine asterisk and that means this query will look for nine one nine followed by anything and finally when you're working with numbers you can use symbols such as greater than and less than to test the values and you can also look for numbers that are between two values so those are some of the most common syntaxes that you can use and you might not use all of these but depending on what type of information you have in your database you can probably find at least two or three that will be useful to you

Channel: GCFLearnFree.org

Views: 157,946

Rating: 4.9194632 out of 5

Keywords: Access 2016, Joins Access, Multi-table query Access, Query Design Access, Microsoft Access 2016, MS Access 2016, Office 2016, MS Office 2016, Microsoft Office 2016, databases, access query, multi-table queries access 2016, access, access 2019, office 2019, office 365

Id: vtgwXZMXBTo

Length: 3min 35sec (215 seconds)

Published: Fri Apr 08 2016