IntroductionWelcome| 00:04 | Hello, and welcome to
Access 2010 Queries in Depth.
| | 00:07 | My name is Adam Wilbert and I am going
to take you deep inside what I consider
| | 00:10 | to be the most powerful
object within an Access database.
| | 00:13 | We will start out by taking a look at
creating some simple select queries to
| | 00:16 | pull relevant fields from our data
tables and then we will combine those fields
| | 00:20 | for multiple tables in this single data sheet.
| | 00:22 | We will then apply selection criteria
to our queries to limit the records that
| | 00:27 | have returned to just to subset
that best satisfies our question.
| | 00:30 | We will also use calculated fields to
get a new perspective on the trends hiding
| | 00:34 | within your database.
| | 00:35 | We will see how to create flexible
queries through the use of parameter
| | 00:39 | requests, which will allow your end-
users to further define what results you
| | 00:43 | queries return, and by automating
common decision-making routines through
| | 00:46 | program flow functions and
conditional statements, you will save time and
| | 00:50 | increase the accuracy and
consistency of the data in your database.
| | 00:54 | Finally, we'll see how to hook our
queries back in the forms and reports for
| | 00:57 | added functionality and we will
apply these lessons to the creation of a
| | 01:00 | flexible reporting tool.
| | 01:02 | All of this as so that you can get
more out of your database than just the
| | 01:05 | data you put into it.
| | 01:06 | So, let's dig into Access 2010 Queries in Depth.
| | Collapse this transcript |
| Using the exercise files| 00:00 | If you have access to the exercise files
used throughout this title, you can put
| | 00:04 | them on the desktop as I've done
or anywhere that makes sense to you.
| | 00:07 | Exercise files for this
title are arranged by chapter.
| | 00:10 | Within each chapter folder is a database that
we will be using for exercises in that chapter.
| | 00:15 | I also wanted to point out that I have
made use of the custom grouping levels in
| | 00:19 | Access's navigation pane, so the
database objects that we will be using for
| | 00:22 | Chapter 1 are in the Chapter 1 custom group.
| | 00:26 | Finally, I wanted to
mention the security warning.
| | 00:28 | If you get this security warning, go-
ahead and either enable the content or
| | 00:32 | simply close the message bar.
| | 00:34 | If you don't have access to the
exercise files, you could follow along using
| | 00:37 | your own Access database.
| | 00:39 | Now, let's get started!
| | Collapse this transcript |
| Introducing the database| 00:00 | This course focuses primarily on
queries, which are only one of Microsoft
| | 00:04 | Access's five main object types.
| | 00:07 | In order to get the most out of this
course, you should have a basic working
| | 00:10 | knowledge of the other four, namely
tables, forms, reports and macros.
| | 00:14 | Now, there is no need to be an expert
in their application but you should feel
| | 00:18 | comfortable knowing what each
object is and what they are used for.
| | 00:22 | Throughout this course, we will be working
with data from the Two Trees Olive Oil Company.
| | 00:26 | In the navigation pane on the left,
you will notice that we have several
| | 00:28 | database objects that have
already been established.
| | 00:31 | I've gone ahead and applied a custom
grouping based off of the chapters for this course.
| | 00:36 | So, if you open up Chapter 1, you will see all
of the files that we will look at in Chapter 1.
| | 00:39 | At the very top, we can click on this
drop-down menu and switch to Object Type,
| | 00:45 | which will show us all of the
objects within our entire database.
| | 00:48 | I will scroll to top and you will see that
we have a list of our tables, our queries.
| | 00:54 | And I will scroll down to find
all of our forms and our reports.
| | 00:59 | We can go ahead and minimize these
using the up arrow on the side of the bar.
| | 01:03 | Let's scroll up to the top.
| | 01:05 | I will go ahead and switch this back into my
custom grouping and we'll look at Chapter 1.
| | 01:12 | Now, when jumping into a new database
with unfamiliar data, it's always a great
| | 01:16 | idea to take a few minutes to review
the tables and relationships in order to
| | 01:19 | familiarize yourself with the
contents that you will be working with.
| | 01:22 | Let's go ahead and go up to the
Database Tools in Relationships window.
| | 01:26 | You will see that our database contains
multiple tables that are all related to each other.
| | 01:31 | We will start with this Orders table.
| | 01:33 | The Orders table has a ProductID
for the product that was ordered.
| | 01:36 | That's linked to the Products table.
| | 01:38 | The Products table in turn is linked to
a table that describes our sizing, our
| | 01:43 | OilID, and our GradeID for each oil type.
| | 01:46 | If we go back to the Orders table,
we will see that that's linked to a table
| | 01:49 | about the customer that placed the
order, the table of our employees that
| | 01:53 | helped with the order,
| | 01:54 | and if we scroll to the right, we
will see a lookup table that give us more
| | 01:58 | information about the states that
our customers and employees are from.
| | 02:02 | Let's take a look at each table
individually to look at the data that's within each.
| | 02:06 | Let's open up our DirectCustomers
table by double-clicking on it in
| | 02:09 | the navigation pane.
| | 02:10 | We will see that our Customers table
has a Customer ID, First and LastName,
| | 02:14 | Phone number, Address
including City, State and Zip.
| | 02:18 | And I will scroll to the right, we have
got our Email address and a Yes and No
| | 02:22 | field that describes whether they
are subscribed to our newsletter.
| | 02:24 | Let's go back to our Employees table.
| | 02:27 | Our Employees table includes an
Employee ID, First, Middle and LastName, Phone
| | 02:32 | number, Address, City, State and Zip, Email.
| | 02:35 | And let's scroll to the right. We have got
their HireDate, their Hourly rate of pay
| | 02:40 | and what Department they are part of.
| | 02:42 | Let's go ahead and take a
look at our Orders table.
| | 02:44 | I will double click on in the navigation pane.
| | 02:46 | Our Orders table references an
OrderID number. This is a unique ID for the
| | 02:51 | transaction. The date that
the transaction was placed.
| | 02:54 | An ID number for the customer that
placed that order, an ID number of the
| | 02:57 | product that was ordered, and the ID number of
the salesperson that assisted with that order.
| | 03:02 | If we open our Products table, we will
see our ProductID number, the full text
| | 03:06 | of what that product is, a unique code
for the type of oil that that product
| | 03:10 | represents, its size, its
costs and this is our cost.
| | 03:15 | We have got the price to the customer
and then we have two calculated fields for
| | 03:19 | MarkupDollars and that's the difference
between the price that the customer pays
| | 03:23 | and then our cost, and then we have
a MarkupPercent, which is the percent
| | 03:26 | between our cost and the
price the customer pays.
| | 03:30 | Let's take a look next at our GradeID table.
| | 03:32 | This is the simple lookup table that
includes our GradeID and the text of
| | 03:36 | what that grade means.
| | 03:38 | The same thing applies to our OilID.
| | 03:40 | We have our OilID numbers and the full
text of what that is in a unique code.
| | 03:46 | We have got our Sizes table that
translates our size into a text version, so we
| | 03:50 | can see that the 8 ounce size is Small
and the 64 Ounce size is our Half Gallon.
| | 03:56 | And finally, we have a lookup table for States.
| | 03:58 | This takes our states' two letter state
abbreviations and it gives us the full
| | 04:02 | name of the state and some information about
what division and region that state belongs to.
| | 04:06 | So, that was just a quick overview
of the Access 2010 environment and the
| | 04:11 | database that we will be
using throughout this course.
| | 04:13 | If any of that was new to you
or you feel you might need some
| | 04:16 | refreshing, checkout Access 2010
Essential Training available on the
| | 04:20 | lynda.com Online Training Library.
| | 04:22 | Now that we are little more familiar
with the data that we will be working with,
| | 04:25 | we can start turning that data
into information with queries.
| | Collapse this transcript |
| Previewing the course| 00:00 | Learning how to effectively leverage
queries in Access is learning how to pull
| | 00:04 | information out of large data sets,
but in the end, queries are simply a
| | 00:07 | middleman between raw data and your analysis.
| | 00:11 | Throughout this course, we will be
looking at a lot of little pieces that can go
| | 00:14 | into your queries but the real power in
Access comes when you combine flexible
| | 00:18 | query with other objects
such as forms and reports.
| | 00:21 | Towards the end of this course,
we will be doing just that by building a
| | 00:24 | reporting system that leverages queries,
forms, and reports and merges them into
| | 00:28 | a single analysis tool.
| | 00:29 | I wanted to preview that tool upfront,
so we can have a better understanding of
| | 00:34 | where this course is headed.
| | 00:35 | So, right now, I have got a form open
that we will be building later in the course.
| | 00:39 | I provided four dropdown menus, so
that the end user can input some parameters
| | 00:43 | that they're interested
in reviewing a report for.
| | 00:45 | I have got a parameter for Geographical
Division. So for instance, I could say I
| | 00:49 | am interested in finding
information about our New England locations.
| | 00:53 | And I have a dropdown menu for Year,
so let's say I am interested in their
| | 00:57 | 2009 reporting year.
| | 00:58 | I can compare these two options with
another location and year or the same
| | 01:02 | location in a different year or a
different location in the same year.
| | 01:05 | So, this is really flexible.
| | 01:07 | Let's go ahead and choose our South
Atlantic states for the same reporting year.
| | 01:11 | Incidentally, each one of these drop-
down menus is using a query to generate
| | 01:16 | this list of options.
| | 01:18 | Normally, the end user will press this
Preview Report button to generate the report.
| | 01:23 | So, let's go ahead and
step through this manually.
| | 01:25 | I am going to run the query instead.
| | 01:28 | The query is going to look to the form and
find the values that the end user selected.
| | 01:33 | When I run this query, I'm only getting
information about the year 2009 and then
| | 01:37 | the division New England and South Atlantic.
| | 01:39 | From here, this data table will get fed
into the report for the final formatting.
| | 01:44 | Let's go back to the form and take a
look at what that report looks like.
| | 01:47 | I will go ahead press Preview Report
and this is the final formatted report.
| | 01:52 | If I zoom in here, we can see I have
got my New England states for the year
| | 01:56 | 2009 and I have got the information from them.
| | 01:58 | If I scroll down at the bottom of the
page, I have got my South Atlantic states.
| | 02:02 | Same year, 2009, and I have got those states.
| | 02:06 | Now, there is also a couple
of other features on here.
| | 02:08 | I have got Division Sales, which adds
up the total sales for all the states in
| | 02:12 | that division, and I have got
the average for the division.
| | 02:15 | So, each of these states,
we have the average value.
| | 02:18 | Each of these boxes uses query code
that we will be looking at in the course.
| | 02:21 | Now, at the bottom of the report, we
also have a line that shows you the date
| | 02:25 | that the report was run.
| | 02:27 | This also uses a function that we
will be looking at in the course to give
| | 02:30 | you the current date.
| | 02:31 | So, between the end user entering
information on the form here and the report
| | 02:35 | popping up on the screen here,
there are several queries running in the
| | 02:39 | background, performing all the grunt work
of collecting relevant data and serving
| | 02:43 | it to the appropriate places.
| | 02:44 | So, that's a quick peek at what
we are going with this course.
| | 02:46 | So, let's start digging into the details.
| | Collapse this transcript |
|
|
1. Creating Queries in AccessUnderstanding queries| 00:00 | At the heart of every database
is of course data, lots of data.
| | 00:04 | Data tables can easily grow to hundreds,
thousands or even millions of records.
| | 00:08 | But trying to manage and makes sense of
such large amounts of data can quickly
| | 00:11 | become overwhelming.
| | 00:12 | At some point, you are going to want to
know, what does all of this data tell us?
| | 00:16 | Luckily, Access provides a set of
objects called queries to help us out.
| | 00:20 | Basically, running a query
is simply asking a question.
| | 00:23 | Who are my top customers?
| | 00:24 | How many products did we sell last month?
| | 00:26 | You ask a question and a
query will return the answer.
| | 00:29 | At its most basic level, learning
queries is less about getting the right answer
| | 00:33 | and more about learning how
to ask the right questions.
| | 00:36 | So, let's suppose that we are
preparing an e-mail newsletter.
| | 00:39 | The first thing we want to ask
ourselves is, who are we sending them to.
| | 00:42 | I am going to go ahead and open up the
Chapter 1 group in my custom navigation pane.
| | 00:46 | Let's take a look at our Direct
Customers table by double clicking on it.
| | 00:50 | Here, we can see that we have a field
called Email address and that's exactly
| | 00:53 | what we will need, but we actually don't
need all of this other information about
| | 00:56 | their physical address.
| | 00:58 | Let's go ahead and run a query
that's based off of this table.
| | 01:00 | I am going to double-click on qry_CustomerEmail.
| | 01:05 | This query takes the FirstName,
LastName, and Email fields from our
| | 01:10 | Direct Customers table.
| | 01:11 | Now, query results look like tables
but they are actually called record sets.
| | 01:15 | This is an important concept in
Access, that queries do not store data;
| | 01:19 | they merely store the instructions on how
to assemble the data from you tables.
| | 01:23 | This way, queries are always up-to-date
without you having to maintain the same
| | 01:27 | information in two or more places.
| | 01:29 | Let's go ahead and go into our
DirectCustomers table and make a quick change.
| | 01:33 | For Salvador Garrison, I am going
to change his email address here.
| | 01:36 | And I will just highlight the first
part and type in Garrison and we will
| | 01:41 | move off of that record.
| | 01:42 | Let's go back into qry_CustomerEmail
and we will see that change is updated.
| | 01:46 | Now, let's take our example to the next step.
| | 01:49 | Instead of just emailing everybody,
we only want to target people in
| | 01:52 | the southern region.
| | 01:53 | If we go in to our
DirectCustomers table, we will see that we have
| | 01:56 | information about state but there is no
information about what region those states belong to.
| | 02:01 | If we open up our States table, we will see
that we can relate states to regions here.
| | 02:07 | So, let's look at one example.
| | 02:09 | Our Salvador Garrison
lives in the state of Oklahoma.
| | 02:11 | If we look at our States table and
find Oklahoma in the list, here it is,
| | 02:16 | we could see that Oklahoma
is in the southern region.
| | 02:19 | Now, this will be a little bit tedious
if we had to go through and do all
| | 02:22 | thousand customers this way.
| | 02:24 | We can use a query to streamline that process.
| | 02:26 | Let's go ahead and open
up CustomerEmailandRegion.
| | 02:30 | This query takes the same
information as the previous, FirstName,
| | 02:33 | LastName, Email, but it also adds
two fields from the table states.
| | 02:38 | RegionName and DivisionName.
| | 02:39 | Now, we can go ahead and sort this.
| | 02:41 | Let's sort it A-Z and then scroll
down til we find our southern states,
| | 02:46 | and these are the customers that we are
going to be targeting in our email campaign.
| | 02:49 | But again, this query is
returning too much information.
| | 02:52 | We don't need the
Northeast or Midwest customers.
| | 02:55 | So, let's take this a step
further and run one more query.
| | 02:58 | CustomerEmailandSouthernRegion.
| | 03:02 | This query is filtered to
just give us the southern states.
| | 03:05 | We could see that we have 297 customers
that will be getting our email campaign.
| | 03:10 | So, while using filters on your tables
would be a quick and dirty way to get
| | 03:12 | insight into some aspect of your data,
| | 03:15 | queries will often work out
better for you in the long run.
| | 03:17 | The ability to save and rerun a query is
just one of the many advantages that we
| | 03:21 | will be exploring throughout this title.
| | 03:23 | As we'll see, queries will be able
to provide answers to some pretty
| | 03:26 | complex questions and of course we
only need to learn how to ask the right questions first.
| | Collapse this transcript |
| Following naming conventions and best practices| 00:00 | Before we jump deeper into queries,
I wanted to take a moment to pass on some of
| | 00:04 | the best practice naming
conventions used by Access professionals.
| | 00:07 | You will see these naming rules
applied throughout this title and I wanted to
| | 00:10 | point them out early so that you can
recognize them and see how they are used.
| | 00:15 | Keep in mind that they're by no means
required by Access but they are tried and
| | 00:18 | true best practice that have been
standardized over several decades of use in
| | 00:22 | various database systems.
| | 00:23 | If I open up my Chapter 1 custom group, you
will notice that we have tables and queries here.
| | 00:29 | Tables are represented by
tbl_ and queries are represented qry_.
| | 00:34 | This is a form of Lozanoski or
Hungarian naming convention where each object is
| | 00:39 | named first by the object type and
then with a unique identifying name.
| | 00:43 | I've used CamelCase words to improve
legibility, but I have omitted spaces to
| | 00:47 | prevent any issues with moving each object
into another database system down the road.
| | 00:51 | Now, Access gives us some keys when we
are looking at our database objects to
| | 00:55 | tell us what they are.
| | 00:57 | Mostly, it relies on icons.
So this is an icon for a table.
| | 01:01 | This is an icon for a query.
| | 01:02 | If I expand my Introduction section,
we've got queries, forms, and reports and we
| | 01:07 | can see their icons.
| | 01:08 | But there are several places within
Access where we don't have the icons to help
| | 01:13 | us out to tell us what
object we are looking for.
| | 01:15 | For instance, if I look at our
Database Tools and then Relationships, we will
| | 01:20 | see a bunch of tables represented but
I wouldn't be able to tell these were
| | 01:23 | tables except for my keys
that I have at the beginning.
| | 01:26 | For instance, I can right-click and say
Show Table and then add a query to this
| | 01:31 | list and close the window.
| | 01:34 | Again, I have got my key here that
tells me this is the query but without this name,
| | 01:38 | I would be hard-pressed to tell the
difference between this table and this query.
| | 01:42 | So, let's take a look at some of
the common tags that we will be using
| | 01:46 | throughout this course.
| | 01:47 | We will be using tbl to denote tables.
tlkp will be a lookup table, a special
| | 01:54 | kind of smaller table that only gives
us additional information. qry will be
| | 01:58 | our queries, rpt will stand for reports,
frm will denote from objects, and mcr
| | 02:06 | will be our macros.
| | 02:07 | We will also see cbo and cmd, which
will denote combo boxes and command buttons
| | 02:13 | that we will be using on our forms.
| | 02:14 | Microsoft Access is really flexible
and it's designed so that you can get
| | 02:19 | quite a bit accomplished without ever needing
to look at the line of Visual Basic or SQL code.
| | 02:24 | If you don't plan out moving your data
to any other environments and you are
| | 02:27 | confident that you'll never need to
pass your database onto somebody else to
| | 02:31 | maintain, feel free to break any or
all of these practices as you see fit.
| | 02:35 | Rest assured that your database will
work just fine if you use long names,
| | 02:39 | spaces, capital letters, whatever you want.
| | 02:42 | If however, there's a slimmest of
possibilities that your project will grow
| | 02:45 | beyond a simple personal endeavor. then I
would urge you to consider putting some
| | 02:49 | of these best practices into your workflow.
| | 02:52 | Being consistent and deliberate in
your methods will only help you down the road.
| | Collapse this transcript |
| Using the Query Wizard| 00:00 | As with many of the objects and controls
that you'll encounter in Access, one of
| | 00:03 | the easiest ways to construct a basic
query is to use the Simple Query Wizard.
| | 00:07 | The wizard will present a series of
options to guide us through the creation
| | 00:10 | process and will provide us with a good
base that we can go back and tweak using
| | 00:15 | all of the tools in the
full design environment later.
| | 00:18 | When constructing a query, whether
you are using the wizard or the Design
| | 00:21 | View, it's critical that we start with a
clear and specific question that we want answered.
| | 00:25 | So, before jumping in with a vague
idea of the kind of information you're
| | 00:28 | looking for, make sure that you can
succinctly verbalize the question that
| | 00:31 | you are going to ask.
| | 00:33 | The more you can visualize the
question beforehand and think about all of the
| | 00:36 | pieces that you will need to gather
to get the right answers, the easier the
| | 00:39 | build process will be.
| | 00:41 | In this movie, we are going to use the
Wizard to walk us through the steps to
| | 00:44 | create a query which will
answer the following question.
| | 00:47 | What are our customer's email addresses?
| | 00:49 | Let's go into Access and use the
wizard to answer this question.
| | 00:52 | I will go up to the Create tab and in the
Query section, I will choose Query Wizard.
| | 00:57 | We will choose this
Simple Query Wizard and say OK.
| | 01:02 | The Simple Query Wizard is
broken up into three parts.
| | 01:05 | We have a drop-down menu where we can
select all of the tables and queries that
| | 01:08 | are in our database.
| | 01:10 | Let's go ahead and select
our DirectCustomers table.
| | 01:13 | The other two fields are
Available Fields and Selected Fields.
| | 01:17 | Available Fields lists all of the
fields that are in our table. Selected Fields
| | 01:20 | are all of the fields that will
be used to construct the query.
| | 01:23 | Let's go-ahead and move FirstName and LastName
over to Selected Fields using the single arrow.
| | 01:29 | If I accidentally grab a field I don't
need, for instance Phone number, you can
| | 01:33 | use the back arrow to move it back.
| | 01:36 | Let's also grab Email address. Go ahead and
say Next and Access will ask us for a file name.
| | 01:43 | The default file name is based off of the
table that we originally selected for our query.
| | 01:47 | We will go ahead and highlight
this and name this query qry_email.
| | 01:53 | We will take a look at the query design
view in a future movie, so for now just
| | 01:56 | go ahead and open the
query to view the information.
| | 01:59 | This is the record set that the query returns.
| | 02:01 | We have got our FirstName,
LastName, and Email addresses from our
| | 02:05 | DirectCustomers table.
| | 02:07 | Let's use the Query
Wizard to build a second query.
| | 02:09 | The question we want to ask this
time is, who are our best customers?
| | 02:13 | For this query, we will need
information from the Customers table as well some
| | 02:17 | information about their orders.
| | 02:19 | Let's go back into Access.
| | 02:20 | Once again, we will go up to the
Create tab and the Query Wizard.
| | 02:23 | We will do another Simple
Query Wizard and say OK.
| | 02:27 | We will select the DirectCustomers
table, so let's scroll up to find
| | 02:32 | DirectCustomers and from there again
we will add FirstName and the LastName.
| | 02:35 | Then we will need some information about
the price of the products that they ordered.
| | 02:39 | Let's go into the
Products table and select Price.
| | 02:42 | We will move that one and add it to our query.
| | 02:45 | Go ahead and say Next.
| | 02:47 | This time we are presented with another
window that we didn't see the first time around.
| | 02:50 | Because price includes numerical data,
Access asks us if we want to do anything
| | 02:55 | specific with that data.
| | 02:57 | We can just show the details, which is
the default view, or we can actually apply
| | 03:01 | some summary calculations.
| | 03:02 | Let's take a look at the
calculations that we can use.
| | 03:05 | Click on the Summary radio button
and then choose Summary Options.
| | 03:09 | For our Price field, we can choose to
sum the prices. Basically add all of the
| | 03:14 | transactions together.
| | 03:15 | We can find the average transaction
price, the minimum, or the maximum.
| | 03:20 | Now, the question that we asked was who
was our best customer and this is going
| | 03:24 | to depend a little bit on how you define best.
| | 03:26 | Is our best customer the customer that
spent the most amount of money or is our
| | 03:30 | best customer is the customer that
might have the highest average transaction?
| | 03:34 | There are a couple of ways that
we can interpret that question.
| | 03:36 | So, let's just go ahead and choose
Sum and Avg and let's also turn on Count
| | 03:41 | records so we can get a total count
for the number of orders that each
| | 03:44 | customer has placed.
| | 03:45 | We will go ahead and say OK and then Next.
| | 03:48 | We will give this new query a name.
| | 03:50 | We will call this qry_
OurBestCustomers and then we will select Finish.
| | 03:56 | Now, I am going to go ahead and expand
these rows a little bit so we can see
| | 04:00 | the full calculated total.
| | 04:02 | And I am just going to drag on
the bar between these column row.
| | 04:05 | So, this query records that gives us the
FirstName, LastName, a total for all of
| | 04:10 | the transactions that they have spend
with us, the average transaction price, and
| | 04:14 | the number of transactions for each customer.
| | 04:16 | We can now sort these columns from high to low.
| | 04:19 | For instance, if I click on this drop-
down menu, I can say Sort Largest to
| | 04:22 | Smallest on Sum Of Price and we will
see that Sebastian Rich has spent the most
| | 04:27 | money overall with our company.
| | 04:29 | If we were to sort by Avg Of Price, we
can say Largest to Smallest, and we will
| | 04:33 | see that these three customers
have all spent the same average.
| | 04:37 | But we can also see that
they've only had one transaction.
| | 04:39 | Let's go ahead and sort the Count column.
| | 04:42 | Largest to Smallest and we will
see that this person has had nine
| | 04:46 | transactions with our company.
| | 04:47 | So, depending on how you define best
customer, we have got three different answers.
| | 04:52 | The Query Wizard provides a quick and
easy way to get started writing queries.
| | 04:56 | You can combine fields from multiple
tables and even apply some basic aggregate
| | 05:00 | calculations to your data.
| | 05:02 | The Simple Query Wizard walks you
through the build process step by step and can
| | 05:05 | provide you with a solid base on
which you can add on more advanced
| | 05:08 | functionality in the query design environment.
| | 05:11 | By starting with a query written in the
wizard, we can begin to see how some of
| | 05:15 | the choices we made in the dialog
boxes translate into the written query.
| | 05:18 | We will take a look at that in the next movie.
| | Collapse this transcript |
| Exploring the design interface| 00:00 | All of the queries in Access are stored in
a common programming language called SQL.
| | 00:05 | SQL or Structured Query Language is a
powerful way to write queries but it can
| | 00:09 | be a bit difficult to read or troubleshoot.
| | 00:11 | Access provides a graphical interface
to creating our queries that eliminates
| | 00:15 | much of the tedium involved
with reading straight SQL markup.
| | 00:18 | This interface is called the Query Design View.
| | 00:20 | Let's take a look at the various areas
of the design interface and how we can
| | 00:23 | begin to build queries
using simple mouse clicks.
| | 00:26 | In my navigation pane, I am going to
expand the Chapter 1 group and I am going
| | 00:30 | to open up the CustomerEmailandRegion query.
| | 00:33 | This opens it up in Record Set view and
we can change to the Design view using
| | 00:36 | this button up here on the menu, click
on the little triangle and pencil icon.
| | 00:41 | The Query Design view split into two
areas. The upper half of the window shows
| | 00:45 | all of the tables that go into building
our query; the bottom half of the window
| | 00:49 | shows all of the fields that are in our query.
| | 00:51 | So, we can see that we have two
tables that make up the data in our query.
| | 00:54 | We have the tbl_
DirectCustomers and the tlkp_States table.
| | 00:59 | I am going to go ahead and move this
down a little bit by clicking on this bar
| | 01:02 | in between and dragging down.
| | 01:04 | I am also going to rearrange these
tables a little bit so I can see all of
| | 01:07 | the fields within each.
| | 01:09 | You can just grab on the
corner and drag them open.
| | 01:12 | You can grab on the header and move
them over. And now I can see all of the
| | 01:15 | fields that are within each table.
| | 01:17 | I can also see that we have a
relationship established between our DirectCustomers
| | 01:20 | table and the States table
based off of the State field.
| | 01:24 | We can see that it's a one to many
relationship based from state to the
| | 01:27 | abbreviation in the States table.
| | 01:29 | And this was previously
established in our Relationships window.
| | 01:33 | From these two tables, we
are pulling a series of fields.
| | 01:35 | We can see that FirstName from the
DirectCustomers table, LastName from the
| | 01:39 | DirectCustomers table, Email address
from the Customers table and the customer ID
| | 01:44 | from the Customers table.
| | 01:46 | From the States table, we are
pulling RegionName and DivisionName.
| | 01:49 | And looking at this, I can also see
that we have an ascending sort order based
| | 01:53 | off of the customer ID.
| | 01:54 | If this wasn't here, the query results
will be returned based alphabetically
| | 01:58 | off of the FirstName.
| | 01:59 | Let's go ahead and create
this query from scratch.
| | 02:02 | We will go up to the Create menu and in
the Queries group click on Query Design.
| | 02:07 | The Show Table window opens up and
here we can choose which tables we want
| | 02:11 | to use in our query.
| | 02:12 | Go ahead and double-click on
DirectCustomers and States.
| | 02:16 | You can now close the Show Table window.
| | 02:18 | At any point, if you want to add additional
tables, there is a couple of ways we can do that.
| | 02:23 | We have a Show Table right up here
or and appear or we can right-click in
| | 02:25 | this field and say Show Tables.
| | 02:28 | That will bring up the Show Table window
again and we can select additional tables.
| | 02:32 | Let's go ahead and close that out and we
will start adding our fields to our query.
| | 02:37 | Let's expand our table so
we can see everything again.
| | 02:40 | From our Customers table, let's choose
FirstName and LastName and you can add
| | 02:45 | those just by double-clicking on their names.
| | 02:47 | You can also drag and drop from
the Customers table into the field.
| | 02:51 | So, for instance I will grab Email.
Drag and drop it into the next open field.
| | 02:56 | Let's go ahead and add
DivisionName and RegionName, and I am
| | 03:00 | simply double-clicking.
| | 03:01 | And then finally, we will add
our CustID. Again double-click.
| | 03:06 | Under the sorting row for CustID, if
you click in this box, you will get a drop
| | 03:10 | down menu of options.
| | 03:11 | I am going to Ascending.
| | 03:13 | So, we will go from lowest
customer ID to highest customer ID.
| | 03:16 | Let's go ahead and run the
query and see where we are at.
| | 03:19 | You can either use the View button or Run.
| | 03:21 | And with the Select query, running the
query is the exact same as viewing the results.
| | 03:26 | So, either of these buttons will work.
They will do the exact same thing for a Select query.
| | 03:29 | I will click Run.
| | 03:30 | And we can see that we have our fields,
FirstName, LastName, Email and CustId
| | 03:36 | that are being pulled from
our DirectCustomers table.
| | 03:39 | We also have DivisionName and RegionName
that are coming from our States table.
| | 03:43 | Let's go back into the Design view and
we will look at a few ways that we can
| | 03:46 | manipulate our grid further.
| | 03:48 | Click on this button to go back into
Design view and let's go ahead and take our
| | 03:51 | CustID and move it to
the beginning of the query.
| | 03:54 | In order to select the field, there is
a tiny gray bar at the very top and when
| | 03:58 | you have the downward pointing arrow, go
ahead and click and it will select that field.
| | 04:01 | From there, you can go ahead and click
and drag to move the field either left or
| | 04:06 | right if we had further to the right.
| | 04:07 | We can go ahead and move it to the
left and drop it at the very beginning.
| | 04:11 | Now, the CustID field is an auto
generated number that really doesn't have
| | 04:15 | any real-world data.
| | 04:16 | it's simply there for Access
to keep the record separate.
| | 04:19 | So, we don't really need to see it.
| | 04:20 | We can go ahead and turn off the Show
button so that Access will use this field
| | 04:24 | for sorting but it won't use it
to actually show up in the results.
| | 04:29 | I can add additional fields to my query
just by dragging and dropping them into
| | 04:32 | the place that I want them.
| | 04:33 | For instance, if I want phone number to
be inserted between LastName and Email,
| | 04:37 | I can drag it and drop it into the grid,
and you will see the Phone number and
| | 04:41 | then Email moved over.
| | 04:42 | We can also use the buttons at the
top menu to insert or delete columns.
| | 04:46 | Let's go ahead and delete the Phone
number and you will see that Email moved
| | 04:49 | back over to the empty spot.
| | 04:51 | Let's review our query results by
clicking Run and we will see our final query.
| | 04:55 | We have got FirstName, LastName, Email,
Division and Region, and everything is
| | 05:00 | being sorted based off of the
customer ID number, not alphabetically.
| | 05:03 | So, while the wizard will be able
to quickly create a very simple query
| | 05:07 | within your database,
| | 05:08 | it really only scratches the surface of
what is possible in the Query Design View.
| | 05:12 | For more complex tasks or to
troubleshoot and modify your queries, the design
| | 05:16 | interface will be your go to tool.
| | 05:18 | As we'll see throughout this title, the
design interface allows us to visualize
| | 05:21 | how are queries are constructed,
which minimizes entry errors and gives us
| | 05:25 | greater control over our query record sets.
| | Collapse this transcript |
|
|
2. Creating Simple Select QueriesDefining criteria| 00:00 | One of the most common uses of queries
is to filter data within your database
| | 00:03 | down to a specific subset based
upon a single common attribute.
| | 00:08 | To do this we'll make use
of the criteria selector.
| | 00:11 | There are many different types
of criteria that we can define.
| | 00:13 | We can specify an exact match like a
specific product or employee's name or
| | 00:18 | we can define a group of values such as all
of the products within a specific department.
| | 00:23 | The criteria field is very flexible
and Access provides some additional help
| | 00:27 | when writing them to ensure
that our syntax is correct.
| | 00:30 | So let's see this in action.
| | 00:33 | We'll go up to the Create tab and
we'll create a new query in Design view.
| | 00:36 | Let's go ahead and add our Employees table
by double-clicking on it. And close Show Table.
| | 00:44 | Now the query that we want to start with
first is which employees live in Arizona.
| | 00:48 | We'll add a couple of fields to our query here.
| | 00:50 | I'm going to open this up we'll add
FirstName and LastName and State to our query.
| | 00:57 | Now if I run it right now, we're
going to get everybody, no matter what
| | 01:00 | state they live in.
| | 01:01 | We can see that we have a total of 200 records.
| | 01:05 | Let's go ahead and run back into Design view.
| | 01:09 | For our criteria, we're going to
specify that we're only interested in
| | 01:12 | employees that live in the state of
Arizona and we can put in the two letter
| | 01:15 | abbreviation for Arizona, AZ.
| | 01:18 | Press Enter to accept that value and
Access actually wraps it in quotation marks,
| | 01:22 | which is the proper syntax.
| | 01:23 | Let's go ahead and run this query again
and we'll see that we're down to three
| | 01:28 | records with just the employees
that live in the state of Arizona.
| | 01:32 | Let's go ahead and switch back into Design view.
| | 01:34 | I can specify multiple
criteria by using the same line.
| | 01:38 | So for instance if I'm interested in all
the employees that have a first name of
| | 01:42 | Jennifer that live in the state of
Arizona I can put it in like this.
| | 01:48 | FirstName is Jennifer and State is Arizona.
| | 01:51 | If I run this I'll see that I only
have one employee that matches both
| | 01:55 | criteria simultaneously.
| | 01:57 | Back in the Design view and let's get rid
of Jennifer here and I'll get rid of Arizona.
| | 02:03 | I'm just going to highlight and press Backspace.
| | 02:07 | Let's expand our reach a little bit and
now say we're interested in all of the
| | 02:11 | states that begin with the letter A.
| | 02:14 | We can use the Like operator to use this query.
| | 02:17 | I'll write the word "like"
followed by a asterisk.
| | 02:23 | What this is telling Access is that
we're interested in all of the states that
| | 02:26 | have an A as the first letter
and then any letters after that.
| | 02:29 | Let's go ahead and press Enter to accept that.
| | 02:32 | Access adjusts our statement a little
bit, capitalizes the word Like, wraps it
| | 02:36 | in quotation marks.
| | 02:38 | If we run that, we'll see the states of
Alaska, Alabama and Arizona are all represented.
| | 02:43 | Let's go back into Design view and
I'll highlight this and get rid of it.
| | 02:48 | The asterisk character is a wild
card and it means any character and any
| | 02:53 | number of characters.
| | 02:55 | So for instance, if I was interested
in all of the employees that have a W
| | 02:58 | anywhere in their last name I can
write that statement this, like *w*.
| | 03:06 | Now I'm asking for any characters,
W, and then any characters after that.
| | 03:11 | I'll press Enter and we'll run that
query and you'll see that our last names
| | 03:16 | either have a W at the beginning,
a W at the middle, or a W at the end.
| | 03:20 | Let's go ahead and switch back into
Design view and I'll get rid of this.
| | 03:29 | Now right below the
Criteria line is the word or.
| | 03:32 | Any criteria that you put on multiple
lines will be treated as an Or statement.
| | 03:37 | So if I was interested in multiple states,
I can say the state of Arizona and on
| | 03:43 | the line below that, I can say New Mexico, 'nm'.
| | 03:46 | Let's go ahead and say Run and
you'll see that we have both states there.
| | 03:52 | Let's go ahead and switch back into
Design view and I'll get rid of these.
| | 04:00 | We can also write an or
statement on a single criteria line.
| | 04:02 | So I could also have written that same
query as 'az' Or 'nm'. And when I press Enter,
| | 04:10 | Access understands that
that's Arizona or New Mexico.
| | 04:14 | Again, the same results.
| | 04:17 | Back into Design view, we'll go
ahead and get rid of this criteria.
| | 04:21 | Another logical operator in
addition to Or is the word Not.
| | 04:25 | This does exactly what you would expect.
| | 04:27 | It excludes records.
| | 04:28 | So I can say Not 'az' and we would get
all of these employees that do not live
| | 04:34 | in the state of Arizona.
| | 04:35 | Now there's a third operator called And.
| | 04:38 | We've already seen how we can
include multiple columns here.
| | 04:41 | So for instance, I have the first name
of Jennifer in the state of Arizona on
| | 04:45 | one line. Access treated
that as an And statement.
| | 04:47 | We're looking for both at the same time.
| | 04:50 | If I were to put in here 'az' And 'ny'
thinking that I'm going to get all of the
| | 04:55 | records for the employees that
live in Arizona and New York,
| | 04:58 | if I say Run I don't get anything and
the reason for that is because Access
| | 05:03 | uses a very literal
understanding of the word And.
| | 05:05 | It is actually looking for all the
employees that live in Arizona and New
| | 05:10 | York at the same time.
| | 05:12 | So that's why the proper
operator for this statement was Or.
| | 05:16 | We will see And coming up here when
we look at mathematical operators.
| | 05:20 | So we have seen how we can use
queries to filter out data down to just the
| | 05:23 | specific records or range of records
that we are interested in, based off of some
| | 05:27 | text-based criteria.
| | 05:29 | While many of these operators will
also work for numerical values, Access
| | 05:33 | provides some additional tools
when defining mathematical operations.
| | 05:37 | So let's take a look at those in the next movie.
| | Collapse this transcript |
| Understanding comparison operators| 00:00 | When the fields in your tables are of
a numerical datatype, Access provides
| | 00:04 | additional operators that you
can use when writing your queries.
| | 00:07 | One type is called Comparison operators
and they sort and qualify data based on
| | 00:11 | the numerical value of the record.
| | 00:13 | Comparison operators are also called
relational operators and they don't simply
| | 00:17 | match an exact value or pattern
like we saw with the Like operator.
| | 00:22 | Comparison operators return records
that match the relationship to a value.
| | 00:25 | For example, whether it's greater than the
value you specify or less than or equal to.
| | 00:31 | Let's build a query that takes a look at this.
| | 00:32 | Once again we're going to go
the Create tab and Query Design.
| | 00:37 | This time we need some numerical values.
| | 00:39 | So let's take a look at our Products table.
| | 00:40 | I'll double-click on Products to
add it to the Query Design View.
| | 00:44 | Go ahead and close the Show Table
window and we'll expand this open so we can
| | 00:48 | see all of the fields.
| | 00:50 | Let's go ahead and build our query.
| | 00:51 | We'll double-click on ProductName, Ounces
to get the size, and Price to get the price.
| | 00:57 | Let's go ahead and run our query and
we'll see that we have a total of 90
| | 01:03 | records down here at the bottom.
| | 01:05 | This is our product name, the size in
ounces, and the price to the customer.
| | 01:09 | So that's our baseline.
| | 01:12 | Let's go ahead and go back into
Design view and we'll take a look at our
| | 01:16 | comparison operators.
| | 01:18 | Let's say that we are interested in
all of the products that are less than or
| | 01:22 | equal to 16 ounces in size.
| | 01:25 | We can write it like this:
less than or equal to 16.
| | 01:29 | If we run this query we'll see that we
have a total of 36 products that are at
| | 01:36 | 16 ounces or smaller.
| | 01:37 | Let's go back into Design view and
I'll go ahead and get rid of this.
| | 01:42 | We could do the same thing with price.
| | 01:44 | Let's say we're looking for all of the
products that are greater than $40 in cost, >40.
| | 01:51 | I'll run that query and we'll see that we
have a total 19 products that are over $40.
| | 01:59 | Let's take a look at all of the xomparison
operators that we can use to build our queries.
| | 02:04 | The first one is Less Than and this is
represented by the less than character.
| | 02:08 | We can use this to answer questions
such as all orders below a $100.00.
| | 02:13 | We have Greater Than and this will be
used for all orders above $100.00.
| | 02:19 | We have Equal To and we can use this
for statements such as Employee Number is
| | 02:24 | 53 or Department is marketing.
| | 02:27 | Take a moment and notice that this is
applying to both numerical and text-based data.
| | 02:33 | We have less than or equal to
Worked 32 hours or less for example.
| | 02:38 | Or worked 40 hours or more
would use greater than or equal to.
| | 02:41 | We also have not equal to and this is
represented by a less than followed by a
| | 02:47 | greater than character.
| | 02:48 | This would help us find where
the Employee ID is not 53 or all
| | 02:52 | departments except marketing.
| | 02:55 | Comparison operators are another tool
that you can use when filtering your data
| | 02:58 | to the specific records that
you want to work with or review.
| | 03:01 | They're useful when you want to locate
records that meet or exceed a certain
| | 03:05 | criteria or to highlight areas of
opportunity within your organization's data.
| | 03:10 | By using comparison operators in
your queries, you will be better able to
| | 03:14 | quickly and efficiently locate the
records with the most relevancy to
| | 03:17 | your specific questions.
| | Collapse this transcript |
| Defining the column headers| 00:00 | When creating queries using calculated
or filtered records, it's always a good
| | 00:04 | idea to properly document your
workflow so that your results will be easily
| | 00:08 | understood by other users.
| | 00:09 | One way to do this is to rename the
field headers in your queries to give them a
| | 00:13 | more meaningful and accurate name.
| | 00:15 | Let's go ahead and build a
query that will demonstrate this.
| | 00:17 | We'll go up to the Create tab and in the
Queries section click on Query Design.
| | 00:22 | We'll add our Products table by double-
clicking and then we can say Close Show Table.
| | 00:27 | I'm going to go ahead and open this
up and let's add a bunch of fields from
| | 00:32 | our table to our query.
| | 00:33 | I'll double-click on ProductName, Ounces,
Cost, Price, MarkupDollars and MarkupPercent.
| | 00:41 | Now these field names come
directly from the table headers.
| | 00:44 | ProductName, Ounces, Cost, Price,
MarkupDollars, MarkupPercent.
| | 00:47 | You will notice that they are written
with no spaces and they are not exactly in
| | 00:51 | the most human-readable format.
| | 00:53 | We can change that by using a
field alias and it works like this.
| | 00:57 | If we click in the very beginning of
the name, you could type in an alias
| | 01:01 | followed by a colon and Access will use
that in the table of results from the query.
| | 01:06 | So instead of ProductName as all one
word we can say Product Name with a
| | 01:11 | space followed by colon.
| | 01:14 | Ounces, well this is our size field
and Ounces is a little bit confusing.
| | 01:18 | So we'll say Size in ounces followed by a colon.
| | 01:24 | Our Cost? Well this is really our
wholesale cost so I'll write in Wholesale
| | 01:30 | Cost followed by colon.
| | 01:33 | The Price field represents our customers' price.
| | 01:37 | So I'll click in the beginning and
write Customers Price, followed by a colon.
| | 01:44 | MarkupDollars and MarkupPercent are both
markup fields but they have different units.
| | 01:51 | So MarkupDollars I'll specify as
dollars with a dollar sign and MarkupPercent,
| | 01:58 | I'll specify the units as percent
with a percent sign followed by a colon.
| | 02:02 | Now if I go ahead and run this query,
you'll see that we have new headers at
| | 02:07 | the top of our columns.
| | 02:08 | Product Name is 2 words.
Sizes is with ounces specified.
| | 02:12 | Let me double-click here just to widen that.
| | 02:14 | Wholesale Cost, Customers Price,
Markup in dollars and Markup in percent.
| | 02:21 | Later on in this course, you'll see
that when we create a calculated field
| | 02:24 | Access is automatically going to add an
alias Expr1, Expr2, etcetera to denote an
| | 02:31 | Expression 1 or Expression 2.
| | 02:33 | Most of the time those are not going
to be helpful at all so we'll be using
| | 02:36 | these aliases to redefine Access's default.
| | 02:39 | By clearly identifying the fields
returned in your queries you'll aid other
| | 02:43 | users that will be working with your
database and will help clearly communicate
| | 02:46 | the meaning of the field in forms and reports.
| | Collapse this transcript |
| Exploring the property sheet| 00:00 | Every object and control in Microsoft
Access has a set of properties that can be
| | 00:04 | defined to give you more
control over your database.
| | 00:07 | Queries and the fields within
your queries are no exception.
| | 00:11 | By understanding the options available
within the query property sheets, you
| | 00:14 | will gain more control over
the queries that you construct.
| | 00:17 | In this movie, we will look at some of
the properties that we can control for
| | 00:20 | each of the fields in our query.
| | 00:21 | Now, if you've saved your query for the last
exercise, you can continue using that here.
| | 00:25 | I've opened the qry_ProductsWithAlias query.
| | 00:29 | We could see that we've already
changed our field headers. Now we need to go
| | 00:32 | ahead and format our data.
| | 00:33 | My Wholesale Cost and Retail Price
represent dollar values, and I want to
| | 00:38 | format them, so that all the
decimal places are aligned vertically.
| | 00:41 | MarkupDollars and MarkupPercent are
calculated fields from the data table.
| | 00:45 | Let's take a look at that so we
could see how these are formatted.
| | 00:48 | I will go into my Chapter 1
folder and open up the Products table.
| | 00:51 | We will switch in to Design view using
the Design button on the Home ribbon.
| | 00:57 | Access is going to give us a warning
message saying that this table is currently
| | 01:00 | being used by our query that's opened.
| | 01:02 | In order to switch into Design view we
can only view the table in Read Only mode,
| | 01:06 | and that's fine. We just want to take a
look at the back end. Go ahead and say
| | 01:09 | Yes and we'll take a look at the
data types for each of our field.
| | 01:12 | MarkupDollars and MarkupPercent use the new
Calculated data type that's new for Access 2010.
| | 01:18 | Here we are taking the price and
subtracting the cost to get the markup in dollars.
| | 01:23 | You can see that the Format is set to
Currency and is set to 2 decimal places.
| | 01:27 | MarkupPercent is also a calculated data type.
| | 01:30 | It takes the price and
divides by cost to get percent.
| | 01:34 | The Format is set to Percent.
| | 01:35 | When we view the data sheet for our
Products table we will see that those
| | 01:38 | formatting rules apply to the column.
| | 01:41 | When we make a query based off of
this table, the formatting follows.
| | 01:44 | Back in our query here, we could see
that the formatting is the same as it
| | 01:48 | appears in the table.
| | 01:49 | Let's use our query properties to format
Wholesale Cost and Retail Price to match.
| | 01:53 | We will switch into Design view,
and we'll open up the Property Sheet.
| | 01:57 | There are a couple of ways we
can open the Property Sheet.
| | 01:59 | We have a button here on the ribbon
called Property Sheet and that will
| | 02:02 | toggle it open or closed.
| | 02:04 | You could use the F4 key on your
keyboard to open or close the Property Sheet.
| | 02:08 | We can also use Alt+Enter on our keyboard.
| | 02:11 | So hold down the Alt key and press Enter
to toggle the Property Sheet open or closed.
| | 02:16 | So whatever method you prefer, go
ahead and open up the Property Sheet and
| | 02:19 | we'll take a look at the
properties for the field that's selected.
| | 02:22 | Currently, we have Product Name
selected and the Property Sheet opens up to the
| | 02:25 | field properties for this field.
| | 02:27 | Let's switch to a Numerical datatype
so we can see all the values available.
| | 02:31 | Click on Wholesale Cost.
| | 02:33 | The Description field here is a great
place to sort notes about your query.
| | 02:36 | Particularly when we move into
creating calculated fields, you can use the
| | 02:39 | Notes field to document what
this specific calculation is doing.
| | 02:43 | The Format field controls how numerical
data will be displayed and there are a
| | 02:47 | range of options
available in the drop-down menu.
| | 02:49 | For Wholesale Cost, we will go
ahead and select the Standard number.
| | 02:52 | The Decimal Places controls how many
digits after the decimal will appear.
| | 02:55 | We will go ahead and select 2.
| | 02:57 | Input Mask controls how data is
entered into the query and how that is
| | 03:01 | saved into the table.
| | 03:03 | Now, I've honestly never come across
to reason why you would want to use this
| | 03:05 | property at the query level.
| | 03:07 | This functionality is much better
applied at a table level instead.
| | 03:10 | So I would recommend avoiding this one
unless you have a really specific use
| | 03:13 | case that requires the data entered
through the query gets formatted differently
| | 03:18 | than data entered into the table.
| | 03:20 | The Caption field acts just like the aliases
that we've added to our headers in the field grid.
| | 03:25 | The difference here is that caption
only appears in the Property Sheet and in
| | 03:28 | the Datasheet view at the very top.
| | 03:30 | It doesn't appear here in
the field in the Design view.
| | 03:33 | Now I prefer to use an alias so I can
see it here in Design view instead of
| | 03:37 | having it hidden in the Property Sheet.
| | 03:39 | Finally, smart tags are a way to link
very specific data types to external content.
| | 03:43 | For instance, you can dynamically link
a stock ticker symbol to a live quote
| | 03:47 | from Microsoft's msnmoney.com web site.
| | 03:51 | You will click in this field and
click the Build button to make that link.
| | 03:53 | Now, we've gone ahead and changed the
Wholesale Cost field to a standard number
| | 03:57 | with two decimal places.
| | 03:58 | Let's do the same for Retail Price.
| | 04:00 | I will click on Retail Price, change
the Format to a Standard number, and the
| | 04:05 | Decimal Places to 2.
| | 04:07 | Let's run our query to see the results.
| | 04:09 | We will see that our decimals are
all right-aligned and the numbers are
| | 04:12 | much easier to read now.
| | 04:13 | Now, there are different schools of
thought on whether you should include
| | 04:15 | the dollar sign or not.
| | 04:17 | I personally prefer to not have the
dollar sign show up in the column of data.
| | 04:20 | I am going to go ahead and
remove it here from the Markup field.
| | 04:23 | Let's go back into Design view.
| | 04:26 | We'll scroll across in our Field
grid until we get to MarkupDollars.
| | 04:29 | I will change the format for this field.
| | 04:32 | I will change the Format from the
Currency that's it's currently set at from the
| | 04:36 | data table to a Standard number.
| | 04:37 | We will change this to 2 decimal places as well.
| | 04:40 | We will click Run and you will see
that the dollar sign has now been removed
| | 04:44 | from this field and I think it makes it
much more easy to read all of the data.
| | 04:47 | Let's go back into Design view and
take a look at the query level properties.
| | 04:51 | Design view. And if you click anywhere
in this blank area up here in the Table pane,
| | 04:56 | you will switch to
the query level properties.
| | 04:59 | Now, the properties for the overall
query mainly offer high-level record
| | 05:02 | management options that are useful only to
select users like Visual Basic programmers.
| | 05:06 | But there is a couple here that
can apply to a wider audience.
| | 05:09 | Again, the Description field is
useful for documenting what the query does,
| | 05:13 | who made it, and why.
| | 05:14 | The Default View allows you to
specify if we like the query to open up in
| | 05:17 | PivotChart or PivotTable view by default
when you double-click it on the navigation pane.
| | 05:22 | We will leave it as Datasheet view.
| | 05:24 | The Output All Fields
property is currently set to No.
| | 05:27 | When set to No, Access will respect the
show checkboxes here in the Query Designer.
| | 05:32 | If you change this to Yes, regardless
of whether you have Show turned off or not,
| | 05:36 | Access will display all
of the fields in your query.
| | 05:39 | I'll leave this on No.
| | 05:40 | The Top Values property is linked to the
Return value on the Query Setup section
| | 05:46 | of the Design ribbon.
| | 05:47 | We could change this to only return a
specific number or a specific percentage
| | 05:51 | of records within our dataset.
| | 05:52 | For instance, if I only want to view
the top 25 records, I could select 25.
| | 05:57 | When I make that change, you will notice
that the Return section up here changes as well.
| | 06:00 | Now you are not only limited to
the values that appear on the list.
| | 06:03 | For instance, I could type in 30, and
Access will show me the top 30 records, or
| | 06:07 | I could type-in 7% and Access will
tell me the top 7% of the records.
| | 06:13 | I will go ahead and change this back to
All and you will see that changed
| | 06:17 | in the Property Sheet as well.
| | 06:18 | The Recordset Type is probably the
most useful of the query level properties.
| | 06:22 | By default, a query's datasheet or its
dynaset is a live link to your data tables.
| | 06:27 | You can make edits to the query results
that will update the data in the tables.
| | 06:31 | Let's take a look at that.
| | 06:32 | If I click to run my query, I can go
in here and change any of these values.
| | 06:36 | For instance I will change Wholesale
Cost for this particular oil to $8 and you
| | 06:41 | see that I am allowed to make that
change and that change is made into the
| | 06:44 | original data table.
| | 06:46 | Let's go back into our Design view.
| | 06:47 | I will click up here to change the
query level properties and I will change my
| | 06:51 | Recordset Type from Dynaset to Snapshot.
| | 06:55 | With Snapshot selected, the datasheet
will be in a Review Only mode and users
| | 06:59 | will not be permitted to
change the data in the link tables.
| | 07:03 | So with this change made, I will go up
to the Run menu and if I try to make a
| | 07:07 | same change, let's change it back to 7,
Access will give me a warning beep
| | 07:11 | saying that I can't change that value.
| | 07:13 | So depending on the query and the
data types in the fields that you are
| | 07:16 | working with, the query Property
Sheet will change to show you only the
| | 07:19 | applicable parameters.
| | 07:21 | It's a good idea to make sure that
your queries are well-documented in the
| | 07:24 | Description view, so that anyone that
needs to review your work down the road,
| | 07:28 | maybe months or years later, will have
a solid understanding of what you did
| | 07:31 | and how it works.
| | Collapse this transcript |
| Printing query results| 00:00 | Often, your queries will be incorporated into
other Access objects such as forms and reports.
| | 00:05 | But occasionally, you'll simply want to
print out a copy of the Datasheet view
| | 00:08 | of your query to pass to a
colleague or a supervisor.
| | 00:10 | If you've ever printed a data table in
Excel, you'll be happy to know that many
| | 00:14 | of the same formatting options
are available to you within Access.
| | 00:17 | Let's go ahead and open up the
Chapter 2 custom group and we'll look for
| | 00:20 | qry_EmployeesNewEngland.
| | 00:22 | Let's double-click on it to run it.
| | 00:24 | Now, let's say that this is the
table that I want to get printed.
| | 00:27 | I can go up to the File menu, go into the
Print tab, and I've got a couple of options.
| | 00:32 | I can send it directly to the
printer without any other options, I can
| | 00:36 | choose the printer and set some of my Print
settings, or I can go into Print Preview mode.
| | 00:40 | If I go into Print Preview mode, I
have some options here to change the size
| | 00:44 | and margins of our page, whether it's
portrait or landscape, how we view it
| | 00:48 | in Print Preview mode.
| | 00:49 | If there were multiple pages, I can go
to a 2-page or a 4-page spread. Or I can
| | 00:53 | export this data to Excel,
a text file, or a PDF file.
| | 00:57 | Let's go ahead and close this window.
| | 00:59 | So that's one way that you can print
out the results of your query or the
| | 01:03 | results of the query dynaset.
| | 01:04 | Now, if I wanted to document the
process though when into building the query,
| | 01:08 | there are a couple of ways that I can do that.
| | 01:10 | If I change my view into SQL view here,
I can copy-and-paste this text and
| | 01:17 | put it into an email or a Word
document, and I could email that to a
| | 01:20 | supervisor or a co-worker.
| | 01:22 | So that's one way we can document this query.
| | 01:24 | Another is to use the Database Documenter.
| | 01:27 | This is a tool that's under
Database Tools > Database Documenter.
| | 01:31 | The Database Documenter will allow me
to choose any of the objects within my
| | 01:34 | database, for instance tables, queries,
forms, reports, macros, and to print out
| | 01:40 | a report that describes
how that object was created.
| | 01:43 | So for instance, if I go in my
Queries tab and I scroll down to
| | 01:47 | qry_EmployeesNewEngland here, check it on,
and then say OK, we're going to first
| | 01:53 | go ahead and close our query, and then
Access provides us with a report that
| | 01:57 | describes what that object
is and how it was built.
| | 02:00 | Let's go ahead and zoom in
here and see what we've got.
| | 02:02 | First of all, Access gives us a
path to where the database is from.
| | 02:06 | We can see the date and time that this was ran.
| | 02:08 | We have the properties for our query.
| | 02:11 | We can see the SQL statement of what
that query is made up of, and if we
| | 02:15 | scroll down further we can see all
of the tables and columns that go into
| | 02:18 | building our query.
| | 02:19 | So using the Database Documenter is a
way that we can describe the process of
| | 02:23 | building our query so that
others could duplicate our work.
| | 02:26 | For more control over the formatting
and appearance of your data, Access
| | 02:29 | provides some simple report wizards that you
can build using your queries as based input.
| | 02:33 | But for a simple hardcopy table
showing the results of the query,
| | 02:37 | the Print options within Access can be
a quick-and-dirty way to share your data among peers.
| | Collapse this transcript |
| Working with joins| 00:00 | When creating queries that include
multiple tables, it's important to understand
| | 00:03 | how those tables are related in
order to get meaningful results.
| | 00:07 | Typically, Access will be able to
assist you in building these relationships if
| | 00:11 | they're not previously defined.
| | 00:12 | But there are occasions when you'll
need to create or modify how your tables
| | 00:15 | are grouped together.
| | 00:16 | Let's take a look at a couple of examples.
| | 00:18 | In the Chapter 2-6 group, let's go
ahead and open that and we'll open up our
| | 00:23 | Sales department query.
| | 00:25 | You can see that we're getting
Employee ID, FirstName, and the Department is Sales,
| | 00:29 | and we have a total of 84
employees in the Sales department.
| | 00:32 | If we look at this in Design view,
we'll see that we've got the Employee ID,
| | 00:36 | FirstName and Department, and the Criteria
Sales. So 84 employees in our Sales department.
| | 00:42 | Keep that number in mind.
| | 00:43 | Let's go ahead and close this query out.
| | 00:45 | We'll open up this query MaySales.
| | 00:48 | Here we've got the employee ID,
FirstName, and the May sales figures.
| | 00:53 | But you can see we only have 31 records here.
| | 00:56 | What happened to all the other employees?
| | 00:57 | Let's go into Design view
and take a look at this.
| | 01:01 | This query is constructed with the
Employees table, the Orders table, and
| | 01:05 | the Products table.
| | 01:06 | We have the Employee ID and the
FirstName coming from the Employees table,
| | 01:10 | the Price coming from the Products table,
and the OrderDate coming from the Orders table.
| | 01:14 | Under Criteria, we have >=#5/1/2010#
And <=#5/31/ 2010#, basically all of May.
| | 01:23 | Finally, in the Price column,
we have the total of Sum.
| | 01:26 | So it's adding up all of the
prices over the course of May.
| | 01:29 | So why when we run this aren't getting
all of our employees in the sales department?
| | 01:33 | Well, we're only getting the employees
that actually had sales in the month of May.
| | 01:37 | If I double-click on the join line
between tbl_Employees and tbl_Orders, we'll
| | 01:42 | see that the default view is to only
include rows where the joined fields from
| | 01:46 | both tables are equal.
| | 01:48 | So that's why our results are only
showing sales numbers for the employees
| | 01:51 | that actually had sales.
| | 01:53 | We're not seeing anything for the
employees that didn't have sales.
| | 01:56 | Now, we can construct a
single query that fixes this issue.
| | 01:59 | But if we were to try and change this
here to include all records from the
| | 02:02 | tbl_Employees and only those records
from tbl_Orders where the joined fields are
| | 02:05 | equal, Access won't allow us to do that.
| | 02:08 | If I try and run this query, Access is
going to give us an error saying the SQL
| | 02:12 | statement could not be executed
because it contains ambiguous outer joins.
| | 02:17 | In order to fix this, we need to
create another query and use this
| | 02:20 | MaySales query as an input.
| | 02:23 | Okay, let me show you what I mean.
| | 02:25 | Let's go ahead and close this MaySales query.
| | 02:28 | Now we don't need to save changes to those.
| | 02:30 | We'll create a new query in Design
view. Go to Create > Create Design.
| | 02:34 | First, we'll add our Employees table,
and then we'll switch the tab to Queries
| | 02:40 | and we will choose qry_MaySales.
| | 02:45 | Go ahead and close the Show Table
window and you'll see that Access has
| | 02:49 | generated a join between
EmployeeID and EmployeeID here.
| | 02:54 | Let's add a couple of fields to our query.
| | 02:56 | Let's add our EmployeeID, FirstName,
Department, and our May sales figure from that query.
| | 03:03 | Now, if I run that query right now,
we'll see the same number of records, 31, that
| | 03:09 | we saw in the previous query.
| | 03:11 | Again, we're only displaying the
employees that had figures for May.
| | 03:15 | Go back into Design view and let's go
ahead and change this relationship between
| | 03:19 | the employees and the query that we
had built by double-clicking on the line.
| | 03:23 | What we want is the option to include
all records from the tbl_Employees and
| | 03:28 | only those records from MaySales
where the joined fields are equal.
| | 03:32 | Now depending on which order you put your
tables in, these two options might be reversed.
| | 03:37 | So yours might be 3 or 2.
| | 03:40 | But what we're looking for is we're
including all records from the Employees table.
| | 03:45 | Access updates the join line to indicate
the choices that we made with the arrow
| | 03:50 | pointing to the MaySales field.
| | 03:51 | If we run this query now, we're going
to get all of our employees regardless of
| | 03:56 | whether they had sales or not.
| | 03:57 | Now we're getting 200 and this is not
the 84 people in our Sales department.
| | 04:02 | So we have one more thing
that we need to change here.
| | 04:04 | You can see that we're also getting
employees in the Advertising or Human
| | 04:07 | Resources departments and it makes sense that
they wouldn't any sales for the month of May.
| | 04:11 | So let's go ahead and
filter them out in Design view.
| | 04:15 | Under Department, I'll
change my Criteria to Sales.
| | 04:19 | Now, when I run this query, I'll get
all 84 people that are in the Sales
| | 04:23 | department, and for the people that had
sales in the month of May we'll get their totals.
| | 04:27 | If they didn't have any sales in May,
it'll be a blank field here in MaySales column.
| | 04:31 | So understanding how your data
tables relate to one another is a critical
| | 04:34 | component to creating well-structured
queries that give meaningful results.
| | Collapse this transcript |
|
|
3. Creating Parameter QueriesUnderstanding parameter queries| 00:00 | So far, we've seen lots of ways that
you the query designer can control the
| | 00:04 | records displayed in the query data sheet.
| | 00:06 | But what if we wanted to setup a
framework and allow the end user of our query
| | 00:10 | to supply some of the input?
| | 00:11 | For instance, what if we have a
question that asks which employees live in the
| | 00:14 | state of blank or blank is
supplied by the end user at runtime?
| | 00:18 | Access allows us to answer these
kinds of questions with a parameter query.
| | 00:23 | A parameter query allows you to create
a query framework that will request some
| | 00:26 | little bit of additional
information every time it's viewed.
| | 00:30 | This saves you from creating a
separate query for each and every possible
| | 00:33 | question that you or your end users might have.
| | 00:36 | Let's go into Access and we will create a
query that answers this specific question.
| | 00:39 | We will go up to the Create tab and
we'll create a new query in Design view.
| | 00:44 | Now, we are interested in finding out
information about our employees and the
| | 00:48 | states they are from.
| | 00:49 | So we will take the Employees table,
double-click on it, and the States table,
| | 00:54 | double-click on that.
| | 00:55 | Go ahead and close the Show Table window.
| | 00:56 | I am going to rearrange these a little,
so I can see all of the fields and we
| | 01:01 | will add a couple of fields to our query.
| | 01:03 | Double-click on FirstName, double-click on
LastName, and we'll double-click on StateName.
| | 01:07 | Now, in previous movies we saw how we can use
the criteria field to specify a specific state.
| | 01:13 | For instance, if I was interested in
all the employees that lived in state of
| | 01:15 | Arizona, I could type-in Arizona for
the criteria, run this uery, and Access
| | 01:21 | returns the 3 employees that live in Arizona.
| | 01:23 | So go ahead and switch back in our Design view
and we will change this into a parameter query.
| | 01:29 | A parameter query uses the square
bracket and then a bit of text to prompt the
| | 01:33 | user for what kind of
information you're looking for.
| | 01:36 | So for instance, I could write open
square bracket, enter state, closing square bracket.
| | 01:42 | When I run this query we will get a
box that pops up that prompts the user
| | 01:45 | to enter the state.
| | 01:46 | This time we will type in California.
| | 01:50 | We have one employee that
lives in the state of California.
| | 01:53 | If I switch back to Design view and then run this
query again, we could type in a different state.
| | 01:58 | This time Florida.
| | 02:01 | Go ahead and say OK and you can see
that we have 6 employees that live in
| | 02:03 | the state of Florida.
| | 02:04 | Let's go back into Design view.
| | 02:07 | Now, we can combine this parameter
request with some of the logical operators
| | 02:11 | that we saw earlier.
| | 02:12 | For instance, if I was interested in
two different States, I could say (nter
| | 02:16 | first state and on the or line
I could say enter second state.
| | 02:23 | Make sure you have enclosed
everything in square brackets.
| | 02:26 | When I run this query, Access
will prompt me for one state.
| | 02:29 | Let's say New York and it will prompt
me for a second state. Let's say Vermont.
| | 02:34 | Now, I can see that I have 7 employees
that either live in New York or Vermont.
| | 02:39 | Let's go back into Design view.
| | 02:41 | I will go ahead and get rid of the second
state and I will expand this field a little.
| | 02:46 | You could also use the Or
statement on the single line.
| | 02:53 | Enter first state or enter
second state. This will give us the same
| | 02:56 | query that we just ran.
| | 02:57 | Let's go ahead and clear this out and
I will just get rid of everything here.
| | 03:03 | We can also use the Like operator and
we see this in the earlier movie, but
| | 03:06 | we can use this with a parameter
request to request something like all the
| | 03:10 | states that begin with the letter A.
We will start with like. We will input
| | 03:14 | our parameter request.
| | 03:18 | Enter state letter. End
that with a square bracket.
| | 03:22 | Now, we need to join this to our
asterisk wildcard and we will see this
| | 03:25 | "concatenates" ampersand use a little
bit later, but here we will just type the
| | 03:29 | ampersand and then asterisk.
| | 03:31 | So this says we want to look for all
the states that start with a letter and
| | 03:35 | they're joined with any letters after that.
| | 03:36 | Let's go ahead and run this.
| | 03:39 | Access will prompt us to enter a letter.
| | 03:41 | I am going to enter A and we will say
OK, and Access will give us all of the
| | 03:44 | results for the states that begin with
the letter A. Parameter queries are a
| | 03:48 | powerful way to add
interactivity to your database.
| | 03:51 | By providing a basic framework, you
can create a single query that serves up
| | 03:55 | answers to lots of different questions.
| | 03:57 | Letting your end users define their own
questions can be a really powerful tool.
| | 04:01 | It adds flexibility to your database
and it could be a way to streamline your
| | 04:05 | work or providing some level of future proofing.
| | 04:08 | Parameter variables will allow
your queries to serve up answers to
| | 04:11 | questions that you didn't even
consider during the design process and I
| | 04:14 | think that's really cool.
| | 04:15 | The Enter Parameter Values dialog box
isn't the only way for your end users to
| | 04:19 | define the variables in a query.
| | 04:21 | In the next movie we will look at how
you can use the data entry forms and
| | 04:24 | buttons to hook into the
variables we've just seen.
| | Collapse this transcript |
| Obtaining parameters from forms| 00:00 | I want to start this movie by passing
on a fundamental concept that can change
| | 00:03 | the way you think about Access.
| | 00:05 | Every objects, every form, every
button or checkbox or label or text box,
| | 00:09 | everything in Access has a value
associated within the database.
| | 00:13 | And every name or value can be
used as an input somewhere else.
| | 00:17 | Really understanding this concept
will set you on the path to becoming
| | 00:20 | a true Access rockstar. And here's why.
| | 00:23 | Access objects, that is the tables,
forms, queries, reports, they are all
| | 00:27 | presented by the interface as
individual components of your database, but in
| | 00:31 | reality it's the interaction and the
interconnections between objects that allow
| | 00:35 | your database to do some pretty amazing things.
| | 00:38 | In the previous movie we looked at
how we can provide our end users with a
| | 00:41 | pop-up dialog box to
define some query parameters.
| | 00:45 | Let's take that a step further and see
how we could define those same parameters
| | 00:48 | using form controls.
| | 00:50 | I've currently got the frm_SalesRange form
opened up from my Chapter 3 custom group.
| | 00:55 | I've had two boxes here
that I can enter values in.
| | 00:58 | I'm looking at the total lifetime sales
to each customer and I have the option
| | 01:02 | here to enter in two parameters.
| | 01:03 | Let's say I am interested in all of
our customers that have spent between
| | 01:06 | $200 and $300 with us.
| | 01:08 | I could types those values here,
200 and 300, and run our query.
| | 01:13 | The query returns only the
customers that we are interested in.
| | 01:17 | So let's see how this is working.
| | 01:19 | Let's go back into our form and we'll
switch into Design view. Let's open up the
| | 01:23 | Property sheet by clicking Property
sheet on the Tools section of the Design
| | 01:27 | ribbon. We'll switch over to the
other tab and if we click on each of these boxes--
| | 01:32 | I will click on the first one
here-- we will see that this box has a name
| | 01:35 | of low. If I click on the
second box it has a name of high.
| | 01:40 | So in our query we can reference these
two boxes, the low and the high box on the
| | 01:45 | form of frm_SalesRange.
| | 01:47 | Let's go into our query and use
those as inputs for our parameter.
| | 01:50 | We will switch to our query, we will
change into Design view, and we will look at
| | 01:55 | the criteria for our lifetime sales field.
| | 01:57 | Now I am going to right-click
here and go into the Zoom box.
| | 02:00 | We can see the whole field at once.
| | 02:03 | In the Zoom box we can see that we are
referencing the low field of the Sales
| | 02:06 | Range form and the high
field of the Sales Range form.
| | 02:10 | We also have some comparison
operators to define the range.
| | 02:13 | So we are looking at greater than or equal
to whatever's in the low field in less
| | 02:17 | than or equal to whatever's in the high field.
| | 02:19 | Let's go ahead and say OK to this.
| | 02:21 | Let's take a look at another
form. I will go ahead and open
| | 02:24 | up frm_EmployeeLookup.
| | 02:27 | This form has a drop-down menu that
asks to show all the employees in the state
| | 02:31 | of blank. Qe could select the state, run
the query, and Access will show us all the
| | 02:36 | employees that live in that state.
| | 02:37 | Let's go ahead and build
this query from scratch.
| | 02:39 | I will close the one that open
the qry_EmployeesLookup and we will
| | 02:43 | duplicate this query.
| | 02:44 | We will create a new query in Design view.
| | 02:47 | Now we are interested in our
employees, so add our Employees table.
| | 02:51 | Let's go ahead and say Close and we
will open this us. We are interested in the
| | 02:56 | FirstName, the LastName, and the State.
| | 03:00 | In the State field the criteria for
state is going to be whatever the value
| | 03:04 | that's currently selected in our form.
| | 03:06 | So we need to know the
name of the specific object.
| | 03:09 | Back into our form we will go into Design view.
| | 03:12 | With our Property Sheet on the
other tab we will select this object.
| | 03:15 | it currently has a name of state.
| | 03:17 | So we are going to reference the
State field on the frm_EmployeeLookup.
| | 03:21 | Let's go back in to our
query and then put the criteria.
| | 03:24 | I will right-click in the Criteria
field under the State and I will select
| | 03:28 | Zoom to give us a little more room to write
and we will write up a hook into the form.
| | 03:33 | It looks something like this. First we
need to tell Access what type of object
| | 03:37 | we are looking for. We are looking for
one of our forms so I will write forms.
| | 03:41 | Each step needs to be
separated by an exclamation mark.
| | 03:44 | So I will go ahead and write Forms!
| | 03:48 | The next thing we want to
know is what we are looking for.
| | 03:50 | We are looking for frm_EmployeeLookup,
followed by another exclamation mark, and
| | 03:59 | then what field on this form are
we wanting. We want the State field.
| | 04:02 | So our entire code read Forms!
frm_EmployeeLookup! State.
| | 04:09 | Let's go ahead and say OK and we will
notice that Access has cleaned up our code
| | 04:14 | a little bit. It added some square
brackets they are each of the elements.
| | 04:18 | And if we go ahead and say View,
we are currently getting no results.
| | 04:22 | That's because our form is
currently in Design view.
| | 04:24 | The value for this field is currently unset.
| | 04:26 | Let's switch back into Datasheet
view and we will select a value.
| | 04:29 | I will select Hawaii.
| | 04:31 | Go back into our query, switch to
Design view, and then switch back to
| | 04:36 | Datasheet view to rewrite it.
| | 04:37 | We will notice that is now picking up
the value and we are only displaying
| | 04:40 | the states of Hawaii.
| | 04:42 | We can go back to our form, pick a new
state, how about Kentucky. In our query if we
| | 04:48 | refresh it, we will get the
new state showing Kentucky.
| | 04:51 | Now this wasn't meant to be an all-
inclusive look at creating amazing forms, but
| | 04:55 | I hope these examples have given you
a little taste of the types of user
| | 04:58 | interface tools that are at
your disposal with Access.
| | 05:02 | By combining what we already know
about creating robust queries with some
| | 05:06 | additional insights into the larger
framework of Microsoft Access databases,
| | 05:10 | my goal is to get you thinking about
your own projects and hopefully get you
| | 05:13 | excited about applying these
tools to your own database needs.
| | Collapse this transcript |
| Creating a combo box| 00:00 | In the last movie we look at how we
could take the value from a drop-down menu
| | 00:03 | on a form and use it as a criteria and a query.
| | 00:06 | In this movie, I want to take a quick
look at how the combo box was created and
| | 00:10 | how the state names are
populated in the drop-down list.
| | 00:12 | I've currently got that form frm_
EmployeeLookup opened. Let's go ahead and
| | 00:17 | switch into Design view.
| | 00:19 | We'll take a look at the Properties
for this form by clicking on the object.
| | 00:23 | If it is not open, you can open up the
Property Sheet and then we'll switch to the Data tab.
| | 00:28 | We can see that the Row Source for this
particular object includes this long code.
| | 00:32 | Now I can see that the select statement
here indicate that this is a query in SQL view.
| | 00:38 | If I click on the Build button we'll open
the same query in the Query Design window.
| | 00:42 | Now this query is embedded in the
form. You won't find it anywhere in
| | 00:46 | the navigation pane.
| | 00:47 | I can run the query to see the results
that are being returned and we notice
| | 00:51 | that we have a column for the
abbreviation for the state and the state name.
| | 00:53 | Let's take a look at this in Design view again.
| | 00:56 | So the query that's populating the drop-
down menu is using the table States.
| | 01:00 | From that we've got a field for the
abbreviation and the field for the state name.
| | 01:04 | We're also using a second field of the
StateName to sort it ascending, but we're
| | 01:08 | not showing it, so this is
providing the alphabetical list.
| | 01:12 | Now this brings up an interesting question.
| | 01:13 | In the drop-down menu we only see the
full state nam. We're not seeing the
| | 01:17 | abbreviation and now that we're
thinking about it, how do the full state name in
| | 01:21 | the form match up to the abbreviated
state name that the query returned?
| | 01:26 | Let's run that query again so
you can see what I'm saying.
| | 01:28 | We'll close this query, we'll return to
the Form view, and we will run our form.
| | 01:32 | I'll go ahead and select Colorado.
| | 01:34 | We'll run our query and we'll see
that Access is matching the full state
| | 01:39 | Colorado to the query with
the abbreviated state CO.
| | 01:43 | So what's going on here?
| | 01:45 | Both of these questions can be answered
by taking a look at the default way that
| | 01:48 | the Combo Box Wizard creates an embedded query.
| | 01:51 | Let's go to the wizard and create
another combo box to see what's going on.
| | 01:54 | We'll go back to the form
and switch into Design view.
| | 01:58 | We'll take our combo box object,
click on that once, and we'll set a second
| | 02:02 | combo box anywhere in the form.
| | 02:05 | If you click once, the Combo Box Wizard will open.
| | 02:07 | Now the first step here is to create the
combo box and it'll get the values from
| | 02:11 | another table or query. We'll go
ahead and say Next. We'll select our States
| | 02:15 | table here and say Next.
| | 02:18 | Access will ask us what fields from
the states contain the values you want to
| | 02:22 | include in the box?
| | 02:23 | Let select Abbreviation and StateName.
We add that to the Selected Fields and
| | 02:28 | say Next. You can go ahead and sort
ascending by StateName to provide the
| | 02:32 | alphabetical list and we'll say Next again.
| | 02:36 | Finally here's where the answer is.
| | 02:38 | By default Access hides the key
column. If you turn that off Access will
| | 02:42 | display the key column.
| | 02:43 | Let's go ahead and leave ours on.
| | 02:46 | We'll go ahead and say Next.
| | 02:48 | Go ahead and select StateName from the
Available Fields to show in the combo box.
| | 02:52 | And we'll say Next and we'll just
accept the default name. Press Finish.
| | 02:56 | Let's take a look at this combo box
and see how it differs from the one
| | 03:00 | we created previously.
| | 03:01 | We will go into Form view, use the new
combo box, and because we decided not to
| | 03:06 | show the abbreviated field, we're
getting both here in the drop-down menu.
| | 03:11 | I can select one and Access displays the
first column only in the drop-down menu.
| | 03:16 | So with the ID column hidden, which
is Access' recommended option in the
| | 03:19 | wizard, the state name is displayed
while the value of the combo box remains
| | 03:24 | linked to the hidden abbreviation.
| | 03:26 | When the query runs it grabs the value
of the combo box, not necessarily what's
| | 03:30 | being displayed to the user.
| | 03:32 | Now obviously this could be pretty
confusing behavior, which is why I recommend
| | 03:36 | building the embedded query
manually as we'll see in Chapter 7.
| | 03:39 | But now that we know that the
abbreviation field is simply hidden, we can
| | 03:42 | go back to our original dropdown menu and
reveal it with a simple tweak to its properties.
| | 03:46 | We'll go back into Design view, we'll
take a look at the properties for the first
| | 03:50 | drop-down menu, and under the Format tab
we'll look for this Column Widths section.
| | 03:55 | We'll see that we have two values here.
| | 03:57 | The first one is 0, the second
one is 1. Let's change 0 to 0.5.
| | 04:02 | Click off of it to save that value,
switch back into Form view, and now we can
| | 04:07 | see that in our dropdown menu we've
revealed both the abbreviation and the
| | 04:12 | full name for the state.
| | 04:13 | So we can see that the value of the box,
the abbreviated field, was always there.
| | 04:18 | It just wasn't displayed; it had a zero width.
| | 04:20 | So that's how we managed to link
the full name of the state to the
| | 04:23 | abbreviation in the query.
| | 04:25 | All of this is thanks to the
default recommended behavior of the Combo Box Wizard.
| | Collapse this transcript |
|
|
4. Applying Aggregate Calculations (aka Totals Queries)Understanding the Totals field| 00:01 | Access databases are really good at
collecting and storing large amounts of raw data.
| | 00:05 | Most of the time you want to summarize
or aggregate data points together to get
| | 00:10 | a better understanding of some of the
trends or rankings that might otherwise be
| | 00:13 | buried within all that data.
| | 00:15 | We can create that query by going
to Create tab and then Query Design.
| | 00:19 | Let's add our DirectCustomers
table and close this window.
| | 00:24 | Let's add a couple of fields here,
FirstName and LastName, and we'll run this query.
| | 00:32 | We'll see that Access after brief
calculation returns a thousand records.
| | 00:36 | This is the number of
customers that we have in our database.
| | 00:39 | We can verify that by going into
Chapter 1 and taking a look at the
| | 00:43 | DirectCustomers table.
| | 00:45 | I'll open this and we'll see that
we have the same thousand records.
| | 00:48 | Go ahead and close this down.
| | 00:49 | I'll go back to our Design view in the Query.
| | 00:52 | Let's add another table.
| | 00:53 | I'll right-click and say Show Table.
| | 00:57 | Let's add our Orders table.
| | 01:00 | We'll close the Show Table
window and we'll add OrderID.
| | 01:03 | If I run the query now, Access
going to returned 2200 records.
| | 01:10 | This is the number of transactions
that we have in our database, and we can
| | 01:13 | verify that by taking a look at
our Orders table. I open that up.
| | 01:17 | We'll see the same value, 2200 records.
| | 01:21 | Okay, let's close that out.
| | 01:23 | So when you add fields to your query,
access expands the data table to
| | 01:28 | accommodate all of the data.
| | 01:29 | We have multiple customers in our
database that have placed multiple orders.
| | 01:33 | We can see that by looking at the
OrderID and we have first and last name
| | 01:37 | repeated several times.
| | 01:39 | So Access takes our query results and
expands them to accommodate the most transactions.
| | 01:45 | We have 2200 transactions and
you can see that some customers are
| | 01:49 | repeated multiple times.
| | 01:50 | Let's go back in our Design
view and take advantage of this.
| | 01:54 | We can go ahead and remove the OrderID.
So you can move our cursor to the very
| | 01:58 | top here. We can right-click and say Cut.
| | 02:01 | Let's add another table to our query.
| | 02:04 | Right-click, Show Table,
an let's add our Products table.
| | 02:08 | From here we can get the total
price that each customer has spent.
| | 02:11 | We'll close this out and add Price to our query.
| | 02:16 | Let's run this query again.
| | 02:17 | We will see that we have the same 2200
records that represents the number of
| | 02:23 | transactions in our database.
| | 02:26 | Back to Design view.
| | 02:27 | Let's go ahead and turn on our totals row,
using this button up here on the ribbon.
| | 02:31 | When we activate that you'll notice a new row
here and all of these fields get a Group By clause.
| | 02:37 | Let's see what happens
when we run the query now.
| | 02:41 | We'll see that number has
diminished from 2200 to 2152.
| | 02:47 | So what does that mean?
| | 02:49 | That means that there are a
couple of records in here
| | 02:51 | where we have several customers that have
placed probably the same order multiple times.
| | 02:55 | So we have fields where the first name, the
last name, and the price were all the same.
| | 03:01 | By turning On the Totals column, Access
grouped those values together into a single record.
| | 03:07 | Let's go back in our Design view.
| | 03:09 | Now we could take advantage of this
grouping level by using a dropdown menu on
| | 03:13 | this Group By field.
| | 03:14 | If I click here under Price we'll
see that we have multiple options for
| | 03:18 | summary calculations.
| | 03:21 | Instead of just grouping those values
together we can add them up using the Sum function.
| | 03:25 | Let's click on Sum and we'll say Run.
| | 03:29 | Now we can see that we're down to 894 records.
| | 03:32 | This is the number of customers
that have actually placed orders.
| | 03:35 | The sum total of all of their orders
is represented in this third column.
| | 03:39 | We can sort it by clicking on this
drop-down menu and say Largest to Smallest
| | 03:44 | and we can see that Sebastian Rich over
the lifetime of our database has spent the
| | 03:48 | most money with the Two Trees
Olive Oil Company. He spent $260.24.
| | 03:51 | Let's go back into Design
view and add a few more fields.
| | 03:58 | We'll add another column for Price.
| | 04:00 | We will change this group by, instead
of Group By, we'll turn this to Average.
| | 04:07 | Let's add one more column for Price.
| | 04:10 | We'll change this one to Count.
| | 04:12 | Okay, so now we've got a query that'll
give us the first name of our customer,
| | 04:18 | the last name of our customer, a sum
total of all of their transactions, the
| | 04:23 | average value for the transaction, and
the number of transactions that they have
| | 04:26 | placed with the company.
| | 04:27 | We will run this query and we
get to see multiple results.
| | 04:32 | Let's expand this average out so we
get the full data and let's go ahead and
| | 04:37 | sort by SumOfPrice again, Largest to Smallest.
| | 04:39 | So Sebastian Rich who spent $260 with
the company usually spends about $43 every
| | 04:46 | transaction and he has placed a total
of six transactions with the company.
| | 04:50 | When building your database it's always
best to keep your data broken into the
| | 04:53 | smallest piece as possible.
| | 04:55 | For instance by having sales figures
for each day, you can use aggregation
| | 04:59 | functions to derive the monthly,
quarterly, or yearly numbers without needing to
| | 05:03 | have separate tables for
month, quarter, or year.
| | 05:06 | By keeping the data points discrete
and using queries to combine the sales
| | 05:09 | figures as needed you'll keep your
database as flexible and it will answer any
| | 05:13 | questions that you might like to throw at it.
| | 05:15 | Aggregate operations will give you
the answers to some of the most common
| | 05:18 | questions that you might have about your data.
| | 05:20 | They provide the mechanism for all kinds
of summaries, trend analysis, or rankings.
| | 05:26 | They're great way to move beyond
simply reviewing your data to understanding
| | 05:29 | what it actually means.
| | Collapse this transcript |
| Creating aggregate calculations| 00:01 | In the last movie, we learned some of
the types of calculated totals that we
| | 00:04 | could use within our queries.
| | 00:05 | Let's take a look at how we can build a
query using the wizard that will include
| | 00:08 | some of these calculations.
| | 00:10 | We'll go up to the Create tab
and we'll select Query Wizard.
| | 00:13 | We will create a Simple Query Wizard.
| | 00:16 | So go ahead and say OK.
| | 00:19 | We'll choose a couple of tables.
| | 00:20 | The first table we want is
our DirectCustomers table.
| | 00:24 | From that, we'll take
our FirstName and LastName.
| | 00:26 | Then we'll choose our Products table.
| | 00:30 | From there, we'll choose Price.
| | 00:33 | We'll add that to our query.
| | 00:35 | Go ahead and say Next.
| | 00:36 | Now because we included Price, which
is a numerical field, Access gives us
| | 00:41 | some summary options.
| | 00:42 | Let's take a look at those.
| | 00:45 | Here Access gives us the ability
to choose Sum, Average, Min, or Max.
| | 00:50 | We can also count up the number
of records within our database.
| | 00:53 | Let's go ahead and say OK.
| | 00:55 | We'll turn all of those on and we'll say Next.
| | 00:59 | Let's go ahead and save
this query as qry_aggregate.
| | 01:04 | We will open this query to view the information.
| | 01:08 | Let's select Finish and we'll see we
have a query that's similar to the one we
| | 01:13 | built in the last movie with
FirstName, LastName, the sum total of
| | 01:17 | transactions, the average price of
their transactions, the minimum order that
| | 01:21 | they've ever placed, the highest
transaction that they've ever placed, and then
| | 01:26 | number of transactions.
| | 01:27 | Let's take a look at this in
Design view to see what the wizard did.
| | 01:30 | There are four interesting things
that I'd like to point out here.
| | 01:34 | First of all, Access
automatically added the Orders table.
| | 01:38 | We didn't select anything from the
Orders table but Access was smart enough to
| | 01:41 | know that in order to go from Direct
Customers to Products, we needed that
| | 01:44 | Orders table in between to make that link.
| | 01:47 | The second thing that's interesting is
the Totals row populated with all of the
| | 01:51 | aggregate functions that we saw before.
| | 01:53 | Now we know that we can use the drop-
down menu to select these when we're
| | 01:56 | building this manually, and we also
have ability to choose from a much wider
| | 02:00 | selection of aggregate functions
than what's presented in the wizard.
| | 02:02 | For instance in the wizard, there was no
way to get standard deviation or variance.
| | 02:08 | The third thing that's interesting is
that Access added generic aliases to
| | 02:12 | the top of our columns.
| | 02:13 | So we have Sum Of Price:
and then we're adding the sum of our price.
| | 02:17 | We've got a column that says Average Of Price:
| | 02:19 | and then that's the one
that gets the average price.
| | 02:22 | We can go ahead and change these at
anytime to be a little bit more specific.
| | 02:26 | For instance, I'll write Average
Transaction, and when we run our query,
| | 02:31 | we'll see that that column
header is a little bit more specific.
| | 02:36 | Back in the Design view.
| | 02:38 | The last thing that's interesting
is how Access handles the counting.
| | 02:41 | Let me scroll over here to the right.
| | 02:44 | We'll take a look at the fields
that provides the count of the records.
| | 02:47 | Let's expand it open so we can see it.
| | 02:50 | Access, instead of using the Total
row to choose Count, it decided to use
| | 02:56 | a function for Count.
| | 02:57 | So basically, it's saying
Count(*) or count everything.
| | 03:02 | In the Totals row it specify
that this is an Expression.
| | 03:05 | So it has Expression to calculate here
and it displays that in the field.
| | 03:10 | So that's as far as the wizard will take us.
| | 03:12 | We could choose from the four
most common aggregate options,
| | 03:15 | Sum, Average, Min, and Max, and
apply those to any numerical data field.
| | 03:20 | We have the option to count up the
number of records in our query and that uses
| | 03:24 | a function for counting.
| | 03:26 | Let's take a closer look at other
functions in calculated fields in the next movie.
| | Collapse this transcript |
| Exploring the Expression Builder interface| 00:01 | In the last movie, we saw that the
Query Design Wizard used a function to
| | 00:05 | calculate the count to the
number of records within our query.
| | 00:09 | You can see that here.
| | 00:09 | I currently got the qry_
OrderHistory query open in Design view.
| | 00:14 | In the very end here, I got the Count function.
| | 00:16 | Let me right click in this field and
I'll say Zoom to open it up in the Zoom box.
| | 00:21 | So the way that Access's wizard
created this counting field is it used a
| | 00:27 | function called Count.
| | 00:29 | Now functions are always
written with the same formatting.
| | 00:31 | You have the name of the function and
then in parentheses wrap whatever you want
| | 00:34 | to apply that function to.
| | 00:36 | So we had a count function that's
counting everything with the asterisk wildcard.
| | 00:40 | We also see that Access's
wizard provided us with an alias.
| | 00:44 | Count Of tbl_Products:.
| | 00:45 | So let's take a look at how we
can apply functions to our database.
| | 00:50 | We'll go ahead and say OK to close the Zoom box.
| | 00:52 | Let's go ahead and close this query.
| | 00:54 | We don't need to save it.
| | 00:55 | I'll create a new query in Design view.
| | 01:00 | We'll add a couple of tables here.
| | 01:01 | We'll add the Direct Customer table and
the Orders table and close Show window.
| | 01:06 | We'll add FirstName and LastName to
our query and then in the third field
| | 01:12 | a we re going to use a function to
count up the number of transactions.
| | 01:16 | Let's right-click and go the Build.
| | 01:17 | This will open up the Expression Builder.
| | 01:19 | Now the Expression Builder
window has a couple of functions.
| | 01:25 | First, the top pane allows you to
write in SQL syntax or functions that will
| | 01:30 | then populate into this field.
| | 01:32 | They'll populate down here.
| | 01:34 | Then there is three windows on the bottom.
| | 01:35 | The bottom window on the left gives you a
list of all of the objects within our database.
| | 01:40 | So it's just the Two Trees.accdb file.
| | 01:43 | If I expand that open, I have
access to everything within my tables,
| | 01:46 | queries, forms, and reports.
| | 01:48 | We also have a list of functions
and we have our built-in functions.
| | 01:52 | These are the functions
that are built into Access.
| | 01:54 | If we've written custom functions,
those would appear into the Two Trees folder
| | 01:58 | and if we are connected to a SharePoint site,
we might see some things under Web Services.
| | 02:01 | But for now, let's just take a look at
the function that are built-in with Access.
| | 02:05 | We'll click on Built-In Functions
and the second two panes populate.
| | 02:09 | Let me go ahead and expand this
window a little bit so we have more room.
| | 02:13 | Middle window shows us the
categories that the expressions fall into.
| | 02:16 | We have Arrays, Conversion,
Database, Date/Time, etcetera.
| | 02:21 | Towards the bottom, we can see
a category called SQL Aggregate.
| | 02:24 | If we click on that, the third
window populates with all of the functions
| | 02:29 | within that category.
| | 02:30 | And these are the ones we saw
before in the drop-down menu.
| | 02:32 | Average, Count, Max, Min,
Standard Deviation, Sum, and Variants.
| | 02:37 | So let's go ahead and use the Count function
to count the number of records in our database.
| | 02:42 | We'll double-click on Count to add
the expression to our Build window.
| | 02:46 | We could see that Access helps us out
with some of the syntax that says
| | 02:48 | we are going to apply the Count
function and we need to supply an expression
| | 02:51 | that we want to count up.
| | 02:53 | So it's saying Count.
| | 02:54 | Well, what are we going to count?
| | 02:55 | If we click in there, we can provide that value.
| | 02:59 | Click on the expression and the whole
section will highlight and then we'll
| | 03:02 | drill into our tables to find
the piece of what we want to count.
| | 03:05 | We go into Orders table and let's count up the
Customer IDs that appears in the Orders table.
| | 03:11 | We'll go ahead and say OK and run our query.
| | 03:15 | Now Access is going to tell us that
we try to execute a query that does not
| | 03:19 | include the specified expression
FirstName as part of an aggregate function.
| | 03:24 | And that's really just a complicated
way of saying that Access doesn't know how
| | 03:27 | we are grouping things together.
| | 03:28 | We need to first to activate our Totals
column anytime we are using a function.
| | 03:32 | Let's go ahead and say OK.
| | 03:34 | It will turn on our Totals column.
| | 03:36 | If we try and run it again,
we'll get a second message.
| | 03:40 | Access cannot have an Aggregate
Function in a GROUP BY clause.
| | 03:43 | You need to change this GROUP BY to
Expression so that Access knows that we want
| | 03:47 | to calculate those value in a
group based on those values.
| | 03:51 | We'll go ahead and say OK and
change our GROUP BY to Expression.
| | 03:57 | Now if we try to run it,
we'll get the calculated results.
| | 04:00 | Let's go ahead and sort our expression,
the Count column, and we'll sort this
| | 04:04 | Largest to Smallest, and we'll see the
Tobias Rivera has had the most number of
| | 04:10 | transactions with our company.
| | 04:11 | He's had 9 total orders.
| | 04:13 | As you get more experience writing out
the expressions that you want to use, you
| | 04:17 | might find it quicker to just write
them out by hand directly in the grid.
| | 04:20 | But for learning the proper syntax,
troubleshooting, or for discovering how new
| | 04:25 | functions work, the
Expression Builder is a fantastic tool.
| | Collapse this transcript |
| Using mathematical operators| 00:01 | One of the key factors in creating a
clean and robust database is knowing
| | 00:05 | what data you need to store and what data
you can derive or create from other records.
| | 00:11 | For instance, if we think about how
our payroll database might work,
[00:00:14.2]
the number we are after is how much the employee
earned at the end of a two week pay period.
| | 00:19 | But will the dollar amount appear
in any of our tables? Probably not.
| | 00:23 | More than likely a payroll database
will simply include a record for the
| | 00:27 | employee's hourly wage and another record
of the number of hours worked on each shift.
| | 00:31 | When cutting the checks the payroll
department will simply add up the number of
| | 00:35 | hours worked during that pay period
and multiply that by the hourly wage.
| | 00:39 | Then they will have the total for the check.
| | 00:42 | By using some simple mathematical
operations, in this case addition and
| | 00:46 | multiplication, the payroll department
is able to derive the value of the check
| | 00:50 | even though it doesn't actually
appear anywhere in the database.
| | 00:53 | The Design view in Access allows us
to easily add some of these types of
| | 00:56 | mathematical operators to our queries.
| | 00:59 | Let's create a new query in Design
view and we'll take a look at that.
| | 01:02 | Let's go ahead and add our Products table
to the query and close the Show Table window.
| | 01:08 | We are not going to use any
fields from the Products table.
| | 01:11 | I simply want to illustrate the concept of
creating a calculated value in the field headers.
| | 01:16 | So in my first field we could type
in the mathematical expression 5+3.
| | 01:21 | If I run this, Access performs that
mathematical operation and returns a value of 8.
| | 01:27 | Now there's 90 records here showing,
because we've built this query using the
| | 01:31 | Products Table as our table source.
| | 01:33 | There are 90 products listed in our
Products table, so Access returns 90 records.
| | 01:38 | In the next field I am going to go ahead and
add another one, 5-3. Let's try 5*3 and 5/3.
| | 01:50 | If we run this query Access performs
the math and returns 2, 15 for 5*3, and
| | 01:57 | let's expand this, 1 and 2/3rd for 5/3.
| | 02:02 | So those are four basic mathematical
operations that Access understands.
| | 02:06 | Let's take a look at a couple of others.
| | 02:07 | I will go back in Design view and I'll
delete all of these records by highlighting
| | 02:13 | the fields and then
pressing Delete on my keyboard.
| | 02:16 | In addition to those four math
operations we can use exponentiation.
| | 02:20 | So I can raise 5 to the 3rd power.
| | 02:23 | Here I am using the up caret,
which is Shift+6 on your keyboard.
| | 02:27 | If I run that, we will get 5 cubed, which is 125.
| | 02:33 | Okay, back in Design view.
| | 02:34 | Let me delete this out.
| | 02:37 | There are two additional mathematical
operators that we can use for division.
| | 02:41 | Our standard division returns
a whole number and a remainder.
| | 02:45 | The second form of division is called
integer division and this is represented
| | 02:48 | by the backslash character.
| | 02:50 | Integer division will ground both values and
only return the whole number, not the remainder.
| | 02:56 | The third form of division is called
modulo and it only returns a remainder.
| | 03:01 | It drops the whole number altogether.
| | 03:02 | So let's see how this could be useful.
| | 03:04 | Let's say that we have a thousand
bottles of olive oil. We are packing them into
| | 03:08 | cases of 12 bottles each.
| | 03:10 | Our standard division
would read 1000 divided by 12.
| | 03:14 | Our integer division would read 1000\12.
| | 03:20 | And our modulo division would read 1000 mod 12,
with a space on each side of the word mod.
| | 03:29 | So let's run this and see what these return.
| | 03:31 | You can see that our standard
division returns 83 and 1/3rd.
| | 03:37 | This is the number of cases that we
can fill with our thousand bottles.
| | 03:40 | So we have 83 cases and then
we have 1/3rd of another case.
| | 03:45 | The integer division only
returns the number of full cases.
| | 03:48 | So with our 1000 bottles we can 83 full cases.
| | 03:52 | The modulo division returns only the remainder.
| | 03:56 | So after all of our cases are packed
we have got 4 bottles left over that
| | 03:59 | didn't fill a new case.
| | 04:01 | So that's how you can use the integer,
modulo, and the standard division.
| | 04:05 | Let's return to the Design view
and we'll see how we can apply these
| | 04:08 | mathematical operators to our query data.
| | 04:11 | Back in Design view, I will
highlight these 3 fields and press Delete.
| | 04:16 | So now let's say we want to calculate the
total price per ounce of our specific oils.
| | 04:21 | We could add a ProductName, our
Ounces, and our Price to our query.
| | 04:28 | In the fourth column we want to add a
calculated field that takes price and
| | 04:31 | divides it by ounces.
| | 04:33 | We can do that in the Expression Builder.
| | 04:35 | Let's right click in the field and select Build.
| | 04:39 | In the Expression Builder we are going to
go ahead and create our calculated field.
| | 04:43 | We could drill down into our tables,
so we will go into TwoTrees.accdb.
| | 04:47 | We will expand our Tables column
and we will find our Products table.
| | 04:52 | From our Products table we will take the
Price field and I will double-click on that.
| | 04:57 | Go ahead and type a forward slash for
divide and then we'll double-click on
| | 05:01 | Ounces, which is our Size field.
| | 05:04 | So now we have a calculated field
that will take the price and divide it by
| | 05:08 | the size in ounces.
| | 05:09 | We will say OK and let's
go ahead and run this query.
| | 05:12 | We could see that we have specific oils,
so the query results will return each oil,
| | 05:18 | the size and ounces, the price to
the customer, and then we have a calculated
| | 05:23 | field that calculates the price
per ounce for a customer's product.
| | 05:28 | Let's go ahead and sort this largest
to smallest and here we have our highest
| | 05:33 | price per ounce product in our inventory.
| | 05:36 | It should be noted that mathematical
operators only work with numerical data.
| | 05:40 | Access does offer some similar
functionality that applies to text and we will
| | 05:44 | see that in the next movie.
| | Collapse this transcript |
| Applying text functions| 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
| | 00:07 | text-based data types.
| | 00:09 | For instance, we could extract a person's
initials from a table of first and last names.
| | 00:13 | We can do this using what
Access calls string functions.
| | 00:16 | The key here is to think about your
text-based data as a simple string of
| | 00:19 | characters or individual letters and
we'll use their positions to help extract
| | 00:23 | the pieces that we need.
| | 00:24 | Let's go under the Create tab and
we'll create a new query in Design view.
| | 00:29 | This time we'll look at our Employees table.
| | 00:30 | We'll double-click to add it
and we can close Show Table.
| | 00:34 | We'll add the FirstName
and LastName to our query.
| | 00:38 | In the third field, we'll invoke our
Expression Builder by right-clicking
| | 00:42 | and selecting Build.
| | 00:43 | Now the Expression Builder will
help us build our String functions.
| | 00:48 | Let's build an expression that will
combine the first name and the last name
| | 00:52 | into a single column.
| | 00:54 | We'll go into our TwoTrees database,
we'll look through our tables, and we'll
| | 00:57 | find our Employees table.
| | 00:59 | Let's grab the FirstName
by double-clicking on it.
| | 01:03 | Now to join two text fields, you'll
use the concatenate symbol which is the
| | 01:06 | ampersand or Shift+7 on your keyboard.
| | 01:10 | This will join the FirstName
to whatever we put after it.
| | 01:12 | Let's go ahead and double-click on LastName.
| | 01:16 | Now we have a calculated field which
will take the characters in the FirstName
| | 01:19 | and it will append the
characters from the LastName to it.
| | 01:22 | Now in order to make this legible, we
want to add a space in between the two.
| | 01:26 | We could do that by inserting a
space between here, wrapping the space in
| | 01:30 | quotation marks, and using
another concatenate symbol.
| | 01:33 | So now we have the characters in the
FirstName, joined to a space, joined to the
| | 01:38 | characters in the LastName.
| | 01:40 | Let's go ahead and say OK and run
this query and we'll see that we have the
| | 01:44 | dirst and last name being
combined into a third column.
| | 01:47 | Let's go back into Design view, right-
click on this field, and return to the build.
| | 01:54 | Okay, so let's highlight this and
we'll delete this expression out.
| | 01:58 | Let's take a look at some of the built-in
functions that can help us with text data.
| | 02:01 | If we go into our Functions in the
Functions Built-In to Access, we have a
| | 02:06 | whole category called Text and there's a long
list of expressions that apply to text fields.
| | 02:11 | Let's take a look at the Left expression.
| | 02:13 | Down in the bottom of the Expression
Builder, Access tells us that the Left
| | 02:18 | function will extract a certain number of
characters from the left side of a string.
| | 02:22 | This will allow us to extract the employee's
initials from their First and LastName fields.
| | 02:27 | Let's double-click on Left to add it
to our expression and we can see that we
| | 02:31 | have two required components.
| | 02:33 | The first is a string, what
characters are we going to extract from.
| | 02:36 | Here we're going to feed it our
FirstName field from the tables,
| | 02:40 | Employees' FirstName.
| | 02:42 | The second requirement is how
many characters you want to extract.
| | 02:46 | We just want the first one so I'll type 1.
| | 02:48 | Let's go ahead and do another
one to join this to the LastName.
| | 02:54 | We'll use our concatenate and then we'll
add another function for Left. Built-In
| | 03:00 | Functions > Text > Left.
| | 03:03 | This time we'll feed it our LastName field
from the Tables > Employees, and LastName.
| | 03:12 | Again, we only want the first character.
| | 03:15 | So my finished expression says
that we're going to extract the left 1
| | 03:18 | character from the FirstName and
then we're going to add to it the left 1
| | 03:22 | character from the LastName.
| | 03:25 | Let's go ahead and say OK and
run our query to see the results.
| | 03:29 | Now we have a field that shows the employee's
initials from the First and LastName fields.
| | 03:33 | Now as you saw in the Expression
Builder, there is a whole long list of
| | 03:37 | expressions that we can use
that apply to text fields.
| | 03:40 | I would encourage you to use the
Expression Builder not only as a tool to help
| | 03:43 | you build expressions, but as a tool
you use to learn about expressions.
| | 03:48 | By going through the list, you can
explore some of the other functions and
| | 03:51 | see how that may work.
| | 03:52 | By combining some of these functions
in our queries, we can come up with some
| | 03:55 | interesting new ways to present our results.
| | 03:57 | When feeding our query results into a
report or simply reviewing the data sheet,
| | 04:02 | the appropriate and creative use
of string functions can increase the
| | 04:05 | legibility of our data.
| | Collapse this transcript |
|
|
5. Working with Dates in QueriesUnderstanding dates as serial numbers| 00:00 | You would think that storing dates
in your database will be a fairly
| | 00:02 | straightforward routine operation.
| | 00:05 | On the surface, to you and I it is.
| | 00:07 | But behind-the-scenes Access does a
lot of work to make sure that your
| | 00:10 | date-specific records are kept in order.
| | 00:12 | Understanding how Access stores,
retrieves, and formats date and time-based data
| | 00:17 | can really help down the road.
| | 00:18 | Access uses a serial number
system to keep track of dates and times.
| | 00:22 | Let's open up the Chapter 5 custom group
and we'll take a look at our Dates table.
| | 00:27 | Here we have a table listing of dates.
| | 00:29 | If I run a query called DateSerial
number, I have those dates listed down and I
| | 00:34 | have a function that converts the date
into the serial number representation.
| | 00:38 | You'll see that dates are represented
by a whole number and they decrease as
| | 00:42 | you go back in time,
| | 00:43 | until you get to December 30th,
1899, which is represented by the serial number 0.
| | 00:48 | Anything before that date gets
negative; anything after that date is positive.
| | 00:52 | Now the same thing applies to times.
| | 00:54 | Let's run another query,
this TimeSerial number query.
| | 00:58 | You'll see that times are represented by
fractions of a day and that makes sense.
| | 01:01 | Starting at 12 midnight, at 0.0, at 12
noon which is halfway through the day is
| | 01:07 | 0.5, three-quarters of the way through
the day at 6 p.m. is 0.75, and if we get
| | 01:12 | to 11:59 and 59 seconds, that's
represented by the serial number 0.9999.
| | 01:19 | Now in these fractions we also
have 0 and we already saw that
| | 01:22 | 'd0 means that this time is
applying to December 30th, 1899.
| | 01:25 | Now you probably never try and extract
a date from a single time field but if
| | 01:31 | we look at our dates again we'll realize
that the whole number is actually saying 0.0.
| | 01:36 | So in Access' world, everything that
happens on a specific date happens at 12 midnight.
| | 01:41 | Let's see how these serial numbers
affect working with comparison operators and
| | 01:45 | dates and time data.
| | 01:46 | When you have a field that stores
date-only data, the relationship to each
| | 01:51 | other is pretty straightforward.
| | 01:52 | May 11th is less than May 12th, May
12th is equal to May 12th and May 13th is
| | 01:58 | obviously greater than May 12th.
| | 02:00 | But if you include dates and times in
the same records, for instance I have May
| | 02:04 | 11th at 11:59:59, things get a little bit weird.
| | 02:08 | The relationship for May 12th
here is obviously less than May 12th.
| | 02:11 | 1 second later at 12 midnight, Access
says this is equal to May 12th and 1
| | 02:17 | second after midnight on May 12, Access
thinks this is after May 12th or greater than.
| | 02:23 | So when using comparison operators with
dates and times, it makes a difference
| | 02:26 | if your date and time
fields are together or discrete.
| | 02:29 | Understanding the serial nature of how
Access stores dates can clear up some
| | 02:33 | evaluations down the road.
| | 02:35 | It's always a good idea to understand
what's going on behind-the-scenes in order
| | 02:38 | to make better sense of how we
can work with that on the front end.
| | 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:04 | date comparisons or define ranges of dates.
| | 00:07 | We've seen these comparison operators
before, but I thought it would be valuable
| | 00:10 | to review them and how they can be
applied specifically to working with dates.
| | 00:14 | We'll create a new query in Design
view and we'll add a couple of tables.
| | 00:19 | Let's grab our Customers
table and our Orders table.
| | 00:24 | Go ahead and close Show Table and
we'll add some fields to our query.
| | 00:27 | Let's add First and LastName and from
the table Orders, we'll add OrderDate.
| | 00:32 | Now in our Criteria
section, we can specify a date.
| | 00:35 | Let's say we are only interested in
records from May 12, 2010. 05/12/10.
| | 00:42 | Let's go ahead and run this query and we
get the two records that happened on that day.
| | 00:47 | Back in Design view as you'll notice
that Access wrapped our date around date
| | 00:51 | delimiters or these hash-marks.
| | 00:53 | Now Access is really
flexible when it comes to dates.
| | 00:56 | We can enter them in a variety of
ways and it'll understand all of them.
| | 00:59 | We can enter a 5-12-2010 and Access will
understand that that's exactly the same thing.
| | 01:05 | If we go back to Design view,
we can even type a date like this.
| | 01:10 | May 12, 2010, and again Access
says that's exactly the same thing.
| | 01:17 | It understands all of those different formats.
| | 01:19 | Let's go back into Design view.
| | 01:20 | Now we can use comparison
operators with our dates as well.
| | 01:23 | For instance, if we're interested in all
the records that happened after May 12th,
| | 01:28 | we can use the greater than
symbol, greater than May 12th, 2010.
| | 01:31 | We'll run that and we'll see all the
records that happened after May 12th.
| | 01:36 | Let's go back to Design view.
| | 01:38 | we could also say greater
than or equal to May 12th.
| | 01:42 | This time it will return dates
including May 12th and everything after.
| | 01:47 | Let's go back to Design view and we'll
apply some logical operators to our criteria.
| | 01:51 | Let me go ahead and expand this
a little bit so I have more room.
| | 01:56 | After my statement greater than or equal
to May 12, 2010, let's add another date
| | 02:00 | to specify a range. And less
than or equal to 5/30/2010.
| | 02:09 | Now we'll get a range of dates.
| | 02:11 | Go ahead and run it.
| | 02:12 | We'll see we have 21 records within that range.
| | 02:15 | Notice that we're getting May 12 and May 30.
| | 02:18 | So the greater than or equal to is inclusive.
| | 02:20 | Let's go ahead and go back to Design view.
| | 02:22 | Now rather than applying greater than
or less than with dates, there are two
| | 02:27 | functions that we can use specific for dates.
| | 02:29 | That would be a Between statement.
| | 02:32 | Here we can write Between 5/12 and 5/30.
| | 02:36 | If we run that, we'll see we get the
exact same number of records, 21, and it
| | 02:42 | includes May 12 and May 30.
| | 02:44 | Finally, let's look at how we can
apply a parameter request with dates.
| | 02:48 | Let's go back into Design view and
instead of supplying the dates hard-coded into
| | 02:52 | our query, let's make this a parameter
request so that the end user can supply
| | 02:57 | the dates that they're interested in.
| | 02:59 | We'll write it like this, Between,
| | 03:01 | square bracket, Enter Start, closing square bracket,
and then we'll write Enter End in square brackets.
| | 03:01 | So now we have a parameter request
that's requesting the start and end date
| | 03:15 | to define the range.
| | 03:17 | If we run this query, we'll get our
Enter Parameter Value box where we can
| | 03:20 | enter the start date.
| | 03:21 | Remember, we can enter this in any format.
| | 03:23 | I'll just say 5-12-10.
| | 03:26 | Press Enter and I get the second
box to ask me for the end date.
| | 03:29 | Let's go ahead and enter
this differently. May 30, 2010.
| | 03:34 | Access returns the same records.
| | 03:36 | So using dates and date ranges in a
query's criteria field is a common way to
| | 03:40 | return the most significant data or to
limit the returns to only the most recent
| | 03:45 | and relevant time periods.
| | Collapse this transcript |
| Formatting dates| 00:00 | There are thousands of ways that dates
can be valuable in your database and just
| | 00:03 | as many variations on how
they're formatted or displayed.
| | 00:07 | They can be written longhand for use
on business letters or abbreviated for
| | 00:10 | use on payroll checks.
| | 00:12 | They can be incorporated into serial,
production, or batch ID numbers, or used
| | 00:16 | for quarterly financial reporting.
| | 00:18 | Because Access stores dates as serial
numbers as we saw in the previous movie we
| | 00:23 | have an infinite number of
ways that we can display our data.
| | 00:26 | One of the ways that we can do
that is with the Format function.
| | 00:29 | The Format syntax looks like this.
| | 00:31 | It requires two pieces.
| | 00:33 | It requires the expression that we want
to format and this is where we want to
| | 00:36 | plug in our DateField, and
it requires some symbols.
| | 00:40 | For the symbols we have a variety of options.
| | 00:42 | We'll use a lowercase d to represent days,
m represents months, y represents years.
| | 00:48 | We can also use q for quarters, wd for
weekdays, and ww for weeks of the year.
| | 00:55 | We'll use these symbols in
a variety of configurations.
| | 00:58 | If we write one d that tells Access that
we want to return a single digit or double
| | 01:02 | digit day of the month.
| | 01:04 | If we use two ds, that tells Access that
we wanted double digit day of the month.
| | 01:08 | So for instance days 1
through 9 we'll get a leading 0.
| | 01:12 | If we use three ds, that tells
Access we want returned a three letter
| | 01:15 | abbreviation for the week and if we use
four ds, that tells Access that we want
| | 01:20 | to return the full name of the day of the week.
| | 01:23 | Months work the same way.
| | 01:25 | We have the option to return a single or
double digit month of the year, a three
| | 01:29 | letter abbreviation, or
the full name of the month.
| | 01:33 | With years we have two options.
| | 01:35 | We can either return a two
digit or a four digit year.
| | 01:39 | So let's see how we can combine these
symbols into various configurations to get
| | 01:43 | different date formats.
| | 01:45 | If we format our date like m-d-yy,
we'll get a date that has a one digit month,
| | 01:51 | a one digit day, and a two
digit year separated by hyphens.
| | 01:56 | If we use two mm/dd/yyyy, we'll get a
two digit month, a slash, two digit day, a
| | 02:05 | slash, and a four digit year.
| | 02:07 | Let's look at another.
| | 02:08 | How about mmm, yyyy?
| | 02:09 | This will return a three letter
abbreviation for the month and the four digit
| | 02:15 | year, separated by a comma.
| | 02:17 | One more here dd mmmm, yyyy. We'll get
a two-digit day, the full name of the
| | 02:23 | month, and the four digit year.
| | 02:26 | Let's jump into Access and we'll use
the Expression Builder to build a couple
| | 02:29 | of these functions.
| | 02:31 | We'll start with the Create
tab and go into Query Design.
| | 02:35 | Next, we'll add our Orders table.
| | 02:36 | Let's add our OrderID and the OrderDate field.
| | 02:39 | In the third field we'll
right-click and select the Builder.
| | 02:42 | So let's start typing out our Format function.
| | 02:45 | We'll write the word Format(. Access's
pop-up help here will tell us that the
| | 02:51 | next thing we need to supply is an expression.
| | 02:54 | This is the date that we want to format.
| | 02:56 | Let's drill into our database to find it.
| | 02:59 | We'll going into the TwoTrees database into
the Tables folder and into our Orders table.
| | 03:04 | Here we could double-click
and select the OrderDate.
| | 03:07 | Now we need to supply the
symbols to help us format it.
| | 03:10 | We'll add a comma and a quotation
mark and then we'll put in our symbols.
| | 03:15 | So let's try m.d.yy.
| | 03:20 | We'll add a closing quote and a closing
parenthesis to finish the Format statement.
| | 03:24 | Let's go ahead and say OK and run our query.
| | 03:28 | Access has formatted the dates for
our specifications using the symbols.
| | 03:32 | We have a period as a separator.
| | 03:34 | Let's go back to our Design
view and let's expand this out.
| | 03:39 | We'll see that Access has actually
modified our code a little bit by
| | 03:43 | putting these slashes in.
| | 03:44 | Now the slashes mean that the next
character is literally what we want.
| | 03:48 | The period has special meaning in SQL,
so Access put the slashes in here to
| | 03:53 | specify that we want to actually
include a period and this isn't mean something
| | 03:56 | different in SQL view.
| | 03:58 | Let's go ahead and highlight this out
and we'll change our function a little.
| | 04:01 | Let's be really verbose now. dddd, mmmm dd, yyyy.
| | 04:13 | Let's see what this does.
| | 04:15 | Go ahead and click Run
and we'll expand this out.
| | 04:19 | We'll see that Access returns a
date like Monday, January 10, 2005.
| | 04:23 | So as you can see, the Format function
can be pretty flexible at how you display
| | 04:27 | dates and can satisfy almost any
reporting need that you may require.
| | Collapse this transcript |
| Using other Date/Time functions| 00:00 | Typically, dates and times stored
within your database are very specific.
| | 00:04 | Dates are usually specific to a particular
day and times are specific to the minute.
| | 00:08 | When it comes time to query your data
by month or year, Access provides a few
| | 00:12 | simple ways to extract just the level
of precision that you need from your Date and Time fields.
| | 00:16 | Let's go to Create and we'll take a
look at a new query in Design view.
| | 00:20 | We'll add tbl_DirectCustomers,
tbl_Orders, and tbl_Products.
| | 00:25 | Let's go ahead and close Show Table.
| | 00:28 | Now, let's say we want to take a look
at the orders that came from specific
| | 00:31 | states over a specific reporting period.
| | 00:33 | We'll add states to our query.
| | 00:37 | We'll add State to our query and
we'll add Price from the Products table.
| | 00:40 | Let's turn on the Totals row, because we
want to group everything down to the State.
| | 00:45 | We could turn this Group By to a
Sum to get a sum total for each state.
| | 00:50 | If we run this, Access will return a
list of each state listed, we have 50
| | 00:55 | represented, and the total price
that each State has contributed to our
| | 00:59 | overall bottom line.
| | 01:00 | Now, keep in mind that the
SumOfPrice is adding up all transactions of the
| | 01:03 | entire life of our database,
which goes back several years.
| | 01:06 | Let's go back into our Design view and
see how we can control this a little bit.
| | 01:10 | In Design view I'll right-click
in the third field here, and invoke
| | 01:13 | the Expression Builder.
| | 01:14 | Let's take a look at some functions here.
| | 01:16 | We'll go into Functions >
Built-In Functions > Date/Time.
| | 01:20 | Let's take a look at a function called Year.
| | 01:21 | If I double-click to add it to my
Expression Builder, we'll see that Year
| | 01:25 | requires one input and that's a Date field.
| | 01:28 | We'll click on that to activate it
and then we'll find the Date field from
| | 01:31 | our Orders table. Expand the
TwoTrees database, Tables, Orders and
| | 01:36 | double-click on OrderDate.
| | 01:37 | So now we've got a function that says
we want to extract just the Year from
| | 01:42 | the OrderDate table.
| | 01:43 | Let's go ahead and say OK.
| | 01:45 | Now we're going to group by
the years that are extracted.
| | 01:48 | If we run this query, we'll see that
each state gets every year listed, so we
| | 01:52 | have data from 2005, 2006, 2007, 2008,
2009, and 2010 in our database and each
| | 01:59 | month and the SumOfPrice aggregation
is now left at the Year level instead of
| | 02:04 | the lifetime of the database level.
| | 02:06 | Let's change this up and look at it for month.
| | 02:07 | We'll go back into Design view and
since we already have this constructed,
| | 02:11 | we can easily change this instead of Year, we
can delete Year and write in the word Month.
| | 02:16 | Now, Access is going to extract
just the month from the OrderDate.
| | 02:19 | If I run that, we'll see
basically the same thing.
| | 02:23 | We have our months here on the right
side, January through December for each state,
| | 02:28 | so Alaska, January through
December, and our SomeOfPrice is aggregating
| | 02:32 | to the month level.
| | 02:33 | Now we do have several years represented here.
| | 02:35 | So this is actually aggregating all of
the Januaries together for instance, and
| | 02:39 | that equals $539 and then all of the
Februaries regardless of which year.
| | 02:44 | So in order to make more sense of this,
we might want to aggregate based off the
| | 02:47 | year and month simultaneously.
| | 02:49 | Let's go ahead and write that in here.
| | 02:51 | Year, open parentheses and then
we'll reference our OrderDate field,
| | 02:54 | square bracket, tbl_Orders, closing bracket.
| | 02:59 | That's the table it comes from.
| | 03:00 | I will expand that over a little bit.
| | 03:03 | Next I need our separator, which is
the exclamation mark, and then we need to
| | 03:07 | tell it what field within
the table, the OrderDate field.
| | 03:12 | We'll input a closing square bracket.
| | 03:13 | Finally, we'll finish our Year
function with a closing parenthesis.
| | 03:17 | Now, we've got the year
extracted, and the month extracted.
| | 03:20 | If I run that, we'll see that we now
have our data broken out byyear and month
| | 03:24 | for the SumOfPrice aggregated to that level.
| | 03:26 | In order to maintain flexibility it's
always best to store database upon the
| | 03:30 | finest level of detail that you
could ever conceivably require.
| | 03:33 | Using queries, it's easy enough to
strip away all of the fluff based on the
| | 03:37 | specific task that you're working with
when you don't need that level of detail.
| | 03:41 | With these date and time functions,
Access makes it easy to see exactly what you
| | 03:45 | 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 to
work with dates relative to each other or
| | 00:04 | relative to a specific and static date range.
| | 00:06 | Access provides a couple of additional
functions that will allow us to define
| | 00:10 | dates relative to today
or relative to right now.
| | 00:12 | These are the queries that require a
date range such as within the last 30 days
| | 00:18 | or 2 weeks from today.
| | 00:19 | The first part requires us to
accurately define today's date.
| | 00:23 | In the next movie, we'll take a look
at how we can combine this with some
| | 00:26 | additional functions to perform
some calculations to define time span.
| | 00:30 | Let's start a new query in Design view.
| | 00:32 | Create, Query Design, and
I'll add our Orders table.
| | 00:36 | Go ahead and say Close.
| | 00:38 | Instead of building an actual query,
I'll use the fields here to demonstrate the
| | 00:41 | Date, Time, and Now functions.
| | 00:44 | The Date function is written like this, Date().
| | 00:50 | This tells Access to fetch the current date.
| | 00:52 | In the next field I'll write
out Time followed by an open and
| | 00:56 | closing parentheses.
| | 00:58 | This tells Access to fetch the current time.
| | 01:01 | In the third field, I'll write Now
followed by open and closing parentheses.
| | 01:07 | This tells Access to get the current
date and time and combine them in one field.
| | 01:12 | Let's go ahead and run this query.
| | 01:15 | I'll expand the fields and you'll see
that Access has returned the current date
| | 01:18 | and time that I'm recording this movie.
| | 01:21 | Today is May 17th, 2011 at 3:33:48.
| | 01:26 | The third column, the Now expression,
takes our Date and Time field and
| | 01:29 | combines them into one field.
| | 01:31 | Now, let's take a look at how we could
use the Format function to format our date.
| | 01:35 | Let's go back into Design view and I'll
highlight these two fields and press Delete.
| | 01:41 | Now I want to use the Format function to
write a statement that will format our date.
| | 01:45 | Let me expand this open a
little bit and we'll start typing.
| | 01:48 | The Format function starts with
Format and an open parenthesis.
| | 01:52 | The first thing we need to supply
is the date that we want to format.
| | 01:56 | In the movie on the Format function we
took the OrderDate from the Orders table.
| | 02:00 | Here we just want to format the current date.
| | 02:03 | So we'll supply the Date function. Date().
| | 02:08 | The second part we need to supply
is how we want it to be formatted.
| | 02:10 | We can use a comma and a quotation
mark to tell it that we're moving onto the
| | 02:14 | next piece and then let's
format it like this, m.d.yy.
| | 02:20 | We'll finish our statement with
a closing quotation mark and a
| | 02:22 | closing parenthesis.
| | 02:24 | Now, if I run this query, we'll see
that the Format function is taking today's
| | 02:27 | current date and it's
reformatting it in the way that I specified.
| | 02:31 | So now that we can accurately
define the current date, we can use this
| | 02:34 | functionality to help us define
date ranges relative to today.
| | 02:38 | We'll take a look at that in the next movie.
| | Collapse this transcript |
| Calculating time intervals| 00:00 | Now that we know how to tell Access to
return today's date and time, we can use
| | 00:04 | that information to help obtain
query results that are relative to today.
| | 00:08 | We have two different functions that
will return slightly different results
| | 00:11 | depending on the type of
question that we're trying to ask.
| | 00:14 | The DateAdd function will allow us to
specify a time frame such as the past 30
| | 00:19 | days or within the last year and
return records within that timeframe.
| | 00:24 | The DateDiff function will allow us to
find how many days have elapsed between
| | 00:28 | today and a specific event.
| | 00:30 | We'll take a look at how we can apply each
in our queries with some real world examples.
| | 00:35 | First let's take a look at the DateAdd function.
| | 00:38 | We'll go to the Create menu and in
Query Design and we'll add our table Orders.
| | 00:44 | Go ahead and say Close to the Show
Table window and in the first field let's
| | 00:48 | just put in the Field for today's date, date().
| | 00:54 | Now let's add a DateAdd function.
| | 00:57 | In the next field we'll
right-click and say Build.
| | 01:00 | We'll go into the Functions, the Built-
In Functions, and the Date/Time category.
| | 01:06 | Let's scroll until we find DateAdd.
| | 01:10 | We'll double-click on it
to add it to our function.
| | 01:11 | So DateAdd requires three parts.
| | 01:15 | The first is an interval. This is if they
were looking for months or days or years.
| | 01:19 | The second part is a number. This is how
many months or days or years to add or subtract.
| | 01:25 | Finally we need the date that
we want to add or subtract from.
| | 01:28 | So if we wanted to find a date that was
24 months ago, we would type it like this.
| | 01:33 | The interval is months, so
we'll use the m character.
| | 01:38 | The number that we want to add
is -24. We want 24 months ago.
| | 01:45 | The date that we want to
add or subtract from is today.
| | 01:49 | That will use the date function to get.
| | 01:51 | So our finished function reads DateAdd("m", -24, date(),
and a final closing parenthesis to finish the function.
| | 02:07 | Okay, let's go ahead and
say OK and run our query.
| | 02:12 | We'll say that Access returns today's date
and the same day two years ago or 24 months ago.
| | 02:18 | Let's go ahead and go back
into Design view and change it up.
| | 02:20 | I'll expand this open and now once it's
created we can just edit it right here.
| | 02:26 | Instead of 24 months ago,
let's say one day in the future.
| | 02:33 | We'll change it to a d to indicate that
we want to add days instead of months.
| | 02:38 | We'll change the number to a 1 so
we'll add one day to today's date.
| | 02:42 | Let's go ahead and run that and
we'll see that we get today and tomorrow.
| | 02:47 | Let's take a look at another one.
| | 02:48 | Let's go back into Design view.
This time we're going to add years.
| | 02:53 | Now years is going to use four y's,
similar to the Format function symbols.
| | 03:00 | Let's add 3 years to today's date.
| | 03:03 | We'll change that 1 to a 3
and then we'll run our query.
| | 03:07 | Now I've got today's date and
a date three years from now.
| | 03:10 | So now that we know how to use today's
date and we know how to specify the date
| | 03:15 | in the future or in the past relative to
today, we can use these two to define a range.
| | 03:20 | Let's go back into Design view.
| | 03:24 | Let's use these two fields to create a
function that tells us what employees
| | 03:28 | were hired in the last two years.
| | 03:30 | Let's go ahead and delete the Orders
table and we'll delete these two fields to
| | 03:35 | get us back to a blank query.
| | 03:37 | We'll Show Table and
we'll add our Employees table.
| | 03:42 | Go ahead and say Close.
| | 03:44 | To our query we're going to add FirstName,
LastName, and we'll scroll down until
| | 03:50 | we get to HireDate and we'll add that as well.
| | 03:52 | For HireDate we'll specify a
criteria that selects everything more recent
| | 03:57 | than two years ago.
| | 03:58 | Let's expand this field open.
| | 04:02 | In our Criteria we'll say it's greater than the
dateadd function, the units or the interval
| | 04:10 | is going to be in years,
and the number is going to be -2.
| | 04:16 | We want a date that's two years ago.
And finally the day that we want to add or
| | 04:20 | subtract that to is today, date().
| | 04:25 | Finally let's finish our function
with a finishing closing parenthesis.
| | 04:29 | So the finished criteria reads that we
want to select all of our dates that are
| | 04:33 | greater than a day two years in the past.
| | 04:36 | Let's go ahead and run this query and
we'll see that we have 20 employees they
| | 04:40 | were hired within the last two years.
| | 04:43 | So this is an example of how we can
use DateAdd to select a range of dates.
| | 04:46 | Let's find out exactly how many days
ago some of these employees were hired.
| | 04:50 | Let's go back into our Design view
and we'll use the DateDiff function.
| | 04:55 | In our fourth column we'll right-click
and go to our Builder and let's go find
| | 05:00 | the DateDiff function.
| | 05:01 | We'll look in Built-In
Functions, Date/Time and DateDiff.
| | 05:04 | DateDiff has several
arguments that we can supply.
| | 05:09 | The first one is interval.
| | 05:11 | This is going to be the same as
with DateAdd, whether we're looking at
| | 05:13 | days, months, or years.
| | 05:15 | The second and third arguments are
the two dates that we want to find
| | 05:18 | the difference between.
| | 05:20 | The firstdayofweek and the
firstweekofyear fields are optional.
| | 05:24 | We can see that if we look at the
hints down here in the bottom. They're
| | 05:27 | wrapped in square brackets which is
Access's way of telling you that these are
| | 05:30 | optional arguments.
| | 05:31 | We would use these if we needed to
redefine when our week started or when our
| | 05:35 | year started. For instance, if we are
on a fiscal year that doesn't coincide
| | 05:39 | with the calendar year.
| | 05:41 | We can go ahead and remove these.
We're not going to be using them.
| | 05:43 | But we do need to supply though is the
interval and in the two dates that we
| | 05:49 | want to calculate the difference between.
| | 05:51 | So the interval for our
example is going to be months.
| | 05:54 | We want to find out how many months has
it been since our employees were hired.
| | 05:59 | We'll wrap that in quotation marks
so that we maintain proper syntax.
| | 06:02 | Then we need to specify the two dates.
| | 06:05 | The first date that we
want to use is the HireDate.
| | 06:08 | So we'll go into our database, we'll
go into our Tables, we'll go into our
| | 06:12 | Employees table, and we'll
double-click on HireDate.
| | 06:14 | The date we want to find the difference
between is today so we'll use our code
| | 06:20 | to specify today's date, date().
| | 06:21 | Let's go ahead and say OK
and we'll run this query.
| | 06:29 | Now we had a query that's showing us all
of the employees they were hired within
| | 06:32 | the last two years and in this fourth
column we're seeing exactly how many
| | 06:36 | months ago from the date of this
recording that they were hired.
| | 06:40 | Working with dates is an integral
part of making sense of all the data
| | 06:43 | within your database.
| | 06:44 | With a little bit of practice and
persistence, mastering date and time-based data
| | 06:49 | and using it to filter out all the
relevant records will help you make your
| | 06:52 | queries that much more successful.
| | Collapse this transcript |
|
|
6. Understanding Conditional StatementsIntroducing the conditional IIf function| 00:00 | In this chapter, we're going to take a
look at creating a new function called a
| | 00:03 | conditional statement.
| | 00:05 | Conditional statements are one of my
favorite features of Access queries.
| | 00:08 | They allow you to create an
automated decision-making process.
| | 00:11 | They're very flexible and very powerful
and they can automate some complex tasks
| | 00:15 | in a short amount of time.
| | 00:16 | So let's dig in to see what this is all about.
| | 00:19 | A conditional statement is an if-then statement.
| | 00:22 | In Access, we will use the IIf
function and yes, there are two I's there.
| | 00:26 | That's not a typo.
| | 00:27 | The first I stands for immediate,
meaning that this is a function that runs
| | 00:30 | inside of an SQL statement.
| | 00:32 | This is so Access can distinguish between
this function and the Visual Basic If routine.
| | 00:37 | The IIf function has a
specific syntax that must be followed.
| | 00:41 | There are three required arguments.
| | 00:43 | The expression to evaluate, the piece to
return if true, and a piece to return if false.
| | 00:49 | The first part of our
condition is the expression.
| | 00:52 | Any mathematical expression that
evaluates to true or false will work.
| | 00:55 | The true part argument is returned if
the expression is true and the false part
| | 00:59 | argument is returned if the expression is false.
| | 01:02 | Many of the mathematical operations
that we've seen in this course can be used
| | 01:06 | to construct your expressions.
| | 01:07 | We can use the operators
Less Than, Greater Than, Equal.
| | 01:11 | And, Or, and Not will also work.
| | 01:13 | We can use any of these to build the
expression that Access will evaluate.
| | 01:16 | An IIf function that evaluates August
sales compared to a plan or goal might
| | 01:21 | look something like this.
| | 01:22 | It reads is the August sales over $100,000?
| | 01:26 | If it's true, they will say that the
store met their sales plan and if that's
| | 01:30 | false, we will say the store
did not meet their sales plan.
| | 01:33 | So let's see how Access would
apply this in our data tables.
| | 01:36 | If we take a column of data called
August Sales and we plug that into our
| | 01:40 | Expression, Access will evaluate the result.
| | 01:42 | So if we take Store 101 and look
at their August sales, 103,588.
| | 01:49 | If we plug that into our IIf statement,
we'll get an expression that reads
| | 01:53 | 103,588 is greater than
100,000. Well, that's true.
| | 01:58 | So the result is, met sales plan.
| | 02:01 | The same thing applies to Store 102.
| | 02:03 | Their August sales is over 100,000,
so they also met their sales plan.
| | 02:07 | Now Store 103, their sales were 98,743.
| | 02:12 | If we plug that into our IIf statement,
we get 98,743 is greater than 100,000.
| | 02:18 | Well, that's a false statement.
| | 02:20 | So Access will return below
sales plan and so on down the list.
| | 02:24 | So you can see that if you had to
manually evaluate August sales and take each
| | 02:28 | store's value and compare it to goal
and then define a result, that could take
| | 02:32 | you quite a long time.
| | 02:33 | By using an IIf conditional function,
Access can automate that process for you.
| | 02:38 | We will start in the next video by
building our own IIf function using
| | 02:41 | the Expression Builder.
| | Collapse this transcript |
| Creating an IIf function| 00:00 | In this video, we will begin writing
our own conditional statements using the
| | 00:03 | Query Designer in Access.
| | 00:05 | We will be using the Expression
Builder to help us generate proper syntax and
| | 00:09 | we'll be using some mathematical
expressions to evaluate our data.
| | 00:12 | So let's get started.
| | 00:14 | First, we need to define the question.
| | 00:16 | The question we want to answer with this
example is which sales team members met
| | 00:20 | the goal of $20 in the month of June 2010?
| | 00:23 | First, we will build a select query
to fill trap only the information we
| | 00:26 | need from the database.
| | 00:27 | We will go up to the Create tab and
create a new wuery in Design view.
| | 00:32 | We will add the Employees table, the
Orders table, and the Products table.
| | 00:38 | Let's go ahead and close Show Table
and we will start filling in the fields.
| | 00:43 | From the Employees table, let's
add Employee ID and FirstName.
| | 00:47 | From the Orders table we will grab
the date and from the Products table we
| | 00:50 | will grab the Price.
| | 00:52 | Now let's start filling in our criteria.
| | 00:54 | For the OrderDate, we're only
interested in records that fall between June 1st,
| | 00:58 | 2010, and June 30th, 2010.
| | 01:00 | So let's expand the field and
we will write that criteria.
| | 01:03 | I'll write Between 6/1/2010 and 6/30/2010.
| | 01:17 | I will go ahead and press Enter to
accept that value and Access updates our syntax.
| | 01:21 | Now let's take a look at Price.
| | 01:23 | For the price, we are looking for
the sum total over the entire month.
| | 01:27 | So activate our Totals row and
change the Grouping level to Sum.
| | 01:32 | The last thing we need to do is change
the Show checkbox for the OrderDate to
| | 01:36 | tell Access that we are not going to show this
field, but we just want to use it for filtering.
| | 01:41 | We also don't want to group by the OrderDates,
so we will change this to a Where clause.
| | 01:46 | So let's go ahead and run
our query to see the results.
| | 01:50 | We can see that we have 36 employees
that had sales in the month of June and we
| | 01:54 | can see their total sales here in this column.
| | 01:56 | Let's go back into Design view real
quick and we will change our column header
| | 02:00 | so we can reference it later
on in the Expression Builder.
| | 02:04 | Go into Design view and before Price,
we will enter the alias June Sales
| | 02:12 | followed by a colon.
| | 02:14 | Let's try it one more time to make sure
everything looks good and I think that looks great.
| | 02:16 | So let's go ahead and save it out.
| | 02:18 | We will save this as qry_JuneSales.
| | 02:20 | I will press Ctrl+S, qry_JuneSales.
| | 02:29 | The next thing we want to do is create
a new query that include the conditional
| | 02:33 | statement that will evaluate the
calculated field here and tell us whether that
| | 02:37 | meets or is below sales plan.
| | 02:39 | We will go to the Create tab and we
will create a new query in Design view.
| | 02:43 | This time instead of building it up
from the table, we will build it off of the
| | 02:46 | query we just constructed.
| | 02:48 | We will switch the Querys tab and
find the JuneSales query we just created.
| | 02:53 | I will scroll down here and there it is there.
| | 02:55 | We will add that to our pane.
| | 02:57 | Let's go ahead and close Show Table.
| | 03:00 | We'll add JuneSales to the field and we will
build our wxpression in the second column.
| | 03:05 | I will right click on the Field and say Build.
| | 03:09 | Now, to construct our IIf
condition, we can find that under
| | 03:12 | Functions > Built-in Functions.
| | 03:14 | I will scroll down to
Program flow and there is IIf.
| | 03:18 | I will double click on it
to add it to my expression.
| | 03:21 | We can see that there is three required parts.
| | 03:23 | We have the expression to evaluate as
true or false, the part to return if it's
| | 03:27 | true, and the part to return if it's false.
| | 03:29 | So the expression that we want to evaluate
is whether the June sales total is over $20.
| | 03:36 | So let's go ahead and write out that expression.
| | 03:38 | I could finally calculate a field if I
drill into my database, in the queries.
| | 03:43 | Let's open this up a little into JuneSales
and there is the JuneSales calculated field.
| | 03:51 | I will double-click on that.
| | 03:53 | Now we want to evaluate if
this field meets the goal of $20.
| | 03:57 | So we will say JuneSales is
greater than or equal to 20.
| | 04:03 | If the JuneSales are greater than or
equal to 20, we are going to say that
| | 04:07 | they "Met Sales Plan."
| | 04:08 | So I'll return that text written in quotes.
| | 04:15 | If the JuneSales is below 20,
they did not meet the sales plan.
| | 04:19 | So for the false part, I will write
"Did Not Meet Sales Plan" and I will
| | 04:22 | wrap that in quotes.
| | 04:29 | So there is our completed expression.
| | 04:31 | If the JuneSales is greater than or
equal to 20, then they met sales plan.
| | 04:36 | If the JuneSales was not greater than or
equal to 20, they did not meet the sales plan.
| | 04:40 | Let's go ahead and say OK
and that will add to our query.
| | 04:43 | We can see Access put in an alias here that
reads Expr1 and that's not very descriptive.
| | 04:49 | So let's go ahead and change that to Sales Plan.
| | 04:51 | I will highlight it and say Sales Plan.
| | 04:54 | Let's add a little bit more information to our
query here so we can see what the results are.
| | 04:58 | We will invoke the Show Table window.
| | 05:01 | This time we'll use the Show Table
button on the ribbon and we will use
| | 05:04 | the Employees table.
| | 05:05 | Let's go ahead and close this out and we
can add EmployeeID and FirstName to our
| | 05:10 | query for reference.
| | 05:11 | Okay, let's go ahead and run
it and see what the results are.
| | 05:14 | We can see that we have a total of 36
records. Those are the 36 employees that
| | 05:18 | had sales and we can see that we
have a calculated field here using our
| | 05:22 | expression to tell us whether they Met
Sales Plan or Did Not Meet Sales Plan.
| | 05:26 | Now we have a little issue here with the
way the query is returning the results.
| | 05:30 | If you think back to that movie where
we talked about joins, you might realize
| | 05:34 | that some of our employees didn't
have sales at all and that they should be
| | 05:37 | represented in this list as well.
| | 05:39 | So let's go back in our Design view and
we will add those people to our query.
| | 05:44 | Back in Design view, I will double
click on the line that joins the JuneSales
| | 05:48 | query with the Employees table.
| | 05:52 | Now the option that we are looking for
is to Include ALL records from the Table
| | 05:55 | Employees and only those records from
JuneSales where the join fields are equal.
| | 06:00 | This means we'll have all the
employees whether they had sales or not and for
| | 06:03 | the employees that did have
sales we will have their data.
| | 06:07 | Let's go ahead and say OK
and take a look at our results.
| | 06:10 | Now we have 200 people
and that's not right either.
| | 06:14 | Now we are getting all of employees in
the entire company, not just the Sales team.
| | 06:18 | So I have to add one more
field to our query to finish it up.
| | 06:20 | Let's go back into Design view.
| | 06:22 | We will add our department to the query.
| | 06:24 | And we want to show the
criteria is just the Sales department.
| | 06:30 | Okay, this should wrap it up. Let's go ahead
and go Run and we will double check our results.
| | 06:36 | There's our 84 sales team members.
| | 06:38 | For the people who had over $20 in
sales, they met their sales plan.
| | 06:42 | For the people who had under $20 of
sales or didn't have any sales at all, they
| | 06:46 | did not meet the sales plan.
| | 06:48 | So there is our first conditional statement.
| | 06:50 | We made use of the Expression
Builder to help us format our statement
| | 06:53 | properly and to reduce errors and we were able
to verify that returned the expected result.
| | 06:58 | I hope that at this point, you're
beginning to see why I like conditions so much.
| | 07:03 | Being able to instruct Access how to
make a decision on its own is pretty cool.
| | 07:07 | But more importantly, I want you to
take a moment before moving onto the next
| | 07:10 | movie and think about all the ways that
your workflow could benefit from using
| | 07:14 | conditional functions, and I promise
that once you start looking you are going
| | 07:17 | to come up with a ton.
| | 07:19 | They're extremely flexible and
can help out in unexpected ways.
| | 07:23 | In the next movie, we will look at
expanding the usefulness of the conditional
| | 07:25 | statement by making use of
several IIf functions that sit together.
| | 07:29 | We will see you there.
| | Collapse this transcript |
| Nesting IIf functions| 00:00 | In the last movie, we took a look at
how we can use a conditional statement to
| | 00:04 | return one of two values.
| | 00:05 | We would supply an expression, and
depending on whether that expression is true
| | 00:09 | or false, we would get a
branching decision tree.
| | 00:11 | Now we can nest conditional statements
together, so instead of a true part, we
| | 00:16 | might evaluate to a second IIf
statement, or instead of false, we might go to
| | 00:20 | a second or third IIf statement that way.
| | 00:23 | Now if we nest multiple conditional
statements together, we can get a branching
| | 00:27 | decision-making tree.
| | 00:28 | This will function much like a
flowchart where we'll start at the top and
| | 00:32 | depending if the statement is true or
false, we'll move left or right, we'll
| | 00:35 | evaluate with a second expression, and
again move left and right from there.
| | 00:39 | At the end of the chain, we can have
four expressions returned, four different
| | 00:42 | values depending on how it
flowed through the chart.
| | 00:45 | Let's go ahead and take a look at this
concept in Access and we'll apply it to
| | 00:48 | a sales tax calculation.
| | 00:50 | So let's create a query that decides
whether or not we should be charging sales
| | 00:54 | tax to our customers.
| | 00:56 | Let's assume that the Two Trees Olive
Oil Company has a physical presence in the
| | 00:59 | state of California and Texas and any
customers purchasing product in those
| | 01:03 | states will need to get charged the sales tax.
| | 01:05 | We can go into our Create tab and
create a new query in Design view and
| | 01:10 | we'll add a few tables.
| | 01:11 | We'll add the DirectCustomers table,
the Orders table, and the Products table.
| | 01:15 | Let's go ahead and close Show
Table and we'll add our fields.
| | 01:21 | From the Customers table
we'll add CustID and FirstName.
| | 01:25 | We'll also add the State so we can identify
the state whether we need to charge tax or not.
| | 01:29 | From the Products table, we need the Price.
| | 01:31 | So we can find out how much tax to charge.
| | 01:34 | In the fifth field, we'll build our conditional.
| | 01:36 | Right-click and say Build and
let's find our conditional function.
| | 01:40 | We'll go to Functions > Built-In
Functions > Program Flow, and double-click IIf.
| | 01:47 | So the first thing we need to do is find
out if the customer lives in California.
| | 01:50 | We'll write State = California.
| | 01:54 | We'll go into our tables, find
our customer, and grab their State.
| | 01:59 | We'll type in =ca to find out whether
that customer lives in California and
| | 02:04 | we'll wrap that in quotation marks.
| | 02:10 | If the customer lives in the state of
California, we need to multiply their
| | 02:13 | order total by 0.08% to get
the amount of sales tax to add.
| | 02:17 | So let's go into their Products table, we'll
find Price, and we'll multiply that by 0.08.
| | 02:26 | So that takes care of our California customers.
| | 02:28 | If the customer doesn't live in
California, we need to find out whether
| | 02:31 | they live in Texas.
| | 02:33 | So for the dalse part,
we'll start another conditional.
| | 02:36 | We'll go back up to our Built-In Functions >
Program Flow, and double-click IIf again.
| | 02:42 | This time we want to find out
if the customer lives in Texas.
| | 02:46 | Go back to DirectCustomers, find
their state, and we'll say =tx.
| | 02:52 | Again, remember our quotation marks.
| | 02:56 | If that's true, we'll charge the
Texas state sales tax at .0.05%.
| | 03:01 | We'll go to Products, Price, *.05.
| | 03:08 | Finally, if this expression evaluates
a false, the customer does not live in
| | 03:12 | Texas, we won't charge any tax at all.
| | 03:14 | So we'll just enter in 0.
| | 03:17 | Let's go ahead and add an
alias to our column header.
| | 03:19 | We could do that by clicking the very
beginning and we'll type in Sales Tax
| | 03:26 | followed by a colon.
| | 03:27 | So there's our completed conditional.
| | 03:29 | If the customer lives in California,
we'll take the price and multiply it by .08.
| | 03:34 | If they don't live in California,
we'll go to the next part, which asks do
| | 03:37 | they live in Texas.
| | 03:39 | If they do live in Texas,
we'll multiply the price by .05.
| | 03:43 | And if they don't live in
Texas either, we'll put in a 0.
| | 03:45 | Okay, let's go ahead and evaluate
this expression by running our query.
| | 03:48 | And here we can see how much sales tax
we're going to add to each customer who
| | 03:53 | lives in either the state of
California, or if I scroll down we'll find a
| | 03:57 | customer that lives in the State of Texas.
| | 04:00 | Let's go ahead and add a couple
of more fields to finish the query.
| | 04:03 | We'll go back into Design view and
we'll add a calculated field here to add the
| | 04:07 | sales tax to the price to get their total cost.
| | 04:11 | This time I'll just right-click and go to
the Zoom window and we'll type this manually.
| | 04:15 | So the first thing we want is an alias
that says Total Cost followed by a colon.
| | 04:20 | Then we want to take the Price field
from our query and we'll wrap that in
| | 04:24 | square brackets, and we'll add it to
our sales tax field, again wrapping it
| | 04:32 | in square brackets.
| | 04:37 | So now we have the calculated field
called Total Cost that will take the price
| | 04:42 | and add it to the sales tax.
| | 04:43 | Let' go ahead and say OK and
run our query one more time.
| | 04:48 | So here's the customer's final
Total Cost after tax, if there is any.
| | 04:52 | So that's how we can use a nested
conditional statement to expand our
| | 04:55 | options even further.
| | Collapse this transcript |
| Using the Switch function| 00:00 | Within the Program Flow category of
functions in the Expression Builder is
| | 00:04 | another function called Switch.
| | 00:06 | The Switch function is similar to
IIf in that it allows Access to return
| | 00:10 | various responses depending on the
results of an evaluated expression.
| | 00:14 | It does this by using matched
pairs of expressions and values.
| | 00:17 | We have exprA and if
that's true we return valueA.
| | 00:21 | If the exprA is False, we'll move on
down the line to exprB and evaluate it.
| | 00:27 | In this way, Switch only
branches in the false direction.
| | 00:31 | In other words, Access only
returns a value if the function is true.
| | 00:34 | If it's not, it moves on down the line.
| | 00:37 | If the condition evaluates to false
at the very end, Access returns a null
| | 00:41 | value or a blank cell.
| | 00:42 | So let's take a look at an
example of how we can apply this.
| | 00:46 | In this example, we want to take a
look at each employee's total lifetime
| | 00:50 | sales with the Two Trees Olive Oil
Company as part of our rewards and
| | 00:53 | recognition program.
| | 00:55 | Let's open up our Chapter 6
custom group, and we'll open this
| | 00:58 | qry_TotalSales query.
| | 01:00 | We'll see that we've got a column for
Employee Name, the Sales Department,
| | 01:06 | and this is the Sum Total of all the sales
throughout their entire tenure with the company.
| | 01:11 | We'll use this query as a basis for
deciding what level of recognition they're at.
| | 01:15 | We'll go to our Create tab and
create a new query in Design view.
| | 01:19 | Let's go ahead and grab that query,
let's enter the Queries tab, and we'll
| | 01:24 | look for qry_TotalSales.
| | 01:28 | We'll double-click on it to add it to the query.
| | 01:31 | Go ahead and say Close and
we'll add each field to our query.
| | 01:36 | Double-click on EmpID,
FirstName, Department, and TotalSales.
| | 01:41 | In the next field, we'll
create our switch expression.
| | 01:44 | So we'll right-click and say Build
and we can find that in the Program group
| | 01:50 | under Functions > Built-In
Functions > Program Flow > Switch.
| | 01:55 | Now Switch needs matched
pairs of expressions and values.
| | 01:58 | So the expression we want to evaluate
is whether they're in our Platinum group,
| | 02:02 | which means they're over a
thousand-dollar sales level.
| | 02:04 | Let's go into our TwoTrees database,
we'll go into Queries, and we'll find the
| | 02:08 | qry_TotalSales query.
| | 02:13 | From there we'll grab the TotalSales
calculation. We'll double-click on it to
| | 02:17 | add it to the expression.
| | 02:18 | So the evaluation expression
that we need is TotalSales > 1000.
| | 02:24 | If that's true, we're going to
say they're in our Platinum group.
| | 02:28 | So for the value, I'll type in Platinum.
| | 02:31 | We'll wrap that in quotation marks and
we'll add a comma to tell Access that
| | 02:35 | we're done with the first pair and we're
ready to start typing out the second pair.
| | 02:38 | Now we're going to look for our
TotalSales as being over $900.
| | 02:42 | So we'll go back to our query, we'll double-
click on TotalSales again, and we'll say greater than 900.
| | 02:51 | If that's true, they're in our Gold group.
| | 02:55 | We'll do another one for 800 at the
Silver level and we'll do one more at the
| | 03:05 | Bronze level for $700.
| | 03:15 | So there is our statement. We've got a
Switch function. We're going to look at
| | 03:18 | TotalSales > 1000, and if that's
true, they're in our Platinum group.
| | 03:21 | If that's not True, we'll go
to the next. TotalSales > 900.
| | 03:25 | Oh, and I see I have a little typo here.
We'll fix that. We'll change that from
| | 03:30 | less than to greater than.
| | 03:32 | So TotalSales > 900, and if that's true,
we'll say they're in our Gold group.
| | 03:37 | If that's false, we'll move on. TotalSales > 800.
| | 03:41 | If that's true, they're Silver.
| | 03:43 | if that's false, we'll move on.
| | 03:45 | Are they greater than 700?
| | 03:47 | If they are, that's Bronze group.
| | 03:49 | If not, Access will return a null
value, so it'll just be a blank cell.
| | 03:53 | Okay, let's go ahead and say OK.
| | 03:55 | So we'll go ahead and run
our query to see the results.
| | 03:58 | Let's sort based upon our Total
Sales. We'll sort Largest to Smallest.
| | 04:03 | Now we can see all of our employees
that in our Platinum group, all of the
| | 04:07 | employees that are in our Gold group,
all the employees that are in Silver,
| | 04:11 | Bronze, and all the
employees who've not yet leveled up.
| | 04:15 | So when used appropriately, both IIf
and Switch can be extremely valuable tools
| | 04:19 | to have in your pocket.
| | 04:21 | Anytime you can automate routine tasks
and allow Access to make decisions for you,
| | 04:25 | it can be a huge benefit
to increasing your productivity.
| | 04:28 | But beyond simply taking decision-
making tasks off your plate, learning to
| | 04:32 | use the Program Flow functions increases the
reliability and consistency of your database.
| | 04:37 | One of the biggest benefits is
that they can ultimately prevent data
| | 04:40 | inconsistencies due to the
introduction of human error.
| | Collapse this transcript |
|
|
7. Linking It All TogetherUnderstanding the reporting tool| 00:00 | Business managers love looking at
comparative data, whether it's different areas
| | 00:04 | over the same time period, or a year-
over-year comparison of the same location,
| | 00:08 | comparing data points can quickly give
some powerful insights into the health of
| | 00:11 | an organization and the overall trends.
| | 00:14 | In this chapter, we're going to take
many of the things that we've learned about
| | 00:16 | queries and combine them
together into a real-world project.
| | 00:19 | We're going to construct a reporting
tool that compares various data points and
| | 00:23 | allows the end user to define
the parameters for the report.
| | 00:27 | Let's take a quick look at how
the completed system will work.
| | 00:30 | I'm going to go ahead and open up the
Chapter 7 section of my custom navigation bar.
| | 00:34 | Let's go ahead and open up the
frm_SalesByDivision-complete form.
| | 00:39 | On the form, I've got areas for
Geographical Division and Year and we have a
| | 00:43 | comparison section as well.
| | 00:45 | For the first one I'll select New
England in 2008, and we'll compare that to
| | 00:49 | New England in 2009.
| | 00:52 | I'll click on the Preview Report button
and Access will generate a report that
| | 00:57 | has exactly the
information that I'm looking for.
| | 00:58 | New England in 2008 and 2009.
| | 01:02 | We can see the different states
within the region and we can make some
| | 01:05 | comparison analysis between the two years.
| | 01:08 | So with a couple of mouse clicks, I'm
able to get some very specific information
| | 01:12 | and make some targeted observations
about the performance of our business.
| | 01:15 | But what's really going on
here and how is this working?
| | 01:19 | From the user's perspective, they
enter information into a form, press the
| | 01:22 | button, and get their report.
| | 01:24 | But in Access, things
happen a little bit differently.
| | 01:27 | Once the user fills up the
form, they request the report.
| | 01:31 | At this point though the report only
has information about how to format data,
| | 01:34 | it doesn't know what data you're looking for.
| | 01:37 | The report will ask the query
for the information it needs.
| | 01:40 | At this point, the query doesn't even
have everything it needs, so it goes and
| | 01:43 | looks at the finished form.
| | 01:45 | The user has supplied the information
into the form and it plugs that data
| | 01:49 | into the query. The query generates a
record set and plugs that data into the report.
| | 01:54 | The report now has something to
format and it serves the final version
| | 01:58 | back to the end user.
| | 01:59 | So what appears to the end user to be a
single action?press the button, get a
| | 02:03 | report?is really a series of steps
that is handled internally by Access.
| | 02:08 | Over the next several movies, we'll
build and hook all of these pieces together
| | 02:11 | starting with the form.
| | Collapse this transcript |
| Building the form| 00:00 | The first step in creating a
reporting tool is to build the form.
| | 00:03 | Here is where the end user will input
the parameters that they're interested in
| | 00:06 | reviewing on the reports.
| | 00:08 | Let's go ahead open up our Chapter
7 custom group and I'll open up the
| | 00:12 | frm_Blank file that I've already started.
| | 00:14 | This basically has some images and
some color formatting already applied.
| | 00:19 | Change this into Design View by clicking
the Design View button here in the top,
| | 00:22 | and we need to add a couple of drop-
down menus for the user to select from.
| | 00:27 | The first one will be the Geographic
Division and the second one will be the Year.
| | 00:32 | We can add combo boxes using this button up
here in the Controls section of the Design Ribbon.
| | 00:39 | We'll select Combo Box and add one to our form.
| | 00:42 | This will open up the Combo Box Wizard
and it presents us with two options.
| | 00:47 | Neither of these options are going
to be exactly what we're looking for,
| | 00:49 | but we'll step through them a few steps
and we'll see why neither of these will work.
| | 00:54 | The first option is I want the combo box
to get values from another table or query.
| | 00:57 | I'll go ahead and say Next.
| | 01:00 | I'm looking for the Geographical Division.
We can get that from the States table.
| | 01:04 | I'll double-click on States. We'll add
Division to our Selected Fields. We'll say Next.
| | 01:11 | Let's go ahead and sort these
Ascending by DivisionName, Next, and Access
| | 01:18 | presents us with a table or a sample of
what that drop-down menu might look like.
| | 01:23 | Because each division could have
multiple states associated with it,
| | 01:26 | the drop-down list that Access
generates with this method will have
| | 01:30 | multiple divisions.
| | 01:31 | We can scroll through the list and see
East North Central is represented many times,
| | 01:36 | and the same for
each of the other divisions.
| | 01:39 | So this isn't the way we
want our drop-down menu to look.
| | 01:41 | Let's go ahead and back up
all the way to the beginning.
| | 01:47 | The other option says I will
type in the values that I want.
| | 01:50 | I go ahead and say Next.
| | 01:52 | Access presents us with a blank
table where we could type in just the
| | 01:55 | values that we want.
| | 01:56 | We could type in the values here,
but this won't be linked to our data table,
| | 02:00 | and if our company were to
expand, say open up some international
| | 02:04 | locations, those locations would not
show up on the drop-down list unless we
| | 02:08 | modify this list manually.
| | 02:10 | So this isn't really a good option either.
| | 02:11 | Let's just go ahead and say Cancel
to get out of the wizard altogether.
| | 02:16 | A better option is to use an
embedded query to link this combo box to.
| | 02:20 | We can go into our Property Sheet for
this combo box. We'll go to the Data tab,
| | 02:25 | and under Row Source click in the empty field.
| | 02:29 | We can use this button on the far right
with the three dots on it to open up a
| | 02:33 | query design window.
| | 02:36 | From here we'll add our States
table and close the Show Table window.
| | 02:42 | We'll add our DivisionName and we'll
turn on our Totals row to group everything.
| | 02:47 | Let's go ahead and view
the results by clicking Run.
| | 02:51 | And we see a data table that will
represent the drop-down menu for our form and
| | 02:56 | this is exactly how we want to format it.
| | 02:57 | Let's go ahead and close the query.
Access will ask if we want to save the
| | 03:02 | statement and we do.
| | 03:04 | And you can see that that SELECT statement
has been populated into this Row Source column.
| | 03:10 | I can right-click on it and say Zoom
to open it up in the zoom window if I
| | 03:13 | want to investigate it.
| | 03:14 | Let's go ahead and close that.
| | 03:17 | So that's the drop-down menu for our
Divisions. Let's add another one for our Year.
| | 03:22 | Once again, go up to Combo Box, click
once in the form, and we'll press Cancel to
| | 03:28 | get out of the wizard.
| | 03:31 | In the Property Sheet, go to Row
Source and click on the Build button.
| | 03:35 | For the Years, we'll look to our
Orders table. We'll add that and say Close.
| | 03:41 | Now we're not looking for the Order
Date, because this is going to be a
| | 03:44 | day-specific feature.
| | 03:45 | What we want is just the years that are
represented by the dates, and we can use
| | 03:49 | the Year function that
we saw previously to do that.
| | 03:53 | In this first field, let's
right-click and go to our Zoom box.
| | 03:57 | We'll type in the Year function and
extract the year from the OrderDate field.
| | 04:02 | year([OrderDate]).
| | 04:11 | Let's go ahead and say OK and we'll
turn on our Totals column to aggregate all
| | 04:16 | those values together.
| | 04:18 | Clicking Run will show us the results,
and that looks like what I expected.
| | 04:21 | So we can go ahead and close this query.
| | 04:25 | We'll save the changes and that has
now been populated into the Row Source for
| | 04:30 | this drop-down menu.
| | 04:31 | Now let's clean up our form a little bit.
| | 04:33 | I'm going to move the labels on top of
the fields. So I'll click on the gray box
| | 04:37 | at the top left corner of each
label and I'll drag it to the top.
| | 04:42 | And I know my Divisions are going to
be a little bit long, so I need to make
| | 04:45 | this wider, and I'll drag
its label to the top as well.
| | 04:49 | Let's go ahead and rename these.
| | 04:53 | This first one is Geographic Region.
| | 04:57 | And the second one, if I
double-click on it, is Year.
| | 05:00 | So let's test this out and see where we're at.
| | 05:03 | Let's change into Form view by
clicking the button here on the View menu and
| | 05:09 | we can see we have a drop-down list for our
Geographic Region and a drop-down list for Year.
| | 05:14 | Okay, so we need to copy these and add
two more versions for our comparison data.
| | 05:19 | One more time back into Design view.
We'll highlight these two boxes, going to
| | 05:26 | right-click, Copy, right-click again, and Paste.
| | 05:31 | Let's go ahead and drag these into
position and I don't need the labels with
| | 05:38 | these because they're already labeled above.
| | 05:40 | So I will click on the label and delete it.
Click on the Year label and delete that.
| | 05:46 | And now we have our four drop-down
menus to select Region, Year, and then our
| | 05:52 | Comparison Region and Comparison Year.
| | 05:56 | The last step is to name each of these
boxes so they have a unique identifier
| | 06:00 | that we can find later.
| | 06:01 | We'll go into our Design view, we'll
click on each drop-down menu, and switch to
| | 06:07 | the Other tab, and we'll give it a unique name.
| | 06:11 | This first box we'll call cbo_region.
The first Year we're going to call
| | 06:19 | cbo_year, the Comparison Region we'll
call cbo_compregion, and the Comparison Year
| | 06:36 | we'll call cbo_comparisonyear.
| | 06:45 | Now each of these boxes has a unique
name and we can identify that when
| | 06:48 | we build our query.
| | 06:49 | Now that our form is built and we've
named the data collection points, we can
| | 06:53 | start building the query and
handle the end user's requests.
| | Collapse this transcript |
| Building the query| 00:00 | The next step in creating our
reporting tool is to construct the query.
| | 00:04 | I'll go up to the Create menu and
I'll create a new query in Design view.
| | 00:09 | Let's add the tables that we're going to need.
| | 00:11 | First, I'm going to choose my Orders table.
| | 00:12 | This will give us
information about the OrderDate.
| | 00:16 | I'll choose my Products table.
| | 00:19 | This will give me information about the
product's price that will aggregate over the year.
| | 00:22 | I also need information for my States table,
but in order to get there I need to have a link.
| | 00:28 | We'll go to DirectCustomers.
| | 00:30 | That will give us the customer that
ordered it and the state it went to, and
| | 00:33 | then we can go to States, which will give
us the states from the Customers table.
| | 00:38 | Let's go ahead and close the Show Table window
and I'll rearrange these just a little bit here.
| | 00:43 | Move my customer over here and expand it.
| | 00:47 | Okay, so now we can see the
relationships between the four tables in our query.
| | 00:52 | I got my Orders table, from which
we'll extract the year from this date.
| | 00:58 | Linked to that I have got my Products table which
will take the price and aggregate over the year.
| | 01:04 | I also have my DirectCustomers table,
which has the State that the order went to,
| | 01:09 | and from the States lookup table I can
get the full name of the state instead of
| | 01:12 | just the abbreviation, and I'll also get
the DivisionName that we'll aggregate by.
| | 01:17 | So these are the four tables we'll
use in this query. Let's go ahead and
| | 01:21 | populate the fields into the field
area that we'll use in our report.
| | 01:26 | The first field we want is a year
and we can construct this using the
| | 01:29 | Expression Builder.
| | 01:30 | So let's right click on this field
and go into our Expression Builder.
| | 01:33 | We'll type in the year function, year(, and
then we'll double-click on it in the table.
| | 01:43 | We'll drill down into our TwoTrees
database, Tables, and we'll go to our table
| | 01:49 | tbl_Orders. From there
we'll double click on OrderDate.
| | 01:55 | Finally, we'll add a closing
parenthesis to finish this statement and say OK.
| | 02:02 | The next field we want to add is our
Price. Let's double-click on that in the
| | 02:06 | Products table and we'll turn on our
Totals row, so that we can sum those
| | 02:10 | together to get the total price over the year.
| | 02:13 | In the Total row I'll change Group By to Sum.
| | 02:16 | The last two fields will be our
StateName and our DivisionName and we'll use
| | 02:22 | these full text titles in the report.
| | 02:25 | Let's go ahead and run this query to see
where we're at and we'll see we've got the years.
| | 02:30 | We've got the total price, each
state listed, and then the division that
| | 02:35 | those states are in.
| | 02:36 | Let's go back into our Design view
and we'll input our query parameters.
| | 02:41 | We want the Criteria for our year column to
come from the Year drop-down menus on our form.
| | 02:47 | We can select those using the same
Expression Builder that we used earlier.
| | 02:50 | In the Criteria row we'll right-click and
say Build. We'll drill down into our database.
| | 02:57 | This time we'll go into Forms > All
Forms, and if you have saved your form
| | 03:02 | earlier you can use that, but
I'll go ahead and choose this
| | 03:05 | frm_SalesByDivision-complete form.
| | 03:09 | From there I'll choose those combo
boxes, cbo_Year. I'll double-click on it to
| | 03:14 | add it to my expression.
| | 03:16 | So our form had two drop-down boxes.
We have the year and the comparison year.
| | 03:21 | We want our query to return results
based off of both values, so I'm going to
| | 03:24 | add an Or statement here and
I'll double-click on cbo_CompYear.
| | 03:30 | We'll go ahead and say OK.
| | 03:33 | The other parameter is going
to be based on the DivisionName.
| | 03:37 | We'll go to our Criteria for Division,
right-click, Build, we'll do the same thing.
| | 03:43 | We'll drill under TwoTrees Forms > All
| | 03:46 | Forms > SalesByDivision-complete > cbo_
division. I'll double-click on that, type in
| | 03:55 | the word Or, and double-click on CompDivision.
| | 04:00 | This will link the two drop-down menus for
divisions in our form as query parameters.
| | 04:05 | We'll go ahead and say OK
and there's our completed query.
| | 04:10 | If at this point we try and run it,
Access is going to look for our form, which I
| | 04:13 | currently don't have opened, and it's
not going to know what data is in here.
| | 04:18 | So let's go back into our Chapter 7
folder. We'll open up the form and
| | 04:23 | we'll input those values.
| | 04:25 | I'll go ahead and say New England for
the year 2008 compared to let's just say
| | 04:31 | Pacific for the year 2008.
| | 04:35 | Now that these values are in the form,
when I run the query the query parameters
| | 04:41 | are going to look to the form and
find the values that I'm looking for.
| | 04:44 | So let's go ahead and switch into the
data sheet view and you'll see that I get
| | 04:48 | the information that I asked for in the form.
| | 04:51 | I've got the year 2008 and I've got the
Pacific and New England divisions.
| | 04:56 | So now that we have two pieces down,
the last step is to create the report that's
| | 05:00 | based off of these query results,
and we'll do that in the next movie.
| | Collapse this transcript |
| Building the report| 00:00 | The final step in creating our reporting
tool is to link our query dynaset to a report.
| | 00:06 | Let's expand the Chapter 7 custom
group and we'll open up the report rpt_
| | 00:11 | SalesByDivision-complete.
| | 00:14 | I'll double-click to open it and Access
is going to ask us for the parameters.
| | 00:18 | Basically it's looking at the
query to get its information.
| | 00:22 | We don't have anything open right now so
that's why we're getting this parameter request.
| | 00:25 | Let's go ahead and say Cancel to
this and we'll get our query setup.
| | 00:29 | In order to do that though, if you
remember, we might need our form.
| | 00:33 | So let's open up our form,
we'll populate these values.
| | 00:37 | I'll select New England 2008
compared to the Pacific region 2008.
| | 00:45 | At this point I can run my query but we'll
use the form as input to generate the dynaset.
| | 00:51 | Now I can go ahead and open my report
and see the formatted query results.
| | 00:56 | So on our report we can see we have
New England, the year 2008, and the
| | 01:00 | states within New England.
| | 01:01 | We've got the summarized total for
the entire year of 2008 for each state.
| | 01:07 | We also have an aggregated function
that's generating the division sales for the
| | 01:13 | entire division over the year and the
average division sale from each state.
| | 01:18 | We can go ahead and scroll down and
select the Pacific region, we can see
| | 01:25 | the same information.
| | 01:27 | The year 2008, states within the
Pacific region, their total for the year,
| | 01:33 | the division total for the year,
and the division sverage for the year.
| | 01:37 | Finally on the bottom we have a
Grand Total section that takes the total
| | 01:41 | division sales from the Pacific and
the New England states and adds those
| | 01:45 | together, and we have a function over
here that's generating the current date
| | 01:50 | that the report was run.
| | 01:51 | Let's take a look at the Design view
to see how some of this is put together.
| | 01:55 | We'll change our view to Design view,
and I'll go ahead and open up our Property Sheet
| | 02:00 | either by pressing F4 or
clicking the button on the ribbon.
| | 02:03 | Let's take a look at the
report properties first.
| | 02:07 | I'm going to go ahead and expand this
a little bit so we can see everything.
| | 02:11 | And we'll see that the Record
Source for our report is based off this
| | 02:14 | qry_SalesByDivision-complete query.
| | 02:18 | If we click on each of these boxes,
for instance StateName, the control source
| | 02:23 | for that is the StateName
field that came from the query.
| | 02:26 | Let's scroll across here is our
SumOfPrice. The control source for that is the
| | 02:31 | SumOfPrice from the query, so this column here.
| | 02:37 | Go back to the report.
| | 02:38 | The Division Sales and the Division
Average are calculated fields and you might
| | 02:44 | recognize this. This is the Sum
function that we've seen previously and it's
| | 02:48 | taking the sum of the
SumOfPrice field from the query.
| | 02:53 | Same thing with the division average.
| | 02:55 | It's an average function that we've seen,
so the average of the SumOfPrice field.
| | 02:59 | Let's go ahead and scroll down. This box
here has that Now function that we saw.
| | 03:08 | So this is the exact same
syntax to generate the current date.
| | 03:11 | So this is how the form was put together.
| | 03:13 | It's basing its results off of the
query and it's taking the query dynaset and
| | 03:18 | formatting it for a printed page.
| | 03:20 | So now that all of our pieces are
together, the last step is to return to the
| | 03:24 | beginning and finalize the form with a
button to jump straight from the form to the report.
| | Collapse this transcript |
| Finalizing the reporting tool| 00:00 | Our three elements, the form, the query,
and the report, are now complete and it's
| | 00:05 | time to provide the
final piece for our end user.
| | 00:08 | Let's expand the Chapter 7 section and we
could see our query, our form, and our report.
| | 00:12 | Let's go back into our form for a moment.
| | 00:16 | The last step is to revise some
functionality to jump from the form to the report.
| | 00:21 | In this completed version
I have a Preview Report button.
| | 00:24 | Let's take a look at how that was created.
| | 00:26 | If I go into the View menu, we'll go
back into the Design view for the form,
| | 00:31 | and we can use our Button wizard here,
Button, to add a new button to the Design View.
| | 00:39 | Underneath the Report Options we
have an option to open the report or
| | 00:43 | preview the report.
| | 00:45 | The difference is the Open Report will
open the report in Layout view, whereas
| | 00:49 | Preview Report will open the
report in the Print Preview mode.
| | 00:52 | We'll go ahead and select Preview Report,
say Next, choose what report we want
| | 00:58 | to preview, rpt_SalesByDivision-complete,
| | 01:02 | go ahead and say Next, and you can
choose a picture or I prefer the text
| | 01:07 | version for the button, and go ahead and say
Next. Any name we'll do here and say Finish.
| | 01:14 | So now we've got a duplicate of
the button that was created earlier.
| | 01:17 | Let's go back into the Form
view and let's test our results.
| | 01:22 | We can choose any geographical region
that we want. Let's take a look at the
| | 01:26 | South Atlantic states in the year 2009
and compare those to the New England
| | 01:31 | states with the same year.
| | 01:33 | So once again, our form is going
to collect the information from the
| | 01:37 | end-user, these four variables.
When I press the Preview Report button,
| | 01:41 | those four variables are going to get fed into our
query. Let me open our query in Design view again.
| | 01:52 | Underneath a year we're taking the
Year combo box value and we actually
| | 01:57 | right-click and go into the Zoom menu.
| | 01:59 | So we're taking the Year Combo box
and the CompYear box. I'll say OK.
| | 02:06 | For the DivisionName, if I go into the
Zoom box, we're loading the values from
| | 02:11 | the Combo division and the
Combo CompDivision boxes.
| | 02:17 | Those are the names that we
gave to these four elements.
| | 02:20 | Once we have those values and we
press Preview Report, the query gets its
| | 02:24 | information, passes it to the report,
the report formats it, and gives it back to you.
| | 02:29 | And there we go.
| | 02:31 | We've got our final report.
| | 02:33 | So that's it. Wee've got a flexible
Reporting tool that will grow with
| | 02:36 | our database over time.
| | 02:38 | Using the same process with a few
modifications to the underlying query we
| | 02:41 | could easily generate a report that
look to the performance of a specific month
| | 02:45 | instead of year, or look at how our sales
team ranks against others within the same state.
| | 02:50 | The options are endless.
| | 02:51 | I'll leave it up to you to decide
which questions are most important as you
| | 02:54 | apply queries to your own database.
| | Collapse this transcript |
|
|
8. Alternative Query TypesFinding duplicate records| 00:00 | In the Query Wizard we've seen that
there are options beyond the simple Select query.
| | 00:04 | One of those is
the Find Duplicates query.
| | 00:06 | The Find Duplicates query is a
specific query type that will locate duplicate
| | 00:10 | records within your database.
| | 00:12 | This can be used for cleaning up
your data tables or reviewing records
| | 00:15 | with multiple entries.
| | 00:16 | We can create one by going to the
Create tab and using the Query Wizard.
| | 00:23 | I'll select the Find
Duplicates Query Wizard and say OK.
| | 00:26 | The first screen ask us which table or query
do you want to search for duplicate fields.
| | 00:31 | We're going to look at our Orders table.
| | 00:35 | The second screen asks which field
might contain duplicate information.
| | 00:39 | Let's identify all of our orders
where we had the same customer placed two
| | 00:42 | orders on the same day.
| | 00:44 | We'll choose OrderDate and CustomerID.
| | 00:47 | Let's go ahead and say Next.
| | 00:50 | This window asks, do you want the
query to show fields in addition to those
| | 00:53 | with duplicate values?
| | 00:55 | Let's add OrderID so we have a reference
point in case we do have duplicate values.
| | 00:58 | I'll go ahead and say Next and I'll
accept the default name and say Finish.
| | 01:05 | Access shows us that we
do have duplicate values.
| | 01:07 | I've got two orders placed by the
same customer on February 9th, 2005 and
| | 01:13 | an additional two orders placed by
a different customer on 11/14/2009.
| | 01:18 | Each order has its own unique OrderID number.
| | 01:21 | Let's go ahead and modify this query a
little bit so we have some more information.
| | 01:24 | We'll go to the Home tab
and switch back to Design view.
| | 01:28 | Now, let's add our customer name and
phone number so that we can call the
| | 01:31 | customer and see if they really intended
to place two orders or if it was a mistake.
| | 01:37 | I'll right-click and say Show Table
and we'll add our DirectCustomers table.
| | 01:40 | Now, Access already identified that
the CustomerID matches in both tables
| | 01:46 | and creates the join.
| | 01:48 | We'll add FirstName and LastName
and phone number to our query.
| | 01:52 | Let's run it again.
| | 01:54 | Now, we can see the customers
that placed those duplicate orders.
| | 01:57 | We have an easy way of calling the
customer and find out if it's a mistake, or
| | 02:01 | if they did intend to place two orders.
| | 02:03 | So Find Duplicates can be really handy in
keeping your database clean and organized.
| | 02:08 | Sorting out data entry errors that can
often arise from importing tables from
| | 02:12 | external sources is another
perfect use for Find Duplicates.
| | Collapse this transcript |
| Identifying unmatched records| 00:00 | The Find Unmatched query is another
query type that we can use to keep
| | 00:04 | our database organized.
| | 00:06 | The Find Unmatched query will look at
one table and find all of the records
| | 00:09 | that will have associated data in another table.
| | 00:11 | Let's take a look at how we can use the
Find Unmatched query to find all of the
| | 00:16 | states that don't have
any customers in them yet.
| | 00:18 | We'll go up to the Create tab
and we'll select the Query Wizard.
| | 00:22 | I'll choose the Unmatched
Query Wizard and say OK.
| | 00:26 | Now, we need to choose the table that has
the information in it that we're looking for.
| | 00:31 | We're looking for all the states that don't
have customers, so I'll choose the States table.
| | 00:36 | Go ahead and say Next.
| | 00:39 | Now, Access asks us which table or
query contains the related records.
| | 00:43 | Here we're looking for customers.
| | 00:44 | So I'll select my
DirectCustomers table and say Next.
| | 00:48 | Access has correctly identified
how those two tables are related.
| | 00:51 | The Abbreviation field in the States table
and the States field in the Customers table.
| | 00:56 | We'll go ahead and say Next again.
| | 00:59 | Now, Access ask us which field we
want to see in our query results.
| | 01:02 | We're interested in finding out which
states don't have customers so we'll
| | 01:06 | just take state's name. Go ahead and
say Next and we'll accept the default name.
| | 01:11 | Press Finish.
| | 01:14 | Access has identified that the state of
Arkansas currently doesn't have any customers.
| | 01:19 | So this could be an area where we
could focus an advertising campaign.
| | 01:21 | Let's take a look at one more
example of the Find Unmatched query.
| | 01:25 | We'll go back to the Query
Wizard, Find Unmatched, and OK.
| | 01:31 | This time we'll find out if we have
any products that haven't had any orders
| | 01:34 | placed with them yet.
| | 01:36 | We'll select the Products table and say Next.
| | 01:40 | Which table or query
contains the related records?
| | 01:42 | We're looking for the Orders
table here. Go ahead and say Next.
| | 01:47 | Again, Access has identified how those
two tables are related. We'll say Next.
| | 01:53 | Which fields do you want
to see in the query results?
| | 01:56 | Well, I'm interested in which products
haven't had orders for them yet, so I
| | 01:59 | want to know the product ID and the
product name that hasn't been used yet.
| | 02:04 | We'll go ahead and say Next and again
we'll accept this default name and say Finish.
| | 02:10 | Now, Access has identified the two
products that haven't had any orders
| | 02:13 | placed for them yet.
| | 02:15 | We have the two sampler sizes in
the extra virgin oil and the pure oil.
| | 02:19 | We can use this information to identify
products that we might want to place a promotion on.
| | 02:25 | So those are just two examples of how
you might apply your Find Unmatched query.
| | Collapse this transcript |
| Creating crosstab results| 00:00 | All of the queries that we've been
working with throughout this course have been
| | 00:03 | examples of select queries, basically
queries that select and manipulate data
| | 00:07 | from our tables and present the
results in a new but temporary table format.
| | 00:12 | But Access also has a couple of
other query types that we can work with.
| | 00:16 | The first type is called Crosstab query.
| | 00:19 | Like the Select queries, the
Crosstab query pulls information from our
| | 00:22 | tables and reorganizes it.
| | 00:24 | But unlike a Select query, the Crosstab
query is presented more like a standard
| | 00:28 | spreadsheet with column headers and row headers.
| | 00:32 | Right now, I've got opened on the screen qry_
AverageWageCrosstab from my Chapter 8 section.
| | 00:38 | We've got three columns here on the left.
| | 00:40 | I've got states, the number of
employees in each state, and the average hourly
| | 00:45 | rate of pay for the employees in each state.
| | 00:48 | After that, I've got some column
headings here for each department and we can
| | 00:51 | see the average rate of pay within
each department for those states.
| | 00:56 | And we scroll across here and we can
see that for the sales department in
| | 00:59 | Alaska the average hourly rate is $16.21.
| | 01:04 | Let's switch into Design
view to see how this is built.
| | 01:06 | I'll go to the Home ribbon
and click on Design view.
| | 01:11 | You can see the Crosstab query has a
new row here and we've selected which are
| | 01:15 | the row headings, for instance the
State, the # of Employees, and the
| | 01:19 | Average Hourly Rate are row headings.
| | 01:22 | You also have a column heading for
departments across the top and the values in
| | 01:27 | the center are the average of
the hourly pay for each employee.
| | 01:31 | So that's how this Crosstab was created.
| | 01:33 | Let's take a look at another example.
| | 01:35 | I'll go ahead and open up my qry_Crosstab query.
| | 01:39 | Currently, this query is presented
as a simple Select query with columns.
| | 01:44 | We've got a CountOfProducts here and then
the Size and Oil Name for those products.
| | 01:49 | This table is giving us the popularity
of each of our products or how many times
| | 01:53 | each product has been ordered.
| | 01:55 | In order to facilitate analysis,
let's change this to a Crosstab query.
| | 01:59 | I'll go to my Design view and
I'll turn on my Crosstab query option.
| | 02:03 | You can see that when we do that,
we've got a new row here called Crosstab,
| | 02:07 | and some blank fields.
| | 02:09 | If we click in each field, we'll see
that it's a drop-down and we can choose a
| | 02:12 | Row Heading, Column Heading,
or Value for each of our fields.
| | 02:17 | For the ProductID, we'll choose Value.
| | 02:19 | The count of the products is what's going
to appear in the center of the crosstab.
| | 02:23 | We'll have the size running
along the top, as our column header.
| | 02:28 | The oil name will run down
the left side as our row header.
| | 02:31 | Let's go ahead and take a
look and see how this runs now.
| | 02:33 | We'll run the query and we'll
see that the data is presented in a
| | 02:37 | completely different way.
| | 02:38 | We've got the oil names on the left,
the sizes across the top, and the count of
| | 02:44 | the number of times each product
has been ordered fills the middle.
| | 02:47 | So depending on the types of
information that you're collecting,
| | 02:50 | the Crosstab query could be a great way to
visualize large amounts of aggregated data and
| | 02:54 | provide further insight into your database.
| | Collapse this transcript |
| Creating backups| 00:00 | There are four additional types of
queries within Access collectively
| | 00:03 | called action queries.
| | 00:05 | We'll take a look at them over the next
few movies starting with the Update query.
| | 00:10 | Action queries differ from everything else
that we've worked on so far in one key area.
| | 00:14 | They actually modify your data tables.
| | 00:17 | In case it's not clear, all of the
queries that we run up to now simply
| | 00:20 | present your data, rearranged and
reformatted, in temporary tables called Record Sets.
| | 00:27 | All of the actual data tables
within your database remained unchanged.
| | 00:31 | Action queries will completely
throw that behavior out the window.
| | 00:35 | They will permanently modify your data.
This can be really powerful when used
| | 00:39 | appropriately or it can make
a really big mess real fast.
| | 00:43 | So before we jump in, I want to you take
a moment to really let that sink in and
| | 00:47 | I also want to walk you through the
steps that are critically important in
| | 00:50 | making a backup of your database.
| | 00:52 | We'll go up to the File tab
and select Save and Publish.
| | 00:58 | Under the Save Database As,
we'll choose Back Up Database.
| | 01:01 | When you press the Save As button,
Access asks us where you want to save that.
| | 01:06 | You'll notice that it appended
today's date to the file name.
| | 01:10 | We'll go ahead and say Save and that's it.
| | 01:14 | Access has backed up our
database into our exercise files folder.
| | 01:18 | Action queries are both destructive and
irreversible, but don't let that prevent
| | 01:22 | you from making use of them.
| | 01:23 | Now that we're protected from any
unfortunate mishaps, we can move forward.
| | Collapse this transcript |
| Creating update queries| 00:00 | The Update query is one of
four action queries in Access.
| | 00:04 | Using it we can create a selection of
our records that all require a specific
| | 00:08 | update or modification to the data
and then update all of those records
| | 00:12 | throughout our entire database.
| | 00:14 | The Update query will not
only breeze through the task,
| | 00:16 | it'll prevent any data entry errors
that typically crop up when manually
| | 00:20 | modifying large amounts of records.
| | 00:22 | Before we begin, if you haven't already
done so, please take a moment and review
| | 00:26 | the previous movie on
creating a backup of your database.
| | 00:30 | We can begin our Update query by going
to the Create tab and we'll create a new
| | 00:33 | query in Design view.
| | 00:35 | Let's suppose that all the employees
in our Human Resources department are
| | 00:39 | getting relocated to the new
corporate office in Dallas, Texas.
| | 00:43 | We want to go through our database and
find all of the employees in the Human
| | 00:46 | Resources department.
| | 00:47 | Then we want to change their
city and state to Dallas and Texas.
| | 00:52 | So first we'll create a Select query to
identify which records will be modified.
| | 00:55 | Let's go ahead and select our
Employees table and say Close.
| | 01:02 | From the Employees table we'll choose
the employee ID and we'll scroll down and
| | 01:07 | find City and State.
| | 01:10 | We'll also need a department so we can
apply a criteria of Human Resources and
| | 01:15 | I'll type that in the Criteria field here.
| | 01:21 | Let's run our query to see which records
which records will be affected by the change.
| | 01:25 | So those are the 13 Human Resources
employees that are all relocating to Dallas, Texas.
| | 01:30 | Let's create the Update
query and update our data table.
| | 01:33 | I'll go back to Design view and I'll switch our
query from a Select query to the Update query.
| | 01:40 | When I do that we'll get a new
row here that says Update To.
| | 01:43 | So what we want to do is update the
city to Dallas and we want to update the
| | 01:49 | state to Texas or TX.
| | 01:53 | Now if we go and this query, Access is going
to tell us that we're about to update 13 rows.
| | 01:59 | Once you click Yes, you can't undo the
command or reverse the changes, so make
| | 02:03 | sure this is what you want to do.
| | 02:04 | I'm going to click No here for a
second and show you that we do have a
| | 02:08 | difference with action queries between
viewing the datasheet and running the action query.
| | 02:14 | Running the action query will
actually apply those changes to your data
| | 02:17 | tables, where as viewing an action query will
show you which records are about to be effected.
| | 02:23 | So if I click View here and change to
Datasheet view, this shows me which city
| | 02:28 | and states are about to be changed,
I'll go back to Design view and this time
| | 02:33 | I'll say Run, and we'll go
ahead and make those changes.
| | 02:38 | Now if I go back into my data tables
for my employees, we can verify that that
| | 02:43 | change has been made.
| | 02:44 | I'll open up my employees table, which
is in the Chapter 1 groups, and I'll scroll
| | 02:49 | over to identify our departments.
| | 02:52 | Every employee that works for the
Human Resources department now says Dallas,
| | 02:57 | Texas under the City and State.
| | 03:00 | Data may need to be updated
for any number of reasons.
| | 03:03 | You may change vendors or have
an updated product ID number.
| | 03:07 | Your business may move, change names,
or get merged with another company, and
| | 03:11 | all of your addresses and phone numbers and
e-mail addresses might need to be altered.
| | 03:15 | Whatever the reason, you'll be able to
quickly and easily keep your database
| | 03:19 | up-to-date with an Update query.
| | Collapse this transcript |
| Making, deleting, and appending records| 00:00 | Creating an archive database is a
great way to house older data that you
| | 00:04 | might want to reference occasionally,
but don't necessarily need in your
| | 00:07 | day-to-day working database.
| | 00:09 | Such as old financial records or sales records.
| | 00:13 | We can accomplish this by
using three action queries.
| | 00:15 | The Make Table, Append, and Delete queries.
| | 00:20 | By now, I'm sure you know the drill.
Make sure your backup is in place before
| | 00:23 | applying any of the action queries.
| | 00:25 | So let's take a look at how we can create an
archive database of some of our older orders.
| | 00:29 | I'll go to the Create tab and Ill
create a new query in Design view.
| | 00:34 | I'm interested in making an archive
database of our older orders so I'll choose
| | 00:38 | my Orders table and close the Show Table window.
| | 00:42 | Now I want to export all of our old
order records to this external database and
| | 00:47 | a shortcut to add everything from one
table to our query is to double-click on
| | 00:51 | this asterisk character at the top.
| | 00:54 | The next thing I want to do is export only
the orders that are older than two years old.
| | 00:58 | We'll add an OrderDate field
so we can supply that criteria.
| | 01:02 | In the Criteria box, I'll right-
click and say Zoom so I can type it in.
| | 01:06 | So I'm looking for all of the
orders that are more than two years old.
| | 01:10 | In other words, I want to identify a
date two years ago from today and find the
| | 01:14 | orders that are older than that.
| | 01:15 | We can do that with the DateAdd
function that we saw earlier in the course.
| | 01:20 | First I'll type a less than or
equals to and then my DateAdd function.
| | 01:25 | The first part of our DateAdd is
what units we want to add or subtract.
| | 01:28 | We're going to subtract two years, so
I'll type in "yyyy" to denote the unit years.
| | 01:39 | The second piece is how many units.
| | 01:41 | We'll go in two years in
the past, so we want -2.
| | 01:45 | Finally, the third component is what
day we want to add or subtract from.
| | 01:49 | We want to subtract two years from
today, so I'll supply the Date function.
| | 01:53 | This will denote today's date.
| | 01:56 | I'll use a closing parenthesis to
finish my function and now I've got a
| | 02:01 | function that will choose all the
orders that are older than two years old.
| | 02:04 | We'll go ahead and say OK and run
the query to preview the results.
| | 02:10 | You can see I have a total of 1756
records that are more than two years old.
| | 02:16 | Let's archive these and get
them out of our working database.
| | 02:19 | I'll switch back into Design view, and
before I do this I want to make sure
| | 02:24 | that I turn off the Show
checkbox under OrderDate.
| | 02:26 | We're only using this field to choose
the criteria to find the orders that are
| | 02:31 | more than two years old.
| | 02:32 | All the records that are going to
be exported are represented by this
| | 02:35 | asterisk in the tbl_Orders.
| | 02:37 | So everything in the tbl_Orders is
going to be exported and then we're only
| | 02:41 | using the OrderDate
field to select the criteria.
| | 02:43 | Let's go ahead and use a Make Table
action query to make a copy of the records
| | 02:49 | and put them in the archive database.
| | 02:51 | Then we'll follow that up with a
Delete query to remove the same records from
| | 02:55 | the current database.
| | 02:57 | Let's go ahead and change this to a
Make Table query by clicking the Make Table
| | 03:00 | button in the Query Type and Access
asks us do we want to create a table in the
| | 03:04 | current database or in another database.
| | 03:07 | I'll choose Another and we'll find the
archive database in our exercise 8 folder.
| | 03:14 | Chapter 8/TwoTreesArchive.
| | 03:17 | Go ahead and say OK and now we need
to supply a name for this new table.
| | 03:22 | I'm going to call it ArchiveOrders.
We'll say OK and we'll run the query.
| | 03:29 | Access tells us that we're about to
paste 1756 rows into the new table.
| | 03:34 | Once you click Yes, you can't Undo the command.
| | 03:37 | Go ahead and say Yes and Access will
have copied all of those records into
| | 03:41 | the archive database.
| | 03:42 | But unfortunately, it doesn't give us any
sort of message to tell us that it happens.
| | 03:46 | It just does as soon as you click OK.
| | 03:48 | Now that our records are archived,
we want to delete the exact same records from
| | 03:52 | the current database.
| | 03:54 | We could follow this up by switching
to a Delete query and we'll see this
| | 03:58 | new line here that says Delete.
| | 04:00 | Let's go ahead and say Run, and Access
tells us we're about to delete 1756 rows
| | 04:06 | from the specified table.
| | 04:08 | We'll go ahead and say Yes
and the action is done.
| | 04:11 | If we try and run it again,
we should get 0 because those records have
| | 04:16 | already been deleted.
| | 04:18 | Go ahead and say No to this.
| | 04:20 | Let's take a look at our archive database
to see what things look like over there.
| | 04:24 | I'll go to my File tab, Open, and
I'll select my archive database from
| | 04:28 | the Chapter 8 folder.
| | 04:31 | I'll open that up. Access is going to
ask me if I want to save the existing
| | 04:34 | query that we're working on,
and we can just say No.
| | 04:36 | I'm going to go ahead and click on
the security warning to hide the message bar,
| | 04:41 | and we'll take a look at our
ArchiveOrders table that we created with the action query.
| | 04:47 | We'll see 1756 records.
| | 04:50 | These are the records that we
just exported from the other table.
| | 04:54 | Once the ArchiveOrders table has been
created in the archive database, any
| | 04:58 | further archivings that come out of
your working database would use an Append
| | 05:02 | query instead of Make Table.
| | 05:04 | The Make Table is to make a brand new
table. Append does the same functionality.
| | 05:09 | It just adds records to the bottom.
| | 05:11 | Once you're confident that your data
has been archived successfully, it's a
| | 05:15 | simple matter to convert your Make
Table or Append query into a Delete query to
| | 05:19 | remove the records from your
current database that you just archived.
| | 05:23 | Because the structure of the query is the
same, only the type of the query is changed,
| | 05:28 | changing from one type directly to the
other ensures that the records that you
| | 05:31 | delete are exactly the same
records that you just copied.
| | Collapse this transcript |
| Uniting tables| 00:00 | The Union query is a special type of
query within Access and it's used to
| | 00:04 | combine two different data
tables with the same structure.
| | 00:07 | Let's open up our Chapter 8 custom group
and we'll open up our Employees table.
| | 00:12 | This table houses all the
information about our in-company employees.
| | 00:17 | We can see that we have 200
employees represented in this table.
| | 00:21 | The FieldReps table has information
about our contracted field representatives.
| | 00:25 | If I open up that, you'll see
that it has an identical structure to
| | 00:29 | the Employees table.
| | 00:31 | We have a total of 100 field representatives.
| | 00:34 | We can use a Union query to take the
100 field reps and the 200 employees and
| | 00:40 | merge them together in a
single query record set.
| | 00:42 | Let's go ahead and close both of these
tables and we'll see how that's done.
| | 00:47 | I'll go into the Create tab and
I'll create a new query in Design view.
| | 00:50 | I'll go ahead and select my
Employees table and say Close.
| | 00:56 | Now I want to select all of my
employees from this table and I can use this
| | 01:00 | shortcut by double-clicking on the
asterisk to choose all of these fields.
| | 01:03 | Now the Union query is only created in SQL View.
| | 01:08 | You can't make the Union query
using the Design View in Access.
| | 01:12 | So what we can do is switch to SQL
view and finish our statements there.
| | 01:18 | This is the SELECT statement that
Access uses to select all of the fields from
| | 01:21 | the Employees table.
| | 01:23 | We can use this as a guide to help us
type out how to select all of the records
| | 01:27 | from the FieldReps table, and we'll
join both of those using a Union statement.
| | 01:32 | So at the end of this-- I'll jump down
two lines so it's easy to read-- I'll
| | 01:36 | write the word Union.
| | 01:37 | I'll move down another two lines and
I'll type in this SQL statement to select
| | 01:43 | everything from the FieldReps table.
| | 01:45 | We can look to the Employees table
above to help us with the syntax.
| | 01:48 | So let's write out the statement.
| | 01:50 | First we'll start with SELECT, and then we want
to tell Access what it is that we want to select.
| | 01:55 | We want to select the table
FieldReps. tbl_FieldReps.
| | 02:00 | And we want to select all of the fields
in the FieldReps table, so we'll out in
| | 02:04 | a period and then an asterisk.
| | 02:06 | Finally, we have to tell Access from
what table we want to select from and it
| | 02:09 | does seem a little bit redundant, but this
is the syntax that we're going to be using.
| | 02:13 | FROM tbl_FieldReps.
| | 02:19 | Finally, we'll add a
semicolon to finish our statement.
| | 02:21 | So now we've got a statement that
takes all of the employees information and
| | 02:25 | adds that to all of the field reps information.
| | 02:29 | When I run this query, you'll see we
have a total of 300 records and I'll scroll
| | 02:34 | to the right. We'll see we have
our employees listed on the top.
| | 02:38 | And if we scroll down, we'll see that those
are joined or unioned with the field sales reps.
| | 02:43 | Now we have a single query
result that has all of our associated
| | 02:48 | employees, whether they're internal
within the company or whether they're
| | 02:52 | contracted employees.
| | 02:54 | Now that we have both tables merged
together and functioning as one, we can save
| | 02:58 | this query out and use it as the
input to other queries down the road.
| | 03:02 | There's no reason why we would
need to physically merge the data from
| | 03:05 | both tables at all.
| | 03:07 | We can use a Union query to join both
tables in a record set without having to
| | 03:11 | duplicate that information in a
new table within our database.
| | Collapse this transcript |
| Embedding SQL code in queries| 00:00 | Sometimes you will want to include
records in your query based upon the
| | 00:03 | results of another query.
| | 00:05 | Often this may include a
calculation based upon your data.
| | 00:09 | For instance, instead of just
selecting values that are above or below a
| | 00:12 | specific and static number, you may
want to select values that are above or
| | 00:16 | below the average of the entire set.
| | 00:19 | In other words, you'll first need to
find out what the mathematical average is
| | 00:23 | and then use that value to define the criteria.
| | 00:26 | You can do this by writing an SQL
statement right into the field or
| | 00:30 | criteria rows of your query.
| | 00:33 | So let's construct a query that asks which
customers have placed above average orders.
| | 00:38 | Let's go into our Create
menu and then Query Design.
| | 00:42 | We'll add our Orders table and
our Products table and click Close.
| | 00:49 | So the first thing we need to do is
find the average order total across
| | 00:53 | our entire database.
| | 00:55 | We can do that by adding Price to our
query, turning on our Totals row, and then
| | 01:00 | changing the Group By to Average.
| | 01:03 | If I run this query, I see that my
average price over the whole database is $29.87.
| | 01:08 | Let's go ahead and back into our
Design view and I'll give this an alias to
| | 01:14 | denote what this value is.
| | 01:16 | This is Average Transaction.
| | 01:20 | I'll run the query one more time to
make sure we're getting the right results
| | 01:24 | and I can see that we got Average Transaction.
| | 01:27 | Now my Average Transaction reads $29.87.
Your average might slightly differ.
| | 01:33 | Earlier in this chapter, I deleted
several records from my database and used
| | 01:37 | them to create an archive database.
| | 01:38 | If you didn't complete those steps,
you'll have more records in your current
| | 01:42 | database and your average will be different.
| | 01:45 | Let's go ahead and switch into SQL
view so we can see the statement that was
| | 01:48 | constructed in Design view.
| | 01:49 | I'll click on my View menu and I'll choose SQL.
| | 01:55 | Here is the statement
that Access has constructed.
| | 01:57 | I'm going to go ahead and copy this statement
to the Clipboard and we'll use it later on.
| | 02:03 | So I highlight it and press
Ctrl+C to copy it to my Clipboard.
| | 02:07 | So now that we have an SQL SELECT
statement that calculates the average price
| | 02:11 | across our entire database, we can
use this as a criteria in another query.
| | 02:18 | Let's go back to our Create menu.
We'll do a new query in Design view.
| | 02:23 | This time we want to find out which customers
have placed orders that were above the average.
| | 02:27 | The tables I'll choose are DirectCustomers,
our Orders table, and the Products table.
| | 02:34 | Go ahead and say Close.
| | 02:36 | Now from our tables,
we'll choose a couple fields.
| | 02:38 | And first I want CustID and FirstName.
| | 02:42 | From my Products table, I'll choose Price.
| | 02:45 | The criteria for Price is anything
above the average. So I'll right-click
| | 02:49 | here and say Zoom to bring up my Zoom box.
So I'm looking for everything above average.
| | 02:55 | I'll say greater than or equal to
and then an open parenthesis.
| | 03:00 | Then I'll paste the SELECT statement that we
copied to our Clipboard from the other query.
| | 03:04 | Ctrl+V to paste that in.
| | 03:06 | I'll close the statement
with a closing parenthesis.
| | 03:10 | So now I have an embedded SQL
statement right in my Criteria field.
| | 03:14 | We'll go ahead and say OK
and I'll run my query.
| | 03:20 | Now in my records, I've got a total of
about 168 customers that have placed an
| | 03:25 | order that is higher than the
average for my entire data set.
| | 03:28 | As my database grows and more
transactions are added to it, the average will
| | 03:33 | move up or down and this query will
automatically update to show me all of the
| | 03:38 | customers that are above the current average.
| | 03:41 | Now that that SQL statement is
embedded into this query, I no longer need the
| | 03:45 | first one that I made.
| | 03:45 | I don't even have to save
it into my navigation pane.
| | 03:48 | I'll go ahead and just close it,
I don't need to save changes, and that
| | 03:52 | statement is permanently
saved within this query.
| | 03:56 | The SQL sub-query can be a useful
tool in keeping your database clean
| | 03:59 | of extraneous pieces.
| | 04:01 | By combining two queries into a
single standalone query, you can eliminate
| | 04:05 | clutter in your navigation pane
while reviewing a very specific subset of your data.
| | Collapse this transcript |
|
|
ConclusionNext Steps| 00:00 | Before we part, I'd like to share some
additional resources that you might find
| | 00:03 | useful in developing your databases.
| | 00:06 | First, I would encourage you to
spend some time digging into the
| | 00:09 | Expression Builder.
| | 00:10 | There are over 150 built-in
functions to choose from and with Access's
| | 00:14 | context-sensitive help system, it makes it
really easy to learn more about each one.
| | 00:18 | Simply select the function that you're
interested in, press the Help button, and
| | 00:22 | you'll be taken directly to the
reference sheet for that function.
| | 00:25 | Another helpful resource can be
found in online discussion forums.
| | 00:28 | I highly recommend the
forums at UtterAccess.com.
| | 00:31 | Here you could join a local
Access user group, post questions about
| | 00:35 | specific Access objects or procedures, and
even download sample projects and code snippets.
| | 00:40 | A second highly active community can be
found at dbforums.com/microsoft-access.
| | 00:46 | Finally, Microsoft's own Access blog
regularly features tips and tricks that you
| | 00:51 | can apply to your own database.
| | 00:53 | I want to thank you for joining me as we took
a closer look at using queries in Access 2010.
| | 00:58 | Have a great day!
| | Collapse this transcript |
|
|
