in this session I'm going to show you how you can run a criteria from multiple tables in this
case three tables we have the contacts table the customers table and the invoices table they are
all linked together and I want to pull let's say the first name last name those are from a
customers table then the contract number and that will be under contracts and then the invoice date
invoice item invoice amount and whether it was paid or not it is from the invoices table now
how we can pull a criteria like this how we can run a criteria like this and then also do some
more things with this whether the invoice was paid or not and even create calculated field so
that if the customer did not pay this invoice you charge them a late fee for it so let's go
ahead and design the query so I'm going to close this and not save the changes here so
here's how you create the query from the thate abel's we know that the tables are linked now
we go under the query design here and then we add the tables so the first table we can add
the tables in any order really doesn't matter so I'll add the first one go to the second and then
the third table then we click on close now let's say that the first field that we want in a query
is the first name then we choose last name so I could double click on it and then let's say
I want the contact number or whatever reason to it as a reference I guess and then we can choose
here the invoice date the item on the invoice the amount and then finally if it was paid or not by
the way you can make this bigger by just dragging it down then double click on paid or not and then
we can go ahead and run and see what this looks like click on run and notice we have pretty much
what we had before so we have first-name lastname number date item and then the amount and whether
it is paid or not now of course you we could go and arrange this in order for example whether
those paid or not and then we'll go here under design view either by right-clicking on it or
just view here then design view and then we need to scroll to move this actually up and then we
can go ahead and order this in ascending order now if I run it now this they're not in order now most
likely it's they are not in order because we have another field that is doing the ordering actually
chose the amount here so let's go back and we want it whether it was paid or not so choose ascending
run it and now notice we have all the knows now what about if we want it to display only the
customers that have not paid because these are the ones that we want to notify and eventually we want
to even send them a letter tell them that there is a penalty or a late fee for the payment that
the invoice was not paid so first let's learn how we can actually limit only those that have
not paid so if we go back here under view and then choose design view we can go here whether
it is paid or no now remember from before when we chose the customer city we ended for enter
for example Lansing or Holland now in this case we have a yes or no so the invoice was either
paid yes or not paid no so if before we enter the city name Lansing or Holland to limit it by in
this case if we wanted those that have not paid that criteria would be here what we will put in
here would be just simply the word no because we want to display these fields with customers that
have a criteria of the invoice not paid or with a status of so now we run this and know this we
have here just those customers if I scroll down there are no customers that have a paid invoice
and what about in this list we do not even want to see the word no here or these fields no but we
want to display only the customers that have not paid their invoices what you can do again we can
hide this field and if you remember earlier we could go here under design view and by the way
I'm not right-clicking on it because we have not saved this query yet so if I go ahead and choose
to save this so invoices we give the name now we can right click on it and choose design view
so that's why I was not clicking on design view there if I don't want to see the field no I can
just uncheck it here it's still gonna run it in that criteria but it's just not going to display
it so notice we don't have that field you'd say well why do I want this list this would come in
very handy for customers that have late payments and usually as most companies do nowadays those
that have not paid their 30,000 bill or more or less here you want to charge them a percentage
fee so now in the next section here we are going to learn how to calculate or to add another field
here that will calculate the late fee so here's how that is done so we go into design view right
click design view or view and then design view on the top and then we're going to add a new field
over here so to add a new field what you'll need to do is you need to click on this builder icon
here on the top and then on here what we are going to do is the amount so the invoice amount double
click on it and then times x is the asteroid let's say we are going to charge 5% 0.05 and
then we click okay now notice it says here e XP r and it has all kinds of other stuff behind it
what we do here is we can type there just leave everything alone and right here where it's a exp
r1 just that section we want to change that to late fee and leave the rest alone so if we go to
the right here notice it's doing the late fee it's calculated the amount times 0.05 so then if we run
this query notice we just created another another column that we added so for this customer that has
a thirty thousand dollar bill the 5% comes to $1,500 so now the question would be how can we
format this and by the way that is how you create a calculated field in Microsoft Access and you can
look this part again in the video just rewind it and see it again how it was done so now we
are going to learn how we can add here the dollar amount or make or format this field as a currency
field so we'll go back here under the query design and go into the design view and then what you do
is there is a property sheet over here an icon on the top right click on it go to the field that we
want to modify the formatting for it and notice it's giving us the properties for the late fee
field or for the invoice paid field and notice for these other ones like let's go to the amount here
the amount was formatted earlier in the table into the dollar amount so now we are going to
go here into the late fee and we're going to format this click on the drop down and we're
going to format that into currency to the dollar amount and then the decimal places you can specify
those as well so you can mark it at two decimal points so now we can close this a property sheet
rerun the query and notice the late fee has been formatted as currency at this point we could save
this query we could actually even click on save up here and that has been saved at this point
because we have not saved it yet earlier and if I close they notice it doesn't ask us to save it
again now if I go here under and run this query quickly notice I have all these late fees what
about if I wanted to get the total here on the very top for these late fees and this is what
it'll earn next getting the total for different fields so let's go ahead and run this query again
notice we have these late fees here and now let's say we want it to see the totals in this query so
in the very bottom here we want the total so what you do is you click on it and then go to
the Home tab and then there is this option here for totals so we click on it and then it's going
to give us one more row in the very end for us to specify wire what totals we want so let's say for
the late fees we want the sum here and then also for the invoices we click on the drop down and we
want the sum of those invoices so then we save it and whenever we rerun it and those numbers change
those will be updated automatically so if I go here under contracts and I modify notice that
this was the total here ended with 850 1.3 million and 850 actually I needed to modify the invoices
because that's where the invoices are pulled from so the amount here for example it says 70
I go and change that to $1 as soon as I update this invoices amount because again the query
was running from the invoices before closed this run the query again notice that has been changed
so now it's 1.39 $851 compared to what it was before so those are some of the queries that's
limitless like of what you can do and how you can run those queries I'll say play with them
but this is a general idea of creating new fields calculated fields and by the way you can go ahead
and change the design here and let's say that you only charge them 7% late fee you'll notice
it from 65,000 it's going to change when you modify that so if we go back here to our design
and we go to our calculation so instead of 0.25 we make it point 7 percent and then run
the query again notice that from 65,000 now we have 91 thousand in late fees so check those out
and it's a lot of fun actually playing with the queries and generating all these types of data