IntroductionWelcome| 00:04 | Hi! My name is Adam Wilbert and I would like
to welcome you to Access 2007: Queries in Depth.
| | 00:09 | In this course, I am going to reveal the
tools that you will need to answer some
| | 00:12 | of the most complex questions
that you might have about your data.
| | 00:14 | I will walk you through some real world
scenarios and show you how to leverage
| | 00:17 | queries to bring a new level of
insight and clarity to your raw data tables.
| | 00:21 | We will look at setting up queries
from scratch to filter and sort your data
| | 00:24 | in your database and I will
demonstrate how to perform calculations on
| | 00:27 | numerical values and use some built-in
Access functions to further refine the
| | 00:31 | way your data is presented.
| | 00:32 | We will also use queries to identify
top reformers as well as to automate some
| | 00:36 | repetitive analysis task.
| | 00:38 | Now, I like to say that a good
database can give you more than what you put
| | 00:40 | into it and queries is where that magic
happens, where raw data becomes useful information.
| | 00:45 | Together we are going to cover all of
that and more in Access 2007: Queries in Depth.
| | Collapse this transcript |
| Using the exercise files| 00:00 | If you're a premium member of the lynda.
com Online Training Library, you'll have
| | 00:04 | access to the Exercise Files
used throughout this course.
| | 00:06 | The Exercise Files are in the Excess Files
folder, which I have placed on the desktop.
| | 00:10 | You can store it wherever you like.
| | 00:12 | There are files for most of the
movies and they reside in subfolders named
| | 00:15 | according to the chapters.
| | 00:17 | It's not necessary for you to use these files;
you can use your own files in place of them.
| | 00:21 | If you're a monthly or annual
subscriber to lynda.com, you don't have access to
| | 00:25 | the Exercise Files, but you can
follow along with your own work.
| | 00:28 | So let's get started.
| | Collapse this transcript |
| Reviewing the Access interface| 00:00 | Before we can get into learning about
queries in depth, I wanted to review
| | 00:03 | some of the common Access interface elements
that we will be using throughout this course.
| | 00:06 | On the left-hand side, we have a
navigation pane which lists all of the tables,
| | 00:09 | queries, forms, and reports within our database.
| | 00:12 | If it's not currently open, you
can toggle it by pressing F11 on your
| | 00:15 | keyboard and then once it's open, you can
continue pressing F11 to minimize and expand it.
| | 00:19 | Microsoft Access uses the same Ribbon
interface that's common throughout the
| | 00:22 | entire Office 2007 suite.
| | 00:25 | Our tabs at the top might change
depending on the task that we are
| | 00:27 | currently performing.
| | 00:28 | We are going to spend a lot of time in
this Create tab using the Query Wizard
| | 00:32 | and query design tools.
| | 00:33 | Finally I want to take a look
at our Database Tools tab and the
| | 00:36 | Relationships window.
| | 00:37 | The Relationships window describes the
relationships between all of our tables
| | 00:40 | within our database.
| | 00:41 | Now, if any of this seems
unfamiliar to you, you might want to review
| | 00:44 | the Access 2007 Essential Training
title available in the lynda.com Online Training Library.
| | Collapse this transcript |
|
|
1. Creating Queries in AccessQuery basics| 00:00 | The data in our database can become
overwhelming with tables growing to
| | 00:03 | thousands of records easily.
| | 00:05 | Luckily, Access provides us with a set
of objects called queries that we can use
| | 00:08 | to quickly filter out and get to the
information that we're interested in.
| | 00:10 | But before we can get into making
queries, we need to understand the data that
| | 00:13 | we have available to work with.
| | 00:15 | Now if you're inheriting a database
like we are today, you want to go through
| | 00:18 | all of the tables to see what's in them.
| | 00:19 | If you have been working with your
database from scratch, you are likely to be
| | 00:22 | intimately familiar with
the data that's in there.
| | 00:25 | But for now we are going to go ahead
and go through our tables and see what
| | 00:27 | we have to work with.
| | 00:27 | Right now I am going to open up our
tbl_DirectCustomers table and we will see
| | 00:32 | that this table contains some
information about all of the customers that have
| | 00:34 | done business with the
Two Trees Olive Oil Company.
| | 00:36 | We have got our FirstName, our LastName,
phone number, some contact information,
| | 00:41 | their email address.
| | 00:42 | On the far right side, we have a
Newsletter column that simply tells us whether
| | 00:45 | they've subscribed to our
online newsletter or not.
| | 00:47 | I can go ahead and close this table and
then open up the Employees table, and we
| | 00:51 | will see that we have some similar
information about the employees that work for
| | 00:54 | the Two Trees Olive Oil Company.
| | 00:55 | We have got FirstName, MiddleName,
LastName, contact information and email
| | 00:59 | address, and scrolling right, we can
also see some information about when
| | 01:03 | they're hired, the rate of pay that they
currently receive, and the department that they work in.
| | 01:07 | I am going to close this
and go to our Orders table.
| | 01:11 | The Orders table has an OrderID, and this
is a unique ID for each order that's placed.
| | 01:15 | We have got the date that the order
was placed, an ID number that links to
| | 01:19 | customer that placed the order, an ID
number that links to the product that was
| | 01:22 | ordered, and an ID number that links to the
employee that assisted them with the order.
| | 01:25 | I can close this and open up our
Products table and we will see those
| | 01:30 | ProductIDs that we just saw in the Orders table.
| | 01:32 | We can also see what the name of that
product is, the code for the specific oil
| | 01:36 | that's in the product, the size of the
product in ounces, the wholesale cost,
| | 01:41 | the retail cost for the customer, and
then we have some information about how
| | 01:45 | much that's marked up.
| | 01:45 | So we have got the markup in
dollars and the markup in percent.
| | 01:48 | I am going to go ahead and
close the Products table.
| | 01:50 | There are mainly four tables in our
database give us some lookup information
| | 01:53 | about some of our other products.
| | 01:54 | So if I open up the GradeID, we will
see that we have a numerical code for the
| | 01:58 | grade and then we have the
text that code represents.
| | 02:02 | Similarly for states, if I open that up,
we see we have a two-digit abbreviation
| | 02:07 | for the states, and then
the text that represents.
| | 02:09 | I am going to go ahead and close that.
| | 02:11 | Now, we can see the relationships
between all of the tables in our database by
| | 02:14 | going up to Database Tools, and then
clicking on Relationships, and this will
| | 02:18 | open up the Relationships grid, where
we can see all of our tables laid out and
| | 02:21 | how they relate to one another.
| | 02:23 | So we can take a look at the Orders
table here and moving to the left we can
| | 02:27 | see that the order is linked
to a product that was ordered.
| | 02:30 | The products are linked to the OilID,
and the Size, and also the Grade.
| | 02:35 | Moving right from the Orders table, we
see the customer and the employees that
| | 02:39 | are associated with that order, and we
also have a lookup table for states that
| | 02:42 | feeds information into both
our customers and our employees.
| | 02:44 | So let me go ahead and close our
Relationships window and we are going to take
| | 02:48 | a look at how we can get some more
information out of our Employees table now.
| | 02:51 | So I am going to open up our
Employees table again and if you're used to
| | 02:55 | working in Excel, you might be familiar with
some of the filtering options that are in Excel.
| | 02:58 | For instance I can click on Illinois
here, IL, and in Access if I right-click
| | 03:03 | on that and then say Equals 'IL', it
will filter down to all of my employees
| | 03:07 | that live in the state of Illinois, and
you can see on the very bottom here we
| | 03:10 | have got four employees.
| | 03:11 | If I now left-click on that icon, you
will see that we have got a list of all
| | 03:15 | the states that are represented in our
database for the Employees table and you
| | 03:18 | will see that Illinois is currently selected.
| | 03:20 | I can turn on another state, for
instance Kansas, KS, and if I go ahead and
| | 03:24 | say OK to that, it will show me all the
employees that either live in Illinois or Kansas.
| | 03:28 | left-clicking again, I can scroll
through the list and I will turn off
| | 03:32 | Illinois and now I am just down to
Kansas, and you can see that I have got
| | 03:36 | total of three employees that
live in the state of Kansas.
| | 03:39 | Going through the Filter menu can be a
little bit tedious and you see that if I
| | 03:42 | go ahead and close our table now with
the filter in place, it's going to ask me
| | 03:45 | if I want to save the table.
| | 03:46 | And I can go ahead and say Yes.
| | 03:48 | Now, if I go ahead and open up the
Employees table again, you will notice that
| | 03:50 | our filter is no longer applied.
| | 03:52 | In the Sort & Filter options on our
Ribbon here, we have got a button here
| | 03:56 | called Toggle Filter.
| | 03:57 | You will notice that if I press that
to toggle it back on, it returns to the
| | 04:00 | state where Kansas is selected.
| | 04:01 | But it no longer gives us any
information about state of Illinois, and you can't
| | 04:05 | go back-and-forth. You can
only save one filter at a time.
| | 04:08 | The better way to do this
is to go through a query.
| | 04:10 | So let me go ahead and show you one of
our queries that we've got set up right now.
| | 04:14 | Under queries, I have got a qry_
EmployeesAZ and if I double-click on that,
| | 04:19 | you will see that it gives me just the
information for the employees live in Arizona.
| | 04:22 | I have also got one for New York.
| | 04:24 | I can go ahead and save as
many of these queries as I want.
| | 04:26 | It will give us information about
just the state that we are interested in.
| | 04:29 | Now, every time you run a query,
Access creates what's called a record set and
| | 04:33 | basically what a record set is,
is a sub-selection of your main table.
| | 04:37 | So every time I run this Employees
New York query, Access goes back to our
| | 04:40 | Employees table, grabs all of the
employees that live in New York, for instance
| | 04:44 | let me remove this filter and switch
to New York, and you'll see that we have
| | 04:50 | our three employees that live in New
York, which are the same three employees
| | 04:53 | that's being returned by this query.
| | 04:54 | Every time you run a query, Access
goes back to the Employees table and pulls
| | 04:58 | the most current information.
| | 04:59 | Now, in our database, we also have a
form called EmployeeLookup and this is
| | 05:02 | a little more advanced way of running a query
and we will get to this later in this course.
| | 05:06 | Basically if we were to run this form,
we could select from a list of states,
| | 05:09 | and that will provide us all of the
available options instead of having a single
| | 05:12 | query listed out for Arizona,
New York, and every state possible.
| | 05:15 | So you don't need to have 50
queries to do a single task.
| | 05:19 | So while at their most basic level
a query is simply a sub-selection of a table --
| | 05:22 | we can get some of that information
from running a filter on our table --
| | 05:25 | a query is definitely a
much more robust way to go.
| | 05:27 | We can save our queries out, and
we can return to them at anytime.
| | 05:31 | As we will see throughout the course,
queries can also do some more complex
| | 05:34 | tasks that you can't simply do with a filter.
| | Collapse this transcript |
| Naming conventions and best practices| 00:00 | Now the user interface in Access gives
us a little bit of a clue as to what type
| | 00:04 | of object we are dealing with.
| | 00:05 | We have got an icon here that indicates
a query, we have also got an icon here
| | 00:08 | that indicates a table.
| | 00:09 | So let me go ahead and open up a table.
| | 00:10 | We have got a DirectCustomers table.
| | 00:12 | I am also going to open
up our EmployeesAZ query.
| | 00:14 | You will see that the structure of
their query is almost identical to the
| | 00:17 | structure of the table.
| | 00:18 | Without the three letter code here I'd
be hard-pressed to tell that this was a
| | 00:21 | query and not a table.
| | 00:22 | Now, we do have the interface here.
| | 00:24 | It shows us a little icon and I know
that this is the icon for a query and this
| | 00:27 | one is icon for a table.
| | 00:28 | But there are a lot of areas in Access
where Access will lump all of our queries
| | 00:32 | and tables together, and it won't give
us any icons, and it won't give us any
| | 00:35 | indication of whether we are
actually looking at a query or a table.
| | 00:37 | For instance if I go up to our
Database Tools and then I click on our
| | 00:41 | Relationships window, we will see
that Access puts all of our tables in the
| | 00:44 | relationship, but it doesn't show us
any icon indicating that these are tables.
| | 00:48 | If I click on Show Table, and then
select Query, I can add a query to our
| | 00:53 | relationships, and again it doesn't show
us any icon to indicate that it's a query.
| | 00:57 | So without the tag saying that this is
a query and that this is a table, I'd be
| | 01:01 | hard-pressed to tell the
difference between the two.
| | 01:03 | So let's take a look at some of the tags
that we will be using throughout the course.
| | 01:06 | We will be using the tag tbl to
indicate a table, we will be using the tag tlkp
| | 01:10 | to indicate a Lookup Table which is
basically a simple table that just provides
| | 01:14 | some additional information.
| | 01:15 | We will be using qry, and this is a big
one, for queries, rpt for reports, and
| | 01:21 | frm for Forms, mcr for Macros and we
can also use these codes to indicate other
| | 01:27 | objects within our database.
| | 01:28 | for instance cbo is a Combo Box
and cmd stands for Command button.
| | 01:32 | Now, let's go back into Access
and see how those fits together.
| | 01:35 | In the Navigation pane we can see how
using these three letter codes will help
| | 01:38 | us keep our database structured and organized.
| | 01:40 | Now Access is perfectly happy letting
you name your objects whatever you'd like.
| | 01:43 | You can use long file names, spaces,
capital letters whatever you want.
| | 01:47 | Access will take care of all the
organization behind the scenes.
| | 01:50 | But as we move deeper into queries,
using these three letter codes will help us
| | 01:53 | be able to read our query, and
understand how they're functioning.
| | 01:56 | If you think that using this type of
naming convention is a little bit complex
| | 01:59 | to the type of database that you'll be
working with, like I said, feel free to
| | 02:01 | break all of these rules.
| | 02:02 | You can name your objects whatever
you like and Access will work just fine.
| | 02:05 | If however there is a slimmest of
possibilities that your project will grow
| | 02:08 | beyond a simple personal endeavor,
then I would encourage you to consider
| | 02:12 | putting some of these practices into place.
| | 02:14 | Being consistent and deliberate in
your methods will only help you out down
| | 02:17 | the road.
| | Collapse this transcript |
| Using the Query Wizard| 00:00 | As with many of the tables and objects
that you'll create within Access, one of
| | 00:03 | the easiest way is to get started is
to use one of the built-in wizards.
| | 00:06 | We could find the Query Wizard within
the Create tab, so I'll move up to the
| | 00:09 | top and then on the far right
side we've got our query options.
| | 00:12 | We've got Query Wizard and Query Design.
| | 00:14 | Let's go ahead and open up the Query
Wizard, so I'll click on the button there
| | 00:17 | and we'll see that we have four options.
| | 00:18 | We've got a Simple Query,
Crosstab Query, Find Duplicates Query and
| | 00:22 | Find Unmatched Query.
| | 00:23 | We'll talk about these last three in
a future movie, but for now we are going
| | 00:26 | to select the Simple Query Wizard.
| | 00:28 | So, go ahead and say OK.
| | 00:30 | The next window that opens up gives
us the ability to build our query.
| | 00:32 | We've got a drop-down list that shows all of
the queries and tables within our database.
| | 00:36 | Right now, I am going to select our
customers, so tbl_DirectCustomers, and in the
| | 00:41 | lower two panes we've got the Available
Fields, which is all the fields from the
| | 00:45 | DirectCustomers table, and then we've
got the Selected Fields that will be
| | 00:47 | actually used in our query.
| | 00:48 | I will take the FirstName, select it,
and using the single arrow, we are going
| | 00:53 | to move that to the right
to move it to Selected Fields.
| | 00:55 | I can do the same thing with the
LastName, move that over, and we are also going
| | 00:59 | to pull the Email address over.
| | 01:00 | Now, I've got a couple of other buttons here.
| | 01:02 | If I wanted to choose all of our fields,
I can click the double arrow over and
| | 01:06 | that will move everything over.
| | 01:07 | If I want to move everything back,
I can use the double arrow back or I can
| | 01:10 | move them individually.
| | 01:12 | So I can move them one at a time
over, or I can move them all back.
| | 01:15 | When you're selecting a field, you can
also double-click on it as a shortcut,
| | 01:18 | instead of using this button to move it.
| | 01:19 | So, I'll go ahead and double-click on
FirstName, double-click on LastName, and
| | 01:22 | double-click on Email.
| | 01:24 | Let's go ahead and say Next and Access
brings up the option to save our query.
| | 01:28 | We are going to go ahead and name this
one CustomerEmail, so using our three
| | 01:32 | letter tags, I am going
to say qry_CustomerEmail.
| | 01:36 | We had two additional options here, to
Open the query to view information, or we
| | 01:40 | can Modify the query design.
| | 01:42 | We'll take a look at the query design
here in the next movie, so for now I'll go
| | 01:44 | ahead and just open it up to
view the details that are pulled.
| | 01:46 | We'll go ahead and say Finish, and
we'll see that our query if pulling
| | 01:49 | information from our Customers table.
We've got a FirstName, the LastName and
| | 01:53 | the email address for each customer.
| | 01:54 | Let's go ahead and close this query
and we'll jump into the Query Wizard one
| | 01:57 | more time and we'll take a
look at some additional options.
| | 02:00 | So Query Wizard, we are going to choose
the simple query again, go ahead and say OK.
| | 02:05 | This time I am going to choose our
Customers table and we'll grab the
| | 02:08 | FirstName and the LastName one more time, and
this time I am going to choose the second table.
| | 02:12 | So, we are going to pull information
from two different tables. We are going to
| | 02:14 | choose the tbl_Products table, and
we are going to choose the price.
| | 02:17 | I am going to go ahead and say Next, and
you'll notice that we get an additional
| | 02:21 | screen this time that we didn't the last time.
| | 02:23 | The first query that we created, the
Customer Email, only had names and email addresses.
| | 02:27 | It didn't have any numerical data.
| | 02:28 | This time we are including the
price, which is a numerical field, and
| | 02:31 | Access gives us some additional
options that we can use when building a
| | 02:33 | query with numerical data.
| | 02:35 | We could choose our summary and then take a
look at some of the options that are available.
| | 02:39 | From our Price field, we can choose
some of the summary values that we want to
| | 02:41 | have Access calculate.
| | 02:42 | We can choose the Sum, which is the
grand total, the Average, the Minimum, the
| | 02:46 | Maximum, and we can also count up the
number of records that are returned.
| | 02:50 | Now, this will be easier to understand
what's going on in the results, so let's
| | 02:53 | go ahead and just select
them all for now and say OK.
| | 02:55 | Go ahead and say Next and this
query we are going to go ahead and name
| | 03:00 | OrderHistory, so once again qry_OrderHistory.
| | 03:05 | One more time we are going to open up
to view the information and say Finish.
| | 03:09 | Now, I am going to go ahead and expand
these cells open a little bit so you can
| | 03:12 | see the numbers that have returned.
| | 03:14 | To make things a little bit more clear,
we are going to go ahead and sort on
| | 03:17 | the summary of price.
| | 03:18 | So, clicking on that little drop-down
arrow, we are going to choose Largest to
| | 03:21 | Smallest, and this will sort all
of our summary prices to the top.
| | 03:24 | Now, we can see Sebastian Rich is the
customer that spent the most money with
| | 03:27 | the company. He has floated up to the top.
| | 03:29 | The total amount of money that
Sebastian has spent with the company is $260.24.
| | 03:33 | We can see that the average order
that he places is approximately $43, the
| | 03:38 | smallest order he has replaced with
$16.33, and the largest order he ever
| | 03:42 | replaced was $80.83.
| | 03:44 | We can also see that Sebastian has
placed the total of six orders with the Two
| | 03:47 | Trees Olive Oil Company.
| | 03:48 | So, the Query Wizard provides a quick and
easy way to get started writing queries.
| | 03:51 | You can combine fields from multiple
tables and you can apply some basic
| | 03:55 | aggregate calculations as well.
| | 03:56 | The Simple Query Wizard walks you
through the build process step-by-step, and it
| | 03:59 | can provide a fairly solid base
from which you can build upon later.
| | 04:03 | We are going to take a look at the
Design Environment in the next movie where we
| | 04:05 | can build upon this query.
| | Collapse this transcript |
| Exploring the Query By Example (QBE) interface| 00:00 | All of the queries in Access are stored in
a common programming language called SQL.
| | 00:04 | SQL stands for Structured Query Language,
and it could be a pretty powerful way
| | 00:07 | to create queries, but a
little bit daunting at first.
| | 00:10 | So, Access actually provides a second
way that we can create our queries called
| | 00:13 | the QBE grid or the Query By Example.
| | 00:16 | I am going to go ahead and open up
one of the queries that we just created
| | 00:18 | in the previous movie.
| | 00:19 | I am going to open up the qry_
CustomerEmail query, and if I double-click on it
| | 00:22 | you'll see that the query runs again,
and it's pulling the FirstName, the
| | 00:25 | LastName, and the Email address
out of our DirectCustomers table.
| | 00:28 | Now, there are a couple of ways that
we can switch to the QBE environment.
| | 00:32 | At the very top on the Home tab we've
have got this icon here that's a triangle
| | 00:35 | and a pencil and a ruler and this
will switch us to our Design View.
| | 00:38 | I can also select on the drop-down menu
below that and select from a couple of
| | 00:41 | additional views that we have.
| | 00:43 | So, we've got the Datasheet View, which
we are looking at right now. I also have
| | 00:46 | PivotTable, PivotChart, SQL View if you
want to take a look at the text behind
| | 00:49 | the scenes, and we have our Design View as well.
| | 00:51 | There is another option that we have
when we want to switch between Datasheet
| | 00:54 | View and Design View, and we can find
that in the lower right-hand corner.
| | 00:57 | We've got a couple of little icons
down here and these are really tiny.
| | 01:00 | We've got the Datasheet View, the PivotTable
View, the PivotChart View, SQL View, and
| | 01:05 | finally in the lower right corner
we've got a small icon for Design View.
| | 01:09 | I use these all the time, because they
are really convenient, they are always
| | 01:11 | there, and sometimes it beats moving up
to this menu, opening the drop-down menu,
| | 01:15 | and then selecting the view that you want.
| | 01:16 | But right now we are going to go ahead
and just select Design View, so you can
| | 01:19 | use whatever method you like.
| | 01:20 | We'll take our query that we opened up
and we'll move it into our Design View.
| | 01:23 | You'll notice that when we switch into
Design View, Access opens up a new tab
| | 01:26 | called Design and we've got some
additional options here that are specific to
| | 01:29 | the query design environment.
| | 01:31 | The Results section here has two options.
We change our View again, and we can also run it.
| | 01:35 | Now, for most of the queries that we
are going to be creating in this course,
| | 01:37 | running is actually identical
to viewing the Datasheet View.
| | 01:40 | We'll talk about why that's different when
we run action queries later on in the course.
| | 01:44 | The second section is Query Type, and
you'll recognize some of these from the wizard.
| | 01:47 | We've got the Append, Update,
Crosstab. These are all the different
| | 01:50 | queries that we can create.
| | 01:52 | Right now, we are doing a simple
select query, so that's the option that
| | 01:54 | we've got selected.
| | 01:56 | The third section of the menu is
called Query Setup, and this is where we can
| | 01:59 | choose some of the options
that will help us build our query.
| | 02:02 | The fourth section of the menu, called
Show/Hide, has some of the options that we
| | 02:05 | can use to manipulate the grid below,
and we'll take a look at that in moment.
| | 02:08 | So, the Query Design
environment has two windows.
| | 02:10 | We've got the upper pane, it's called the Table
pane, and this will have all of the tables that we are
| | 02:13 | using to create our query.
| | 02:15 | Right now, we've got our
DirectCustomers table. The lower half of the window is
| | 02:18 | the Design Grid and down here we can
take a look at all of the tables that are
| | 02:21 | being used to create a query.
| | 02:23 | So, we can see that we are pulling the
FirstName from the DirectCustomers table.
| | 02:26 | We've got the LastName from the
DirectCustomers table and we've got the Email
| | 02:29 | from the DirectCustomers table.
| | 02:31 | We can move the tables around for organization.
| | 02:33 | We can expand them open a little bit if we
want to view all the fields within each table.
| | 02:37 | To add additional tables to our
query, we've got a couple of options.
| | 02:39 | We've got the Show Table button up here.
We can also right-click in any open
| | 02:42 | space here and choose Show Table.
| | 02:44 | That will bring open a new window
called Show Table and we could choose any
| | 02:48 | additional tables from our database
that we want to include in our query.
| | 02:50 | For instance I can include a tbl_Products.
| | 02:52 | We'll go ahead and close this out, and
I am going to expand this open so I can
| | 02:56 | see a little bit more.
| | 02:56 | To add a field from a table, down into
our query design environment, all we need
| | 03:00 | to do is double-click on it.
| | 03:01 | So for instance, if I want to include a
ProductName into our query, I would just
| | 03:04 | double-click on it and then drop it down.
| | 03:06 | There are couples of other ways we
can do it. We can click and drag.
| | 03:08 | So, for OilCode, I could click
and drag that down and drop it.
| | 03:12 | I can also select a range of fields
by using some of the standard Windows
| | 03:15 | shortcuts. So I can click on the first
one. I can Shift+Click and click on the
| | 03:19 | third one to select a range.
| | 03:21 | I could also use the Ctrl key on my
keyboard. If I press that down, I can select
| | 03:24 | individual records to add to the group.
| | 03:26 | At that point, I can go ahead and
click and drag and select all of those and
| | 03:29 | drop those down into our design grid.
| | 03:31 | So, that added a lot of fields here to
our query that I actually don't really
| | 03:34 | need, and we can get rid of
them in a couple of different ways.
| | 03:36 | I don't want to actually include a
ProductName into our query, so I can click
| | 03:40 | on this gray bar at the very top of the
field name, and the whole field will select.
| | 03:43 | Once it's selected I can right-click
on it and say Cut or I can say Delete
| | 03:47 | Columns from the Query Setup menu.
| | 03:48 | So, let me go ahead and delete this column.
| | 03:50 | I can continue deleting columns
and that will delete out of my query.
| | 03:53 | Another way to delete some of your
fields is just click on the gray bar above
| | 03:56 | the field name, and again, you
can Shift+Click to select a range.
| | 04:00 | Once it's done you can right-click and say
Cut and we'll get rid of all those as well.
| | 04:04 | Finally, to remove a table from our
query designer, we can right-click on the
| | 04:07 | table header and say Remove Table,
and this goes back to where we started.
| | 04:11 | So, let's go ahead and take a moment
and we are going to recreate this query
| | 04:14 | from scratch using some of the
methods we just took a look at.
| | 04:16 | So, I am going to go ahead and close
this and Access is going to ask if we
| | 04:20 | want to save changes, and I don't necessarily
need to, so I am going to go ahead and say No.
| | 04:24 | This time we are going to go to the Create
tab and we are going to choose Query Design.
| | 04:29 | Access opens up a new Query Design
environment and it automatically opens up the
| | 04:32 | Show Table window where we can
select the tables that we want to use.
| | 04:34 | So, we are going to recreate the
customer email query from scratch.
| | 04:38 | So I am going to pull in the
DirectCustomers table just by double-clicking on it.
| | 04:41 | I am going to go ahead and say Close,
because I am done with this window and I
| | 04:45 | am going to expand this out a little bit.
| | 04:46 | Now, if you remember, the customers
query includes the FirstName, the LastName,
| | 04:50 | and the Email address.
| | 04:52 | I find it easiest just to go through
and double-click on everything, so I
| | 04:54 | am just going to double click on
FirstName, double click on LastName, and
| | 04:57 | double click on Email, and you'll
see that they all get dropped down into
| | 05:00 | our query environment.
| | 05:01 | If I go ahead and say Run, I'll switch to
Datasheet View, and we'll see that we get the
| | 05:05 | results from the query that we built
using the wizard in the last movie.
| | 05:08 | We've got the FirstName, the LastName
and the Email address pulled from the
| | 05:12 | tbl_DirectCustomers table.
| | 05:13 | So, we just created our query from
scratch using the Design environment and this
| | 05:16 | query is identical to the one
that we created using the Wizard.
| | 05:19 | In the next chapter we are going to
take a look at how we can use the Design
| | 05:22 | environment to move beyond the options
that are available within the Wizard.
| | Collapse this transcript |
|
|
2. Creating Simple Select QueriesDefining a single criterion| 00:00 | One of the most common uses of queries
is to filter data within your database
| | 00:04 | down to a specific subset of
information that we are looking for.
| | 00:07 | We saw a little bit of this earlier in
the movie where we talked about how we
| | 00:09 | can filter out our data tables.
| | 00:11 | Let's take a look at how we can save
some of our queries to do the same thing.
| | 00:14 | Right now, I am going to open up our
Employees query and I am going to open that
| | 00:17 | straight into Design View by right-
clicking on it and then saying Design View.
| | 00:20 | This will open up that query in our design grid.
| | 00:23 | I am going to maximize the window to
give us a little more breathing room and
| | 00:26 | we'll move the grid down.
| | 00:27 | We can see that this query is pulling
information from our Employees table and
| | 00:32 | we are gathering the EmpID, the
FirstName, the Middle initial, the LastName and
| | 00:37 | the State from our Employees table.
| | 00:39 | If I run it, we will notice that the
query is pulling all of the employees that
| | 00:42 | we have in our entire table.
| | 00:43 | So, we've got a total of 200 employees
and they are all represented in this query.
| | 00:47 | Let's go ahead and switch back to our
Design View and start working with some
| | 00:50 | criteria to filter things down.
| | 00:51 | You'll notice in the query grid we
have a line called Criteria and anything
| | 00:55 | that we type in here is going to match
criteria based off of the record that we're in.
| | 00:59 | So, for instance under FirstName I am
going to write "Kevin" and I am going to
| | 01:04 | press Enter and now I am going
to go ahead and run the query.
| | 01:08 | When I run the query you'll notice
that Access is returning only the records
| | 01:11 | where the first name matches Kevin
and I've got a total of two employees
| | 01:14 | with the name of Kevin.
| | 01:15 | Let's go ahead and switch
back to our Design View.
| | 01:18 | Let me go ahead and delete this.
| | 01:20 | We can do the same thing with States,
so instead of a name of Kevin I am going
| | 01:24 | to write NY for New York.
| | 01:25 | This time, I didn't write any
quotation marks, so you'll notice that when I
| | 01:29 | press Enter, Access
automatically enters those in for me.
| | 01:31 | We'll go ahead and run this and we'll
notice we've got three employees that live
| | 01:36 | in the state of New York.
| | 01:37 | Let's go ahead and go back to Design View.
| | 01:39 | So, let's see what happens when I do a
sort based off of the FirstName and the State.
| | 01:43 | If I write the word Kevin in here again
and tab away from it so that Access puts
| | 01:47 | in the quotation marks, now we've got
as a query that's asking for all the
| | 01:51 | employees whose first name is Kevin
and that live in the state of New York.
| | 01:54 | I go ahead and run this, you'll
notice that I actually don't get any
| | 01:57 | employees here because we don't any employees
named Kevin that live in the state of New York.
| | 02:00 | Let's go back into Design View.
| | 02:03 | I am going to go ahead and remove the
criteria for New York and we are just
| | 02:06 | going to focus on the FirstName for a moment.
| | 02:09 | Instead of writing out the FirstName
and making an exact match we can make use
| | 02:12 | of what Access calls wild cards.
| | 02:14 | If we use the asterisk key, which is
Shift+8 on your keyboard we can create a
| | 02:18 | query that's asking for any employee
whose first name starts with a K. If I go
| | 02:26 | ahead and click run, we'll notice that
we have a total of 16 employees whose
| | 02:30 | name starts with K. Back to Design View.
| | 02:31 | Now the asterisk character matches any
number of characters and any characters,
| | 02:34 | which is why we can use the asterisk
key to define any number of any character,
| | 02:35 | which is why we can say K* and that
will match any employee whose name starts
| | 02:36 | with a K and has any
number of characters afterwards.
| | 02:37 | We can also put an asterisk at the beginning.
| | 02:38 | Now, we are asking for any employee
that has a K anywhere in their first name.
| | 02:40 | So, if I go ahead run this we'll find
Jackson, who has a K in the middle of his
| | 02:43 | name, Derek who has a K at the end of
his name, and Kevin that has a K at the
| | 02:46 | beginning of his name.
| | 02:48 | Let's go back to our Design View and we'll
clean this out, get back to where we started.
| | 02:52 | There are a couple of other things that
we can do making use of our Criteria line.
| | 02:57 | Now, we've already seen that if we
include a criterion on two different records,
| | 03:00 | for instance, we had Kevin as a first
name and New York as a state, Access
| | 03:04 | treats that as an And statement.
| | 03:06 | So, basically it's looking through a
database and finding all of the employees
| | 03:08 | who both have a first name of Kevin
and live in the state of New York.
| | 03:12 | We can also use this Or line down here and
this is a different way of writing a statement.
| | 03:18 | So, we can write a query that
looks at a couple of different states.
| | 03:21 | For example, I can say ME for
Maine or NH for New Hampshire.
| | 03:26 | Now, what Access is going to do is
return all the employees that either live in
| | 03:29 | Maine or New Hampshire or if I run that
we'll see that we have a total of six.
| | 03:34 | Let's go back to our Design View.
| | 03:36 | There is one other way we can write this.
| | 03:39 | Instead of writing it on multiple lines,
we can include everything on one line.
| | 03:42 | So, I am going to get rid of New
Hampshire from the second line and I am going
| | 03:46 | to expand our window out so we
have a little bit of more room.
| | 03:48 | Now, let's say that we are interested
in finding all of the employees that live
| | 03:51 | in our New England states.
| | 03:53 | There are six New England
states and that includes Maine.
| | 03:55 | Now I am going to write the word Or.
| | 03:58 | the next New England state is New
Hampshire Or Vermont Or Connecticut Or Rhode
| | 04:04 | Island Or Massachusetts, MA.
| | 04:08 | Now, when I press Return Access fills
in all of our quotation marks, so it has
| | 04:11 | the order of operations correct.
| | 04:13 | So, now we can see that we are
selecting Maine or New Hampshire Or Vermont Or
| | 04:17 | Connecticut Or Rhode Island Or Massachusetts.
| | 04:20 | Now, interestingly we have a state
called Oregon and the abbreviation for
| | 04:24 | Oregon is actually OR.
| | 04:25 | So what happens when we write Or OR?
| | 04:27 | If I press Enter because of the
ordering Access knows that we are looking for
| | 04:32 | Massachusetts Or Oregon here.
| | 04:35 | So, because of the order it actually knows
this is the value and not another Or statement.
| | 04:39 | I am actually not interested in Oregon.
| | 04:41 | I just wanted to point that out really quick.
| | 04:42 | So, I am going to delete that out and
now when I run our query, we'll find all
| | 04:47 | of the employees that
live in New England states.
| | 04:48 | Okay, so let's go back to our Design View.
| | 04:53 | So, Or is actually just one of three
logical operators that we can use in our
| | 04:56 | databases, the other two being And and Not.
| | 04:59 | So I can write a statement that says
for instance Not NY, and if I run that
| | 05:05 | we'll get all the employees
who do not live in New York.
| | 05:07 | Let's go ahead and change
this back to our Datasheet View.
| | 05:11 | The third logical operator is And and
it's a little bit tricky to use the word
| | 05:14 | And when we are dealing with text based data.
| | 05:16 | For instance, if I wrote NY and NH
for New York and New Hampshire and I run
| | 05:22 | this, you'll notice that we
don't get any records back.
| | 05:25 | Let's go back into Design View
and figure out what happened here.
| | 05:28 | So, right now we are looking for all of the
employees who live in New York and New Hampshire.
| | 05:32 | Now, Access is pretty literal in
its understanding of the word And.
| | 05:36 | It makes sense that new employee
actually lives in both New York and New
| | 05:39 | Hampshire simultaneously, so that's
why it will not returning any records.
| | 05:43 | Typically, you are not going to be
using the word And on single Criteria line.
| | 05:47 | We saw earlier that when you put
criteria for two different records, for
| | 05:51 | instance we have that first name of
Kevin and the state of New York, Access
| | 05:54 | treats that as an And statement.
| | 05:55 | But typically when you are dealing with
text you are not going to have the word
| | 05:58 | And on a single line.
| | 05:59 | | | 06:01 | in the next movie.
| | 06:11 | There are some
exceptions to that and we'll take a look at that
| | Collapse this transcript |
| Comparison operators| 00:01 | When the fields in your data table are
of a numerical data type, Access provides
| | 00:04 | some additional operators that we can
use when we're constructing our queries.
| | 00:08 | One type is called a comparison
operator and they sort and qualify data based
| | 00:12 | upon the numerical value of each record.
| | 00:14 | Comparison operators don't simply
match an exact value or pattern like we saw
| | 00:18 | with a like AND, OR, and NOT
operators in the previous movie.
| | 00:23 | Comparison operators return records
that match the relationship to a value, for
| | 00:26 | example whether it's below or
above that value that we specify.
| | 00:30 | So take a look at some of the comparison
operators that we can use in our queries.
| | 00:34 | The first one is less than and we can
find that by pressing Shift+Comma on our keyboard.
| | 00:38 | And the type of questions that the
< character will help us answer is
| | 00:42 | something like all orders below $100.
| | 00:45 | We also have a greater than
character which is Shift+Period.
| | 00:48 | And this will help us find all
orders above the $100 for example.
| | 00:51 | Then equal to and that will help us
find records where the employee number is
| | 00:55 | equal to 53, or the
department is equal to marketing.
| | 00:59 | We also have less than or equal to.
| | 01:01 | So for instance if we're looking for a
part-time employee situation, we have
| | 01:05 | worked 32 hours or less. Or if we are
looking for an overtime situation, we can
| | 01:10 | find all employees that work 40 hours
or more using greater than or equal to.
| | 01:13 | We also have a not equal to option
which is basically the less than and greater
| | 01:18 | than characters put together.
| | 01:19 | And this will help us to find all
records where employee is not ID 53 or all
| | 01:24 | departments except Marketing.
| | 01:25 | So let's switch into Access and take a
look at these comparison operators in action.
| | 01:30 | So I've got a query called Products
and I'm going to right-click on that and
| | 01:35 | say Design View to open it
directly into Design View.
| | 01:37 | We go ahead and maximize this,
so it gives me some more room here.
| | 01:40 | And we this query is pulling
information from our Products table and from it
| | 01:45 | we're getting our ProductName, the size
in Ounces, the wholesale Cost, the Price
| | 01:50 | to our customers, and then the
MarkupDollars, and the MarkupPercent.
| | 01:54 | Now, we can use our comparison
operator in the Criteria section, just like
| | 01:57 | we were doing before.
| | 01:58 | For instance, I can say <16 for
Ounces to find all the products that are
| | 02:04 | less than 16 ounces.
| | 02:05 | And if I run it, we'll see all the
products that are less than 16 ounces.
| | 02:09 | Let's go ahead and switch back to Design View.
| | 02:11 | Now, we can use the comparison
operators in the same way that we're using
| | 02:15 | criteria in the last movie, where we can
select couple of different things at once.
| | 02:19 | For instance, we can find all the
products whose size is <16 ounces and the
| | 02:24 | Markup is >5 dollars.
| | 02:26 | I go ahead and run that we'll see that
there's only 8 products that we carry who
| | 02:31 | is less than 16 ounces in size,
but their markup is greater than $5.
| | 02:34 | Let's go ahead and witch
back to Design View again.
| | 02:38 | By modifying our criteria slightly, I am
going to change this to <=16 and this will
| | 02:44 | find all the products who's ounces are
16 or less and the markup is >5 dollar.
| | 02:48 | Also we have a few more products.
Now we have some of the 16 ounce sized
| | 02:51 | products that are mixed in.
| | 02:52 | Let's go ahead and switch this back
and I'm going to get rid of those Markup
| | 02:57 | criteria, and I'm going to
get rid of the Ounces criteria.
| | 03:01 | Now, we also have two other
comparison operators. We have equals, which is
| | 03:05 | basically the same thing as a Like
operator that we saw in the previous movie.
| | 03:08 | So I can say =16 and then it'll show us
all of our products where the size is 16.
| | 03:15 | I can also say <>16 and that'll show
us all the products that are not 60.
| | 03:20 | So everything else.
| | 03:22 | The comparison operators are another
tool that we can use when filtering down
| | 03:25 | our data to specific records that
we're interested in working with.
| | 03:29 | They're especially useful when you
want to find records that meet or exceed a
| | 03:32 | certain criteria within your database.
| | 03:34 | By using comparison operators in
your queries, you'll be able to quickly
| | 03:37 | and efficiently locate the records
with the most relevancy to your specific questions.
| | Collapse this transcript |
| Defining the column headers with aliases| 00:00 | When we're creating our queries using
calculated or filtered records it's always
| | 00:04 | a great idea to properly document your
workflow, so that your results will be
| | 00:08 | more easily understood by other users.
| | 00:10 | One way to do this is to rename the
field headers in your queries to give them
| | 00:13 | more meaningful and accurate names.
| | 00:15 | We can take a look at our
Products query that we've already built.
| | 00:18 | And we can see that the field names that
we have here in our query come directly
| | 00:21 | from the Products table.
| | 00:22 | So we've here ProductName,
Ounces, Cost, Price,
| | 00:25 | MarkupDollars, and MarkupPrercent.
| | 00:27 | Now, you can rename these headers so
that they'll be more meaningful and provide
| | 00:31 | a little more information about
the query that we were returning.
| | 00:33 | So let's go ahead and switch into our
Design View and right now the field header
| | 00:38 | for product name is
ProductName, all pushed it together.
| | 00:40 | I can go ahead and give this a new
title by clicking at the very beginning of
| | 00:43 | the field and typing in where we
want the new title of the field to be.
| | 00:47 | So for this I'm just going
to simply name it Product.
| | 00:49 | After the Product, I'm going to put a
colon so that Access knows that everything
| | 00:53 | before the colon is the new header and
everything after the colon is where the
| | 00:56 | data is actually coming from.
| | 00:57 | Now, if I re-run the query, we'll see
that the field header has now changed
| | 01:00 | to the word Product.
| | 01:01 | I can go back into Design View and
do the same for the rest of these.
| | 01:05 | So instead of Ounces, I'm going to put
the word Size and then in parenthesis
| | 01:09 | I'm going to put the
units. So this is fluid ounces, Fl Oz.
| | 01:14 | Now, you can't use periods in here, as
you normally would with an abbreviation,
| | 01:18 | because Access won't allow you to
use the period in a header name.
| | 01:21 | So I'm just going to go ahead and
abbreviate fluid ounces and then add a colon
| | 01:24 | to denote that that's the new field title.
| | 01:26 | For Cost, this is actually the
wholesale cost or the cost of the company.
| | 01:30 | So I'm going to designate that Wholesale Cost.
| | 01:32 | That will be a little more specific.
Again finishing with the colon.
| | 01:36 | The Price is our customer's price, so we're
going to name that Retail Price with a colon.
| | 01:41 | MarkupDollars and MarkupPercent,
we're going to change that a little bit.
| | 01:45 | We're going to write it Markup, and then
inparenthesis we're going to denote the unit,
| | 01:48 | so this one is dollars,
again, finishing with a colon.
| | 01:53 | And for MarkupPercent, we're going to
do the same thing, Markup and then in
| | 01:56 | parenthesis is the unit, this in percentage.
| | 02:00 | Finally, we'll put a colon there to denote
that that's the new alias for that field.
| | 02:04 | And now when we run our query again,
we'll notice that we have all new
| | 02:07 | headers up here at the top.
| | 02:08 | They're a little bit more specific
and accurate based off of the results
| | 02:11 | that we're returning.
| | 02:12 | And we can see that it's a lot
more clean and easier to understand.
| | 02:15 | So by clearly identifying the fields
returned in your queries, you'll aid other
| | 02:19 | users that will be working with your database.
| | 02:21 | This also helps communicate the
meaning of a field as you build reports and
| | 02:24 | other forms off of your query results.
| | 02:26 | Adding a custom field header in the
form of an alias is a simple and effective
| | 02:29 | way to document the meaning of a
particular column in your queries.
| | 02:32 | And we'll see this will become more
important as we move into some more complex
| | 02:35 | calculations later on in the course.
| | Collapse this transcript |
| Exploring the field properties| 00:01 | In the previous movie we took a look at
how we can apply aliases to our column
| | 00:04 | headers to be a little bit more
specific about the types of data that we're
| | 00:07 | returning in our queries.
| | 00:08 | Now, we can go into our qry_Products
one more time and we can see the saved
| | 00:11 | aliases that we created in the last movie.
| | 00:14 | Now, I'm taking a look at our table here
that's being returned by this query and
| | 00:17 | I'm noticing a few other
areas that we can clean this up.
| | 00:19 | Let me go ahead and maximize this.
| | 00:21 | And let's take a look at retail price.
| | 00:23 | Now, I know this is supposed to
represent a dollar value, but our decimals
| | 00:26 | are all over the place.
| | 00:27 | We've got 2 decimals here, we've got no
decimals here, and we've even got values
| | 00:31 | that have only a single decimal.
| | 00:33 | So I want to clean that up a little bit.
| | 00:34 | Same thing with Markup $, and
Markup % actually has a different problem here.
| | 00:38 | Right now we're saying this as a percent,
but it's written as a decimal fraction.
| | 00:42 | So for instance, we've got our
Wholesale Cost of $10, our Retail Price is $15,
| | 00:47 | which represents 150% markup, but
we're multiplying that by 1.5, so this the
| | 00:51 | decimal representation of that percent.
| | 00:53 | So I want to actually change that so
that it matches what we're saying it is up
| | 00:56 | here at the column header.
| | 00:57 | And we can do all of this using
the Property Sheet of our query.
| | 01:00 | So let's go ahead and take a look at that.
| | 01:02 | I'm going to go ahead and switch
into Design View, and we can get to our
| | 01:05 | Property Sheet in a couple of different ways.
| | 01:07 | I've got a button up here on our ribbon
and this is actually a toggle, so I can
| | 01:10 | click it on to turn our
Property Sheet and turn it off.
| | 01:13 | I also have a couple of shortcut keys on
our keyboard and we can press Alt+Enter
| | 01:17 | and that will toggle it on and off.
| | 01:18 | I can also press the F4 key and that
will also turn on our Property Sheet off and on.
| | 01:25 | Finally, we can go ahead and right-
click on the field and from the drop-down
| | 01:28 | menu select Properties from the list
and that will open up the Property Sheet.
| | 01:32 | Now, we have to pay attention to
where we've clicked in here, because the
| | 01:34 | Property Sheet simply tells us that
we're looking at field properties.
| | 01:37 | And right now I know that I'm
adjusting the field properties for this field
| | 01:40 | because it's the last one that I clicked in.
| | 01:41 | If I click in a different field, the
Property Sheet still says Field Properties,
| | 01:45 | so it doesn't indicate which
field that we're looking at.
| | 01:47 | So you have to actually pay attention
to where you're clicking in your design
| | 01:49 | grid to know which properties you're adjusting.
| | 01:52 | So let's go ahead and do our Wholesale
Cost and I'm going to actually change the
| | 01:54 | Format of this number to Currency.
| | 01:57 | And what this is going to do is apply a
formatting rule where we have a dollar
| | 02:01 | sign before the number and then
everything is rounded to two decimal places.
| | 02:04 | We're going to do the same thing for
Retail Price. Change to that using the
| | 02:08 | drop-down menu to Currency.
| | 02:11 | The Markup $ is also a Currency value.
| | 02:15 | And Markup % we're going
change the Format to a Percentage.
| | 02:20 | We can also adjust the number of
decimal places that we showed to increase our
| | 02:23 | level of precision that we want to indicate.
| | 02:24 | Right now I'm just going to
leave it to 2 decimal places.
| | 02:26 | Let's go ahead and re-run our queries.
| | 02:30 | And we can see that the numbers that
are returned are a lot more clean and
| | 02:32 | easy to read, so we've got a dollar sign,
we've got two decimal places for all of
| | 02:35 | our currency values,
| | 02:36 | and we also have our percentage data
that's represented as a natural percentage.
| | 02:40 | So I want to point out a little caveat
about doing this. While we're adjusting
| | 02:43 | the field properties, all that it's
actually doing is adjusting how the numbers
| | 02:46 | are formatted in our query results.
| | 02:48 | It's not actually changing any
of the data from our source table.
| | 02:51 | So for instance, right now we've got
percentage values, but in our data table
| | 02:54 | these are still represented as a decimal.
| | 02:56 | If I go into our Design View and I
use the comparison operators that we saw
| | 02:59 | before, for instance I want to
find all of our percentages that are >150%,
| | 03:03 | we might think that we're going
to enter it this way, >150, but I were to
| | 03:08 | run that query, we'll notice
that we don't get any results.
| | 03:10 | And that's because were still using
the comparison against the decimal value
| | 03:14 | that's in the original table.
| | 03:15 | So in order to do this I would actually
need to use the decimal version,
| | 03:18 | >1.5, even though we are
formatting it as a percent.
| | 03:22 | So if I say >1.5 and then
run it, we're filtering up all that Markup percentages
| | 03:27 | that are >1.5, but then we're
formatting it as a percentage, so we'll see that
| | 03:31 | these are all greater than 150%.
| | 03:32 | Let's go back into our Design View,
and so far we talked about the properties
| | 03:37 | for our fields down here. We also have
the ability to adjust the properties for
| | 03:41 | entire query by clicking in the upper
pane, and if I click there you'll notice
| | 03:45 | that it changes to Query Properties.
| | 03:46 | We're going to go ahead and take a look
at Query Properties in the next movie.
| | Collapse this transcript |
| Understanding query level properties| 00:01 | In the last movie, we took a look at
how we can use the field properties in
| | 00:03 | our query to organize and clean up some of
the results that are returned with our query.
| | 00:08 | We also have some additional properties
that we can adjust for the query itself.
| | 00:11 | So let's go ahead and take a look
at what we have available there.
| | 00:12 | So, let me go ahead and open up our
Products query and we can see that our
| | 00:16 | numbers are cleaned up by using the
field properties from the previous movie.
| | 00:19 | We'll switch in the Design View and
this time we're going to open up the
| | 00:22 | properties for our entire query by
clicking in the upper pane of our window.
| | 00:27 | If it's not already open, we can
open up our Property Sheet in a
| | 00:29 | couple different ways.
| | 00:30 | We've got the toggle button up on the ribbon.
| | 00:32 | We can press the shortcut keys Alt+Enter,
| | 00:34 | we can press F4, or we can right-click
in the open area and select properties
| | 00:39 | from the list there.
| | 00:40 | So, let's go ahead and ensure that
we're taking a look at the query properties
| | 00:44 | by looking at the selection
type up here in the Property Sheet.
| | 00:47 | The first property for
our query is a Description.
| | 00:49 | This particular cell doesn't
have a whole lot of room for typing.
| | 00:52 | So, what I'm going to do is right-click in the
cell and choose Zoom to bring up the Zoom box.
| | 00:56 | Now this allows me to type in a useful
description for our query, so we can make
| | 00:59 | sure that we understand how it relates
to some of the other objects, tables, and
| | 01:02 | forms within our database.
| | 01:04 | So, I can type in a description, such
as This query pulls data from the form
| | 01:09 | "frm_ProductsSearch" and feeds it
into the report "rpt_ProductSearch".
| | 01:13 | So this is an example of how we can
use the description for our query to make
| | 01:17 | sure that we understand how the
query results relate to some of the other
| | 01:20 | objects within our database.
| | 01:21 | So, if we're pulling information from
a form and feeding into our report,
| | 01:24 | this is a good way that you can
make sure that we remember how those
| | 01:26 | relationships have been set up.
| | 01:28 | Now, let me point out one
thing about the Zoom box.
| | 01:30 | We have a scrolling slider here and
that seems to indicate to me that we can
| | 01:33 | keep typing a bunch of text in here.
| | 01:35 | Now I've got some dummy text saved to
my clipboard that I'm going to paste in.
| | 01:40 | We can go ahead and type in a
description that seems to be as long as we want.
| | 01:43 | Once we press OK though, we're going
to get an error message here and Access
| | 01:47 | is telling us that the string returned is
too long and the results will be truncated.
| | 01:50 | At this point we don't have any other option,
but to say OK and let Access chop it off.
| | 01:55 | So, we're going to say OK and if I go
back to view the description after it's
| | 01:58 | been edited, we'll go ahead and zoom
again, we'll see that Access chops it off
| | 02:02 | at about 255 characters.
| | 02:04 | So, don't go crazy in here. Just
get to the point to make sure that we
| | 02:07 | understand how this query can relate to
some of the other objects in our database.
| | 02:10 | So, we could go ahead and delete all
the dummy text, and just leave it with
| | 02:14 | that simple description.
| | 02:15 | I'm going to go ahead and say OK.
| | 02:16 | So, I've got a couple of other
properties that I want to take a look at.
| | 02:20 | We've got our Default View and
currently that's set to Datasheet.
| | 02:23 | If we're working with PivotTables or
PivotCharts, we can change to that setting here.
| | 02:27 | That way when we double-click on our
query in the navigation pane on the left,
| | 02:30 | it'll open up directly into those views instead.
| | 02:32 | We're just working with Data Tables,
so I'm going to leave it on Datasheet.
| | 02:36 | We've got another set of properties
here called Output All Fields and the
| | 02:39 | default value is No.
| | 02:41 | Using the drop-down menu, we see
that our options are both No and Yes.
| | 02:44 | If we change it to Yes, then Access is
going to ignore the status of our Show
| | 02:47 | boxes down here in our Query Designer.
| | 02:49 | Basically, Access allows us to include
a field and sort or filter based off of
| | 02:53 | that field, but we don't necessarily
need to show it in our query results.
| | 02:56 | So basically, we can use a field and a
criteria to filter out our results, but
| | 03:00 | we don't need to show it in the return.
| | 03:01 | If we have these checkboxes turned off,
and we set our property to Output All
| | 03:05 | Fields to Yes, then Access is going
to ignore that these are turned off and
| | 03:07 | just return them anyway.
| | 03:08 | Typically, you're going to want to use
the Show boxes to control which fields
| | 03:12 | are displayed in your query results.
| | 03:13 | So, we're going to leave our default
output fields to No, in which case Access
| | 03:17 | is going to respect the settings that
we have down here with our Show boxes.
| | 03:20 | The next property we have to take
a look at is called Top Values and
| | 03:23 | currently that's set to All.
| | 03:25 | Using the drop-down menu,
we have some options here.
| | 03:27 | We have 5, 25, 100, and then we have 5% and 25%.
| | 03:31 | If we select 5, then Access is
going to return the top five results.
| | 03:35 | It's not going to return
everything. Only the top five.
| | 03:38 | We can also type in a number that we want.
| | 03:39 | So, for instance, if I want it to
return 10% of my records, I can change that
| | 03:44 | here and I don't think it's
necessary to select one from the list. I can
| | 03:46 | type in whatever I like.
| | 03:47 | You might have noticed that when I
change this to 10% and press Enter, it's also
| | 03:52 | changing the Return up here on our ribbon.
| | 03:54 | So, these two values are actually linked.
| | 03:55 | Return is the same as Top Values here.
| | 03:57 | So, I can adjust those to return 100
records and you'll notice that the Top
| | 04:01 | Values down here change to 100.
| | 04:03 | Let me go ahead and reset this to All, so
we're getting everything in our query results.
| | 04:08 | The other property that I want to
point out is called Recordset Type and the
| | 04:11 | default value is called Dynaset.
| | 04:13 | Basically, when I run a query, Access
is returning what's called the Dynaset.
| | 04:18 | This is basically a live link between
our data in the query return and the
| | 04:22 | data in our data table.
| | 04:23 | So, if I were to make an edit here in
the query, let's write the word EDIT after
| | 04:26 | the name of my product and go down
the cell to save those changes, if I then
| | 04:30 | move into my Products table, we take a
look and we'll notice that that edit has
| | 04:33 | actually been saved to my data table.
| | 04:35 | Now, depending on the task that
you're trying to accomplish, this could be
| | 04:38 | either really useful or
it could be really dangerous.
| | 04:40 | So, what we can do here is let's close
our Products table and I'm going to get
| | 04:45 | rid of this edit that I
made so it's not saving it.
| | 04:46 | Let's switch back into Design View.
| | 04:50 | Back in the Query Properties, by
clicking up here in the open area, I'm going to
| | 04:54 | change my Recordset Type to Snapshot.
| | 04:57 | So, Snapshot allows us to view an up-to-
date and current look at our data table,
| | 05:01 | but we won't be able to
edit any of the data within it.
| | 05:03 | Let's go back into Design View and
switch to the Query Properties again.
| | 05:07 | Now, there's a bunch of other options
in here, and these are typically not
| | 05:11 | things that you're going
to ever need to deal with.
| | 05:12 | If you've noticed as I've been
clicking through here that you can click on an
| | 05:15 | option and Access gives us a little
bit of text down here to describe what
| | 05:18 | that property does.
| | 05:19 | But typically these are things that you're
not going to want to adjust in your query.
| | 05:22 | So, the Query Properties gives us a lot
of options that we can do to adjust how
| | 05:25 | our queries operate, how they're
documented using the Description field, or how
| | 05:29 | the fields themselves are
formatted in the query results.
| | Collapse this transcript |
| Working with joins and primary keys| 00:00 | When creating queries that include
multiple tables, it's important to understand
| | 00:03 | how those tables are related to each
other in order to get meaningful results.
| | 00:07 | Now typically, Access is going to pull
information from the Relationships window
| | 00:10 | that we've set up between our tables,
so that it'll understand how the tables
| | 00:12 | relate when we create our queries.
| | 00:13 | But sometimes we're going to need
to go into our query and modify those
| | 00:16 | relationships manually.
| | 00:17 | Let's take a look at how we can do that.
| | 00:19 | So, I'm going to go ahead and open up
our JuneSales query and I'm going to
| | 00:22 | right-click on it and say open
that directly into Design View.
| | 00:25 | So, our JuneSales query has three tables.
| | 00:27 | We've got our Employees table, our
Orders table, and our Products table.
| | 00:31 | Each of our products is represented in
the Products table one time and we can
| | 00:35 | have multiple orders for the same product.
| | 00:37 | So, we can say that this is
a one-to-many relationship.
| | 00:39 | Same way, we've got our Employees
table and each of our employees are only
| | 00:42 | represented here one time, but they
can be represented on multiple orders.
| | 00:46 | So again, this is a one-to-many relationship.
| | 00:49 | The query that we have set up right now
has employee IDs from the Employee table.
| | 00:53 | We've got our FirstName
from the Employees table.
| | 00:55 | We also have a calculation that takes
Price and we're finding the sum total of
| | 00:59 | all the prices and we're going to
call that June Sales with an alias.
| | 01:02 | Then we have our OrderDate, and we see
that we have a criteria set on our date.
| | 01:06 | Now, we'll go ahead and talk about
dates in a future movie, but for now just
| | 01:09 | know that we're using this criteria to
filter out only the records that occur
| | 01:11 | between June 1st 2010 and June 30th 2010.
| | 01:15 | So the question that this query has set
up to answer is how did our Sales team
| | 01:18 | do during the month of June?
| | 01:19 | Who sold the most
products and who sold the least?
| | 01:22 | So, let's go ahead and run this
query and see what the results look like.
| | 01:25 | We can see that we have Rose in the lead.
| | 01:26 | She sold a total of $110
worth of product in June.
| | 01:29 | If we scroll to the bottom, we can see
that Patrick sold the least at $10.57.
| | 01:33 | I'm going to go ahead and maximize this
window so we can see the very bottom,
| | 01:37 | and taking a look at the number of
records that were returned, we have 36.
| | 01:40 | Now I know a little bit about the data
tables that we have in this database,
| | 01:43 | and I recognize that we have a
problem here, because we should have 84
| | 01:47 | salespeople on our team.
| | 01:48 | So, where did everybody else go?
| | 01:50 | Let's go back into the Design View.
| | 01:52 | The answer to our problem can be
found in the way these two tables are
| | 01:54 | related to each other.
| | 01:55 | So, I'm going to go ahead and double-
click on the joining line between the two
| | 01:58 | tables and we can bring up
the join properties for that.
| | 02:01 | The default option is called an inner
join and we can see that it's only going
| | 02:05 | to include rows where the joined
fields from both tables are equal.
| | 02:07 | Let's go ahead and get this out of
the way so I can see this a little bit.
| | 02:10 | Basically, what we're saying is that
we're only going to return records where
| | 02:12 | the employees are represented in
the Orders table and the orders are
| | 02:16 | represented in the Employees table.
| | 02:18 | Basically, if an employee didn't have any
sales, then it's not going to be returned.
| | 02:21 | What we want is this option number two.
| | 02:24 | We want to include all the records
from the table Employees, and only those
| | 02:27 | records from table Orders
where the joined fields are equal.
| | 02:29 | Basically, we want every employee,
whether they had sales or not, and then for
| | 02:33 | the people that did have sales, we're
going to return that information for them.
| | 02:35 | So, let's go ahead and say OK to this.
| | 02:37 | Now we're going to try and run our query.
| | 02:40 | Access throws up a little warning message here.
| | 02:42 | In dealing with Access, I've learned
that the warning messages that we get are
| | 02:45 | actually really useful if we
take a moment to read what they say.
| | 02:48 | So, this one says the SQL statement
cannot be executed because it contains
| | 02:51 | ambiguous outer joins.
| | 02:53 | Basically, since we've adjusted the
join properties between the Employees table
| | 02:56 | and the Orders table, Access is
getting a little bit confused about how all
| | 02:59 | three of these tables are
supposed to relate to each other.
| | 03:01 | The warning message goes on to give
us a hint as to how we can fix this.
| | 03:04 | To force one of the outer joins to be
performed first, create a separate query
| | 03:08 | that performs the first join, and
include that query in your SQL statement.
| | 03:11 | So, the answer is that in order to
perform this task, we actually need to
| | 03:14 | create two queries.
| | 03:15 | So, let's go ahead and say OK to this
message and I'm going to reset our join
| | 03:18 | back to the way it was.
| | 03:19 | So again, double-click on that,
select option 1, and say OK.
| | 03:22 | Let me go ahead and close this query
and Access asks if we want to save it.
| | 03:26 | Let's go ahead and save our query.
| | 03:28 | The next step is to create a new query.
| | 03:30 | We're going to go to
Create and then Query Design.
| | 03:32 | We're going to choose our Employee table.
| | 03:34 | We're also going to switch the tab to
Queries, and we're going to include the
| | 03:37 | query that we just created.
| | 03:39 | Go ahead and close this window.
| | 03:41 | We see that Access is already
recognizing that the employee ID in the Employees
| | 03:45 | table should match the
employee ID from the JuneSales table.
| | 03:47 | Let's go ahead and
populate some data into our query.
| | 03:50 | We want employee ID, FirstName, and then
we want the calculated June Sales value
| | 03:54 | from the JuneSales query.
| | 03:56 | So let's double-click to move that down.
| | 03:58 | Next, we want to adjust the join
properties between these two tables.
| | 04:00 | Let's double-click on that line and now
we can go ahead and select option 2 to
| | 04:04 | include all the records from the
employees table and only records from JuneSales
| | 04:07 | where the join fields are equal.
| | 04:08 | We'll go ahead and say OK,
and we'll run our query.
| | 04:11 | So, after a bit of processing,
Access returns 200 records.
| | 04:15 | Now, that's not the 84 that we're expecting
in the number of salespeople that we have.
| | 04:19 | So, we've got one more step that we need to do.
| | 04:20 | One more time, let's go back in the Design View.
| | 04:24 | Now, since we're returning all of
our employees, we're actually getting
| | 04:27 | everybody in the whole company,
regardless of whether they work in the Sales
| | 04:29 | department or HR or Marketing or Advertising.
| | 04:32 | So, what we want to do is filter down to
just the people in the Sales department.
| | 04:35 | So, I'm going to expand this window a
little bit and we're going to add a
| | 04:38 | department filter in here.
| | 04:40 | Under criteria for
departments, I'm going to write sales.
| | 04:43 | Press Enter to accept it, and now when
we run it, we'll see that Access returns
| | 04:48 | the 84 records that we were expecting.
| | 04:49 | Let's go ahead and sort this from
largest to smallest on the sales.
| | 04:52 | We can see that once again we've
got Rose at the top of the $110.
| | 04:55 | In the middle, we've got Patrick with
$10.57 for sales, and then we've got
| | 04:59 | everybody else in the Sales department
that didn't have any sales for the month.
| | 05:02 | So, understanding how your data
tables relate to one another is a critical
| | 05:05 | component to creating well-
structured queries that give the meaningful
| | 05:08 | results that you expect.
| | Collapse this transcript |
| Printing query results| 00:01 | Often the results of your queries are
going to be incorporated into other Access
| | 00:04 | objects such as Forms or Reports.
| | 00:07 | But occasionally you want to get a
printout of the Datasheet View of your query
| | 00:09 | just to pass on to a colleague or supervisor.
| | 00:11 | If you've ever printed a Data Table
within Excel, you'll be happy to know that
| | 00:15 | many of the same formatting options
are available to you within Access.
| | 00:18 | So let's go ahead and take a look at
how we can print our query results.
| | 00:21 | So let me go ahead and open up our qry_
Products, and we can see that this is the
| | 00:24 | one we've spent some time on earlier,
formatting it through properties.
| | 00:27 | So we've got our headers all
named appropriately and we've got our
| | 00:30 | formatting all set up.
| | 00:31 | Let's go ahead and go up to Office
button, go down to Print, and we'll
| | 00:35 | choose Print Preview.
| | 00:36 | Now this is the same Print Preview
interface that's common throughout the
| | 00:40 | entire Office suite.
| | 00:41 | We've got our option to send it to the printer.
| | 00:43 | We've got some options for page layout,
so we can change it in size, whether
| | 00:47 | it's portrait or
landscape in some of the margins.
| | 00:49 | We've got some options for how we're
displaying the print preview of screens, so
| | 00:53 | whether we want to zoom in, view
it as a one-page or two-page spread.
| | 00:56 | The data section gives us some export
control, so we can export to Excel, we
| | 01:00 | can export to a PDF file, a Word file,
or we've got some additional file types
| | 01:04 | we can export to in the More menu.
| | 01:06 | Finally, we've got the Close Preview button.
| | 01:08 | Let's go ahead and expand this out a
little bit and I am going to zoom in here
| | 01:11 | to the top and we can see that we've
got currently a name at the top of our page
| | 01:14 | and we've also got the
date that we were printing this.
| | 01:17 | In the Page Setup here, in the Page
Layout section, I can click on that.
| | 01:20 | And, we can see that we've got the
same controls for margins and another Page
| | 01:24 | tab for layout landscape or portrait.
| | 01:27 | But under Print options I also
have an option to Print Headings.
| | 01:29 | And, if I turn that off and say OK, we'll
notice that we lose the title and the dates.
| | 01:33 | I am going to go ahead and go back to the
Page Setup and turn my Print Headings back on.
| | 01:38 | Finally, we can just go to our Print
menu and we can choose any available
| | 01:41 | printer within our system.
Let me go ahead and Cancel out of that.
| | 01:45 | Now depending on what additional
extensions or software you have on your system,
| | 01:49 | we might have some
additional options available to us.
| | 01:51 | On this particular computer I've got
Acrobat Professional installed and as
| | 01:54 | part of that Installation Package, it installs
say this additional toolbar into the Office suite.
| | 02:00 | Otherwise we can go into the Microsoft
Office button and if we go down to Save As,
| | 02:03 | you might see an option
to export to a PDF or XPS file.
| | 02:07 | If you don't have either these
options available to you, we can actually
| | 02:09 | download a free extension from
Microsoft that will add that ability into
| | 02:12 | the full Office suite.
| | 02:13 | So let's take a look at
where we can get that from.
| | 02:16 | You can visit the Microsoft download
Center at www.microsoft.com/downloads.
| | 02:22 | Once here, we can do a search and we are
going to do a search for Office 2007, Save As PDF.
| | 02:31 | In the search results we'll find an
option to install an add-in for Microsoft
| | 02:34 | Office 2007 Save As PDF or XPS.
| | 02:38 | If you click on the Download link,
it'll download an executable file to your
| | 02:40 | computer, and after running that
you'll have the ability to export PDF files
| | 02:44 | from any of the Office programs.
| | 02:45 | Now back in Access, we actually
already have that extension installed on our
| | 02:49 | system and if you didn't have it, you'll
notice that you've got a couple of new buttons here.
| | 02:52 | We've got a PDF or XPS button on our ribbon.
| | 02:55 | We also could go up to Microsoft Office
button > Save As, and if you didn't have
| | 02:59 | it installed, you know
this options is there as well.
| | 03:01 | So let's go ahead and close the print preview.
| | 03:05 | So that was a way that we can quickly
generate a printed report that shows the
| | 03:08 | results of our query.
| | 03:09 | Let's go into Design View for moment.
| | 03:11 | So let's say that we are putting
together a scientific report or a grant
| | 03:14 | proposal where not only do we need to
document the results of our query, we
| | 03:18 | also need to document the
process that led to those results.
| | 03:20 | So basically we need to a have report that
shows all the fields that we have in our query.
| | 03:24 | All the criteria that we were filtering
by and any calculations we might have
| | 03:27 | used to derive those results.
| | 03:29 | From the Design View, you would think
that you could probably go up to the
| | 03:31 | Office button and select Print, but
we'll notice that it's actually grayed out.
| | 03:35 | And if you have the PDF extension
installed, if you go to Save As, you'll also
| | 03:38 | notice that PDF is grayed out as well.
| | 03:40 | So how do we document our process?
| | 03:42 | There are a couple of options
that we have available to us.
| | 03:45 | One option is to switch our view into
SQL mode and basically this is the text
| | 03:49 | that's created behind the scenes as we
edit our query in the QBE environment.
| | 03:53 | So we can simply select this, copy it
and paste it into an email or a text file.
| | 03:57 | So that's one way that we
can document our process.
| | 04:00 | Another way is to go up to our Database
Tools menu and we can use a tool called
| | 04:04 | Database Documenter.
| | 04:05 | Let's go ahead and click on that.
| | 04:07 | And we'll see that the Database
Documenter brings up a list of all the objects
| | 04:10 | within our database.
| | 04:11 | I can switch to the Queries tab, if it's not
selected, and choose to document our qry_Products.
| | 04:17 | I've got some options available to
us, so let's take a look at those.
| | 04:20 | We get a lot of information
that we can include in our report.
| | 04:24 | We can include information about
the properties for our query, the SQL
| | 04:28 | statement, any parameters that we
might have saved, the relationships between
| | 04:32 | the data tables that we were using
and any user permissions that might be
| | 04:35 | associated with our query.
| | 04:37 | For the fields we can
choose to document Nothing.
| | 04:39 | Or we can show Names, Data Types and
Sizes, or we can include Names, Data Types,
| | 04:44 | Sizes and Properties for our fields.
| | 04:46 | We can also include information
about indexes, but we haven't been using
| | 04:49 | indexes and our queries.
| | 04:50 | So let's go ahead and just accept
these default settings and say OK.
| | 04:54 | And if I press OK one more time, it's
going to ask us to close our query first
| | 04:58 | and we can go ahead say OK.
| | 05:00 | And Access will jump us back into our
Print Preview mode and we can take a look
| | 05:04 | at the report that was generated by zooming in.
| | 05:06 | And we can see that we have a detailed
report about how this query was created.
| | 05:10 | We've got information about our properties,
including the date and time that it was created.
| | 05:12 | We've got our SQL statement and we can
see that we have some information about
| | 05:17 | the fields and all of the properties we have
associated with those fields within our query.
| | 05:21 | We can also page through our reports,
and we've got several pages here, to see all
| | 05:25 | the details that go in
the creation of our query.
| | 05:28 | So using the Print options within
Access is an easy way that we can document,
| | 05:31 | both the process and the results of our
queries, in order to include a hardcopy
| | 05:35 | printed report or to shares with our viewers.
| | Collapse this transcript |
|
|
3. Creating Parameter QueriesUnderstanding parameter queries| 00:01 | So far we've seen lots of ways that
you as the query designer can control the
| | 00:04 | records that are displayed in the query results.
| | 00:07 | But what if we wanted to set up a
framework and allow the end user of our query to
| | 00:10 | supply some of the input?
| | 00:11 | Lets go ahead and take look at this
qry_EmployeesAZ, and I am going to
| | 00:15 | right-click and go into Design View.
| | 00:17 | And you can see that this query
has a criteria of Arizona, or AZ.
| | 00:20 | If I run this query, it's going to
return all the employees that live in
| | 00:24 | the state of Arizona.
| | 00:25 | Now the results might change over
time as we gain or lose employees within
| | 00:29 | our organization, but the underlying
structure of the query is always going to be the same.
| | 00:32 | It's always going to return the
employees that all live in Arizona.
| | 00:35 | Let's take a look at the Design View
again and we'll see how we can make this a
| | 00:38 | little bit more flexible.
| | 00:40 | So back in the Design View, I am
going to get rid of the criteria AZ.
| | 00:44 | We can use what's called a parameter
query in order to prompt the end user for a
| | 00:48 | little bit of extra
information when the query is run.
| | 00:51 | We define a parameter value
by using the square brackets.
| | 00:54 | So instead of hard coding this query
to always return results for Arizona,
| | 00:58 | I want to ask the end user
which state they are interested.
| | 01:00 | I am going to input a parameter query
by opening it with a square bracket and
| | 01:05 | then I am going to ask the end
user, What state are you interested in?
| | 01:07 | I am going to end that
with a closing square bracket.
| | 01:11 | Now when I run this query, I am going
to get a dialog box that says What state
| | 01:14 | are you interested in?
| | 01:16 | Let's say I am interested in the state of
Florida, so I'll type in FL and say Run.
| | 01:20 | And now we're getting all the
employees that live in state of Florida.
| | 01:23 | Switch back into Design
View and run the query again.
| | 01:25 | Now I am interested in Alabama, so
I'll type AL. Go ahead and say OK.
| | 01:30 | Now the exactly same query is
returning just the results from Alabama.
| | 01:33 | So you can see how including a
parameter request into your query can quickly
| | 01:37 | make a query more flexible.
| | 01:39 | Let's go ahead and go back into our
Design View and take a look at some other
| | 01:41 | options that we have.
| | 01:42 | In a previous movie we looked at the
comparison operators greater than or less
| | 01:45 | than or greater than an equal to, etcetera.
| | 01:47 | Let's take a look at how we can combine the
comparison operator with a parameter request.
| | 01:51 | I am going to go ahead and delete this
request for State, I am just going to
| | 01:54 | highlight the whole thing and press Delete.
| | 01:56 | I am going to also add a couple of
more tables to our query, so I am going to
| | 01:59 | right-click and say Show Table.
| | 02:00 | I am going to add our
Orders and our Products table.
| | 02:03 | Go ahead and close this.
| | 02:08 | And I am going to add Cost from
the Products table to our query.
| | 02:11 | So, for our Cost criteria, let's say
we want to set up a query that prompts
| | 02:15 | for a maximum price.
| | 02:16 | I am going to use the operator <= and
then I am going to prompt for our parameter.
| | 02:22 | So I am going to open up the square bracket,
I am going to type in Enter Maximum Cost,
| | 02:26 | and I am going to close
it with a square bracket.
| | 02:29 | Now Access is going to ask me for the
maximum cost that I am interested in.
| | 02:32 | This time I am going to
type in $10 and I'll say OK.
| | 02:35 | And we'll see that Access returns
only the products that are $10 or less.
| | 02:37 | Let's go back into our Design View
and take a look at another example.
| | 02:41 | Let's go ahead and get rid of this parameter.
| | 02:44 | Okay, now I want to set up a query
where we can look up employees based off to
| | 02:50 | the first letter of their last name.
| | 02:52 | In the criteria section for LastName,
we are going to use a like operator
| | 02:55 | that we've seen before.
| | 02:56 | I am going to enter in the
parameter, First letter of Last Name.
| | 03:03 | I am going to finish that with a square bracket.
| | 03:06 | And I am going to use the
asterisk wildcard that we've seen before.
| | 03:09 | In order to do that I need to use an
ampersand to say that we're linking these together.
| | 03:13 | And then we're going to add the asterisk.
| | 03:13 | We'll take a look at the SYANTAX a
little bit later in the course when we
| | 03:16 | look at text expressions.
| | 03:18 | Now we'll go ahead and run this query now.
| | 03:21 | And we are going to get a parameter
request that says What is the first
| | 03:23 | letter of Last Name?
| | 03:24 | Let's say I am interested in all of
our employees that have a last name that
| | 03:27 | starts with W. I am
going to go ahead and say OK.
| | 03:29 | You'll see that Access returns those results.
| | 03:32 | If I go back and run it again,
now I am interested in F and Access
| | 03:36 | returns those results
| | 03:38 | Letting your end users define their own
questions can be a pretty powerful tool.
| | 03:42 | It adds flexibility to your database
and could be a way to streamline your work
| | 03:45 | while providing some level of future-proofing.
| | 03:48 | Parameter variables will allow your
queries to serve answers to questions that
| | 03:52 | you might not have even
considered during the design process.
| | 03:55 | The Enter Parameter Value dialog box
isn't the only way that your end users can
| | 03:58 | define variables in a query.
| | 03:59 | In the next movie we will take a look
at how we can use data entry forms and
| | 04:03 | buttons to hook into the
variables that we've just seen.
| | Collapse this transcript |
| Obtaining parameters from other Access objects| 00:00 | I want to start this movie by passing
on a fundamental concept that I think
| | 00:04 | might change the way you think about Access.
| | 00:07 | Every object and every form, every
button, checkbox, label, text box has a name
| | 00:12 | or a value associated
with it within the database.
| | 00:15 | In every name or value it can be used as
an input somewhere else in your database.
| | 00:19 | And really understanding this concept
will set you on the path to becoming a
| | 00:22 | true Access rock star and here is why.
| | 00:24 | Access objects, that is the tables,
the forms, the queries, the reports,
| | 00:28 | etcetera, they are all presented in the
interface as individual components of your database.
| | 00:32 | But in reality, it's the interaction
and interconnections between the objects
| | 00:36 | that allow your database to
do some pretty amazing things.
| | 00:39 | In the previous movie, we looked at
how we can provide our end users with a
| | 00:42 | popup dialog box to request some
additional bit of information when the query is run.
| | 00:46 | Let's take that a step further and
we'll see how we can define those same
| | 00:49 | parameters using a button or a form control.
| | 00:52 | Right now, I am going to open up
our EmployeeLookup form here and it's
| | 00:55 | called frm_EmployeeLookup.
| | 00:58 | This is pretty well formatted.
| | 00:59 | It's going to ask me to Show me all
employees in and I've got dropdown menu.
| | 01:03 | If I use the dropdown menu,
you can select any of the states.
| | 01:06 | I am going to go ahead and select Colorado
and I have got a button that says Run Query.
| | 01:10 | If I click on that, it runs a query
called EmployeesLookup and filters down to all
| | 01:14 | the employees that live in the state Colorado.
| | 01:16 | Now, functionally this is working the
same exact way as a parameter query that
| | 01:19 | we were looking at in last movie.
| | 01:21 | If we switch into Design View, we
will see they we were simply asking for a
| | 01:24 | parameter here in the Criteria section.
| | 01:26 | So, we can see that we are obtaining
the value of this parameter from the State
| | 01:29 | object within the EmployeeLookup form.
| | 01:32 | Let's go ahead and take a look at our
EmployeeLookup form and see where that's coming from.
| | 01:35 | Let's go back into our form here and I am
going to switch in the Design View for our form.
| | 01:40 | I am also going to open up our Properties box
and I am going to use the F4 key to do that.
| | 01:45 | If we take a look at the properties for
this dropdown menu, we'll see that the
| | 01:48 | name of this menu is called State.
| | 01:50 | So if we go back to our query one more time.
| | 01:52 | We can see that the State value
is the same as that dropdown box.
| | 01:55 | So, whatever is selected in the dropdown
box when this query is run is what gets
| | 01:58 | populated as the criteria.
| | 02:01 | Let's take a look at another example.
| | 02:02 | Let me go ahead and close the
Property Sheet here and close the
| | 02:05 | EmployeeLookup query.
| | 02:06 | I am also going to go ahead and
close the EmployeeLookup form.
| | 02:08 | I've got another form here called
SalesRange and when I run that, we are going
| | 02:12 | to get asked to supply a couple of values.
| | 02:14 | The Total Lifetime Sales to each Customer.
| | 02:16 | We want to find the customer that
has spent between $150 and $200.
| | 02:21 | If I run the query, Access returns
the result that we've expected.
| | 02:26 | Take a look at the data sheet
behind this to see how this is working.
| | 02:29 | If I expand this to open a little
more, we can see that we have got some
| | 02:32 | comparison operators here.
| | 02:33 | So we have got comparison operator that
says greater than or equal to whatever
| | 02:37 | the value of the low field in the
SalesRange form is and less than or equal to
| | 02:43 | whatever the high value
is in the SalesRange form.
| | 02:46 | So, if we take a look at the Design
View of our SalesRange form, we should be
| | 02:49 | able to find the low and high
values in these two text boxes.
| | 02:52 | Let me click on this text box and
we'll open up our properties again.
| | 02:56 | This time I'll use the button on the Ribbon.
| | 02:59 | We can see that the name of this field is
low and the name of the right field is high.
| | 03:03 | So, whatever the end users typed into
these two boxes when you run the query is
| | 03:07 | what gets populated as the criteria
and that's what gets run as the query.
| | 03:10 | Now this wasn't meant to be an all-
inclusive look at creating amazing forms, but
| | 03:15 | I hope these examples have given you
a little taste of the types of user
| | 03:18 | interface tools that are at
your disposal within Access.
| | 03:21 | By combining what we already know
about creating robust queries with some
| | 03:25 | additional insight into the larger
framework of Microsoft Access databases
| | 03:28 | and specifically forms, my goal is to
get you thinking about your own projects
| | 03:32 | and hopefully get you a little bit
excited about applying these tools to your needs.
| | Collapse this transcript |
|
|
4. Creating Calculated FieldsUsing Totals to calculate values| 00:01 | Access databases are really good at
collecting and storing a large amount of raw data.
| | 00:05 | However, when it comes to understanding
what all that data is telling us, it can
| | 00:08 | quickly get overwhelming.
| | 00:10 | Most of the time you want to
summarize or aggregate data points together in
| | 00:13 | order to get a better understanding of
some of the trends or rankings that might
| | 00:16 | otherwise be buried within your data.
| | 00:18 | So, let's create a new query
where we can apply this concept.
| | 00:20 | I am going to go up to the Create menu,
and this time I am going to create a new
| | 00:24 | query in our Design View.
| | 00:25 | I am going to include our DirectCustomers table.
| | 00:27 | I also want to get some information
about their products they have ordered.
| | 00:30 | Now, if we think back to our
relationships window, you might recognize that
| | 00:34 | these two tables aren't
directly linked together.
| | 00:36 | We also need to include our Orders table
that provides a relationship between the three.
| | 00:40 | Go ahead and close your Show Table window.
| | 00:42 | I am going to include our FirstName and
LastName from the DirectCustomers table.
| | 00:47 | I also want to get the
Price from the Products table.
| | 00:51 | I'm going to use a criterion here so
we can filter out only a few records.
| | 00:55 | So it's a little bit
easier to see what's going on.
| | 00:57 | I am going to use our friend Sebastian Rich.
| | 00:59 | He is our top customer.
| | 01:00 | So let's go ahead and run this query,
and we are going to see how much Sebastian
| | 01:03 | has spent for each of his orders.
| | 01:05 | He has placed a total of
six orders with the company.
| | 01:07 | Let's go ahead and switch back into Design View.
| | 01:09 | I am going to go ahead and toggle on
our Totals row by using the button up
| | 01:12 | here on our Ribbon. I'll turn that on.
| | 01:15 | You will see that we've got a new row
here called Total, and we've got some
| | 01:18 | default values that apply to each of our fields.
| | 01:20 | So we've got a Group By
class for each of these fields.
| | 01:22 | Under Price I am going to open up the
menu and I see that we have a lot of
| | 01:25 | options to choose from.
| | 01:26 | I am going to go down the list
here. I am going to Sum first.
| | 01:29 | And if I run this query again, we'll
see that we have a new total here and
| | 01:33 | Access gives it a new title called SumOfPrice.
| | 01:35 | This is going to be the sum total of all
the orders that Sebastian has spent with us.
| | 01:39 | So, we can easily see that he
has spent $260.24 with the company.
| | 01:42 | Let's go ahead and go back to the
Design View and take a look at some of these
| | 01:45 | other total options.
| | 01:46 | Let's take a look at average.
| | 01:48 | If I run it again, and now I am going to
have to expand this out a little bit so
| | 01:52 | I can get a better look at the number.
| | 01:53 | Access again has changed the title.
| | 01:55 | So now where it says AvgOfPrice, I
can see that the average order has been
| | 01:58 | 43 dollars and about 37 cents.
| | 01:59 | We go into our Design View again.
| | 02:03 | Let's change this to minimum. Run it again.
| | 02:07 | We now have the minimum order that he
spent and the smallest order was $16.
| | 02:11 | Back up in Design View,
and I think you get the idea.
| | 02:14 | We can also get the maximum order that
he spent, we can get the number of orders
| | 02:17 | that he has ordered with us, we can
also get some statistical information such
| | 02:20 | as the standard deviation or
the variance in his orders.
| | 02:23 | We can also return the first or
the last order that he spent with us.
| | 02:26 | But for now, let's go ahead and
change this to... let's say Average.
| | 02:30 | I am going to get rid of our criteria here.
| | 02:32 | So I am going to delete
Sebastian Rich from our criteria.
| | 02:35 | Now, when I run this query again, we've
got all of our customers sorted out and
| | 02:38 | we can find the average total for
every order that they have placed.
| | 02:41 | So, aggregate operations will give you
the answers to some of the most common
| | 02:44 | questions that you might have about your data.
| | 02:46 | They provide a mechanism for all kinds of
summaries, trend analysis, or rankings.
| | 02:50 | They're a great way to move beyond
simply reviewing your data to understand
| | 02:53 | what it actually means.
| | Collapse this transcript |
| Introducing mathematical operators| 00:01 | One of the key factors in creating a
clean and robust database is knowing
| | 00:04 | what data you need to store and what data
you can derive or create from other records.
| | 00:09 | For instance, if we think about how a
payroll database might work, the number
| | 00:12 | we're after is how much the employee
earned at the end of a two-week pay period.
| | 00:16 | But will that dollar amount appear in any
of your tables? And I would say probably not.
| | 00:21 | More than likely, a payroll database
will simply include a record for the
| | 00:24 | employee's hourly wage
| | 00:25 | and will have another table that describes the
number of hours they worked during each shift.
| | 00:30 | At the end of the pay period, when
they are cutting the checks, the payroll
| | 00:32 | department will simply add up the number
of hours worked, they'll multiply that
| | 00:36 | by the hourly wage and you will
get the total amount for the check.
| | 00:39 | By using some simple mathematical
operations, in this case addition and
| | 00:43 | multiplication, the Payroll department
is able to derive the value of the check
| | 00:47 | even though that value doesn't
actually appear in the database.
| | 00:49 | The Design View in Access allows us
to easily add some of these types of
| | 00:52 | mathematical operations to our queries.
| | 00:55 | So let's see how we can apply this.
| | 00:56 | We are going to go up to the Create tab and I
am going to create a new query in Design View.
| | 01:00 | I am going to go ahead and add the
Products table for good measure, even though
| | 01:04 | I am not going to actually use any
of the data from the Products table.
| | 01:07 | Now, in the fields, we can actually just
type a mathematical expression right in here.
| | 01:11 | We could type in things like
addition so I could type in 5+3.
| | 01:13 | we could do subtraction, 5-3.
| | 01:17 | I could do multiplication, so 5*3.
| | 01:20 | We can do division, 5/3.
| | 01:23 | We can even do
exponentiation, so we could
do 5 raised to the third power.
| | 01:28 | Here I am using the up caret,
which is Shift+6 on your keyword.
| | 01:30 | If I go ahead and run this query, you
'll see that Access does the math and
| | 01:34 | returns the results.
| | 01:35 | So we see Expression 5 was
our exponent, so 5^3 is 125.
| | 01:41 | 5/3 is 1 and two-thirds.
| | 01:43 | Now, we are returning a total of 90
records here because if we go back into
| | 01:46 | Design View, I have the
Products table to sit up here.
| | 01:49 | Access requires one table
to be linked every query.
| | 01:51 | So that's why this is up here,
even though we are not using it.
| | 01:54 | So let me go ahead and get rid
of all these math expressions.
| | 01:57 | I'll right-click and say Cut.
| | 01:59 | Now, Access actually also provide
another two options for division and I want
| | 02:02 | to tell about the differences between the two.
| | 02:04 | So let's say that we are packaging up
bottle of olive oil and we are going to
| | 02:07 | ship them out to our retail locations.
| | 02:09 | We have got a thousand bottles and we are
putting them in the cases of 12 bottles each.
| | 02:12 | Normally, you would say 1000/12 to give
you the number of cases that you have.
| | 02:16 | If I go ahead run this expression,
we'll see that Access returns 83 and
| | 02:21 | one-third cases full.
| | 02:22 | Now, this is a little bit hard to deal with.
| | 02:24 | So let me take you through the other
two options that we have for division to
| | 02:27 | see how they'll help us out here.
| | 02:28 | So, we'll go ahead and return to
our Design view and the first option is
| | 02:32 | called integer division and that's starting
with a backslash instead of a forward slash.
| | 02:36 | So let me give this field an alias
here and we are going to call Full Cases.
| | 02:39 | We are going to use
integer division. So 1000/12.
| | 02:47 | The second form of division is called modulo.
| | 02:48 | I am going to write an alias
that says Remaining Bottles.
| | 02:53 | The mathematical operation for
this is going to be 1000 Mod 12.
| | 02:57 | That's how we would write a modulo expression.
| | 02:59 | Now when I run this, let's
take a look at what the results are.
| | 03:01 | We'll see that the original
division returned 83 and one-third.
| | 03:05 | That means 83 and a third cases can
be filled with our thousand bottles.
| | 03:10 | The integer division only returns
the full cases that I have returned.
| | 03:13 | So we can get 83 cases.
| | 03:15 | The modulo division only returns the
number of bottles that are left over at
| | 03:18 | the end of the day.
| | 03:19 | So we have four bottles left over.
| | 03:21 | So these two numbers,
| | 03:22 | the integer division and modulo
division, are much cleaner in this particular
| | 03:26 | examples, depending on the
particular scenario that you're working with.
| | 03:29 | Now that we have seen that how these
all mathematical expressions work within
| | 03:32 | Access, we are going to take a look
at how we can apply them using our data
| | 03:35 | within the data tables in the next movie.
| | Collapse this transcript |
| Using mathematical operators| 00:01 | In the last movie, we took a look at
the seven mathematical operators that
| | 00:03 | Access has at our disposal.
| | 00:05 | Let's see that how we can use these actual data.
| | 00:07 | We'll go up to our Create tab and we
will create a new query in Design view.
| | 00:11 | Then I am going to add our Products
table to our query and we are done with the
| | 00:13 | Show table window, so I can close that.
| | 00:16 | Now, for this query, we are interested
in finding out the price per ounce of all
| | 00:20 | of the products that we carry.
| | 00:21 | That may include the ProductName, the Price,
and the size, which is in the Ounces field.
| | 00:27 | Now we are going to create the
mathematical expression that will give us the
| | 00:30 | price per ounce of each product.
| | 00:32 | Let's go ahead and right click and
say Zoom to bring up the Zoom window.
| | 00:35 | So, the expression is going to
look something like this: price/size.
| | 00:37 | Now, this isn't actually
the correct syntax that we need.
| | 00:42 | We need to be more specific so that Access
knows exactly where these values are coming from.
| | 00:46 | So I am going to go ahead and rename
price, and I am going to tell it that
| | 00:50 | It's coming from the table products,
tbl_products, and it is coming from the
| | 00:57 | field named price.
| | 00:59 | And this is the syntax that Access requires.
| | 01:01 | We need to wrap the table name in square
brackets, and then we separate each by a period.
| | 01:06 | I am going to do the same
thing for our size field.
| | 01:08 | So the size is coming from the table
products and is coming from the ounces field.
| | 01:14 | So once again, we are taking the
ounces field from the table products.
| | 01:18 | So our full expression reads "take
the price in the products table divided by
| | 01:22 | the size, which is in ounces,
from the products table."
| | 01:24 | We'll go ahead and say OK and Access
gives it a Expression 1 alias and we can
| | 01:31 | go ahead and rename this.
| | 01:32 | We want to call this dollars per ounce.
| | 01:37 | If I run this, we will see that we have
got a calculated field that gives us the
| | 01:41 | price per ounce of each of our products.
| | 01:42 | If I were to sort base off of this,
I can click on the downward arrow and say
| | 01:47 | Sort Largest to Smallest.
| | 01:48 | We will see the highest price
per ounce product that we carry.
| | 01:52 | So by using some other mathematical
operators to combine the data that's in
| | 01:55 | our data table, we can come up with new values
that might give us some insight into our data.
| | 01:59 | It should be noted these mathematical
operators will only work with numerical data.
| | 02:03 | Access does offer some similar
functionality that applies to text and we'll take
| | 02:06 | a look at those in the next movie.
| | Collapse this transcript |
| Using text | 00:01 | As with the mathematical operations
that we looked at in the previous movie,
| | 00:04 | Access provides us with a few ways that we
can modify and combine our text based data as well.
| | 00:10 | For instance, we can extract a person's
initials from a table of first and last names.
| | 00:13 | We can do this using what
Access calls string fFunctions.
| | 00:17 | The key here is to think about your
text based data as a simple string of
| | 00:20 | characters, so individual letters,
and we will use those positions to help us
| | 00:24 | extract the pieces that we need.
| | 00:25 | Let's go ahead and create a new query.
Create and then the Query Designer.
| | 00:30 | This query is going to be
all about our employees.
| | 00:32 | So we are going to add that table
and close the Show Table window.
| | 00:35 | I want to add the first name and last
name to our query just for reference and
| | 00:40 | then in the third field, I am
going to right-click and say zoom.
| | 00:42 | The first function that I want to talk
about is called Concatenates and that's
| | 00:45 | represented by the ampersand or the And symbol.
| | 00:48 | And we can use that to join two separate
fields together, and this is how that will work.
| | 00:52 | So, I am going to add the field
FirstName from the table Employees.
| | 00:56 | So I will start tbl_Employees,
period, and then the firstname field.
| | 01:00 | I'm going to use Concatenate to add
that to the LastName field and then we're
| | 01:09 | going to go ahead and say OK.
| | 01:11 | Access is automatically going to give it
a name of Expression1 and we can change
| | 01:15 | and we can change this alias
if we wanted to, to say Full Name.
| | 01:19 | Now, if I run our query, we will see
that we get our FirstName column and the
| | 01:22 | LastName column directly from our table.
| | 01:24 | We also have this new calculated field that
combines the first and last names together.
| | 01:29 | Now currently we don't have any spaces
in between and so we can go back into our
| | 01:32 | query and fix that up a little bit.
| | 01:34 | So back into the Design View,
right-click again and say Zoom.
| | 01:37 | Access is actually putting these
together exactly as it's written, so we are
| | 01:40 | joining the FirstName directly to
the LastName with nothing in between.
| | 01:44 | Let's change that around a little bit
by typing in a quote, and then a space,
| | 01:48 | and then a closing quote
and then another ampersand.
| | 01:51 | So now this is reading, we are going to
join the first name to a space and join
| | 01:55 | all of that to the last name.
| | 01:56 | Let's go ahead and say OK, run it again,
and we see that cleaned up a little bit.
| | 02:01 | Let's go back into our Design View
and take a look at another example.
| | 02:04 | In the fourth field, again
right-click and say Zoom.
| | 02:08 | For this field, we want to extract
just the employee's initials and we can do
| | 02:11 | that using a function called Left.
| | 02:13 | We will type in the word Left
and then open a parenthesis.
| | 02:16 | Now left enquires two elements. First
we need to know which field we want to
| | 02:20 | extract the data from and then we need
to know how many characters to extract.
| | 02:23 | So we are going to feed it the
first name from our Employees table,
| | 02:27 | tbl_Employees, and then the
FirstName field from that table.
| | 02:31 | Then we are going to add a comma and we are
going to say how many characters to extract.
| | 02:36 | In this case, we just want the
first letter from the first name.
| | 02:39 | Go ahead and close our parenthesis and
we will use Concatenate to add a period
| | 02:42 | to the end of this.
| | 02:44 | So and, and then put a
period and quotation marks.
| | 02:47 | So we are literally adding
a period to the end of that.
| | 02:49 | Let's go ahead and say OK and if we
say run, we will notice that we get the
| | 02:54 | first initial of the person's first name.
| | 02:55 | One more time, we can go back
into our expression, we are going to
| | 02:59 | right-click, and go to Zoom.
| | 03:02 | We could join this whole thing to
their last name by typing in tbl_Employees.
| | 03:10 | And we are going to choose the
LastName field from the table.
| | 03:13 | So now we have an expression and while I
am actually here in the Zoom box I can
| | 03:17 | change the alias right here.
| | 03:19 | So this is going to be the initials
and so that's going to be the First
| | 03:23 | Initial and LastName.
| | 03:27 | So I've got a statement that reads like this.
| | 03:29 | From the FirstName in the table
Employees, we are going to extract the first
| | 03:32 | character using the Left function.
| | 03:34 | We are going to join that to a period
and then we are going to join that to the
| | 03:38 | employee's LastName.
| | 03:40 | Now I am actually going to add this as a
period and a space and go ahead and say
| | 03:43 | OK to this, and if I run it now, we
have got a new field that combines the
| | 03:48 | first initial of the person's
first name with the last name.
| | 03:52 | By combining some of these functions
into our queries, we can come up with some
| | 03:54 | interesting new ways to present our results.
| | 03:56 | When feeding our query results into a
report or simply reviewing the data sheet,
| | 04:01 | the appropriate and creative use
of string functions can increase the
| | 04:04 | legibility of our data.
| | Collapse this transcript |
|
|
5. Using the Expression BuilderThe Expression Builder interface| 00:00 | In the last chapter, we took a look at
how we can use mathematical and some text
| | 00:04 | based expressions, in order to
build functionalities into our queries.
| | 00:07 | Now you might have noticed that that
included a lot of typing where we had to
| | 00:09 | type out the field and table
names that the titles were coming from.
| | 00:12 | We can use what's called the Expression
Builder to minimize some of that typing.
| | 00:15 | Let's go up into our Create tab and
again we are going to go to Query Design and
| | 00:19 | we are going to go ahead and use the
Expression Builder to recreate the query
| | 00:21 | from the last movie.
| | 00:22 | We are going to add our Employees
table and close the Show Table window.
| | 00:26 | I am going to right-click in the first
field and this time instead of going to
| | 00:29 | Zoom, I am going to go to Build.
| | 00:30 | Now there is another way we can invoke
the Expression Builder and that's using
| | 00:33 | the Build button up here on the Ribbon.
| | 00:35 | Either way, go ahead and
start up the Expression Builder.
| | 00:38 | Now the Expression Builder window has
several panes to it and the first one
| | 00:42 | up here at the top, functions just like
the Zoom box that we have been working with.
| | 00:45 | We could type in our functions here and
Access will put that into the field down here.
| | 00:49 | We also have some options for the
mathematical operators that we looked at.
| | 00:53 | We've got Concatenate
represented here with the ampersand.
| | 00:55 | We also have our comparison
operators and our logical operators.
| | 00:58 | In the bottom half of the window,
we have access to go into all of our tables,
| | 01:02 | queries, forms, reports and we also
have some built-in functions that we will
| | 01:06 | look out in the next movie.
| | 01:07 | For now, let's just drill into the
Tables folder and you will notice that we
| | 01:10 | have a list of all the tables
that are currently in our database.
| | 01:13 | I am going to go into our Employees
table and the second pane populates with all
| | 01:17 | of the fields from the Employees table.
| | 01:18 | So let's rebuild the expression
that we built in the last movie.
| | 01:21 | I am going to choose our FirstName and
we are going to use Concatenate to join
| | 01:26 | that to our LastName.
| | 01:26 | So I simply double-click on FirstName,
click on the Concatenate ampersand symbol,
| | 01:31 | and add that to our LastName.
| | 01:31 | Now as we learned in the last movie,
this is going to join the FirstName
| | 01:34 | directly to the LastName and
we want a space in between.
| | 01:37 | So we are going to add our space in
there, using quote space quote to indicate
| | 01:41 | that we are literally adding a space
in between those two, and we are going to
| | 01:44 | join that to our last name
using a second ampersand symbol.
| | 01:47 | Once we say OK, Access drops that
down into our field and it gives it an
| | 01:51 | Expression 1 as the alias. We can name
this again Full Name and now when we run
| | 01:57 | our query, we get the full name of our
employee all put together in one field.
| | 02:02 | As you get more experience writing out
the expressions that you want to use, you
| | 02:05 | might find it quicker just to write
them out by hand directly in the QBE grid.
| | 02:08 | But for learning the proper syntax,
troubleshooting or exploring and discovering
| | 02:12 | how new functions work,
the Expression Builder is a fantastic tool.
| | Collapse this transcript |
| Exploring some built-in functions| 00:00 | Using the Expression Builder, not
only do you have an easy way to reference
| | 00:03 | every record of every table, form or
query, you also have a list of all the
| | 00:07 | built-in functions within Access.
| | 00:08 | Let's take a look at some of these.
| | 00:09 | We're going to go ahead and create a
new query and we are going to go into
| | 00:12 | Design View again and we
will choose the Products table.
| | 00:15 | Let's go ahead and close Show Table.
| | 00:17 | I am going to right-click on the
first field and choose Build, open up
| | 00:20 | the Expression Builder.
| | 00:21 | We have seen how we can go into the
Tables folder and select from all of the
| | 00:24 | tables within our database.
| | 00:25 | We can also go into this Functions
folder down here and when I open that up,
| | 00:29 | I've got two options. One
of them is Built-In functions.
| | 00:31 | When I open up that folder, I get
a list of all the function groups
| | 00:34 | available within Access.
| | 00:35 | We can take a look at some of these.
| | 00:36 | We have got functions that deal with
conversions, so we can move from one data
| | 00:39 | type to another or reformat some of our data.
| | 00:42 | We have got some functions that deal
with Date and Time and we are going to have
| | 00:44 | a whole chapter on Date
and Time functions coming up.
| | 00:46 | We also have a group being called
Financial and this is a list of Financial
| | 00:49 | Calculations such as
calculating rates of return.
| | 00:52 | Now, at anytime if we want to learn
how one of these functions we can simply
| | 00:55 | select it from the list and then click on Help.
| | 00:57 | Now the Microsoft help documents
have gotten much better over the years.
| | 01:00 | When you click on Help in the Expression Builder,
| | 01:02 | it actually jumps right to the help
document for that specific expression.
| | 01:05 | So I can click on Rate and we get
some help here that shows that the Rate
| | 01:08 | functions has five arguments and the
help document also defines the variables.
| | 01:13 | We have some information whether they
are required or not, what kind of data
| | 01:16 | type that they expect, and we also have some
remarks about how we can use that function.
| | 01:20 | Let's go ahead and close the help document.
| | 01:22 | We also have some mathematical
functions and we can use some of these, for
| | 01:25 | instance, we can use this to round our data.
| | 01:28 | The Program Flow group contains some
functions that allow us to automate some of
| | 01:32 | our processes and we will look
at those in an upcoming chapter.
| | 01:34 | We also have some aggregate functions
that we can use and this should look
| | 01:37 | familiar to you. These are the same functions
that are available through the Totals field.
| | 01:41 | So let's go ahead and see how we can
use this in our Expression Builder.
| | 01:43 | I am going to go ahead and select
Average from the list by double-clicking on it
| | 01:47 | and we can see that Access populates
the Expression Builder statement with the
| | 01:50 | syntax that's required.
| | 01:51 | So we've got Average and we have got an
expression that's wrapped in parenthesis.
| | 01:54 | So Access is helping us out here a little bit.
| | 01:56 | It's telling us that it expects us to
provide an expression that it is going to
| | 01:59 | go ahead and Average.
| | 02:00 | So I can go ahead and click on
expression and it highlights this bit of text,
| | 02:04 | and now we can go into one of our
tables, let's say our Products table, and
| | 02:07 | choose price and if I double-click on price,
it will replace that bit of text with
| | 02:12 | link to the price field in our Products table.
| | 02:15 | I can go ahead and say OK and it
populates that expression down into our field.
| | 02:19 | Now it also gives us an alias here. We
can replace this with Average Price and
| | 02:25 | now when we run our query, we get the
Average Price for all of our products.
| | 02:28 | So not only is the Expression Builder
great tool for helping you construct valid
| | 02:32 | expressions, it's a fantastic resource
for expanding your understanding of all
| | 02:36 | the functions that Access brings with it.
| | 02:38 | The Expression Builder will quickly
let you browse the list and learn how and
| | 02:41 | why to use every function at your disposal.
| | 02:43 | It's well worth the time investment to
peruse the list of available functions
| | 02:46 | and think about how they can be
used within your own workflow.
| | Collapse this transcript |
|
|
6. Working with Dates in QueriesUnderstanding dates as serial numbers| 00:00 | You would think that storing dates
in your database would be a fairly
| | 00:02 | straightforward and routine operation.
| | 00:04 | On the surface, for you and I it is,
but behind-the-scenes Access does a lot of
| | 00:08 | work to make sure that your date-
specific records are kept in order.
| | 00:11 | Understanding how Access stores,
retrieves and formats date and time-based data
| | 00:14 | can really help down the road, and
there are a lot of functions built in that
| | 00:17 | deals specifically with dates and times.
| | 00:19 | So let's take a look at how Access
understands a date field versus a field that
| | 00:22 | includes both the date and the time.
| | 00:24 | So we have got a simple table
here and we want find out how Access
| | 00:27 | understands the relationship between
these three dates, May 11, May 12, and
| | 00:30 | May 13, as they relate to May 12.
| | 00:33 | May 11, Access understands that is
less than May 12 or before May 12.
| | 00:36 | Access has also understood that May
12 is equal to May 12 or that May 13 is
| | 00:41 | greater than or after May 12.
| | 00:42 | So there are no real big surprises there.
| | 00:45 | Now let's see what happens when we include
a field that includes both dates and times.
| | 00:48 | So right now it's May 11, 11:59 and 59
seconds, or one second before midnight.
| | 00:53 | Access understands that this
is less than or before May 12.
| | 00:57 | At the stroke of midnight Access says
that we are equal to or exactly at May 12,
| | 01:01 | and one second later, so one second
after midnight on May 12, Access thinks that
| | 01:05 | this is greater than May 12.
| | 01:06 | So there is a slight discrepancy
between the way that Access understands dates,
| | 01:10 | versus dates and times
in relation to each other.
| | 01:12 | Let's go into Access and take a look
at some data tables to see why this is.
| | 01:15 | I have got a couple of sample queries setup.
| | 01:18 | I am going to go ahead and
open up this query qry_DateSerial.
| | 01:19 | So what this query is doing is taking
the dates from our Dates table and it's
| | 01:25 | also reformatting them into the
serial number that Access understands and
| | 01:28 | stores in its database.
| | 01:29 | You can see that at the top of our
list, all the date serial numbers are
| | 01:33 | positive and they get
smaller as we go back in time.
| | 01:35 | When we get into the early
1900s we're down to single digits.
| | 01:38 | We will see that December 31st, 1899
is actually the serial number of one.
| | 01:43 | December 30th, 1899 is represented by
the serial number zero and anything before that
| | 01:47 | gets increasingly negative.
| | 01:49 | So the serial number representation
of a date is a whole digit, whether
| | 01:52 | positive or negative.
| | 01:53 | Let's go ahead and take a look at how
we represent times as a serial number.
| | 01:56 | I have got a query setup
here called qry_TimeSerial.
| | 02:00 | And I am going to go ahead and open
that up and I've got a list of times here,
| | 02:03 | ranging from 12 midnight, which is
represented by the serial number .0.
| | 02:07 | Halfway through the day we are at
12 noon and that's represented by the
| | 02:10 | decimal .5. Six hours later at 6 p.m. we are
three quarters of the way through the day.
| | 02:14 | So it's represented by .75, and all the
way at the end of the day we have got
| | 02:18 | 11:59 and 59 seconds, which is one
second before midnight and that's represented
| | 02:22 | by the serial number .999988.
| | 02:25 | So if you think about times as
fractions of a day, this seems to make sense.
| | 02:29 | So we get a whole number that
represents the day and the decimal that
| | 02:32 | represents the time.
| | 02:33 | Now for looking at these serial numbers,
we see that the zero is at the very
| | 02:36 | beginning here, and we just saw a
moment ago that the zero represents
| | 02:38 | December 30th, 1899.
| | 02:40 | I have got another query here that
extracts the date for the time-based values
| | 02:43 | and if I run that we can see indeed
that we're getting the date of 12/30/1899.
| | 02:48 | Now obviously you are never going to
extract a day from the time, but if I take
| | 02:51 | a look at how this relates to our dates serials,
| | 02:53 | for instance, if I try and extract
the time from our date, this will make
| | 02:56 | a little more sense.
| | 02:57 | So Access is understanding that the
serial number is a whole number, which
| | 03:01 | is actually .0, which gives us the hour zero,
and the minute zero or exactly 12 midnight.
| | 03:06 | So this is why we have a slight
discrepancy when we are sorting our dates.
| | 03:09 | If we have just a date value, it's
understanding that as 12 midnight and if
| | 03:12 | we are including a time value, that's
understanding that as a fraction of the
| | 03:15 | day past the date.
| | 03:17 | Now as a best practice, it's always a
good idea to keep your dates and times
| | 03:20 | separated into two separate fields.
| | 03:22 | Because the date is represented by a
whole number and the time is represented by
| | 03:26 | a fraction, it's easy to add
them together again when needed.
| | Collapse this transcript |
| Specifying a range of dates or times| 00:00 | Because Access stores dates as serial
numbers, it becomes very easy to perform
| | 00:03 | date comparisons or to define ranges of dates.
| | 00:06 | We have seen these comparison
operators before, but I thought it would be
| | 00:09 | valuable to review them and see how
they can be applied specifically to
| | 00:12 | working with dates.
| | 00:13 | I am going to go ahead and
create a new query in Design View.
| | 00:16 | I am going to go ahead and add our
Orders table and we are also going to add
| | 00:20 | our Customers table.
| | 00:21 | Let's go ahead and close the Show
Table window and I want to populate our
| | 00:25 | query with the first and last name of
the customer and the date that they
| | 00:29 | placed their order.
| | 00:30 | In the Criteria for the date, I want to
specify a specific date that I am interested in.
| | 00:33 | For instance, I'm
interested in January 9th, 2010.
| | 00:37 | Now when I press Enter to accept this
criteria, Access adds a little bit of code.
| | 00:40 | So just like we are entering a text
criteria and Access adds a quotation marks
| | 00:43 | around it, when you are entering a date,
| | 00:45 | Access is going to wrap it in pound symbols.
| | 00:48 | Let's go ahead and say Run, and we
will see the five customers that placed
| | 00:51 | orders on January 9/20/2010.
| | 00:53 | Let's go ahead and go back to our Design View.
| | 00:54 | We can also use comparison operators
with our Date Criteria. So let's go ahead
| | 00:58 | and take a look ahead at all the orders
that are placed before 1/13/2005, I was
| | 01:00 | going to write less than 1/13/2005 and say Run.
| | 01:05 | We have got a total of five orders
that were placed before 13th and we notice
| | 01:09 | that our results don't return the 13th.
| | 01:10 | Let's go ahead and return to our Design View.
| | 01:14 | So let's change this up, and we
are going to make it greater than or
| | 01:16 | after January 13th, 2005.
| | 01:17 | I run the query again and we will see all the
customers that have placed orders after the 13th.
| | 01:23 | But notice again that we actually don't
return any orders that were placed on the 13th.
| | 01:25 | Let's go back into our Design View.
Because it's often a matter of convenience
| | 01:29 | to include the date that we are
specifically interested in our criteria, one the
| | 01:33 | things we can do is change this up to
a greater than or equals to statement.
| | 01:37 | That way when we run it, the date that
we are actually asking for also appears
| | 01:40 | in the query results.
| | 01:41 | Let's take a look at another
example of how we can use dates.
| | 01:45 | Back in Design View, I am going to get
rid of this and we can specify a range
| | 01:49 | of dates by using a between and
statement. So I can write between, the first
| | 01:53 | date that I am interested, let's say
January 1st, 2010, and then the last date
| | 01:59 | that I am interesting in, 1-31-2010.
| | 02:02 | So essentially the entire month of
January in 2010. Let's go ahead and run
| | 02:06 | this query, and you will notice that
when we use "between and," not only do we
| | 02:10 | get the dates between the two that we
specify, but we'll also get the dates
| | 02:13 | that we actually asked for.
| | 02:14 | So January 1st. If we scroll to
the bottom, we also get January 31st.
| | 02:18 | So it's a little bit different than
the results that we would have gotten if
| | 02:20 | we'd used our comparison operators to specify
dates greater than the 1st and less than the 31st.
| | 02:25 | Using dates and date ranges in a
query's Criteria field is a very common way to
| | 02:29 | return the most significant data or to
limit the returns to only the most recent
| | 02:33 | and relevant time periods.
| | Collapse this transcript |
| Using the Format function| 00:00 | There are thousands of ways that
dates can be valuable in your database and
| | 00:03 | there are just as many variations on
how they can be formatted or displayed.
| | 00:07 | They can be written longhand for use
on the business letter, or they can be
| | 00:10 | abbreviated for use on a payroll check.
| | 00:12 | They can be incorporated in the serial,
or production, or batch ID numbers, or
| | 00:16 | they can be used for
quarterly financial reporting.
| | 00:18 | Because Access stores dates as serial
numbers, as we saw in a previous movie,
| | 00:22 | we have an infinite number of ways
that we can display that data.
| | 00:25 | One of the ways that we can do
this is using the Format function.
| | 00:27 | So we'll go ahead and create a new
query in our Design View again, we'll choose
| | 00:32 | the Orders table, and
close the Show Table window.
| | 00:34 | So we're going to add OrderDate to our
query, and we're also going to add OrderID.
| | 00:38 | In the third column, I'm going to right
-click, and we're going to open up the
| | 00:41 | Zoom window, so we have more room to type.
| | 00:42 | I'm also going to change
the font size up a little bit.
| | 00:45 | Okay, the first thing I want to do is
type in an alias for this new field, and
| | 00:48 | we're going to call this Formatted Date.
| | 00:50 | I'm going to end that with a colon, so that
Access knows that this is the name of the field.
| | 00:55 | We're going to start
writing our Format function.
| | 00:57 | We're going to write the word
Format and an open parenthesis.
| | 01:00 | Now Format requires two arguments.
| | 01:02 | First you need to know what we want to format.
| | 01:04 | We're going to go ahead and format
the data that's down in our OrderDate
| | 01:07 | field of the tbl_Orders.
| | 01:09 | So let's go ahead and
add that to our expression.
| | 01:11 | We're gong to open with a square bracket,
tbl_Orders, and the field name is OrderDate.
| | 01:19 | Now as we continue typing, the Zoom
box isn't wide enough to hold all this.
| | 01:24 | So it's going to wrap the text around.
| | 01:25 | But this is okay because everything is
going to actually you put on the single line.
| | 01:29 | So once we tell Access what we are
formatting, we need to tell Access how to format it.
| | 01:33 | So since we're done with the first
argument, we're going to put a comma in and
| | 01:36 | then the next part is how to format it.
| | 01:38 | Now Access uses a couple of
variables in order to define the formatting.
| | 01:41 | We're going to use the lowercase
letter m to define how we format a month.
| | 01:45 | If we use a single m, Access is going
to format the month with a single or
| | 01:48 | double digit number.
| | 01:49 | so 1 for January or 10 for October.
| | 01:52 | If we use two lowercase Ms together,
Access is going to format the month with
| | 01:55 | a two digit number.
| | 01:56 | So 01 for January, and 10 for October.
| | 01:59 | If we use three lower case Ms together,
Access is going to abbreviate our month
| | 02:03 | with the three letter abbreviation
for the month name and if we use four Ms
| | 02:06 | together, Access is going to format
the month with the entire month name.
| | 02:10 | So I'm going to end this with a
quotation mark and the closing parentheses to
| | 02:13 | finish off our Format function.
| | 02:14 | I'm going to say OK.
| | 02:15 | We'll just take a look at
how Access deals with this.
| | 02:17 | So we see in our Formatted Date field,
we can have the month formatted as
| | 02:21 | four different ways.
| | 02:22 | We've got a single digit, we've got
the double-digit, we got the three letter
| | 02:25 | abbreviation, and we have the full name.
| | 02:27 | Let's go ahead and go back into our
Design View and go back into our Zoom box.
| | 02:31 | Now we can replace these Ms with the letter
D and D indicates how we format our days.
| | 02:37 | We've got the same options here.
| | 02:39 | We can either use a one-digit day, we
can use a two-digit day, we can use a
| | 02:43 | three letter abbreviation for the day,
and we can use the full name of the
| | 02:46 | day by using four Ds.
| | 02:47 | Let's go ahead and say OK and run that again.
| | 02:50 | We can see that we have the
date formatted in different way.
| | 02:52 | We have a one or two-digit
dates, the two-digit date,
| | 02:55 | so if we scroll down, we can find some
that are having 1 or 01, three-letter
| | 02:59 | abbreviation, or the full name.
| | 03:01 | So let's go back into the Design View and
make this actually represent real data.
| | 03:04 | Let's go ahead and do a Zoom.
| | 03:06 | So I'm going to put this all together, and we
can format our days in a couple of different ways.
| | 03:11 | We can be really concise
and a little bit trendy.
| | 03:14 | We can format our day with m.d.yy,
to represent a two-letter year.
| | 03:20 | Let's go ahead and say OK and Run.
| | 03:22 | We can see we have a concise formatting on
our date with a period used as a separator.
| | 03:26 | One more time, we'll go into the Zoom box and
we'll change our day a little bit differently.
| | 03:30 | We'll see that Access understood what
we meant to type in and it changed our
| | 03:33 | code a little bit to match
its syntax standards here.
| | 03:36 | So that's where the slashes come from.
| | 03:38 | Let's go ahead and format our
date a little bit differently.
| | 03:40 | This time we can make it into a standard
that's a little bit more common in Europe.
| | 03:44 | We're going to put the day first.
| | 03:45 | We could say dd mmm yyyy.
| | 03:50 | Now this is going to return a two-
digit date, a three-letter abbreviation for
| | 03:53 | the month, and then a four-digit year.
| | 03:55 | We can run that, and see we
have now 10 Jan 2005, for example.
| | 04:00 | One more example here.
Right-click, go back into Zoom.
| | 04:03 | Now let's be as verbose as possible.
| | 04:05 | Let's go ahead and return a
date that looks like this.
| | 04:08 | Four Ds to represent a full day, comma,
four Ms to represent the full month, two more
| | 04:15 | Ds to represent the day in numbers, comma,
and four Ys to represent a four-digit year.
| | 04:20 | Let's go ahead and run that.
| | 04:22 | We can see we've got a date
formatted as Monday, January 10th, 2005.
| | 04:26 | So as you can see, the Format function
can be really flexible in how you display
| | 04:30 | your dates and it can satisfy almost
any reporting need that you may require.
| | Collapse this transcript |
| Using other Date/Time functions| 00:00 | Typically, dates and time store within
your database are going to be very specific.
| | 00:05 | Dates are usually specific to a
particular day and times are typically
| | 00:08 | precise to the minute.
| | 00:09 | But when it comes to evaluating or
aggregating your data, you might want to look
| | 00:13 | at it a little less granularly.
| | 00:14 | When it comes time to query your data
by month and year, Access provides a few
| | 00:18 | simple ways to extract just the level
of precision that you need from your
| | 00:20 | date and time fields.
| | 00:21 | So we'll go ahead and create
a new query in Design View.
| | 00:23 | I'm going to add our Orders table, and
I'll go ahead and close the Show Table window.
| | 00:26 | Now we're going to add two
instances of our OrderDate.
| | 00:29 | I'm also going to turn on our
Totals row and I'm going to change the
| | 00:33 | first Total to Count.
| | 00:35 | If I run this query, we're going to
get a list of the number of orders that
| | 00:39 | were placed on each day.
| | 00:40 | We can see that some days had multiple
orders and some days only had one order.
| | 00:43 | Let's go ahead and go back into our Design View.
| | 00:46 | So if I wanted to organize my
database off of a different level of detail,
| | 00:49 | for instance, I want to group
everything by a specific month, I can use a
| | 00:52 | function to tell Access to do this.
| | 00:54 | Let's go ahead and right-click on our second
OrderDate column and open up the Zoom box.
| | 00:58 | At the very beginning, I am going to
type it a new alias and I'm going to
| | 01:01 | call this Grouping.
| | 01:02 | I'm going to use a function for Month.
| | 01:04 | I'm going to wrap our OrderDate
field in square brackets to tell Access
| | 01:08 | that this is the data
that we're going to be using.
| | 01:10 | So now we're going to be extracting
just the month from the OrderDate.
| | 01:14 | Go ahead and say OK and run our query again.
| | 01:16 | You can see that we get a numeric code
that represents the month that we're in.
| | 01:19 | So for instance, in the month of
January we sold a total of 199 products.
| | 01:23 | For the month of December we sold 151.
| | 01:26 | Now this is grouping all January's together.
| | 01:28 | So it doesn't matter if it's in
January 2005 or January 2010. All the January
| | 01:32 | dates are getting looped together in this view.
| | 01:34 | We can go back into our Design View
and do the same thing for year.
| | 01:37 | This time we're going to type
it directly into the field here.
| | 01:39 | So I'll expand this open a little bit.
| | 01:40 | We're going to put a new alias here.
| | 01:42 | We're going to call this one Group by Year.
| | 01:43 | We're going to use the Year function.
| | 01:46 | I'm going to pull the year out of
our OrderDate, finishing with a square
| | 01:50 | bracket, and a closing parenthesis.
| | 01:52 | I'm going to go back to our first one here
and specify that this is Group by Month.
| | 01:56 | So we can tell the difference between the two.
| | 02:00 | Now when I run our query, we'll see
that now we have all of our January
| | 02:03 | 2005s grouped together.
| | 02:05 | We can see that we have a
total of 29 orders in January 05.
| | 02:07 | But let's go ahead and sort
this little bit differently.
| | 02:10 | I'm going to sort the
Month from smallest to largest.
| | 02:13 | Then we'll sort the Year
from smallest to largest.
| | 02:16 | Now we can get a better look at our
data, so we have everything in order from
| | 02:19 | January to December 2005, and then
January to December 2006 and so on.
| | 02:22 | So this is how we would use the Month
and Year functions to format our dates.
| | 02:26 | We have similar functions for day, so that
will be a Day and then parenthesis, or
| | 02:30 | Weekday and then parenthesis.
| | 02:33 | There are also functions that do the
same thing for times, for hour, minute, and
| | 02:37 | second, and they will work exactly the same way.
| | 02:39 | So in order to maintain flexibility,
it's always best to store data based on the
| | 02:42 | finest level of detail that
you can ever conceivably require.
| | 02:46 | Typically, this is going to be a
date specific to the day and a time
| | 02:48 | specific to the minute.
| | 02:49 | Using queries, it's easy enough to
strip away all the fluff based upon the
| | 02:52 | specific task that you're working with and
when you don't need that level of detail.
| | 02:56 | With these Date and Time functions,
Access makes it easy to see exactly what you
| | 02:59 | need and nothing that you don't.
| | Collapse this transcript |
| Defining today's date| 00:00 | So far we've taken a look at how we can
work with dates relative to each other,
| | 00:03 | or relative to a specific and static date range.
| | 00:06 | Access provides us with a couple of
additional functions that will allow us to
| | 00:10 | define dates relative to
today, or relative to right now.
| | 00:13 | This is useful for all queries that
require a date range such as within the last
| | 00:17 | 30 days, or two weeks from today.
Dates with a specific relationship to the
| | 00:21 | current date and time.
| | 00:23 | The first part of this requires us to
accurately define today's date and time.
| | 00:26 | In the next movie, we'll take a look
at how we can combine this with some
| | 00:28 | additional functions to perform some
calculations to define a timespan relative to today.
| | 00:33 | Once again, we're going to go ahead
and create a new query. Design View.
| | 00:37 | I'm going to add our Orders table,
and close the Show Table window.
| | 00:40 | Now the first function I
want to show you is called Date.
| | 00:43 | If we just write the word Date, an open
parenthesis, and then immediately close
| | 00:47 | it with a close parenthesis, and
then run this query, Access is going to
| | 00:51 | return today's date.
| | 00:52 | You can see that I'm
recording this movie on July 19, 2010.
| | 00:56 | So let's go ahead and go
back to our Design View.
| | 00:58 | I'm going to change the Expression 1 alias to Today.
| | 01:01 | We also have a function called Time and
that's going to return the current time.
| | 01:05 | So I'm going to write Time as an alias,
and then I'm going to write the Time
| | 01:08 | function, which is followed by an
open and then a closed parenthesis.
| | 01:11 | Finally, we have a function that
will return both the date and the time.
| | 01:15 | I'm going to call this one Now as
an alias and the function is actually
| | 01:19 | called Now as well.
| | 01:20 | So I'll write Now, open and
then close the parenthesis.
| | 01:22 | Let's go ahead and run these again.
| | 01:25 | We'll see that, a little more
specifically, not only is it July 19, 2010, but
| | 01:30 | it's currently 1:10 and 50 seconds PM.
We can see the Now column combines both of
| | 01:34 | these fields into one.
| | 01:35 | Let's go ahead and switch into
Design View and wait a couple of seconds.
| | 01:39 | And then if we switch back,
we'll see that has been updated.
| | 01:42 | It's now 1:11 and 12 seconds.
| | 01:44 | Now if we wait a long enough, we
would also see the date roll over, but that
| | 01:47 | would make a really long movie.
| | 01:48 | So you are just going to
have to trust me on this one.
| | 01:50 | Let's go ahead and switch back into Design View.
| | 01:53 | Now we can combine these three
functions with some of the formatting options
| | 01:57 | that we saw previously.
| | 01:58 | I'm going to go ahead and delete
these two fields from my query.
| | 02:00 | I'm going to highlight that and
this time I'll press Delete Columns.
| | 02:03 | I'm going to combine our Format
function with today's current date.
| | 02:07 | We can do that by writing Format, open
parenthesis, and let me expand this out
| | 02:11 | so we can see what we're doing.
| | 02:13 | If you remember, the Format
function requires two arguments.
| | 02:15 | First we need to know what we're formatting.
| | 02:17 | So right now, we're
going to format today's date.
| | 02:19 | So I'm going to follow that with a comma.
| | 02:20 | The second thing we need to provide is to
tell Access how we're going to format this date.
| | 02:24 | So let's say I want to find out today's day.
| | 02:27 | I will write four Ds to give us a full
name of the day, wrap that in quotation
| | 02:30 | marks, and then finish my Format
function with a closing parenthesis.
| | 02:34 | Let's go ahead and run that query.
| | 02:35 | We'll see that today is in fact Monday.
| | 02:37 | So we can combine formatting with our
Date, Time, and Now functions in order to
| | 02:42 | give us specific information
about today's date and time.
| | 02:44 | Now that we can accurately define
the current time, we can use this
| | 02:47 | functionality to help us define
date ranges relative to today.
| | 02:51 | We'll take a look at that in the next movie.
| | Collapse this transcript |
| Working with time intervals| 00:00 | Now that we know how to tell Access
how to return today's date and current time,
| | 00:03 | we can use that information to help
obtain query results that are relative to today.
| | 00:07 | We have two different functions that
will return slightly different results
| | 00:09 | depending on the type of
question that we're going to ask.
| | 00:12 | The first one is DateAdd.
| | 00:14 | This will allow us to specify a
time frame such as the past 30 days, or
| | 00:17 | within the last year.
| | 00:18 | It will return records within that time frame.
| | 00:20 | Let's go ahead and take a
look at how we can use DateAdd.
| | 00:23 | We're going to start a new query in Design View.
| | 00:25 | I'm going to add our Orders table and we're
going to go ahead and close the Show Table window.
| | 00:30 | In the first field, I'm going
to right-click and say Zoom.
| | 00:32 | We're going to take a
look at the DateAdd function.
| | 00:35 | The first thing I'm going to do is
add an alias to our field header.
| | 00:37 | We're going to call this 30
Days Ago, followed by a colon.
| | 00:44 | Now the DateAdd function
has three required parts.
| | 00:48 | The first part is called the interval
and this is going to be the units that
| | 00:51 | we're adding or subtracting from our date.
| | 00:52 | So this is where we tell Access whether we
want to add or subtract days, months, or years.
| | 00:58 | The second part is called number and
this is going to tell Access how many of
| | 01:01 | the interval that we're
going to add or subtract.
| | 01:03 | So, how many days, months, or years?
| | 01:05 | The final part is the Date.
| | 01:07 | This is the date that we want to
add or subtract the values from.
| | 01:09 | Now we can supply this with a
date from one of our tables,
| | 01:12 | for instance, the OrderDate from the
tbl_Orders, or we can specify today's date
| | 01:15 | by using the Date function with
an open and then close parenthesis.
| | 01:17 | Now we need to tell Access
where our interval is going to be.
| | 01:21 | We're looking at 30 days ago, so our
interval is going to be a d for day.
| | 01:25 | The number that we want
to add or subtract is 30.
| | 01:27 | But since we're subtracting it,
we would need to put a negative.
| | 01:29 | Let's go ahead and say OK and run our query.
| | 01:33 | Now earlier I told you that I'm
recording this movie on July 19th, 2010.
| | 01:37 | So a day 30 days ago will be June 19th, 2010.
| | 01:39 | That's the result that we get.
| | 01:41 | Let's go ahead and go
back into our Design window.
| | 01:43 | I'm going to right-click and go back to Zoom.
| | 01:45 | Now there are a couple of
different variables that we can use here to
| | 01:49 | specify different units.
| | 01:50 | We're seeing how we can use d for day.
| | 01:52 | We can also use w if we
wanted to add or subtract weeks.
| | 01:55 | We could use m if we
wanted to add or subtract months.
| | 01:58 | If we wanted to add or subtract years,
it's similar, but we need to put four Ys
| | 02:02 | to tell Access that we're
going to add or subtract a year.
| | 02:04 | So I'm going to change this
back to a d. I'm going to say OK.
| | 02:07 | I'm going to expand this window open a
little bit more so I can see what we're doing.
| | 02:11 | I'm going to take this and I'm
actually going to copy this out.
| | 02:14 | So I'm going to highlight the whole
thing and press Ctrl+C. Now I'm going to
| | 02:18 | delete this field because we're
going to do something slightly different.
| | 02:20 | So I'm going to delete this column.
| | 02:22 | Now I want to create a query that will
select all of the orders that were placed
| | 02:26 | within the last 30 days.
| | 02:27 | I'm going to make this query using the
OrderID and then an OrderDate field.
| | 02:32 | For the Criteria of the OrderDate, I want to
specify the same formula that we just created.
| | 02:35 | So I'm going to say greater than,
and then I'm going to paste in the
| | 02:38 | formula that we created.
| | 02:39 | Let me expand this out so we can read it.
| | 02:42 | So I'm saying the Criteria for our
OrderDate is going to be every day that's
| | 02:46 | DateAdd 30 days ago.
| | 02:49 | So my Criteria is going to be every
OrderDate that's greater than and then this
| | 02:52 | is the formula for 30 days ago.
| | 02:54 | Let's go ahead and run this
query and see what we get.
| | 02:57 | We can see that we get a total of 19
orders that were placed within the last 30 days.
| | 03:01 | So I want to remind you that this 30
days ago is relative to today, the day that
| | 03:05 | I'm recording this movie.
| | 03:06 | If you're following along with the
Exercise Files, your results might differ
| | 03:09 | slightly as you'll be
running query in a different day.
| | 03:11 | In the next movie, we'll take a look
at a similar function called DateDiff.
| | Collapse this transcript |
| Using the DateDiff function| 00:00 | In the last movie we took a look at how
we can use the DateAdd function to allow
| | 00:03 | us to specify a date in the past.
| | 00:05 | I'm going to go ahead and open up the
query that we are working on, and that is
| | 00:07 | called to qry_RecentOrders.
| | 00:08 | And if I go ahead and view it in Design
View, I'll open up our field and we can
| | 00:14 | take a look at the Criteria.
| | 00:15 | So I specify that we want to view
order dates that are greater than and then
| | 00:19 | we're using or DateAdd function
to specify the date 30 days ago.
| | 00:22 | Now if I run this query again, I'll
take a look at it, and I know that all of
| | 00:25 | these orders were placed within the
last 30 days, but I don't know exactly how
| | 00:28 | many days ago that they were placed.
| | 00:30 | I can use the DateDiff function to tell us that.
| | 00:32 | Let's go back into Design View and
we're going to create a new calculated field
| | 00:35 | here and let's do that in the Zoom box.
| | 00:38 | The name of this field is
going to be Days Elapsed.
| | 00:41 | And the function that we're going to
using is DateDiff and this function
| | 00:46 | is similar to DateAdd.
| | 00:47 | It requires three arguments.
| | 00:50 | The first one is the Interval and
this will be same unit that we were using
| | 00:53 | before, so whether we're
looking at months, days, or years.
| | 00:56 | The second argument is our first date,
and our third argument is our second date.
| | 01:00 | So basically the two dates that
we want to compare to each other.
| | 01:02 | Let's go ahead and plug in our numbers.
| | 01:04 | Data here is going to be days, the
first date is going to be coming from
| | 01:10 | our orders table, so I can specify
that as our tbl_Orders, and our field is OrderDate.
| | 01:24 | The second date that we want to compare to is
today, so go ahead and use our Date function.
| | 01:27 | Let's go ahead say OK and
we'll run our query again.
| | 01:30 | You'll see that we now have a column
that says Days Elapsed and we can tell
| | 01:34 | exactly how many days have
passed since this order was placed.
| | 01:36 | So DateDiff and DateAdd are two of the
functions that are available to us when
| | 01:39 | we're working with date-based data.
| | 01:41 | Working with dates is an integral
part of making sense of all the data
| | 01:44 | within your database.
| | 01:45 | With a little bit of practice and
persistence, mastering date and time-based
| | 01:48 | data formats and using it to filter
all the relevant data that you need will
| | 01:52 | help make your queries
that much more successful.
| | Collapse this transcript |
|
|
7. Understanding Conditional StatementsIntroduction to the conditional IIf function| 00:01 | In this chapter we're going to take a
look at creating a new expression called a
| | 00:03 | conditional statement.
| | 00:04 | Now, conditional statements are one of
my favorite features of Access queries.
| | 00:07 | They allow you create an
automated decision-making process.
| | 00:10 | They're really flexible and very
powerful and can automate some complex tasks in
| | 00:14 | a short amount of time.
| | 00:15 | You can think of a conditional
statement as an If, Then statement.
| | 00:19 | So If A is true, then we'll return B.
Otherwise we'll return C. We can rewrite
| | 00:24 | this as If A, B, C, and this is
implied to If A, then B, then C. Putting it
| | 00:30 | into a format that Access will understand, we
have the IIF statement, which is the conditional.
| | 00:35 | So IIF and then we have three
argument,s A, B, and C. The A is stands for our
| | 00:39 | expression that we're going to
evaluate, B stands for our true part, and C
| | 00:44 | stands for the false part.
| | 00:45 | We take an expression and evaluate it
and an expression can be anything like
| | 00:49 | today equals Monday or
total sales is greater than $50.
| | 00:54 | Anything that Access can say,
yup, that's true or no that's false.
| | 00:57 | So once we evaluate the expression,
if it's true, Access will return the
| | 01:00 | true part of our function; if it's false,
Access will return the false part of our function.
| | 01:05 | When creating the condition that we
want to evaluate, we can use most of the
| | 01:08 | comparison operators that
we've seen in this course.
| | 01:10 | So that will be less than,
greater than, equal to, etcetera.
| | 01:13 | You can also use some
logical operators, AND, OR, and NOT.
| | 01:16 | In our queries, this is that how we
might type out our conditional statement.
| | 01:20 | And let's take a look at how we can read this.
| | 01:22 | The first part asks, is
August sales over $100,000?
| | 01:25 | If that's true, we'll return the value
"met sales plan." If that statement evaluates
| | 01:30 | to false, we'll say we're "below sales plan."
| | 01:32 | So let's take a look at how
this works in our data table.
| | 01:35 | Here I've got five stores. I've got
store number 101 and we can see that their
| | 01:38 | August sales is $103,588.
| | 01:41 | If we take that value and plug it into
our equation, we get a statement that
| | 01:45 | reads 103,588 is greater than 100,000,
and that statement is true, so we'll
| | 01:51 | return the true part of our
expression, "met sales plan."
| | 01:54 | For store 102, we've got an expression
that reads 125,491 is greater than 100,000.
| | 02:00 | Now that's also true.
| | 02:03 | So we can say store number 102
met their sales plan as well.
| | 02:06 | For store 103 we have an expression
that reads 98,743 is greater than 100,000.
| | 02:12 | Now that statement is actually false,
so we're going to skip to the end and
| | 02:15 | we're going to put in our false
part expression, below sales plan.
| | 02:18 | We can finish up the other two
stores and see how they fared as well.
| | 02:22 | Now, Access can process a conditional
statement much faster that I can I can go
| | 02:25 | through the data table.
| | 02:26 | Now, I'm sure if you think about the
data that's in your database, you can come
| | 02:29 | up with a lot of instances where you
have to go through your data, review it,
| | 02:33 | take some sort of decision, and
then put that into your data table.
| | 02:36 | By using a conditional
statement we can automate that process.
| | 02:40 | In the next movie we'll go ahead and
jump into Access and we'll see how we can
| | 02:43 | use conditional statements in our queries.
w
| | Collapse this transcript |
| Creating an IIf function with the Expression Builder| 00:00 | In this video, we'll take a look at
writing our own conditional statements using
| | 00:03 | the Query Designer in Access.
| | 00:04 | We're going to use the Expression
Builder to help us generate proper syntax and
| | 00:08 | we'll use some mathematical
expressions to evaluate our data.
| | 00:10 | Let's take a look at how we could do that.
| | 00:11 | We'll go ahead and go to the Create tab,
new Query Design and I'm going to add
| | 00:15 | three tables: our Customers table, our
Orders table, and our Products table.
| | 00:20 | Go ahead and close the Show Table window.
| | 00:22 | Now, from these tables I'm going to
add a bunch of fields to our query.
| | 00:24 | First, we're going to add the OrderID,
the customer's first name and last name,
| | 00:28 | the state the customer is from, and
the price that they paid for their order.
| | 00:32 | Next, we're going to create
our conditional statement.
| | 00:34 | In the next open field I'm going to
right-click and say Build and that will open
| | 00:38 | up our Expression Builder.
| | 00:39 | Now, we're going to use this to build
an expression to determine whether or not
| | 00:42 | we need to charge sales tax to our customers.
| | 00:44 | We can find the IIf conditional
statement under the Functions folder, the
| | 00:48 | Built-In Functions, in the Program Flow
group. Double-click on IIf and it will
| | 00:53 | populate into the Expression Builder.
| | 00:54 | Now, you can see that the
IIf requires three parts.
| | 00:57 | We have the expression, the
true part, and the false part.
| | 01:00 | For expression we need to obtain
the state that the customer is from.
| | 01:02 | We can find that in the Tables,
Customers folder, and in the State field.
| | 01:08 | So I'll double-click on that and
that will add it to our expression.
| | 01:10 | Now, we don't need to
charge sales tax to everybody.
| | 01:12 | So let's say that the Two Trees Olive
Oil company operates in California.
| | 01:15 | We have a web-based business and all of
our customers order over the Internet.
| | 01:19 | We only need to charge sales tax to
the customers that actually live in
| | 01:21 | California where we're based.
| | 01:23 | So for the table state we're going to
evaluate the expression =CA, and I'm going
| | 01:28 | to wrap that in quotation marks.
| | 01:30 | So as we're evaluating our IIf statement
if the customer's state is California
| | 01:34 | then we're going to return the true part.
| | 01:36 | The part that we want to return is the
sales tax rate that we're going to charge.
| | 01:39 | In IIf we're going to say it's 8.25%.
| | 01:39 | If the customers that not live in California
we're going to return sales tax rate of zero.
| | 01:45 | So let's go ahead and say OK.
| | 01:46 | And Access actually populates an Expression 1
as our field header and we can go ahead
| | 01:51 | and rename this to Tax Rate.
| | 01:52 | Let's go ahead and run the query.
| | 01:55 | Now, we can see for all of our
customers who live in California we're getting a
| | 01:58 | value of eight and a quarter, and for
everybody else we're getting a value of 0.
| | 02:01 | Let's go into our Design
View and we'll do one more.
| | 02:03 | Right-click in the next
open field and say Build.
| | 02:07 | We'll go down into our Functions group,
Built-In Functions, Program Flow and IIf again.
| | 02:13 | This time let's say we have a bonus
program going where if you place an order
| | 02:16 | for more than $50 you get free shipping.
| | 02:19 | Our expression will look something like this.
| | 02:20 | We go into our Tables, Products and
grab the Price, and then we're going to say
| | 02:25 | it evaluates to >50.
| | 02:28 | So if price is greater than 50 we'll
return the true part and we're going to put
| | 02:31 | up a message that says "Free Shipping."
| | 02:35 | If the price of less than $50 we don't
want to return anything, we're just want
| | 02:38 | a blank cell, and we can use the
word NULL to return a blank value.
| | 02:42 | Go ahead and say OK and Run.
| | 02:45 | And I'm going to sort this by Tax Rate
and we can see that most of the people
| | 02:49 | that live in California
were ordering less than $50.
| | 02:50 | Let's scroll down and find this person,
Hunter Foster who lives in California,
| | 02:55 | so they will be paying the 8.25 sales
tax, but they also ordered over $80 worth
| | 02:58 | of products, so they get free shipping.
| | 03:00 | So there are our first
two conditional statements.
| | 03:02 | We made use of the Expression Builder
to help us format our statement properly
| | 03:05 | and to help reduce errors.
| | 03:07 | And we were also able to verify
that it returned the expected result.
| | 03:09 | I hope at this point that you
are beginning to see why I like
| | 03:12 | conditional statements so much.
| | 03:13 | Being able to instruct Access how to
make decisions on its own I think is pretty cool,
| | 03:17 | but more importantly I want you
to take a moment before moving onto the
| | 03:19 | next movie and think about all the
ways that your workflow could benefit from
| | 03:23 | using conditional statements.
| | 03:24 | I promise that once you start looking
you're going to find a ton of ways where
| | 03:27 | you can find them useful.
| | 03:28 | In the next movie, we'll take a
look at one of the other Program Flow
| | 03:31 | functions called Switch.
| | Collapse this transcript |
| Using the Switch function| 00:01 | Within the Program Flow category of
functions within the Expression Builder is
| | 00:04 | another function called Switch.
| | 00:06 | The Switch function is similar to IF in
that it allows Access to return various
| | 00:10 | responses depending on the
results of an evaluated expression.
| | 00:14 | The difference between IF and Switch is
that Switch decision tree only branches
| | 00:18 | in the false direction.
| | 00:19 | But you can have as many options as you want.
| | 00:22 | In other words, Access reads through
the function and returns the first true
| | 00:25 | condition it happens upon.
| | 00:27 | If the condition evaluates to false,
Access proceeds to the next condition and
| | 00:31 | keeps going until it gets to the end.
| | 00:32 | So let's take a look at how we
would write a Switch expression.
| | 00:35 | You start with the word Switch and
then in parenthesis we have pairs of
| | 00:39 | expressions and values.
| | 00:40 | We have an expression A and then a value A,
expression B, value B, expression C, value C, etcetera.
| | 00:47 | The decision tree would
look something like this.
| | 00:49 | We have expression A. If that's true
then Access will return value A. If it's
| | 00:53 | false, we'll move on to the next expression.
| | 00:55 | We have expression B, if that's true,
we return value B. If expression B is false
| | 01:01 | we move on down the line.
| | 01:02 | If we get all the way to the end and
get to expression C and if expression C is
| | 01:06 | false, Access will return a
blank cell or the word NULL.
| | 01:09 | So let's go ahead and apply
this in a query inside of Access.
| | 01:13 | I've got a query here called qry_Switch.
| | 01:16 | I am going to right-click on that
and open it up in the Design View.
| | 01:19 | I've got a query built here
that's actually built off of another query.
| | 01:23 | I've got Employee Name, FirstName and the
TotalSales as a calculated value in this query.
| | 01:28 | I also have a switch statement set up
here and actually instead of opening it up,
| | 01:32 | I'm going to right-click and say Zoom.
| | 01:34 | And this is how our switch
statement is constructed.
| | 01:37 | The field name is going to be Rating,
and this is part of a rewards and
| | 01:40 | recognition program that we
have set up for employees.
| | 01:42 | We are going to take a look at our
employee's total sales throughout their
| | 01:45 | entire lifetime with the company and
evaluate them based on their ranking.
| | 01:49 | So we're going to use the Switch()
function here, and we're going to
| | 01:52 | evaluate total sales.
| | 01:53 | If the total sales is greater than $1000
then we'll consider that employee as
| | 01:56 | part of a Platinum group.
| | 01:58 | If the total sales is greater than $900,
then we'll consider those employees as
| | 02:01 | part of our Gold group.
| | 02:02 | If the total sales is greater than $800,
we'll consider them part of the Silver
| | 02:06 | group and if the sales are greater than
700, they are part of our Bronze group.
| | 02:10 | So let's go ahead and say
OK and we'll run our query.
| | 02:12 | I'm going to apply a sort to our total
sales and we'll go from largest to smallest.
| | 02:17 | We will see that we have several
employees that are above $1000 for the company,
| | 02:20 | and they'll get a rating of Platinum.
| | 02:23 | Below them we have a group that's above
$900, and they'll get a rating of Gold.
| | 02:27 | We can go ahead and switch back to our
Design View and I'm going to go into our
| | 02:31 | Zoom box one more time.
| | 02:32 | So what's happening is that for each
employee, we're going to take their total
| | 02:36 | sales and plug it into the formula
everywhere you see the world TotalSales.
| | 02:39 | Then we're going to go
through and evaluate the expression.
| | 02:42 | So if their total sales is greater than
a thousand, Access will return the value
| | 02:45 | to Platinum and then move
on to the next employee.
| | 02:49 | If the total sales is less than a thousand,
then we'll move to the next statement.
| | 02:52 | If the total sales is also less then
900, we move to the next statement.
| | 02:55 | If the employee's total sales is 850
for example, we'll plug that in here.
| | 02:59 | 850 is greater than a
thousand. That's not true.
| | 03:02 | So move to the next one.
| | 03:03 | 850 is greater than 900, also not true.
| | 03:07 | We move to the next one.
| | 03:08 | 850 is greater than 800. That's true.
| | 03:11 | So that employee is part of our Silver group.
| | 03:14 | So that's how the Switch function works.
| | 03:16 | When used appropriately, both IIF and
Switch can be really valuable tools to
| | 03:20 | have in your pocket.
| | 03:21 | Anytime you can automate some routine
tasks and allow Access to make decisions
| | 03:25 | for you could be a huge
benefit to increase your productivity.
| | 03:28 | But beyond simply taking decision-
making processes off your plate, learning to
| | 03:32 | use some of the Program Flow
functions increases the reliability and
| | 03:35 | consistency of your database.
| | 03:37 | One of the biggest benefits is
that they can ultimately prevent data
| | 03:39 | inconsistencies due to the
introduction of human error.
| | Collapse this transcript |
|
|
8. Using Alternate Query TypesUsing crosstab queries| 00:00 | All the queries that we've been
working with throughout this course have been
| | 00:03 | examples of select queries.
| | 00:05 | Basically queries that select and
manipulate your data from a data tables and
| | 00:08 | present the results in a new
but temporary table format.
| | 00:12 | Access also has a couple of other
query types that we can work with.
| | 00:15 | And the first one I want to look
at is called the crosstab query.
| | 00:18 | Like the select queries, the
crosstab query pulls information from our
| | 00:21 | tables and reorganizes it.
| | 00:23 | But unlike a select query, the crosstab
query is presented more like a standard
| | 00:27 | spreadsheet with column headers and row headers.
| | 00:29 | We can take a look at this easily if we go
into our Create tab and then to Query Wizard.
| | 00:33 | You can see the second option down as
Crosstab Query Wizard and I'm going to go ahead and say OK.
| | 00:37 | Now this first window says which table
the query contains the fields you want
| | 00:41 | for the crosstab query results.
| | 00:42 | We're going to build a crosstab query
that deals with our employee information.
| | 00:45 | Let's go ahead and say Next.
| | 00:48 | The next screen asks for which fields
you want to use as our row headings and
| | 00:51 | we're going to go ahead and choose State.
| | 00:53 | When I move that over, you'll notice
that Access updates the sample down here
| | 00:56 | so, we're seeing the State is going
to be our left-hand column of our table.
| | 00:58 | Let's go ahead and say Next.
| | 01:01 | This screen asks for which values we
want to use as our column headings and
| | 01:04 | we're going to choose Departments.
| | 01:06 | Select that and you'll see
again those samples updating.
| | 01:09 | Go ahead and say Next.
| | 01:11 | Finally, we need to supply the
information that's going to go into the tabulated
| | 01:14 | area down here and by default, it's
selecting the first field and it's choosing
| | 01:17 | average of employee ID.
| | 01:19 | What I really want to do is
get our count of our employees.
| | 01:21 | So let's go ahead and select Count
and we'll see that it updates here.
| | 01:24 | I also want to include a Summary column,
so I'm going to leave this checkbox on
| | 01:27 | and I'll point out what that does in a moment.
| | 01:29 | Go ahead and say Next.
| | 01:30 | We are going to save our
query as qry_DepartmentsByState.
| | 01:31 | We'll leave this option on to view
our query and go ahead and say Finish.
| | 01:39 | And you'll see that we get a
crosstab query set up where we have States in
| | 01:42 | the left-hand column.
| | 01:43 | We've got our Departments across the top.
| | 01:45 | And we have got a grid that counts the
number of employees in each department
| | 01:47 | and lists them by state.
| | 01:49 | We also have a Summary column here
called Total Of Employee ID and this is the
| | 01:52 | total number of employees.
| | 01:54 | So for instance in Alaska, we've got a
total of five employees, two of them are
| | 01:58 | in the Customer Service department, one
is in Human Resources, and we've got
| | 02:01 | two people in Sales.
| | 02:02 | I've also got another query set up
already called AverageWageCrosstab, and if I
| | 02:07 | open that up, you'll notice that
it's in a similar format with states on the
| | 02:09 | left and our departments on the right.
| | 02:12 | The difference is that in the middle,
we are calculating the average wage by
| | 02:15 | Department and by comparing these
two tables together we can perform some
| | 02:18 | interesting analysis work.
| | 02:19 | We'll go ahead and close both of
these down and I'll go ahead and save the
| | 02:23 | changes the query we just saved.
| | 02:24 | I've got one more query set up right
now and I'll go ahead and open that up
| | 02:29 | and the name of it is qry_Crosstab, but is
currently not displaying in the crosstab format.
| | 02:33 | I can tell this is a select query
right now based on the icon and you can see
| | 02:37 | that this is the icon for a crosstab query.
| | 02:40 | This query is pulling out our OilName and
sizes and we're getting a count of the
| | 02:43 | number of times that each has been ordered.
| | 02:45 | You'll see that there is
a lot of duplication here.
| | 02:47 | We've got the word Gallon
represented 18 times because we have 18 different
| | 02:50 | oils and we've got each of our oils
listed five times because we have five
| | 02:53 | different sizes of each.
| | 02:54 | Let's go ahead and switch in to our
Design View and we're going to go to the
| | 02:57 | Home tab and then press Design.
| | 02:59 | We can update this to be a crosstab query,
which will be a much more efficient way
| | 03:02 | to display that data.
| | 03:03 | If I switch to Crosstab, you will notice
that we've got a new row down here and
| | 03:06 | we can tell Access which
fields put into which areas.
| | 03:09 | So the count of our Product ID is going
to be the value in the center of the table.
| | 03:13 | Our size is going to be our Column Headings
and our OilName is going to be the Row Headings.
| | 03:18 | Now if I run this query again to see
the results, we'll get it in a much
| | 03:20 | more efficient table.
| | 03:21 | Now I can look up and down these rows
here and see that our Light Oil is the
| | 03:24 | bestseller in the Gallon size.
| | 03:26 | Now, it would be convenient if we could
actually sort base out for these column
| | 03:29 | headers, for instance Largest to Smallest.
| | 03:30 | But Access actually throws up an error
and says that we can't sort when it's in
| | 03:33 | the crosstab format.
| | 03:34 | What we could do though is export
this into Excel and we can take a look at
| | 03:38 | some of the data there.
| | 03:38 | We can go to the External
Data tab and press Excel.
| | 03:42 | I am going to go ahead and browse,
and I'll save this in my desktop,
| | 03:46 | our Exercise Files folder and then Chapter 8.
| | 03:48 | Let's go ahead and say Save and I want
to pay attention to this checkbox right
| | 03:53 | here that says Export data
with formatting and layout.
| | 03:55 | While this is not checked, Access is
going to export the data table as if it was
| | 03:59 | just a simple select query.
| | 04:00 | So basically those three
columns that we saw earlier.
| | 04:02 | If you want to export this in the crosstab
format we need to make sure this is turned on.
| | 04:06 | Let's go ahead and say OK.
| | 04:07 | And I don't necessarily need to save the export
steps, so we can go ahead and just say Close.
| | 04:11 | Let's go into Excel and we'll
see what this data table looks like.
| | 04:14 | I am going to browse out to my Exercise 8
folder and open up the Excel file that we just made.
| | 04:19 | Now in Excel I can go ahead and click on
Gallon and I'm going to apply a filter
| | 04:23 | here, sort largest to smallest.
| | 04:25 | We'll see that the Light oil
comes up to the top in the gallon size.
| | 04:28 | So that's our best-selling gallon
product. And sort largest to smallest,
| | 04:31 | we'll see that the
Virgin oil comes to the top.
| | 04:33 | So the Virgin oil and the
small sizes are bestseller.
| | 04:36 | So depending on the types of information,
you're collecting, the crosstab query
| | 04:39 | can be a great way to visualize a
large amount of aggregated data and provide
| | 04:43 | further insight into your database.
| | Collapse this transcript |
| Finding duplicate queries| 00:01 | In the Query Wizard, we've seen that
there are options beyond the simple select
| | 00:03 | query. One of those options is
called the find duplicates query.
| | 00:07 | Now, the find duplicates query is a
specific query type that will locate
| | 00:10 | potential duplicate
records within your database.
| | 00:12 | This can be useful for cleaning up
your data tables or reviewing records
| | 00:15 | with multiple entries.
| | 00:17 | We can get to that by going up to
the Create tab and constructing a new
| | 00:19 | query with the wizard.
| | 00:20 | We're going to select the Find
Duplicates Query option and say OK.
| | 00:24 | Now we're going to take a look at our
Orders table to find out if we have any
| | 00:27 | potential duplicate orders
that are in our database.
| | 00:29 | Let's go head and say Next.
| | 00:31 | The next window that comes up
asks us which fields might contain
| | 00:34 | duplicate information?
| | 00:35 | Now, because of the way the table is
constructed, I know that OrderID is a unique value.
| | 00:38 | It's the primary key for the table.
| | 00:40 | So there is no possible way that we
could have two orders with the same ID.
| | 00:43 | But we could have two
orders of the same order date.
| | 00:46 | In fact, it's pretty likely that we'll
have multiple orders with the same order date.
| | 00:48 | We could also potentially have two
orders with the same customer ID, two orders
| | 00:53 | for the same product, or two
orders from the same salesperson.
| | 00:56 | But the likelihood that we have two
orders that are placed on the same day,
| | 00:59 | by the same customer, for the same product, and
with the same salesperson is exceedingly low.
| | 01:04 | So those are the ones that we want to look for.
| | 01:06 | Let's go ahead and say Next.
| | 01:08 | You can also include the OrderID field
as a reference, but it won't be used to
| | 01:11 | determine whether an
order is a duplicate or not.
| | 01:13 | Let's move that over and go ahead and say OK.
| | 01:15 | The next screen gives us an
opportunity to save our query.
| | 01:19 | I'm going to call this qry_DuplicateOrders.
| | 01:23 | We're going to choose the option to
view the results and we'll say Finish.
| | 01:26 | Access goes through our Orders table
and it finds two orders that are likely
| | 01:29 | candidates to be duplicates.
| | 01:31 | Now, this doesn't necessarily
indicate that we have a problem.
| | 01:33 | We could have a situation here where we
have an order that was accidentally put
| | 01:36 | into our Orders table twice, in
which case we have a duplicate entry.
| | 01:39 | But there could be a perfectly valid
reason why we have two orders that look similar.
| | 01:43 | I can see that our order date is a
couple of days before Mother's Day, and there
| | 01:47 | could be a situation where we have a
single customer who is placing an order
| | 01:49 | for a specific bottle of olive oil for
his mom, and placing a second order to
| | 01:53 | go to his grandmother.
| | 01:54 | So determining whether we have a
problem with our database might involve some
| | 01:57 | additional investigation.
| | 01:59 | Sorting out data entry errors that can
often arise with importing tables from
| | 02:02 | external sources is a
perfect use for Find Duplicates.
| | 02:05 | However, finding potential
duplicate records in your database doesn't
| | 02:08 | necessarily indicate that there is a problem.
| | Collapse this transcript |
| Finding unmatched queries| 00:01 | Over time, your database will grow and change.
| | 00:03 | Records will get added and removed.
| | 00:05 | Throughout these changes, it's
possible that records in some tables may lose
| | 00:08 | connection to related records in other tables.
| | 00:10 | If we take a look at our Relationships
window by going to Database Tools and
| | 00:13 | then clicking Relationships, we'll see
that the most common join type between
| | 00:16 | tables is the one-to-many relationship.
| | 00:18 | That means that each product in our
Products table will only be in there once,
| | 00:21 | but we can have each product in
our Orders table multiple times.
| | 00:25 | The next query type that I want to
take a look at is called Find Unmatched.
| | 00:28 | That will review two tables together.
| | 00:30 | It will find all the records
in one that aren't in the other.
| | 00:32 | For instance, all the products in our
Products table that haven't had an order
| | 00:35 | placed for them, or going the other way,
all of the orders placed for products
| | 00:39 | that don't exist in our Products table.
| | 00:41 | Now, if we review the relationship
between these two tables by double-clicking
| | 00:44 | on the join line, we'll see that
we're Enforcing Referential Integrity
| | 00:47 | between these two tables.
| | 00:48 | What that means is that it's
actually not possible to have an order for a
| | 00:51 | product that doesn't
exist in our Products table.
| | 00:53 | So we won't find any
unmatched records that direction.
| | 00:56 | But it is perfectly likely that we
might have a product in our Products table
| | 00:59 | that nobody has yet placed an order for.
| | 01:01 | So let's go ahead and take a look at our
unmatched query to see if this is the case.
| | 01:04 | I'm going to go ahead and
close our Relationships window.
| | 01:06 | Go to the Create tab.
| | 01:08 | We're going to create a
new query with the Wizard.
| | 01:10 | We're going to select the Find
Unmatched Query, and go ahead and say OK.
| | 01:13 | Now, the first screen asks us Which
table or query contains the records we
| | 01:17 | want in our results?
| | 01:18 | We want to find all the products
that aren't in the Orders table, so
| | 01:21 | we'll select Products.
| | 01:22 | Go ahead and say Next.
| | 01:24 | The next screen asks which table or
query contains the related records?
| | 01:27 | Here we're going to go ahead and select Orders.
| | 01:28 | One more time say Next.
| | 01:31 | Now, Access has already identified that our
ProductID occurs in both tables. So that's good.
| | 01:35 | We can go ahead and say Next.
| | 01:37 | For our query, we want to
include a ProductID and a ProductName.
| | 01:40 | Let's go ahead and say Next, and save our
query as qry_UnmatchedProducts, and say Finish.
| | 01:47 | Now, we'll see that Access has
identified a single product in our Products table
| | 01:51 | that nobody has yet placed an order for.
| | 01:53 | That's our Virgin Oil, the new 640
ounce size, which is intended for
| | 01:56 | the restaurant market.
| | 01:58 | Just like the Find Duplicates query,
finding unmatched records doesn't
| | 02:01 | necessarily mean that we have a problem.
| | 02:03 | The Find Unmatched query can be a simple
way to identify potential problem areas
| | 02:07 | and indicate avenues of further investigation.
| | Collapse this transcript |
| Working with action queries and backups| 00:00 | There are four additional types of
queries within Access and they are
| | 00:03 | collectively called action queries.
| | 00:05 | We'll take a look at them over the next
few movies, starting with the update query.
| | 00:08 | Now, an action query is different from
everything else that we have worked on so
| | 00:10 | far in one key area; they
actually modify your data tables.
| | 00:14 | In case it's not perfectly clear,
all of the queries that we've run up to
| | 00:17 | now simply presented your data,
rearranged and reformatted, in temporary
| | 00:21 | tables called record sets.
| | 00:22 | All of the actual data tables within
your database have remained unchanged.
| | 00:26 | Action queries will throw
that behavior out of the window;
| | 00:28 | they will permanently modify your data.
| | 00:30 | This can be really powerful when used
appropriately or it can make a really
| | 00:33 | big mess really fast.
| | 00:34 | So before we jump in, I want you to
take a moment to let that sink in and I
| | 00:37 | also want to walk you through the
critically important steps of creating a
| | 00:40 | backup of your database.
| | 00:41 | Luckily, Access makes this really easy,
and we can go up to the Office button,
| | 00:45 | down to the Manage button,
and select Backup Database.
| | 00:48 | Access is going to ask us where we want
to save it, and I am going to put this
| | 00:50 | in our Chapter 8 folder, but if you
have access to an off-site backup location
| | 00:54 | or remote server, you could also put it there.
| | 00:55 | You can also see that Access has appended
today's date to the end of our file name.
| | 00:59 | Go ahead and say Save.
| | 01:00 | And that's it. You've got
a backup of your database.
| | 01:03 | Let's go browse out to our Chapter 8
folder and we see that our action query's
| | 01:06 | backup is right here.
| | 01:07 | You can also take a look at the file
size and see that Access has actually
| | 01:09 | compacted our database quite a bit, but
don't worry about that, because all of
| | 01:12 | our data is still intact.
| | 01:14 | Action queries are both destructive and
irreversible, but I don't want that to
| | 01:17 | prevent you from making use of them.
| | 01:19 | Now that we're protected from any
unfortunate mishaps, we can move forward.
| | Collapse this transcript |
| Updating queries| 00:00 | The update query is one of
four action queries in Access.
| | 00:03 | Using it we can create a selection of
our records that all require a specific
| | 00:06 | update or a modification to their
data and then update all those records
| | 00:09 | throughout the entire database.
| | 00:11 | The update query not only breezes
through the task, but it will also prevent
| | 00:14 | any data entry errors that typically
crop up when you're manually modifying
| | 00:17 | large amounts of records.
| | 00:18 | Before we begin, if you haven't already
done so, please take a moment to review
| | 00:22 | the previous movie on
creating a backup of your database.
| | 00:25 | So let's say that we're thrilled with
the recent performance of all of our sales
| | 00:27 | people and we want to give everybody
in the Sales department a $5 raise.
| | 00:31 | Let's go ahead and create
an update query to do that.
| | 00:33 | We'll go the Create tab and
then a New Query Design window.
| | 00:36 | Let's go ahead and add our employees
table and from that, we're going to go
| | 00:41 | ahead and add Employee ID, First and Last Name.
| | 00:44 | Let's scroll down and select
their HourlyPay and their Department.
| | 00:48 | For our Department criteria, we're
going to specify Sales, to get everybody in
| | 00:52 | the Sales department.
| | 00:53 | Now let's go ahead and take a look at
our Datasheet View and we'll see that we
| | 00:56 | have our employee IDs, first and last
name, the current rate of pay, and we can
| | 01:01 | verify that everybody is
in the Sales department.
| | 01:03 | So let's take a look at Inga Burns here.
| | 01:04 | Currently she is making $12.97.
| | 01:07 | Keep that number in mind, because we
are going to review that in a moment after
| | 01:09 | we have run our update query.
| | 01:10 | Let's go back into Design View, and
we'll switch this to an update query.
| | 01:15 | And when we do that, you'll notice that we
get a new line down here that says Update To.
| | 01:18 | So we're going to update our HourlyPay
to the HourlyPay+5, and we put that in
| | 01:23 | with square brackets and then our
mathematical expression +5. So throughout this
| | 01:29 | course I have been using the View
button and the Run button interchangeably.
| | 01:32 | That's because we have been
dealing exclusively with select queries.
| | 01:34 | Writing a select query merely shows the
Datasheet View, so these buttons are the same.
| | 01:38 | But when we're dealing with action
queries, such as make table, append, update,
| | 01:42 | and delete, Run will
actually perform those operations.
| | 01:45 | We can use the Datasheet View of our
update query to review the records that
| | 01:48 | are going to be modified and
we can double-check our work.
| | 01:50 | So we're going to be
modifying only the HourlyPay records.
| | 01:53 | Let's switch back into Design
View and go ahead and run our query.
| | 01:56 | Now you should get a message that
says you are about to update 84 row(s).
| | 02:00 | Go ahead and click Yes to this and
Access actually doesn't give us any
| | 02:03 | indication that the operation was
performed, but if we change our query back to
| | 02:07 | a select query and then take a look to
our Datasheet View again, we can see
| | 02:11 | that everybody's pay went up by $5.
Now, if nothing happened when you tried to
| | 02:14 | run your update query, that might be
because you have to adjust some of your
| | 02:17 | security permissions within Access.
| | 02:19 | And you can do that by going to the
Office button, Access Options, going to the
| | 02:23 | Trust Center, and then taking a
look at our Trust Center Options.
| | 02:27 | If you go to Trusted Locations, you
might have to add your Exercise folder as a
| | 02:30 | Trusted Location, by going to Add
new location, and then browsing to the
| | 02:33 | location of your Exercise folder.
| | 02:35 | I've already done that, so I'm going to go
ahead and say Cancel and back out of this.
| | 02:38 | Your data might need to be
updated for any number of reasons.
| | 02:41 | You might change vendors and have to
update product ID numbers, or your business
| | 02:45 | may move, change names, or get
merged with another company, and all your
| | 02:48 | addresses or phone numbers or email
addresses might need to get altered.
| | 02:52 | Whatever the reasons, you'll be able
to quickly and easily keep your database
| | 02:54 | up-to-date with an update query.
| | Collapse this transcript |
| Making tables and appending queries| 00:00 | We can use a make table query to permanently
save the results of the query as a new table.
| | 00:05 | And we can use the append query to
take records from one table and append
| | 00:08 | them to another table.
| | 00:09 | Let's how we can use these to action
queries together to create a master email
| | 00:13 | list for a marketing campaign.
| | 00:14 | First let's go up to the Create tab
and create a new query in Design View.
| | 00:17 | I am going to add our Customers table
to the query and we'll go ahead and close
| | 00:21 | the Show Table window.
| | 00:22 | Now we want to add our FirstName and
LastName and our customers' email addresses.
| | 00:27 | Now since all of our customers haven't
signed up to receive newsletters from us,
| | 00:30 | I want to make sure that I include
the Newsletter field as well, and we are
| | 00:33 | going to apply a criteria on that, so
you can filter out just the customers who
| | 00:36 | have agreed to receive our newsletters.
| | 00:38 | I can go ahead and view the results
and I can see that we have a total of 555
| | 00:42 | customers who've signed up for our newsletter.
| | 00:44 | Let's go ahead and switch back into
our Design View and now we're going to
| | 00:46 | convert our query into a make table query.
| | 00:48 | Access is going to ask is for the new
table name that we want to save and we'll
| | 00:52 | call this tbl_EmailCampaign.
| | 00:53 | Let's go ahead and say OK.
| | 00:56 | Now for the new table I want to
include the first name, the last name and
| | 01:00 | the email addresses of all our
customers that have signed up for our
| | 01:02 | newsletter, but we don't necessary need
to have the Newsletter column in the new table.
| | 01:06 | So let me go ahead and uncheck the
Show box and then I'll click Run.
| | 01:09 | Access tells us we are going to paste
555 rows into a new table, and that's
| | 01:13 | exactly the operation that we
want to do, so go ahead and say Yes.
| | 01:15 | And we'll see that we have a new
table in our database now, and I can open
| | 01:19 | it up and we can see that it has
just the first and last name and the list
| | 01:22 | of email addresses.
| | 01:23 | Let's go ahead and close that table and
we're done with this query, so we can go
| | 01:26 | ahead and close it, and
I don't need to save it.
| | 01:28 | Now the next thing I want to do is
take all of our employees and add their
| | 01:31 | addresses to the EmailCampaign table as
well. We can do that with an append query.
| | 01:36 | Once again we'll go to the Create tab
> Query Design. This time I am going to
| | 01:40 | select our Employees table.
| | 01:41 | And from there we're going to take
FirstName, LastName and Email address.
| | 01:47 | We can switch to Datasheet View to see
that we're getting the results that
| | 01:49 | we expect, and we can see that we are
getting 200 of our employees that all
| | 01:52 | have email addresses.
| | 01:53 | One more time we'll go to Design View.
| | 01:55 | This time we're going to switch it to
an append query. Access is going to ask us
| | 01:59 | which table we want to append to?
| | 02:01 | And we can either type in the name
or use the dropdown list here and
| | 02:03 | select EmailCampaign.
| | 02:05 | Go ahead and say OK.
| | 02:07 | And when we run our query, Access
is going to tell us that we are about
| | 02:10 | to append 200 rows.
| | 02:11 | That's exactly what we want
to do, so go ahead and say Yes.
| | 02:14 | And I am going to close this
query without saving it as well.
| | 02:17 | Now if we open up our EmailCampaign table, you
can see that we now have a total of 755 records.
| | 02:22 | So that's 555 from our
Customers table and 200 from our Employees table.
| | 02:27 | We have a master list of all the email
addresses for the people within our company.
| | 02:30 | So that was just a couple of examples
of the ways that you can use a make table
| | 02:33 | and an append query to
modify data within your database.
| | Collapse this transcript |
| Deleting queries| 00:00 | The delete query is obviously the most
destructive of the four action queries.
| | 00:04 | While the other three action query
types modify and move records around,
| | 00:08 | the delete query can actually remove large
quantities of data from your database as
| | 00:12 | if they had never existed.
| | 00:13 | I'll repeat my word of warning here again.
| | 00:15 | If you haven't backed up your
database, make sure that you do that before
| | 00:18 | performing any delete queries.
| | 00:19 | While it's probably the rare
exception where you want to permanently remove
| | 00:22 | records from your database, it does happen.
| | 00:24 | One of the most common reasons is when
you're exporting out data from and rarely access data
| | 00:28 | from your database to a second archive database.
| | 00:30 | This way, you can keep only the most
relevant and current data within your
| | 00:33 | working database to keep
it running smooth and fast.
| | 00:36 | The archive database will contain all
of your past records that you might need
| | 00:39 | to review periodically, but not
often enough to justify slowing down your
| | 00:43 | everyday work with
unnecessarily large data tables.
| | 00:46 | So, let's take a look at how we can
use the make table and delete queries in
| | 00:49 | conjunction with each other
to create an archive database.
| | 00:51 | First, I'm going to go up to the
Create tab, and a new query in Design View.
| | 00:55 | Right now, we want to export all of
our old orders to an archive database.
| | 00:58 | So I'm going to add our Orders table to
the query and close the Show Table window.
| | 01:01 | I'm going to add all of the
fields from our Orders table.
| | 01:04 | I can do that by selecting the first
one, Shift+Clicking on the last one, and
| | 01:07 | then drag that whole
thing down to the first field.
| | 01:09 | Access will populate these across.
| | 01:11 | Now I want to filter it
down to just our older orders.
| | 01:14 | I want to say every order that
was placed before 12/31/2006.
| | 01:18 | So I'll say <=12/31/2006.
| | 01:24 | I'll switch over to Datasheet View to
see that we're getting the results that I expect.
| | 01:27 | I see that I've got 819
records that are being selected.
| | 01:30 | Let's go back into our Design View.
| | 01:32 | We can switch this to a make table query.
| | 01:35 | Access is going to ask us for the table name.
| | 01:37 | I'm going to call this
table old orders, tbl_OldOrders.
| | 01:44 | I also have an option to either create
this table on the current database like
| | 01:46 | we did in the previous movie, or
I can put it in another database.
| | 01:50 | I'm going to select that and
browse to my Exercise folder.
| | 01:53 | For me, that's on the Desktop/Exercise Files.
| | 01:56 | I'll go into Chapter 8.
| | 01:58 | Here, I've already got an
Archive database set up.
| | 02:00 | So I'm going to select that
one and say OK. Select OK again.
| | 02:03 | Then we'll run our query.
| | 02:05 | Access is telling us that it's about
to paste 819 rows into a new table.
| | 02:09 | That's what we want.
| | 02:10 | So I'll go ahead and say OK.
| | 02:11 | Now we want to get the old
orders out of our current database.
| | 02:14 | That's pretty easy.
| | 02:15 | We just need to switch to a delete query.
| | 02:16 | We're going to keep the same
parameters, and run the query again.
| | 02:19 | Now, Access is telling us that
we're about to delete 819 rows.
| | 02:23 | That's what we want again.
| | 02:24 | So, we'll go ahead and say OK.
| | 02:25 | I'm going to close our query without saving it.
| | 02:28 | Now if I take a look at our Orders table,
I'll see that I've got 1381 records,
| | 02:32 | instead of the 2200 that we started with.
| | 02:34 | Let's go ahead and close our
current working database, and review what
| | 02:37 | happened in the archive.
| | 02:38 | We'll open that one up.
| | 02:40 | We've got one table in
here called tbl_OldOrders.
| | 02:42 | If I open that, I find that I have
the 819 records that we just exported.
| | 02:47 | So, that's how you can use a make table
action query and combine that with the
| | 02:50 | delete action query in order
to create an archive database.
| | Collapse this transcript |
|
|
ConclusionGoodbye| 00:00 | In this course we covered several of
the techniques that are available to you
| | 00:03 | when using queries to gain
better insight into your data.
| | 00:06 | I hope that you find this information useful
as you apply it to your own Access projects.
| | 00:09 | It's been a pleasure
exploring queries in-depth with you.
| | 00:12 | Have a great day!
| | Collapse this transcript |
|
|
