Welcome to Office
2016, Video Number 40. Hey, in this video we've got
to talk about Access 2016 and how to build queries. Now, queries are when we get to
ask questions of the data set and return useful information. We're going to see these
15 amazing examples. We'll see and logical test
queries, or logical tests, parameter queries,
contains queries. We'll see how to group by. We'll see how to create
formulas or expressions. And we'll even see how to create
a query from multiple tables simultaneously. Now, we want to go over
to our class web site. Over here on our class website,
you can click on the link below the video to get to this. If you followed along with
video 39, in this video we created the database tables
relationships, and so on. If you have that file, just
use that file with this video. If you don't have that,
then simply download Woodencraft's
"Database Finished" after video thirty-nine
and use that. Now, here's our
Access Database-- two tables, two forms. If we double-click in the
navigation pane or products table, we could see we
have a number of fields and a number of records
with our products. Now what we're going
to do is build queries. Now, what is a query? It's simply a question
we ask of the data set. Really, it's a synonym
for data analysis. Here's our raw data. And we're going
to ask a question to get useful information. Now the first question
we're going to ask is, please show me the
Description column and the Sell Price column. And then sort the
Sell Price ascending. We might want something
like this at our kiosk. Print it out and laminate
it so we have the prices. Now we're going to
close this table. And we're going to create our
very first query, going over to the Create tab. And just like with
our tables, we're going to create a query using
Design View, so Query Design View. Now immediately, it
opens up without a name. Down here is the Query Grid. And it will always ask
us, which tables do you want to ask a question of? Now, it says Show Table up here. So I'm simply going to
double click products. And just like that, if I close
this and pull down the corner, I can even move
this to the side, just like that we
get a field list. We're allowed to ask questions
of the fields in the Product table. Now I'm going to come
down to the bottom part. This is the Query Grid. I'm going to point
to the middle. And when I see that cursor
I'm going to click and drag. Now these columns here
are where we drag fields. And then Field Table,
that will be for us. We'll just have two fields. We can then sort, choose
whether or not to show it. And this is where we
build our criteria. What do we want to
see in the column? What conditions
are on the column? We can use and logical
test, or or logical test, or other logical tests also. Now, our goal is to
show Descriptions. So I'm going to double-click
that from the field list. And sure enough, it puts the
field down in our Query Grid, Description field in
the Products Table. And by default, it's
show this column. Now just to show you that even
this basic double-clicking to fill, that's a type of query. If I go over to results-- and I'm not going to
use the Run button, I'm going to use the
Data Sheet View or Design View to follow our convention
from earlier in the class. If I click on Data Sheet
View, I could see the answer. This is the answer
to our question. Our question was simple. Hey, show me Description column. Now I'm going to go
back to Design View. That's a toggle there. We also need Sell Price. So I'm going to
double-click Sell Pricing. Just like that, it shows it. If I come up in View,
that is a question. It's hey, show Description
and Sell Price. Now, we might come
and view this here. So right on the surface
of the Data Sheet View, we can change the column width. I see that cursor. So I can click and drag. Oftentimes though, with our
query, we later build reports. And then we print
out the reports. Now I'm going to go back
down to Design View, because we have one last task. I need to click in the Sort
Row for the Sell Price column. Click the drop down
and say ascending. Now I'm going to look. And sure enough, there
is my final query result. I wanted to see
Description, Sell Price, and Sell Price sorted
from smallest to biggest. Now I want to Control +
S, because Query 1 is not a good name. Control + S, and I'm going
to call this something like Description and Sell Price. Now I can click OK. And look at that. We have our first query. Now I'm going to come
over and close this. And just like we
saw earlier, if I want to change a price
in the Products Table, I'm going to use the form. Double-click Products. If I want to change the price-- so, Barnyards
Friends is really $1. Remember, that's raw data. So all I have to do is close it. It's all ready, automatically. Double-clicking in
the Products Table, we can see one right there. I'm going to close this. But of course, a query
automatically updates. So if I double-click
to open this, it doesn't update because
this is Sell Price. I changed Cost. Luckily, I can come over
to Products, double-click. The Cost was really $27. I meant to change
the Sell Price. We're going to change it to $1. Notice it totally obeyed me. I changed Cost and
everything updated. Of course, what I
was looking at wasn't pointing to the Cost column. But now that raw data
will automatically show up if I double-click our query. And there it is, sorted at
the top, Barnyard Friends. That is quite awesome in Access. Any query we create
will automatically reflect any changes
in the raw data. Now I'm going to close and
go back to Products Table. And the Sell Price
really isn't $1. It's $50. And close this. All right, so that's
our first query. Double-click Products Table. I might ask another question. I might ask the question
hey, please show me all the records for
the supplier SC. So I'm going to close
this table and go over to Create Queries, Query Design. There is my Show Tables. I'm asking a question
of the Products Table. So I double-click that. Pull this down. Pull up the bottom grid. And I want to show all fields. Now, you can
actually double-click this to show all fields. But then you can't add
criteria to a particular field. So we're not going to use that. I want all these. I don't want to double-click
product ID, then description. I want to highlight all of them. So I'm going to
click on the top one. And like we have done so
many times in this class, before clicking on the last
one I hold Shift and I click. Now I can simply drag. And I'm going to drag it-- not to the second
or third column-- I want to drag it
to the first column. And instantly, all of
the fields are listed. Now, we want to see all of
the records for Supplier Code. So we come to the
Supplier Code column. And notice criteria. So all I have to do is in
this row right here, type SC. Now in Access and Excel,
as we've talked about, they are not case sensitive. So I could type capital SC. I could type little sc. I'm going to type capital SC. Either way, you go. Now when I click View, I could
see just the records for SC. Now I want to
Control + S. And I'm going to call this
Products from SC. We might call this
Product Records from SC if you wanted to. Click OK. And there is our second query. Now, I'm going to
close this query. And we want to do
our third query. I want to come over,
double-click Products. And our question
this time is, please show all records when
the on hand units are greater than or equal to 10. So we should get
exactly 2 records. I'm going to close this,
come over to Create Queries, Query Design. Double-click Products,
close, pull up the grid. Expose the full field list. I want to see everything. So I'm going to click on
the first, shift, click on the last. Drag to the first column. Come over to On Hand. This is the criteria row. And I'm going to type
greater than or equal to 10. That is our condition. When I click View,
just like that I see only the records
where on hand is greater than or equal to 10. Control + S, I'm going to
name it something like On Hand greater than or equal to 10. Click OK. This might be a
useful query, right? Customer wants to buy 10. You need to know which
products are available. Now, I'm going to close this. We have three queries. Now if we look at
our Products Table, the question we're going to
ask of the raw data this time is please find any products
where the sell price is less than $10 and the on hand
is greater than or equal to 8. Maybe a customer comes in and
has eight kids at a party, but doesn't want to
spend more than $10. Bucks. Dollars. We're going to close that table. Create, design, show our
table, products, close. Click the corner. Expose that field list. Drag the grid up. Now, all I want is
description on hand. I'm using Control
and Sell Price. I use the Control key to select
items not next to each other. I should have probably
just double-clicked. But you can do this also. You can click and drag. And just like that we
get those three fields. Now we have two columns,
On Hand and Sell Price. And each has a condition. Sell Price, I need to very
carefully type less than 10. Now I come over to On Hand,
greater than or equal to 8. Now we have two conditions. And they're on the same row. That means this is
an and logical test. We can only show a
record if on hand is greater than or equal to 8 and
the sell price is less than 10. Now, I want you to notice that
the conditions, or criteria, for this and logical
test are on the same row. Now over in Excel, if we were
doing Advanced Filter, which we did not do in this
Business 216 class, but which we will do in the
class after this, Business 218. When you do an and logical
test in Advanced Filter, you do exactly the same thing. You put the conditions, or
criteria, on the same row. So when the conditions,
or criteria right here, are on the same row, that
means an and logical test. Now when I click
View, we can clearly see that for each record
both tests are met. On Hand is greater than
or equal to 8 and Sell Price is less than 10. Now we need to save
this query, Control + S. And we're going to name this
somethings like Sell Price less than or equal to $10
and On Hand greater than or equal to 8. Click OK. And there we have
our fourth query. Now I'm going to close this. And we want to create
our next query. And the question
we're going to ask is show us the records
where Sell Price is between $5 and $10. So we're going to
close this table. Go up to Create,
Design, Query Design. Double-click Products Table. Click Close. Pull up the grid. Pull down the field list. And we want to see all fields. So click on the first, hold
shift, click on the last. Drag to the first column. Now we need to come
over to Sell Price. And what we said
was an and logical test is on the same row. Well over in Excel, if we
were doing Advanced Filter, we'd have to repeat the
Sell Price column twice. But over here, as we saw
when we created the table with an and logical test, we can
actually use the and operator. Not only that, we can use
between operator also. So I can type out. And in fact, I'm going
to increase the column width just a bit here. There's the cursor. I'm going to click and drag. And right in the
criteria row I'm going to type between
space 5 and 10. Now when I hit Tab, we can
see it properly capitalizes both between and and. But that will work as a
logical test in Access. Now we can go over to View. And there we see the Sell Price. We only get the products
that are between $5 and $10. Control + S, and we're
going to name this Price Between $5 and $10. And by the way, when it says
between over here, the 5 and the 10, that means the
lower limit and the upper limit are included. I'm going to click OK. There we can see
our fifth query. Now I'm going to close
this and test that. I want to come over to
Products form, double-click. Actually, I'm going to change
the window size on this Access database. Hopefully, the window size
will work a little bit better so we can see down here, also. Now I want to change the sell
price for our first product, Barnyard Friends. We're going to change
it temporarily to $10. Now, I want to come down here
and go to the next record. Now we're going to
change Blocks In Box. Sell price is going to be $5. Now if that between is
working, meaning including the lower and the upper,
then both of these products will be included in that query. I'm going to close
the form, come over to price between $5
and $10, double-click. And sure enough, there's
the $5 Blocks In Box. And there's the
Barnyard Friends, $10. I'm going to close
the Query Object, go back to the Products form. Change the sell price for
Barnyard Friends to $50, next record selling price $27. Close this form. Go over to our
query, double-click. And there it is, updated. All right, we're going
to close the query. Now, what if you didn't want
to include the 5 and 10? Let's try something. Let's copy this, since it
has mostly what I want. Click in the Navigation pane. I'm going to use my
keyboard, Control + C. And then I'm going to use
the keyboard for paste, Control + V. Now, it doesn't want
a repeat query name. So it says Copy
of Price Between. I'm going to change it,
Sell Price greater than 5 and less than 10. When I click OK-- now when I open this,
right-click Design View. Actually if I
double-click and open it, it's exactly the same because
we haven't changed the criteria underneath. But I can come up
to Views, Design. And over here,
instead of between, I'm simply going to say greater
than 5 and less than 10. When I hit Tab, it will
properly get rid of that space I had there in the capital A
and D. Now when I click View, I'm getting the
same exact thing. But when I close this
and I want to save this, come over to
Products, for example. And we're just going to
change the lower end. So Barnyard Friends
is going to be $5. Let's close the form. I want to open up Between. And of course, that one
will include that $5. Sell Price right there. But Sell Price greater
than 5 and less than 10 when I double-click,
it's not included. If we go and look
underneath, that's exactly what we did when we
created a data validation and logical test rule in
the Products Table for Cost. Here we're doing it in a query. But in both cases, we actually
typed out comparative operator number and then the
word and, and then another comparative
operator and number. Now I'm going to pull down the
grid here, just a little bit. Control + S, we can close this. Now, that was Query
number 5 and 6. Now we want to do
Query number 7. And actually, let me
show you a cool trick. If I was in a business I'd
keep them right like this, because the description
is what we want to look at when we're opening it. But just to keep
this consistent, when I go and save this and
let you download this to check, I'm actually going to
number all of these. Description and Sell
Price was our first query. So I'm simply going
to click on it. We can rename it. Either right-click
rename, or the keyboard, just as we used in
Windows Explorer for renaming files
and folder F2. I'm going to use the Home
key, 01, Space, and Enter. I'm going to down arrow to
Products, Records from SC, F2, Home, 02, Space, and Enter. Down arrow to On Hand greater
than or equal to 10, F2, Home, 03, Space, and Enter. So we're totally allowed
to rename queries. Right now, we want to
create our seventh query. Create Queries, Query Design,
double-click Products. Close the Show Table. Pull down the field list. And we want to look at what
an or logical test looks like. We want to show all fields. So Product ID, shift, Wholesale,
Retail, click and drag to the first column. We want to show Supplier Code,
either SC Toys or AP Toys. So down in the criteria
row, notice it says or. It reminds you when
you're doing or. You put them on different rows. Actually as we
mentioned earlier, if we're doing Advanced Filter
or criteria over in Excel, it goes on different rows. So I'm simply going to
type SC, down arrow, AP. I have my two conditions
on different rows. That means it will look through
this supplier code column and ask the question,
are you SC or are you AP? Any time it gets
a true for either one it will show that record. I'm going to go up
to View and click. And there it is, SC or AP. Control + S, I'm going to name
this something like Supplier Code Toy Records SC or AP. Click OK. Oops, I forgot to number it. I'm going to close it
before I rename it. Click, F2, Home, 07,
Space, and Enter. All right, now we'd like
to see how to do formulas. And our first formula, like
we think of it over in Excel, will be calculate the average
price for all of our products. If I look at the
Products Table, I simply want to take the average
of the sell price. I'm going to close this. We're going to go up to
Create Queries, Query Design, double-click Products, close. Show table. Pull down the field list. Double-click Sell Price. And now I see field, table,
sort, show, and criteria rows. But I don't see a
row for formulas. Now there's a big
button up here. You can click that sigma. Or you can right-click
and point to totals. Just like over in Excel,
sigma is just the symbol for we're going to
make some calculation. Now, we'll talk about what
Group By means in just a second. But for the time
being, I simply want to notice that there's
Sum, Add, Min, Max. And notice they're not
exactly the same names as they are over in Excel. But if I click
Average, that's it. That will now, for the
sales price column, calculate the average. Now when I click
View, it's going to be strange because it's going
to give us a single answer. That is the answer for what
is the average price of all of our sell prices. I move the column
width, Control + S. I'm going to call this something
like Average Sale Price and click OK. Now I want to close this query. And I want to copy this. Oops, I forgot again. F2, Home, this one's going
to be 08, Space, and Enter. Now I want to copy this, so
Control + C, Control + V. Now this time I'm going to try
and remember to say 0 9. And we're going to come to
the end, Average Sell Price by Supplier. Now really I should
change this to Group By, because that is
the database term for when you're making a
calculation with condition or criteria. So I'm going to click OK. And we'll see what we mean. Now, right-click Design View. Now, we have our total
row with average. But we want to
drag Supplier Code, double-click and send it down. And when I double-click watch
what it does, it Groups By. What this tells
the query to do is please take one of each Supplier
Code and calculate the average. You can think of this
like a Pivot Table. When we drag Supplier Code
down to the rows area, we get a unique list. And then we do average
calculation on Sell Price. Group By means group the prices
for each supplier together. And then calculate the average. And so when we come
up and we view, there it is, Supplier Code. Boom, boom, boom, a unique
list of the actual Supplier Codes from our Products Table. And there are the
average prices. Now, we could go
down underneath. We could actually highlight
this whole column. And watch this. I'm going to click and drag to
move it before the Sell Price. Now when I click View, there we
see like a Pivot Table, boom, boom, boom. There's our unique
list of items. And there is our average price. I'm going to Control
+ S, close this. I want to copy this. Because now, I want to
actually find the Min and Max for each supplier. So I select it,
Control + C, Control + V. I'm going to remember
to call this 1 0. And we're going to say, instead
of Average, Min and Max Sell Price, Group By Supplier. Click OK. Now I can come down,
right-click Design View. I'm going to change this
calculation from Average to Min. And I'm going to
double-click Sell Price. Come over to where
it says Group By. That's the total row. Click that drop down. And we're going to say Max. Now when I click View
there it is, unique list of Supplier Code, Min and Max. Sort of like a Pivot Table,
but over here in Access. Control + S, we're
going to close this. Now, we actually want to do
an official formula over here in Access. We'll get to do two of them
in our next two queries. Now our first formula-- and we'll go look at
the Products Table-- we need to calculate
Inventory Value. And inventory value will
be On Hand time Cost. Now when we create
a formula in Access, we actually have to
take the field names. And in our case, we're
going to multiply On Hand field times the Cost field. And we'll have to put the
field name in square brackets. If you remember over in Excel,
when we use the Excel table feature, there we saw field
names in square brackets. All right, I'm
going to close this. Create, Queries, Query and
Design, double-click Products, Close. Show table. Expand the Product field list. And we want Description. So I'm going to
double-click because we want the name of each product. And then here's where we're
going to create our formula. Now, we could expand the column
here and type out our formula right there. Or we can right-click
and point to zoom. And in the zoom canvas here
we can create our formula. Now, there's two parts. We're always going
to have to create the name of the column or
the field, then type a colon to separate the name,
and then the formula. So the name of this
column is simply going to be Inventory Value. And then I type a colon. That colon says
everything before is the new name of
this column or field. Everything after is the formula. Now remember, we want
Cost times On Hand, open square brackets, On
Hand, close square bracket. And remember what we said
when we created the table. If I misspell the
field in the table, I better misspell it everywhere
else in the database. So since I'm referring
to that column and I didn't spell it wrong,
I'm going to spell it correctly. On Hand, that is a field
in square brackets. Now I multiply,
open square brackets times the name of the Cost
field, close square brackets. That's our formula. When I click OK, I can see
the formula down there. When I come up and click View,
there's our Inventory Value. You can change the column width. Control + S, and
I'm going to give it a smart name like
Inventory Value. Click OK. Now, I want to go
back underneath. And if you misspell
something, it's going to think you want
something called a Parameter Query. So if you accidentally misspell
it and come up and click View, it wants you to do
a Parameter Query. And our query number 15
will be a Parameter Query. And we'll see how that works. But if you get this while
you're typing out your formula, it means you misspelled
your field name. I'm going to click Cancel. Facts based on that
ask, click View. And there it is. And of course, that's
totally dynamic. Two videos ago, we changed
the prices in our form and watched how our
inventory value updated. I'm going to close this. I'm going to say yes. I want to save it. Now our second formula,
Create, Queries, Query Design, Product Table, we want
to calculate gross profit for each product. So I'm going to double-click
Description, come down to the second field. Right-click zoom. We're going to type Gross
Profit, colon, square bracket. And gross profit is Sell
Price minus Cost, sell Price, close square bracket, minus,
open square bracket, Cost, close square bracket. That formula will
work if we have our colon, which means
everything before is the name of our field. Everything after is fields. And we spelled them right. So when I click OK-- I'm going to increase
the column width-- come over to View, there it is. Uh oh, it looks like we
changed the raw data in one of our examples and
forgot to change it back. We'll go fix that
in just a moment. Now I want to save this. Control + S, Gross Profit for
Each Product, and click OK. Now I'm going to close this
and go look at Barnyard Friends down in our forms, double-click. And sure enough,
there's that $5. This was actually $50. Now I want to enter that
number and close this. Go back to-- and look
at that, I am not remembering to name these. F2, Home, that's going to be
12, Space, Enter, Space, Enter. Now I'm going to hit Enter
to open up Inventory Value. And there there's the
correct Inventory Value. So it's totally dynamic,
this Inventory Value Query. I'm going to close this. Now, we have three more queries. And the next query-- and we'll go look at
the products table-- is sometimes you want
to do a Contains Query. I want to search for any product
that contains the word rail. So there is a railway
bridge, midget railroad. So we want to search for
all the railroad toys. I'm going to close the table,
Create, Query Design Products Table. I want to show all the fields. So I highlight all the
fields, drag them down to the first column. And I come over to Description. And there's a wild card
that we're going to use. And it's an asterisk. so I asterisk, rail, asterisk. Now asterisk stands for
zero or more characters. That means when I type
asterisk rail asterisk, I can find the word rail. And it would be
returned in our query. I could find railroad because
that asterisk is zero or more characters. And road has four characters. Or I could find midget railroad. And this wild card asterisk
also is over in Excel. And it always stands for
zero or more characters. If you wanted a single character
you'd use the question mark. Now, let's test this query. I'm going to click View. And just like that
it's totally working, Control + S, Products
that Contain Rail. Click OK. Now, I'm going to close this. And we have two more queries. And here's an awesome query. We're going to ask a
question of both tables. Now, I want to open up Products. And I want to return
all of the products that have on hand less than five. If there's on hand
less than five, I need to reorder that product. Now if I'm going
to reorder, I would like to know what the cost is. I definitely want the
Product ID and Description. But I also need,
from Supplier Data, I need to know the name of
the person and the telephone number. That means I'm pulling
data from both tables. The reason that
this will work is because there's a relationship. For example, products-- there is
a product, Barnyard Friends 3. But notice, this record has SC. And through the relationship,
when I pull Cost, On Hand, Description
of Product ID, because there's a
relationship for SC and the query also ask for
first name and telephone number, it'll totally know to
pull Sue and telephone number for that SC. See If there wasn't a
relationship between these two tables, then we couldn't
pull data from both tables. All right, I'm going
to close both tables. We're going to go over to
Create, Queries, Query Design. Double-click Products. Double-click Supplier Data. Close Show Tables. And look at that. It even shows us
the relationship, a one to many relationship. Now I'm going to
select Product ID. And before I click on
Cost, I'm holding Shift. I'm going to click and drag
down to the first column. Then I'm going to come over
and double-click last name-- this is so amazing-- and telephone number. Now right now, if
I click Data View, there's no condition
or criteria. So it just shows me Product
ID, Description, On Hand, Cost, Name and Telephone Number. That might be something useful. But we want to limit it. We don't want anything to
show up that has on hand five or above. So I'm going to go underneath. And I'm going to say
please, On Hand, you have to be less than five. Click View. And there's our
useful information. We can print this out. And in fact, in our
next video, we'll build a report based on this. And then we have
our description, how many we have on
hand, the cost, the name and the telephone number. Control + S, and
we're going to call this something smart like
Product Reorder Phone List. All right, I'm
going to click OK. Close this query. Our last query is going
to be a parameter query. Now if we look at
Product Records from SC and go underneath and look,
we see that this is for SC. But this would be a
useful query if I could-- when I opened it,
it asked me which Supplier Code I would like. So I'm going to close this
query with 0 2 Product Records selected. I'm going to Control
+ C, Control + V, and call it Records by
Supply or Parameter Query. Click OK. Now, I can see it's no good. I just can't seem to number
them while I'm creating them. So I will F2 each one of these. And this will be 15, Enter. All right, I'm going to move
the width of this, right-click. Actually if we double-click
it, we see we get the SC. But now let's go
underneath to Design. And we simply come down
here and in the criteria-- remember earlier, we
had square brackets. And if we type anything here
that is not a field name, it will turn into
a parameter query. So I'm going to type
something polite, because that will
be the message. It'll say Enter Supplier Code. And now when I come over
to View, how cool is that? I'm going to type
AP and click OK. And sure enough, there
are the AP records. Now I'm going to
Control + S, close this. Because now, this is
a parameter query. When I double-click and open
it, it will always ask me. Hey, I want to see Sue Chin. So I type SC. And there it is. All right, that was
a lot about queries. We saw a basic just pull
two columns and sort. We saw how to add conditions
that are criteria. We did conditions and criteria
with comparative operators. We did an and logical
test, between. We saw how to exclude
the upper and lower limit for a between logical test. We did an or logical test. We did aggregate calculations,
aggregate for the overall sales price, aggregate where
we did group by supplier. We did min and max
group by supplier. We calculated formulas
or expressions. We did inventory
value, gross profit. We even did a contains query. And then we did a query based
on two tables, created a reorder phone list. And then our last query
was a parameter query. Now that is the bulk
of the Access videos. We actually have
two more videos. We'll see how to create
a basic report or two, clicking the report button. And in our final
query, we'll see how to export data from Access. Now if you like that video, be
sure to click that thumbs up. Leave a comment and sub. Because there's always lots
more videos to come from Excel Is Fun, including two
more short Access videos. All right, we'll
see you next video.
