In this session I'm going to demonstrate how to create queries from multiple tables in an Access database we'll create those queries using the query wizard and then in the next session we're going to the advanced query method in order for us to create queries from multiple tables we need to first make sure that the tables have been linked via these relationships here so you can check that from database tools and then relationships and you should see these links and notice here that we have a customer stable just linked to the contacts table so let's say that we want to create a listing or a query as a technical term is of the customer ID the first name last name street address and then this is if state and then we want to also list the contact information for these customers so here's how we do that we'll close this first and.
Now we go here under create and then we go under query wizard and then click on ok and then we are going to go first to customers we're going to pick customer ID first name last name street address cities dibs and then we go to the next table here we go under contacts and then we'll pick up the contact number and then the amount date and type then we click on next and then click on next again and then here is have some kind of meaningful name and then finish notice at this point we have the list where the data that we requested customer first name last name and such and the data has been pulled from both tables so that is creating a query using the query wizard you could do also the similar thing as well if we save this now we can go and create one for invoices so let's say rather see the contract and how many invoices remain to be paid.
So we go here under query wizard and then go under simple query wizard again and we can do this with two other tables so we can go under contracts we can get the contact number and then we can go under invoices and then pick the invoice number the date and whether it's what's paid or not and then next and then give it a meaningful name click on finish and here we have a contact number the customer ID the contract amount date the type the invoice specific number to date that the invoice was issues and whether the invoice was paid so next we are going to learn how to utilize the advanced queries in a database in this session I'm going to briefly demonstrate how to utilize the query design in Access 2016 so far in our access tutorial we have learned how to use the query wizard in defining and designing a query but in most cases in Access 2016.
And previous versions of access a more effective way to utilize queries and design queries is to use a query design the way that works is that instead of you going through step by step and adding specific fields in a query you can actually design this query using this method so to utilize the query design we click here a query design icon and then the big idea here is is that you pick the tables that you want to work with initially so in our case here the tables have been linked by using the relationship module that we saw earlier and now we'll pick the tables that we want to utilize and then click on okay typically the relationship looks like this so we have customers then we have contacts and.
We have invoices the way it works is that you have these tables here with all the different fields from each table and you pick specific fields from each table and you're creating in your query so for example we want here first name last name and let's say the invoice number and typically you can either double click on these fields that you want to add to the query down below or you can simply drag these fields down here like we did a moment ago if you double click on this asterisk sign it will insert all the fields that are part of that table now here we want for example.
The invoice number the contract number and double clicking at this point the item and the amount and then we want also whether it was paid or not now let's assume that these are the fields that we want in our case now you can run this and see what it looks like notice you have the first name last name actually I kind of backwards you can readjust that we can go back and readjust it and.
We have invoices paid or not if we want to tweak this query again we go here under design mode and go into design view and then tweak this again so if we want it prism first name to be first simply drag this to the left and once we move the field four way we want to customize this view the way we want then we can run this again and now no this first name is in the beginning and then you have last name and so on so that's how you briefly utilize the query design in Microsoft Access 2060 in an Access database notice as well if we go back here to the design view you can define the sorting order you can define specific criteria whether the criteria is either.
Or different various criteria by the various fields here and that's what we'll learn next and before they finalize its completely here notice also there are additional parameters that you can utilize here and we'll cover this shortly as well such as the query builder and the totals field and such in this session I'm going to briefly demonstrate how to utilize criteria how to define the criteria within a query in Microsoft Access 2016 using a query design so here's how it works let's say we have this query here called the invoices in contract actually have not named this yet it's query number one we go here under a query design view and now let's say that we wanted to see for example only the invoices that have not been paid if we go under the invoices table and notice here that this is.
A yes or no field to enter a criteria so we see only the invoices that have not been paid we go here under the design view and what we want to do is here under invoices paid notice there is an option for criteria there is a row here that we can put a criteria for this specific field so we want to display only the invoices that have not been paid in that case we want to display those that meet the criteria for no so we simply have to put no in there and now click somewhere else outside of this area and then around this query now notice it's displaying only those that have not been paid if we want it to display the text here whether yes or no or invoices paid earn with the wording no next to it just to be sure we make sure that this check mark and by the way that should be there automatically make sure that has the check mark right here now if we go.
And put on it again notice it says invoice paid no and it's displaying only those if we want to hide that field we go back to this view and then just take out the checkmark and then run it again and it will not display so that's how you insert a criteria within a query using the query design now you can add multiple criteria's as well if we run this query right now notice that there are invoices ranging from a thousand dollars to more than thirty thousand here so let's say we want to see only the invoices that have not been paid of greater than ten thousand dollars so to insert that criteria we go back to the query design here and then under the amount for the specific criteria we want to put them so the both criteria will be met we say greater than equal ten thousand.
And then click anywhere outside of this field and then run this.
So we are saying we want the criteria all these fields plus the amount needs to be greater than or equal to ten thousand and then the invoice needs to be not paid we run this and now notice we have all these invoices display the other thing that we could do is we could sort this and we can go back here to the design view again and customize this further so under the sorting criteria we say we want to sort this in descending order let's see the largest amounts first followed by the smallest ones so notice we are doing things so far click on the run again and now notice the one hundred and five thousand dollar invoice comes first and then the rest are following that then if we go back to the design view we can even insert as many criteria's.
As you want and I hope you get the idea so you could sort for example by a specific zip code or by a specific city and so on under city for is only if I wanted to add an additional field and I want to insert it right there just simply drag it in there and then we could have various criteria so right now I don't have any criteria by city and.
If I run it now this is just going to display the city but let's say that I want the city Lansing or Holland so I'm display those two cities now in my case here I can go back to design view and I'll enter two criteria so one of them will be Lansing and you have to type that correctly and I can say or Holland so it could be either one of them and then run them notice it is displaying only the city Lansing or Holland but then notice that the criteria is not quite what we were expecting earlier notice that we have 2500 says no longer just 10,000 or more the reason for that.
Is because we have here in our criteria stating that the criteria could be Lansing greater than 10,000 and invoice is not paid or anything from the city called Holland so we either have to move this up here or here why is the criteria either or that we are using earlier but then keep in mind that it's not going to apply so we have to put them like this with a or here and the additional two criteria's then we run it and notice these are the only clients that have not paid their invoices yet with the greater than 10000 balance and only Lansing or Holland just for those two cities so that's how the query design works with multiple criteria's within the query you.
J. Amoros
Read More: 25 Tools Every Marketer Needs | Free + Paid Digital Marketing Tools
No comments:
Post a Comment