IntroductionWelcome| 00:04 | Hi! I'm Alicia Katz Pollock, and welcome
to Access 2010 Essential Training.
| | 00:09 | In this course, I'll introduce you to
Access 2010, the Microsoft Office Suite's
| | 00:12 | Database Application, and
demonstrate all of its features.
| | 00:16 | I'll show you how to build an Access
database from scratch, including Tables,
| | 00:21 | Queries, Reports, and Macros.
| | 00:25 | We'll use the new Table tools to
build tables quickly and efficiently.
| | 00:29 | We'll explore the Query
Designer to analyze your data.
| | 00:33 | I'll walk you through New Form and
Report Layout tools that use tables for
| | 00:36 | straightforward design, and we'll
build Macros to automate your database.
| | 00:41 | Whether you are new to Access or have
years of experience, I'll show you tips
| | 00:45 | and strategies for effective database design.
| | 00:47 | Now, let's get started with
Access 2010 Essential Training.
| | Collapse this transcript |
| Using the exercise files| 00:01 | If you are a premium member of the
lynda.com Online Training Library, you have
| | 00:05 | the access to the Exercise
Files used throughout this title.
| | 00:08 | The Exercise Files are in the Exercise Files
folder, which I have placed on the desktop.
| | 00:13 | You can store it wherever you'd like.
| | 00:14 | There are files for most movies.
| | 00:16 | They reside in sub-folders
named according to the chapters.
| | 00:20 | It is not necessary for you to use these files.
| | 00:22 | You can use files of your own in place of them.
| | 00:25 | If you are monthly or annual subscriber
to lynda.com, you don't have access to
| | 00:29 | the Exercise Files, but you can
follow along with your own work.
| | 00:32 | Let's get started.
| | Collapse this transcript |
|
|
1. Access BasicsDatabase concepts and terminology| 00:00 | Before we can began to build an
Access 2010 database, it's important to
| | 00:04 | understand fundamental
database concepts and terminology.
| | 00:07 | First, what is a database?
| | 00:09 | A database is a collection of
information about one subject.
| | 00:13 | Real life examples might be a
telephone book, a way to track your music
| | 00:16 | collection, or the way your
business attracts its customers in sales.
| | 00:21 | A database program gives you a way to manage
that data and to analyze it to make it useful.
| | 00:25 | You can sort the information in many
ways, extract just certain pieces of info,
| | 00:30 | or summarize it in reports.
| | 00:32 | Let's take a look at the
terminology you'll need.
| | 00:35 | First off are Tables.
| | 00:36 | A Table is one collection of
information and it's organized into
| | 00:39 | Fields, Records, and Data.
| | 00:42 | Fields are the columns in the table.
| | 00:43 | They are the categories
of info you are collecting.
| | 00:46 | In a phone book, these will be the
person's name, address and phone number.
| | 00:51 | Each is one person or thing you
are collecting information about.
| | 00:54 | In a phone book, each
person's listing is their record.
| | 00:57 | Data values are the actual
pieces of information themselves.
| | 01:01 | Alicia Katz Pollock, 123
Main Street, (800) 555-1212.
| | 01:07 | Many people try to store this
information in Excel, because it's organized
| | 01:10 | in rows and columns.
| | 01:12 | But Excel is a flat file,
| | 01:13 | meaning that all it gives you is one long list.
| | 01:16 | Access is a relational database, and it
gives you additional organizational tools
| | 01:20 | that are far more efficient, flexible,
and less prone to error. As you can see
| | 01:24 | here, if we capture our
customer orders in Excel,
| | 01:27 | for each one, we'd have to repeat the
customer's information, the products,
| | 01:31 | orders and the prices.
| | 01:33 | That's a lot of repetitious typing,
and you open yourself up to data entry
| | 01:37 | errors, like this Zip Code right here.
| | 01:39 | That was keyed in wrong.
| | 01:40 | In a Relational database, the orders
are tracked in three different tables:
| | 01:44 | one with information about the customers,
one table with information about the
| | 01:48 | products we carry and the third
table with the orders themselves.
| | 01:51 | Now, to make this work we have to add
some techniques into the table structures:
| | 01:55 | Primary Keys and Foreign Keys.
| | 01:58 | Primary Key is the unique
identifier for each record in the table.
| | 02:02 | You have to have some way
of distinguishing each entry.
| | 02:04 | I can't use Gino's Pizza
because it's not unique.
| | 02:08 | There may be only one in my neighborhood,
| | 02:09 | but I am there is more than one Gino's
Pizza in the United States, and I would
| | 02:13 | love for all of them to be my customers.
| | 02:15 | So I added an extra field called the
Primary Key and create a numbering system,
| | 02:20 | so that each store has its own id number.
| | 02:23 | Next, these Primary Keys become
Foreign Keys, also known as common fields,
| | 02:27 | linking the related
information between two or more tables.
| | 02:31 | It's the field that both tables have
in common, and this allows me to gather
| | 02:35 | data from both when I
take an order, or run reports.
| | 02:39 | That brings me to Relationships.
| | 02:41 | Access has a window where I tell it
how the Primary Keys and Foreign Keys
| | 02:44 | relate to each other.
| | 02:46 | The most common is a one-to-many
relationship, where one record in the first
| | 02:50 | table has multiple
occurrences in the second table.
| | 02:54 | Here, one customer, Gino's Pizza,
hopefully will order from us many times.
| | 02:59 | There are other relationship types as well.
| | 03:01 | But one-to-many is the most common.
| | 03:03 | Our next terminology are Data Types.
| | 03:05 | Data Types define your fields so
that Access knows how to manage
| | 03:08 | them appropriately.
| | 03:10 | Let's take a look at the different types.
| | 03:12 | AutoNumber sequentially increments
each record, and it's commonly used as
| | 03:15 | a Primary Key field.
| | 03:17 | Text is used for alphanumeric
characters, any letters and numbers.
| | 03:21 | Number is used for numbers
that you actually calculate.
| | 03:24 | Currency is for dollars,
cents, and foreign currency.
| | 03:28 | Data/Time is for dates and times.
| | 03:31 | Yes/No fields are for either/or
scenarios, and they can be defined as On/Off,
| | 03:34 | Yes/No and True/False.
| | 03:37 | Associate files from other programs,
such as Word or Excel, and include them
| | 03:41 | along with your record.
| | 03:43 | Hyperlinks are used for
e-mails and Web site URLs.
| | 03:46 | Memos are for when you have
long fields, long commentary.
| | 03:49 | It allows you to take notes.
| | 03:51 | Calculations do math based
on data in your other fields.
| | 03:55 | Lookups gather their data from other tables.
| | 03:59 | By using the appropriate data types,
you'll build your database in accordance
| | 04:02 | with industry standards.
| | 04:03 | When you are planning your database,
you need to take a look at the big picture
| | 04:07 | to make sure you are
creating your tables effectively.
| | 04:10 | This process is called normalization,
and there are accepted standards in
| | 04:14 | the database industry.
| | 04:16 | First, you want to use the
smallest meaningful fields possible.
| | 04:19 | You don't want one field called Name.
| | 04:21 | You want two fields, Last Name and First Name.
| | 04:24 | Otherwise, how would you sort by Smith if
the person's Name field said Fred Smith?
| | 04:29 | The next phase is looking at your data.
| | 04:32 | If you find that you are entering the
same information repeatedly, that's a sign
| | 04:35 | that those fields should be broken out
into a separate table and then linked
| | 04:39 | with a common field.
| | 04:41 | Also, think about the big
picture and your long-term growth plans.
| | 04:44 | It's easy to plan ahead, but
challenging to go back and add new tracking fields
| | 04:48 | once you have been in business for some time.
| | 04:50 | For example, you might not need, right
now, to track how your customers found out
| | 04:54 | about you, but if you add that to your
marketing plan two years from now, you
| | 04:57 | won't have that data for
any of your current customers.
| | 04:59 | So now, we are really ready to
begin our essential training.
| | 05:03 | In this course, we are going to build a
working database for Two Trees Olive Oil.
| | 05:07 | We'll create four tables: one with our
line of olive oils, one of our employees,
| | 05:12 | one table with our customers and a
forth tracking all orders placed.
| | 05:17 | Once that's done, we'll
make forms for data entry,
| | 05:19 | queries to analyze our company in sales,
and reports so we can print it all out.
| | 05:24 | Tables, forms, queries, and reports
will all be referred to as your database
| | 05:28 | objects throughout the course.
| | 05:30 | Now that you are familiar with the
terms I am going to use, let's get started.
| | Collapse this transcript |
| Starting Access| 00:00 | If you've never opened Access before,
click on your Start button in the lower
| | 00:04 | left-hand corner of your screen.
| | 00:06 | Click on All Programs and then
on the Microsoft Office folder.
| | 00:11 | Then at the top, you'll see Microsoft Access
with a red letter A. Go ahead and click on that.
| | 00:17 | Now, please note that the appearances
of these windows may be different if you
| | 00:20 | are using Windows Vista or XP.
| | 00:22 | But the procedure is the same.
| | 00:25 | Now, if you've opened Access before,
you'll see it on the Start menu.
| | 00:30 | You can click right on it to open up
a blank database, or if you hover your
| | 00:33 | cursor over it, you'll get a
flyout menu, and you can pick one of the
| | 00:37 | databases to open that way.
| | 00:39 | Now, note that you might not have
anything here right now, if you haven't
| | 00:42 | used Access before.
| | 00:43 | But let's go back to the
file we've already opened.
| | 00:46 | Once you have the window open,
click on the middle button in the upper
| | 00:49 | right-hand corner to
maximize it, so it fills the screen.
| | 00:52 | Now, we are ready to go.
| | Collapse this transcript |
| Creating a new file| 00:00 | When you are starting a database
project you can either start with the
| | 00:03 | pre-designed template and modify
it, or you can start from scratch.
| | 00:07 | Once you've opened up Access, you can
click right here to open up a brand-new file.
| | 00:11 | Use this Blank Web database icon if
you'll be integrating Access with SharePoint.
| | 00:17 | Click on Sample templates, and you
can see the database templates Microsoft
| | 00:21 | created to get you started.
| | 00:23 | Click this Back button to
get back to your choices.
| | 00:26 | You can also go online to the new
Office.com Web site, where there are a variety
| | 00:30 | of templates that you can download
to your computer with just a click.
| | 00:33 | You can see the file size, the rating and
a screenshot of the database right here.
| | 00:38 | Let's go back again and choose a blank database.
| | 00:43 | On the lower right-hand side,
we need to name our database,
| | 00:46 | erase what's there, and
we'll call ours TwoTreesOliveOil.
| | 00:54 | If you'd like to change the default
location, click this yellow folder and save
| | 00:58 | it where you like it go, then click Create.
| | 01:02 | Now we have a blank
database ready for us to populate.
| | 01:05 | But first, let's explore the
Access program's interface.
| | Collapse this transcript |
| Trusting a file| 00:00 | Every time you open an Access data file,
you have to go through a security check,
| | 00:04 | since databases are potential security threats.
| | 00:07 | Acro can place a virus in a macro,
which is a piece of automated code, and when
| | 00:11 | it runs, the virus can infect your computer.
| | 00:14 | To prevent this, when you create a new
Access file, or open up an existing file,
| | 00:18 | there are two places you can go:
| | 00:20 | The first is to your File tab and to
this Info button right here, and you'll
| | 00:25 | see a Security Warning.
| | 00:27 | I can click on Enable Content and
click on Enable All Content right here.
| | 00:32 | But there is another place that I can do it.
| | 00:34 | If I go to the Home Ribbon, the first
time I open up the file, I'll see Security
| | 00:38 | Warning in this yellow bar.
| | 00:40 | And if I click on Enable Content,
it will set it as a trusted document.
| | 00:44 | And if I go back to the File Ribbon, you'll
see that the Enable Content is no longer here.
| | 00:48 | This one security setting will alert
Access that you are confident that the file
| | 00:52 | is your own, or created by
a source that you trust.
| | Collapse this transcript |
| The Quick Access toolbar| 00:00 | At the top left of the window,
you'll see a few tiny icons.
| | 00:04 | This is the QuickAccess toolbar.
| | 00:06 | The buttons that are showing, by
default, are Save, Undo, and Redo.
| | 00:10 | Right now, they are grayed out because
I haven't actually done anything yet.
| | 00:13 | If I want to add additional buttons to
this toolbar, I can click on the dropdown
| | 00:17 | arrow, and a list of commands will appear.
| | 00:20 | Notice the check marks in front
of the buttons that we have now.
| | 00:23 | So let's say I'd like save a
couple of steps when printing.
| | 00:26 | I can click on Quick Print.
| | 00:28 | And now I have a button on the toolbar that
will send my document straight to the Printer.
| | 00:33 | I can also click on More commands.
| | 00:35 | And you can actually add any command
from the entire Access program to your
| | 00:39 | QuickAccess toolbar.
| | 00:40 | For instance, I can click on this Close
Database button, click the Add button,
| | 00:46 | and now it's on my QuickAccess toolbar.
| | 00:48 | I'll click OK to close it, and here it is.
| | 00:52 | If you like, you can also have
this menu show below you Ribbon.
| | 00:56 | I'll click on that again
to bring it back to the top.
| | 00:59 | You can modify the
QuickAccess toolbar anyway you like.
| | 01:01 | So you can click on your commonly used
commands from anywhere in the program,
| | 01:05 | making the workflow as efficient as possible.
| | Collapse this transcript |
| Backstage view| 00:00 | The Backstage view is where you'll
find all the commands for managing
| | 00:03 | your database file.
| | 00:05 | To use the Backstage view, click on the
red File menu at the top left of the screen.
| | 00:10 | Save, Open, and Close are right on top.
| | 00:12 | If you need to rename your database
or move it to a new location, you can
| | 00:15 | use Save Database As.
| | 00:17 | If you want to make a duplicate of
one of your Tables, Forms, or Reports,
| | 00:21 | use Save Object As.
| | 00:23 | Here are your recently used databases,
so you can open them with one click.
| | 00:27 | The Info button gathers
information and tools. It's dynamic.
| | 00:32 | It will change according to your database.
| | 00:34 | Simple tools that may appear here
include Compact & Repair, encryption, enabling
| | 00:38 | content in SharePoint Publishing Services.
| | 00:41 | The Recent button shows
your recently opened files.
| | 00:44 | And down here, you can specify how many of
them you'd like to see in the menu on the left.
| | 00:49 | The New button allows you to create a new
Database from scratch or from a template.
| | 00:53 | Print has all your printing options.
| | 00:56 | Quick Print will send your objects
straight to the Printer without a dialog box.
| | 01:00 | Print will open up the Printing Options
dialog box, allowing you to choose your
| | 01:03 | printer, or your number of copies.
| | 01:06 | If you want to only print a certain
set of records, you can highlight them
| | 01:09 | first and then click here
to print just that group.
| | 01:13 | I'll click Cancel, go back to
the File menu, and back to Print.
| | 01:18 | Print Preview shows you what your page
will look like when printed, and gives
| | 01:21 | you the options for Paper Size, Margins, Portrait,
and Landscape, zooming in to see it ,and more.
| | 01:28 | You can even export from this window
into an Excel file a delimited Text File,
| | 01:34 | a PDF, or attach it to an e-mail.
| | 01:37 | Click the big red X to close the Print
Preview and return to your database, and
| | 01:41 | then click back on the red File tab
again to return to Backstage view.
| | 01:45 | The Save & Publish button allows you to
convert the database to previous formats.
| | 01:49 | And here are a number of advanced features
that we will look at later in this course.
| | 01:54 | You can use Package and Sign to
attach a digital signature to confirm
| | 01:57 | authenticity when you e-mail it to others.
| | 01:59 | You can back up your database.
| | 02:01 | Use the ACCDE button to create a stand-
alone run-time application, allowing others
| | 02:06 | to use your database even if they
don't have Access on their computers, and
| | 02:10 | click here to save your
database to a SharePoint server.
| | 02:13 | If I click on Publish to Access Services, you
can convert your database to a Web-based file,
| | 02:19 | so you can publish it on the
Internet via a SharePoint server.
| | 02:22 | Help has been brought down to this menu.
| | 02:26 | And here are Access's Options both
of which we will explore in future
| | 02:29 | lessons, back again to File.
| | 02:32 | And here is the Exit button to quit Access,
| | 02:35 | although you can also use the big
red X in the upper right-hand corner.
| | 02:39 | The Backstage view is a
comprehensive zone for working on your database
| | 02:43 | file itself.
| | Collapse this transcript |
| Exploring ribbons| 00:01 | Let's take a look at how
Access 2010's Interface works.
| | 00:04 | Across the top are Ribbons,
containing all of Access's commands.
| | 00:09 | There are four tabs:
| | 00:10 | Home, Create, External Data, and Database tools.
| | 00:15 | Let's start with the Home Ribbon.
| | 00:16 | We will open up our Products Table.
| | 00:19 | The first button, View, allows you to
switch between this Datasheet View and the
| | 00:25 | Design View so that you
can work on its structure.
| | 00:27 | I can click on it again to toggle back.
| | 00:31 | The Clipboard group is where you will
find your commands for cutting, copying,
| | 00:34 | pasting, and the Format Painter.
| | 00:36 | We will talk about these groups, Sort &
Filter, Records and Finds, in later lessons.
| | 00:42 | On this side, you'll find
your tools for working with text.
| | 00:45 | Notice the words down here, Text Formatting.
| | 00:48 | This is called a group name, and refers
to all the command buttons in this area.
| | 00:52 | When you see a little box in the
corner, this is called the Launcher.
| | 00:55 | Click on it, and you'll get a full dialog
box with additional commands for that function.
| | 01:01 | Let's look at the Create tab.
| | 01:03 | It has all the commands for making
new Tables, new Queries, new Forms, new
| | 01:07 | Reports and new Macros.
| | 01:10 | The External Data tab contains all the
tools for importing your information into
| | 01:14 | the database from other programs, and
exporting the data out of your database
| | 01:19 | into other useful formats.
| | 01:21 | The Database tools contains exactly
that, tools for managing your database.
| | 01:25 | We will go into all of these during this course.
| | 01:28 | Now there is a little arrow right here.
| | 01:30 | Click on it to hide and show the Ribbon,
giving you more space to work when you need it.
| | 01:35 | Now, because we have the Products Table
opened, we will see additional tools up here.
| | 01:40 | These colored tabs contain commands
for modifying that particular object.
| | 01:45 | The colored tabs are context-sensitive,
and will come and go or change as you work.
| | 01:50 | Now, click on this little triangle right here.
| | 01:53 | It opens and closes the Navigation
Pane where you can see and work with
| | 01:56 | your database objects.
| | 01:58 | We will explore the Navigation
Pane in detail, later in this chapter.
| | 02:02 | Now let's go down to the bottom
and open up your Products Split
| | 02:06 | Form, double-click on it.
| | 02:09 | Down here at the bottom, you can
see the Record number that you're on.
| | 02:13 | You can use these little arrows to
scroll through them, forward and back.
| | 02:18 | You can go back to the first
record or to the last record.
| | 02:22 | This button here with the yellow
spark creates a New Blank record.
| | 02:26 | I can click here in this Search box to
find a specific record that I am looking for.
| | 02:30 | And down here in the bottom right-
hand corner are quick buttons to switch
| | 02:35 | between the different views of your object.
| | 02:38 | And if I right-click on the tabs, I can
save an object, close it, close all the
| | 02:46 | tabs or toggle between my different views.
| | 02:49 | I'll go ahead and click Close
All, and both tabs will close.
| | 02:53 | Now that you are familiar with
Access's layout, let's learn to use it.
| | Collapse this transcript |
| Using the Navigation pane| 00:00 | The Navigation Pane has a few tricks
up at sleeve so that you can use it
| | 00:04 | effectively for your workflow.
| | 00:06 | Let's take a look at the
Navigation Pane and all its features.
| | 00:09 | First, you can click this little button
to collapse the Navigation Pane, to make
| | 00:13 | more room for your workspace.
| | 00:15 | Click it again to bring it back out.
| | 00:17 | You can also resize it by holding your
cursor over the edge and getting this
| | 00:20 | double-headed arrow. Then you
can make it narrower and wider.
| | 00:24 | If you double-click on it, it will
close completely and open back up again.
| | 00:31 | You can click on the section
headers to open and close them.
| | 00:38 | You can also sort the
Navigation Pane so it's useful for you.
| | 00:42 | Click on this little arrow in the circle.
| | 00:45 | Right now, it's sorted by Object Type
so that all the tables, the queries, the
| | 00:49 | forms and reports are grouped together.
| | 00:52 | Let's change it to Tables and Related Views.
| | 00:54 | Now, you'll see the Customers :
Table and all of the objects that use
| | 00:58 | information from it - they call that
dependent on - and here's the SalesReps :
| | 01:02 | Table and all of the objects dependent on it.
| | 01:05 | This can be practical, but it also
makes it sometimes more confusing because
| | 01:09 | you'll see the same objects
repeated throughout the column.
| | 01:13 | I can click on that again
and see them by Created Date.
| | 01:19 | The most recent objects will be on top.
| | 01:21 | The oldest ones will be on the bottom.
| | 01:24 | Drop it down again, and we
can also use the Modified Date.
| | 01:28 | These are the ones that I've worked with today.
| | 01:30 | These are the ones I
haven't worked with for a while.
| | 01:33 | Let's bring it back again
to Tables and Related Views.
| | 01:37 | And you also have Filter By Group.
| | 01:39 | Now, if I just want to see everything
related to the Customers table, I can
| | 01:44 | filter, and now I just see the Customers
| | 01:46 | Ttable and everything related to
that, or the SalesReps table.
| | 01:52 | Let's click on the arrow one more time,
and come back to All Tables, and we
| | 01:57 | can see everything.
| | 01:59 | And because it's the most organized,
let's bring it back to Object Type.
| | 02:04 | Because the Navigation Pane is so
flexible, you can organize it any which way
| | 02:07 | you want, depending on your task at hand.
| | Collapse this transcript |
| Getting help| 00:01 | It's comforting to know that when you
are stuck, help is just a click away.
| | 00:04 | Let's open up this table right
here by double-clicking on it.
| | 00:08 | If you're not sure what a button does,
hold your cursor over it and a tooltip
| | 00:11 | will appear with the name of the
button, and sometimes description text.
| | 00:16 | To get more information come up here to the
blue question mark in the right-hand side.
| | 00:21 | Click on it and the Help Center will appear.
| | 00:24 | Click the link closest to
what you're looking for.
| | 00:26 | Let's say I wanted to know about Queries.
| | 00:29 | It gives me a list of topics.
| | 00:31 | Choose one, and it will give you an
article with explanations, and a list of steps.
| | 00:39 | At the bottom, you'll
also find related articles.
| | 00:42 | There is a Search box at the top of the window.
| | 00:46 | Let's say I'm going to
search for "creating a query."
| | 00:50 | And in my search results, let's do
"Create queries for a new database."
| | 00:55 | Some topics will redirect you
online to Microsoft's Web site.
| | 00:59 | You may wind up in a video library or in
the knowledge base. Either way, you must
| | 01:04 | be online to use these resources.
| | 01:06 | I'm going to close this Help.
| | 01:09 | You can also get help from the Backstage view.
| | 01:12 | Click on the red File tab
and then down here to Help.
| | 01:16 | You'll find a Help area, and the top
one is that same blue question mark.
| | 01:21 | And when I click on it, I
get the same help window.
| | 01:24 | When you've got a question, the
first place you should always look are
| | 01:27 | Microsoft's Help files.
| | Collapse this transcript |
|
|
2. Creating a First Database and Using the ToolsPlanning and designing your database| 00:00 | Before we can start building our
database, we need to plan it out.
| | 00:04 | No matter how much enthusiasm you
have for building your database to
| | 00:07 | organize your business, you can't just jump in
and start typing, then make corrections later.
| | 00:11 | All of the components are
interlaced, and the more time you spend in the
| | 00:14 | beginning planning and designing, the
less frustration you'll have later, or
| | 00:18 | worse, the less work you'll
have to redo. Plan ahead.
| | 00:22 | What information can you imagine ever needing
to collect, even if you're not using it now:
| | 00:27 | customer referral sources for marketing,
additional details about the product.
| | 00:31 | The more you build in from the start the
less back-filling you'll need to do later.
| | 00:36 | Let's take a look at the project
for this course. TwoTreesOliveOil.
| | 00:39 | What information do we need to track?
| | 00:41 | Customers, Sales Reps, Products, and Orders.
| | 00:46 | Next, what information
do we need about each one?
| | 00:49 | It's helpful to write these out
on paper, or in a Word document.
| | 00:53 | Think about the relationships within the data.
| | 00:55 | How will you put one set
of information into another?
| | 00:58 | Then examine what kind of
end results you'd like to see:
| | 01:02 | an order form to take customer
orders that automatically recognizes our
| | 01:06 | products, a report of what products
sold best, which customers sold the most,
| | 01:11 | and reports for your sales reps,
everything from contact information to how
| | 01:15 | much money they made to company.
| | 01:17 | These are just a sample of the
analysis we'll do on our own data.
| | 01:20 | But as you think about these questions
for your own project, be sure to include
| | 01:24 | all the fields you'll need to
generate these types of reports.
| | 01:27 | Planning ahead can be over half the
work in developing a database solution
| | 01:31 | for your company.
| | Collapse this transcript |
| Creating tables using Application Parts| 00:01 | When you're ready to make your first
table, you can either build it from scratch
| | 00:04 | or use Application Parts as a starting point.
| | 00:07 | You can even save your own
Application Parts for future reuse.
| | 00:10 | So, let's create our first table,
and we'll use Application Parts.
| | 00:14 | Click on the Create tab,
and here on Application Parts.
| | 00:20 | Application Parts are pre-
created database templates.
| | 00:22 | Some include not only tables, but
combinations of forms, tables, and reports as well.
| | 00:29 | We're going to use this
Contacts Application Part.
| | 00:32 | Go ahead and click on it.
| | 00:33 | And we'll instantly have a Contacts table,
three forms, four reports, and a query.
| | 00:40 | Now, we're not going to be using most of these.
| | 00:42 | We're going to be focusing on this table.
| | 00:44 | So let's start by deleting them.
| | 00:46 | To delete objects, I could click on it
and right-click on it, and click Delete.
| | 00:51 | But I don't have to do that over and over again.
| | 00:53 | You can use a Shift-click technique.
| | 00:54 | I'll click on the first one,
ContactDetails, and then hold my Shift key down
| | 00:59 | and click on Label.
| | 01:00 | And that will highlight all of the
objects that I want to delete, and then I can
| | 01:04 | just hit Delete on my keyboard, and
it says that this will remove them from
| | 01:08 | groups, and I can't undo this.
That's absolutely fine. Click Yes.
| | 01:12 | Now, I want to rename this
Contacts and call it Customers.
| | 01:16 | So I'm going to right-click on it,
and come down here to Rename and change
| | 01:20 | the name to Customers.
| | 01:23 | Now, let's open it up.
| | 01:24 | Double-click, and I can see all
of the fields that I put in there.
| | 01:28 | Most of these are going to be useful
for me, but there are some that are not.
| | 01:32 | I'm going to use this as a tour
to show you how the fields work.
| | 01:35 | First is an ID field.
| | 01:38 | Click up here where it says Table tools.
| | 01:40 | Click on Fields, and we can see
the Data Type, which is AutoNumber.
| | 01:46 | An AutoNumber acts as a counter.
| | 01:48 | It starts with 1, and every time I add
a customer to it, it'll automatically
| | 01:52 | increase to 2, 3, 4, et cetera.
| | 01:55 | I don't have to type anything in myself.
| | 01:56 | Now, note that this is not an accurate
count of how many records I have in my
| | 02:00 | databas,e because if I delete record 14, for
example, the counter would then go 12, 13, 15.
| | 02:06 | The numbers do not get reused.
| | 02:08 | For us though, we need a different system.
| | 02:10 | TwoTrees identifies its customers by two
store initials followed by its zip code.
| | 02:15 | So we need to change this field's data
type from AutoNumber to Text, allowing me
| | 02:21 | to enter a combination of letters and numbers.
| | 02:24 | So, let's learn more about the data types.
| | 02:26 | Let's look at all of our fields.
| | 02:28 | So far you've seen AutoNumber and Text.
| | 02:31 | Let's talk about a couple of more.
| | 02:33 | Most of these are going to be Text
fields, but let's make a distinction here.
| | 02:38 | Zip Code is a Text field, even
though it's made up of numbers.
| | 02:43 | And that's because you don't
do calculations on zip codes.
| | 02:45 | You don't add your zip and my zip
together to make a third location.
| | 02:49 | The Number data type is used for
things that you have to add, subtract,
| | 02:52 | multiply, and divide, like quantities.
| | 02:55 | Let's scroll- down and take
a look at a couple of more.
| | 02:57 | Web Page is a hyperlink data type.
| | 02:59 | Hyperlinks are either for Internet URLs or
for e-mail addresses. Tab over to Notes.
| | 03:05 | This is a Memo field, which allows me
65,535 characters when I enter them, or 2
| | 03:13 | GB of space, if the data is
entered programmatically.
| | 03:16 | It also allows for rich-text
formatting. Hit Tab again.
| | 03:20 | The next field, Attachments, allows me
to store documents, like Word files
| | 03:24 | or Excel spreadsheets.
| | 03:25 | This will be a good place to save my
contracts, or copies of purchase orders.
| | 03:28 | And let me come back here for a moment to Notes.
| | 03:31 | There are a number of other data types as well:
| | 03:34 | Date/Time, Currency, Yes/No, OLE Object,
and we'll see these in later lessons.
| | 03:40 | Now, I want to show you something interesting.
| | 03:42 | These two fields right here are Calculated,
and let me show you how they come about.
| | 03:48 | Let's start with our ID field.
| | 03:49 | TwoTrees identifies all of the
customers by two initials from their name, and
| | 03:55 | then their zip code.
| | 03:57 | So, I'm going to put in GP94004,
and the company name is Gino's Pizza.
| | 04:06 | The owner's name is Morelli, and I'm
tabbing across to go from field to field.
| | 04:11 | Now, I've got just this little bit of
information in, and normally I would
| | 04:15 | go ahead and fill in the rest of this
information, but I want to show you something.
| | 04:18 | Over on the far right-hand side, I
have those Two Calculated fields, and the
| | 04:23 | Contact Name now became Gino Morelli,
and it has File As Morelli, Gino.
| | 04:29 | Now, those are not going to be useful for me
in this database, so I do want to delete them.
| | 04:35 | But before I do, maybe I have an instance
in the future where I do want these fields.
| | 04:40 | I'm going to go ahead and show you how
to save these table as an Application
| | 04:44 | Part for future use.
| | 04:46 | So, go to the Backstage view by
clicking on the red File tab and come down to
| | 04:50 | Save & Publish, and choose Template.
| | 04:55 | When Template is orange, click on Save As.
| | 04:58 | And I have this form to fill out.
| | 05:00 | This Name is going to be called Customer Table.
| | 05:05 | I could put in a description if I'd like.
| | 05:07 | It gives me an opportunity to put in a Category.
| | 05:09 | I'm going to go ahead and
leave this as User Templates.
| | 05:12 | If I'd like to associate my own icon with my
Application Part, I can put in an icon right here.
| | 05:19 | These right now are grayed out.
| | 05:20 | I'll put a check mark here in
Application Part so that it shows up with my
| | 05:23 | Application Parts menu.
| | 05:26 | And when I do that, it lights up Primary Table.
| | 05:30 | The table that I select here will be
the default that appears in my wizard.
| | 05:34 | We only have one, so I'll choose Customers.
| | 05:36 | The Instantiation Form allows you to
pick a form in your part that will be run
| | 05:40 | one time, right after the part is inserted,
and then will be deleted when closed.
| | 05:44 | This can be useful as a splash screen,
or if you have a more complicated part
| | 05:48 | that require some form of
setup code before it can be used.
| | 05:52 | If I want to include the data in that
new Application Part, I can check this box.
| | 05:57 | We're going to leave that
blank, and I'll click OK.
| | 06:01 | It says that my template has been
successfully saved, and I'll click OK.
| | 06:06 | And now when I go to the Create tab and
look under Application Parts, I can see
| | 06:11 | my User Template right there.
| | 06:12 | Now that I've saved that Application Part,
let's go ahead and delete these two fields.
| | 06:17 | I'll click on Contact Name, and I'll hit Delete.
| | 06:20 | It will say, "do you want to
permanently delete the files?" and I'll say Yes.
| | 06:24 | And then I'll come over here
and delete this field as well.
| | 06:28 | We've made a lot of changes to
this table. So, let's save it.
| | 06:32 | Click on the Save icon up
here in the Quick Start toolbar.
| | 06:36 | So, so far you've learned how to create
a table using Application Parts and some
| | 06:40 | of the field properties
on the Table Tools Ribbon.
| | 06:43 | Next, we'll look at building a table
from scratch in the Datasheet View.
| | Collapse this transcript |
| Creating tables in Layout view with Quick Start| 00:01 | Access 2010's Layout field allows you
to create a table quickly and easily.
| | 00:05 | Let's build our second table for our sales reps.
| | 00:07 | First, click on the Create tab
and then on the Table button.
| | 00:13 | Now look over on the right.
| | 00:14 | You'll see contextual
Table tools, Fields and Table.
| | 00:19 | Right now, we want to be on the Fields tab.
| | 00:22 | We can start adding in our own fields.
| | 00:23 | But first, I want to show
you a tool called Quick Start.
| | 00:26 | These are pre-created sets of
fields to speed up your table design.
| | 00:30 | Click on this More Fields button
and then scroll down to the bottom.
| | 00:34 | You'll see Quick Start, and we want Name.
| | 00:37 | It will add in Last Name and First Name for you.
| | 00:41 | Next, click inside the field under
Click to Add, so that our next fields go
| | 00:45 | after our Name Fields.
| | 00:47 | If I'm still highlighted on First Name,
our fields will go in out of order.
| | 00:51 | So let's go back down to Quick
Start, and this time choose Address.
| | 00:57 | It adds all of the standard address fields:
| | 01:00 | Address, City, State Province,
ZIP code and Country Region.
| | 01:04 | Right now, Country Region is highlighted,
but because all of our employees are
| | 01:08 | in the United States, we don't need it.
| | 01:10 | Click on this Delete button
right here, and it will disappear.
| | 01:14 | So now, let's put in our phone numbers.
| | 01:17 | Click right on Click to Add, and it
will drop down a list of field types.
| | 01:22 | A phone number is Text.
| | 01:24 | Even though it's made up of numbers, it
does have parentheses and dashes, and we
| | 01:28 | don't add, subtract and multiply phone numbers.
| | 01:31 | So click on Text and type in "HomePhone"
with no space but with two capital letters.
| | 01:39 | When I hit the Tab key, it
immediately pops up another data type list.
| | 01:43 | Click on Text again.
| | 01:44 | And this time type in CellPhone, also with no
spaces, and the capital letters for each word.
| | 01:51 | Hit Tab again.
| | 01:52 | Our next field is going to be an Email.
| | 01:54 | So we want it to be a Hyperlink,
type in "Email" without a hyphen.
| | 02:01 | So now we're going to do
another Quick Start field.
| | 02:03 | So I'm going to scroll over and
click underneath Click to Add.
| | 02:08 | Sometimes it will jump back to the beginning.
| | 02:10 | In that case, scroll back over and try it again.
| | 02:12 | So to do another Quick Start field,
we're going to click on More Fields, and
| | 02:18 | then down to the bottom and
choose Start and End Dates.
| | 02:22 | Two fields are inserted.
| | 02:23 | And they're automatically given
what's called a short Date format.
| | 02:27 | If you want different Date and Time
formats, when you click on More Fields, you
| | 02:31 | have six different options right here.
| | 02:34 | The next field that we
want is going to be a picture.
| | 02:36 | So we're going to make it
the data type of Attachment.
| | 02:40 | It puts up a paperclip right here.
| | 02:43 | Now let's enter in a field using these
buttons right here to specify our data type.
| | 02:47 | We're going to put in our
salary, which is going to be money.
| | 02:50 | So we'll use the Currency button,
and type in "Salary." Press Tab.
| | 02:56 | Now we're going to use another data type: Yes/No.
| | 03:00 | Now before I click it, a Yes/No field
gives you a check box that you can use to
| | 03:04 | toggle or mark records.
| | 03:06 | I like to include one if I'm going to
run mailing labels, for example, because
| | 03:10 | it gives me a quick way of
flagging the ones I need.
| | 03:12 | There are two ways we can add this one.
| | 03:14 | I can either click this Yes/No button
right here, or if I come up to More Fields
| | 03:19 | and drop it down, I actually have
several different types, a Check Box, Yes/No,
| | 03:23 | True/False and On/Off.
| | 03:27 | We're going to go ahead and choose Yes/No.
| | 03:28 | And we're going to give it the name "Flag."
| | 03:32 | Now notice it comes up with the
default value of No, or sometimes a zero.
| | 03:36 | Both of these are programming syntax
to indicate that the check box is empty.
| | 03:40 | Now that we have our table built, let's save it.
| | 03:43 | Click up on the Save icon up here,
and call it "Sales Reps," and click OK.
| | 03:52 | So now you've made a table in Layout View.
| | 03:54 | Next, we'll add another
table using Access's Design View.
| | Collapse this transcript |
| Creating and editing tables in Design view| 00:00 | Access 2010's Table Design View is the
most powerful place to create your tables.
| | 00:05 | Click on the Create button
and then on Table Design.
| | 00:10 | This Design View has the most
detail for table construction.
| | 00:13 | In addition to all the features on the
Table Tools Field's Ribbons, it has many
| | 00:16 | more customizable field properties.
| | 00:19 | So we'll build a table from scratch here.
| | 00:20 | And in the next lesson, we'll look at
the tables we built to add in our field
| | 00:24 | properties, down here.
| | 00:26 | So our next table is our Products table.
| | 00:28 | Two Trees Olive Oil sells olive oil, both to
retail stores and wholesale to restaurants.
| | 00:33 | The fields we'll need to create are the
item SKU, the name, the size, our cost,
| | 00:39 | the retail price and our profit margin.
| | 00:42 | So this view is a list view.
| | 00:43 | I think of it like building your house.
| | 00:45 | Here's where we lay the foundation,
build the structure and hang the drywall.
| | 00:50 | So the first thing we need is our ID field.
| | 00:52 | Because this is a Product, let's type in SKU.
| | 00:56 | The SKU will be a combination of
type of oil and the bottle size.
| | 00:59 | So it will be text.
| | 01:01 | We can enter that in the Description field, too.
| | 01:03 | Now this Description is optional.
| | 01:07 | It doesn't show up anywhere but here.
| | 01:09 | But if you'll be working with others
on the database, or handing it off to
| | 01:12 | someone else in the future, the more
information you can give them, the better.
| | 01:16 | Hit the Tab key to go to the
next row. Type in "Product."
| | 01:20 | It will be Text. So tab across.
| | 01:23 | Next is Size, and Size is going to be a
Number. Because we may want to calculate
| | 01:30 | total volume, type Ounces
in the Description line.
| | 01:34 | Next is Price, and we'll change the
Data Type to Currency, then Cost, also
| | 01:44 | Currency, and then our Profit margin.
| | 01:48 | Now notice that I don't have
to pulldown the dropdown menu.
| | 01:51 | I can start typing the Data Type,
and it will autofill automatically.
| | 01:55 | And all I have to do is press Tab to accept it.
| | 01:58 | If you want to change the order of the
fields, for instance, you decide that
| | 02:01 | you'd like Cost to be before Price,
click on the gray box to the left of the
| | 02:04 | Field and then drag it up or down.
| | 02:07 | We'll drag it up above Price and let go.
| | 02:11 | Last, let's save the table.
| | 02:13 | Click on the Save button, and we'll
call the table "Products" and click OK.
| | 02:19 | Now you'll get an error message
asking you to set a primary key.
| | 02:22 | Now we're going to do that in our
next lesson, but if this was your real
| | 02:26 | database, this would be a good time to do it.
| | 02:29 | So for us, we're going to say No.
| | 02:31 | So now you've used Design
View to create a table quickly.
| | 02:35 | Later, we'll use it to set our Field
Properties, allowing us to control the
| | 02:38 | data that's entered in.
| | Collapse this transcript |
| Setting a primary key| 00:01 | A Primary Key is how Access knows that there
aren't any identical records in your Table.
| | 00:05 | Every record needs a unique identifier,
a way to distinguish it from other
| | 00:09 | potentially similar entries.
| | 00:11 | We're going to set the Primary
Keys in all three of our tables.
| | 00:14 | Let's start with our Customers.
| | 00:16 | Double-click on Customers.
| | 00:17 | Then click on the Design View button.
| | 00:20 | Because we created Customers using the
Application Parts, Access is assigned to the
| | 00:25 | Primary Key automatically.
| | 00:27 | So we can just go ahead and close the table.
| | 00:30 | Next, open up the Sales Reps table,
and let's do it a slightly different way.
| | 00:33 | This time right-click on
it and choose Design View.
| | 00:38 | Because we started this table in the
Layout View using Access's ID field, it also
| | 00:42 | already has a Primary Key.
| | 00:44 | But let's change the Field Name,
change ID and add "Emp" in front. "Emp" is
| | 00:51 | standing for employee.
| | 00:52 | Save and close the table.
| | 00:54 | But when we open up the Products table
in Design View, there is no Primary Key
| | 00:59 | because we built it from scratch.
| | 01:01 | Click on the SKU field, and then click this
Primary Key button up here. Now it has a key.
| | 01:08 | Save and Close this table.
| | 01:11 | Now that our primary keys are set,
Access will begin to recognize relationships
| | 01:15 | between our data and our tables.
| | 01:16 | And we'll see that come into play
throughout the rest of this course.
| | Collapse this transcript |
| Creating a lookup field| 00:01 | Lookup fields allow the database user
to choose data from a list instead of
| | 00:04 | typing in their own fields.
| | 00:06 | This both speeds up data
entry and reduces errors.
| | 00:09 | When using a Lookup field, you can
either reference data in another table or
| | 00:13 | type in your own list.
| | 00:14 | So let's create our fourth
table, one to take our orders.
| | 00:17 | Click on the Create tab and
then click on Table Design.
| | 00:22 | First, we need an OrderID,
essentially an invoice number.
| | 00:28 | Let's use the AutoNumber Data Type.
| | 00:32 | This will also be our unique identifier.
| | 00:34 | So click on the Primary Key button.
| | 00:37 | Next, we need the date of our order.
| | 00:40 | Enter in "OrderDate" and choose
Date/Time for the Field Type.
| | 00:47 | Next, we need to know who was placing the order.
| | 00:49 | We'll do a Lookup field to reference the
customer's ID, and as an added bonus use
| | 00:54 | the company name to help us know who was who.
| | 00:56 | So enter in Customer and, from the
Data Type list, choose Lookup Wizard.
| | 01:05 | In the dialog box, leave it on the
default so that we can get the data from
| | 01:09 | another table and click Next.
| | 01:12 | The Table we'll choose is
Customers. So click Next.
| | 01:18 | Now, which fields from the Customers
Table contain the values we want included
| | 01:22 | in the Lookup field?
| | 01:24 | So we want to use the CustID
as a reference, and also the Company
| | 01:28 | name, and click Next.
| | 01:30 | We'll want our options list sorted by Company.
| | 01:34 | So pick that from the list
and then leave it on ascending.
| | 01:40 | In the next window, it will
show us how our dropdown will look.
| | 01:43 | But because we have customers with more
than one branch, we'll turn off Hide key
| | 01:47 | column so that we can see both the ID
number and the Company name. Click Next.
| | 01:53 | On this screen, we have the choice of
which of the two fields we want to store.
| | 01:57 | We're going to go with
CustID. Click Next.
| | 02:01 | If you'd like, you can change the Field
Name to a label and make it say anything
| | 02:05 | you would like it to say.
| | 02:06 | We are going to leave it with Customer.
| | 02:08 | We are going to Enable Data
Integrity and tell it to Cascade Delete.
| | 02:12 | That way if we delete a customer
from our database, it will also delete
| | 02:16 | their associated orders.
| | 02:17 | Go ahead and click Finish.
| | 02:19 | It says, The table must be saved
before relationships can be created.
| | 02:23 | Go ahead and Save now? We'll say Yes.
| | 02:26 | And our Table Name is
going to be Orders. Click OK.
| | 02:31 | Okay, so now let's finished off our orders table.
| | 02:34 | Click back under the next Field Name and enter
in Quantity, and this is going to be a Number.
| | 02:40 | Don't forget that you can just
type the letter and it will auto fill.
| | 02:44 | Last is OrderStatus, again
with no spaces in between.
| | 02:48 | This is going to be another Lookup field.
| | 02:50 | So we'll start the same way, selecting
the Lookup Wizard, but this time we're
| | 02:55 | going to type in the values that we want.
| | 02:58 | So click that radio button and click Next.
| | 03:01 | Here is where we enter in our choices.
| | 03:04 | I'll click in the box, and our
OrderStatus has three options.
| | 03:08 | The first is that the order has been processed,
| | 03:11 | the second is that it's been backordered,
and our third, OrderStatus is complete.
| | 03:18 | Then click Next again.
| | 03:20 | If I want to change the label, I could.
| | 03:22 | We're going to leave it with OrderStatus.
| | 03:25 | Now if we want the user to only be able
to use our three choices, we want them
| | 03:29 | to limit it to the list.
| | 03:31 | So we'll put a check mark in this box.
| | 03:34 | Otherwise, the user can also
enter in additional values.
| | 03:38 | We don't want to allow multiple values,
because the order can only have one status.
| | 03:43 | So I'll click Finish.
| | 03:46 | Again, save the table.
| | 03:47 | Now if you get this error
message, just go ahead and click Yes.
| | 03:52 | By using Lookup fields, you can pull
data from other tables or restrict the
| | 03:56 | choices the user can enter into a field.
| | Collapse this transcript |
| Creating multi-value fields| 00:00 | When using Lookup fields, you can also
make them multi value so that the user
| | 00:04 | can pick several options off of the list.
| | 00:07 | Let's add a Multi Value
Lookup field to the Customer table.
| | 00:10 | Right-click on Customers and choose Design View.
| | 00:13 | Scroll down and click on the gray box
next to WebPage and then choose Insert Row
| | 00:19 | to add a row above it.
| | 00:20 | In the Field Name, enter Type.
| | 00:22 | Now TwoTrees Customers fall into two
categories: Retail stores and Restaurants.
| | 00:28 | In a few cases, our customers both buy
our olive oils as food ingredients and
| | 00:32 | sell the bottles out in front.
| | 00:33 | We're going to make a field with a
dropdown, allowing the user to pick one,
| | 00:37 | the other, or both.
| | 00:38 | Click on the Data Type and choose Lookup Wizard.
| | 00:42 | Click on "I will type in the
fields that I want" and click Next.
| | 00:47 | Here's where we enter the values for the list.
| | 00:49 | The first will be Retail, and the
second will be Restaurant. Click Next.
| | 00:56 | If you wanted to change the label, you could.
| | 00:58 | If you want the user to only be able
to select from these two choices, put a
| | 01:02 | check mark in this Limit To List box.
| | 01:04 | Otherwise, the user can also
enter in additional values.
| | 01:07 | By checking off Allow Multiple Values, we
can now pick as many as we want from the list.
| | 01:12 | Click Finish, and let's take a
look at our field properties.
| | 01:16 | Click on this Lookup tab, right down here.
| | 01:18 | Combo Box is the default view.
| | 01:22 | If I choose this dropdown, I could
also change it to a Text Box so that they
| | 01:26 | would have to type in their values, or a List
Box, which would show them all at one time.
| | 01:31 | The Combo Box creates a dropdown.
| | 01:33 | Value List means that you've typed it
in yourself, as opposed to gathering the
| | 01:37 | fields from another table.
| | 01:38 | The Row Source are the options.
| | 01:41 | You could add as many as you wanted by
hand here, by putting in another semicolon
| | 01:44 | and putting in your options in quotation marks.
| | 01:47 | Now let's see how that looks in our table.
| | 01:50 | Click on the Datasheet View, and it
will ask us to save our table. Say Yes.
| | 01:55 | Scroll over to the right, until you
see Type, and click on the first row for
| | 02:01 | Gina's pizza and drop it down.
| | 02:03 | We see Retail and Restaurant.
| | 02:05 | We can put a check mark in
any one, or both. Click OK.
| | 02:10 | Now Gina's pizza is noted as a Restaurant.
| | 02:13 | Multi Value Lookup fields give you
extra flexibility when a field can
| | 02:17 | have multiple entries.
| | Collapse this transcript |
| Using calculated fields| 00:00 | You can create a field right in your
table that will perform calculations
| | 00:04 | based on your data.
| | 00:05 | Right-click on our Product's
table and open it in Design View.
| | 00:09 | We're going to create a Profit field and
on the Data Type list choose Calculated.
| | 00:16 | It opens up an Expression Builder. Type
in equal sign and then double-click on
| | 00:21 | Price, a Minus sign and
then double-click on Cost.
| | 00:26 | Notice that both the fields
came up in square brackets.
| | 00:29 | That's the notation Access uses in its
backend programming to signify a Field Name.
| | 00:33 | Click OK. When we start entering Products in our
Table, all we'll have to do is enter in
| | 00:39 | the Cost and the Price,
and Access will do the rest.
| | 00:42 | We'll test this in a
later chapter of this course.
| | 00:45 | And not only can you have the field
Auto Calculate, but you can easily run
| | 00:48 | queries and reports using this
data, without having to program
| | 00:51 | calculations there as well.
| | Collapse this transcript |
|
|
3. Setting Up the DataSetting field properties| 00:01 | Once you have your fields set up, good
database designs suggests that you set
| | 00:05 | field properties to control what
data can be entered into the fields.
| | 00:08 | The more specific you are the
fewer data entry errors you have.
| | 00:12 | You can set the most common field
properties in Datasheet View, and all of
| | 00:16 | them within Design View.
| | 00:18 | Let's start with out Customers Table.
| | 00:20 | Double-click on it and then come up
to Fields Ribbon and click on that.
| | 00:24 | First, we have our Company name field.
| | 00:27 | Let's come over here and make
it Required by checking this box.
| | 00:31 | That means that you have
to enter something into it.
| | 00:33 | You can't leave it blank.
| | 00:35 | Next let's look at the Field Size.
| | 00:37 | Access reserves this number of spaces
for every field, even if you don't use it.
| | 00:42 | Limiting the Field Size to the actual
number of characters needed will help your
| | 00:45 | database file size stay small.
| | 00:48 | Field Size is 255 characters by default,
which, for this field, is way to big.
| | 00:53 | Let's edit that down to 50 characters,
but be sure not to make this Field Size
| | 00:58 | too small, or you'll cut off your information.
| | 01:00 | I'll click back on Company to accept the value.
| | 01:04 | I'll get an error message
that some data may be lost.
| | 01:07 | "The setting for the Field Size
property of one or more fields has been
| | 01:10 | changed to a shorter size.
| | 01:11 | If data is lost, validation
rules may be violated as a result.
| | 01:15 | Do I want to continue anyway?"
| | 01:17 | Now since I haven't actually entered
much data into this database there is not
| | 01:20 | going to be a problem.
| | 01:21 | I'm just going to go ahead and click Yes.
| | 01:24 | So now let's take a look at
another field and its properties.
| | 01:27 | Scroll over to the right until you
find State/Province and click on it.
| | 01:34 | First, let's change its name and
caption. We don't need Province, and our reports
| | 01:38 | will look better if it just says State.
| | 01:40 | So click on the Name & Caption button.
| | 01:44 | Now notice that Name is StateProvince with
the words run together and no punctuation.
| | 01:50 | Field names cannot contain certain
punctuation marks that are used in
| | 01:53 | programming: periods, commas,
question marks, slashes and asterisks.
| | 01:58 | You also can't start along with a space.
| | 02:00 | This convention of using capital letters
for each word and running them together
| | 02:04 | is sometimes called Camel Caps.
| | 02:06 | A caption, on the other hand, can have
spaces and punctuation because it's not
| | 02:11 | used in behind-the-scenes programming.
| | 02:13 | It only shows up in your forms and reports.
| | 02:15 | So let's change both the Name and the
Caption to State and click OK. There it is.
| | 02:23 | Now let's set as Default Value.
| | 02:26 | Most of my customers are in New York, so
instead of having to type New York, New
| | 02:29 | York, New York every time, I
can have this field autofill.
| | 02:32 | To do that, click in it, and then on
this Default Value button. The equal sign
| | 02:38 | is used in Access coding to tell the program
that you are about to enter in a data value.
| | 02:43 | Type in a quotation mark.
| | 02:46 | This is used in Access coding to
indicate that you are entering letters. Type in
| | 02:50 | capital NY and another quotation mark.
| | 02:54 | When you click OK, you'll
see New York in the field.
| | 02:57 | If a customer is from a different
state, you can just write over the New York
| | 03:01 | without issue. This just saves some time.
| | 03:05 | Last, all states will have just two
characters, so change the Field Size to 2.
| | 03:09 | You'll get that same error
message, and yes, we do want to continue.
| | 03:18 | Close the table and if asked to save, say Yes.
| | 03:22 | So now let's open up the Sales Reps
Table to set a few more Field Properties,
| | 03:27 | and this time we'll use the Design View.
| | 03:29 | Double-click on Sales Reps,
and we'll use this button.
| | 03:33 | Now notice that there's a
top-half and a bottom-half.
| | 03:35 | The top-half will toggle back and forth
between Datasheet View and Design View.
| | 03:40 | The bottom-half will let you
choose them off of the list.
| | 03:44 | You can also toggle back and forth using
these buttons down here. Right now we're
| | 03:48 | in Design View, and I can toggle back
and forth with datasheet by using these.
| | 03:52 | Now click on the LastName field and
make it Required using this setting right
| | 03:59 | down here. Use the dropdown and click
Yes, and let's do the same thing for
| | 04:04 | FirstName, Required > Yes.
| | 04:09 | And then let's make the same changes to
State that we did before, so that you can
| | 04:13 | see how they look in this view.
| | 04:16 | Make the Field Size 2.
| | 04:18 | Change the Caption to State, and
while we're at it, let's change the Field
| | 04:24 | Name up here as well.
| | 04:27 | Make the Default Value "NY"
and notice when you click off, that will
| | 04:31 | add quotation marks by itself.
| | 04:34 | Another property we can set here is
to force the State to appear in capital
| | 04:37 | letters, no matter how it's typed in.
| | 04:40 | Click in Format and type a Greater Than
sign - that's a Shift+period - which will
| | 04:44 | force the characters to capitals.
| | 04:46 | If you want lowercase, use a less then sign.
| | 04:50 | I also want to point out the date
formats. Scroll down and click on StartDate
| | 04:58 | and then on the Format dropdown. You
can see different Date and Time formats.
| | 05:04 | Long Date gives us the day of
the week, the month, day and year.
| | 05:08 | Medium Date writes it out like
this and Short Date, like this.
| | 05:13 | Let's set the StartDate to Short Date,
and then let's do the same thing for End
| | 05:17 | Date, Format, drop down, Short Date.
| | 05:23 | Now lets close and Save the Sales Reps Table,
and now let's open up the Products Table.
| | 05:30 | Another way to get directly into
Design View is to right-click on the Object
| | 05:34 | name and then choose Design
View off the shortcut list.
| | 05:37 | Let's start with the SKU.
| | 05:39 | None of our SKU's are more than six
characters, so let's change the Fields Size
| | 05:43 | here to 6, skip down to Required and make it
Yes, and also make sure that Indexed says Yes.
| | 05:52 | This will cause Access to process the SKU
field so that searches happen more quickly.
| | 05:58 | Now click on the Size Field. Numbers
have several different Size options.
| | 06:04 | Click on Field Size and drop it down. Long
Integer is the most common for whole numbers.
| | 06:10 | Single is the most common
for numbers with decimals.
| | 06:13 | We want our size to be an Integer.
| | 06:17 | Now Save and Close the Products table, and
let's set our last properties in the Orders table.
| | 06:24 | Double-click on Orders, and this time
right-click on the Orders tab to toggle
| | 06:30 | over to Design View.
| | 06:33 | Let's work with the Order Date.
| | 06:35 | First, make the Format Short.
| | 06:39 | Now, let's set it up so the default date
is today's date. Click on Default Value,
| | 06:44 | and then on the far right use this
little builder button, type an equal sign, and
| | 06:50 | then double-click on Functions, and
then double-click on Built-In-Functions.
| | 06:56 | Click on Date and then scroll down and find Now.
| | 07:01 | And it will put it up in the
top with parentheses, and click OK.
| | 07:07 | Now, when I switch over to Datasheet View,
I'll Save it, and it will put today's
| | 07:14 | date in the Order Date field.
| | 07:17 | We'll test all these Field properties
at length in Chapter 4 of this course.
| | 07:23 | By taking the time to lock down your
Field properties, you'll be sure that your
| | 07:26 | data entry will be as clean as possible.
| | 07:28 | After all, a database is only as
good as the data entered into it.
| | Collapse this transcript |
| Setting input masks| 00:01 | Input masks are a field property that
automatically formats phone numbers,
| | 00:04 | Social Security numbers and other
data so that all you have to do is enter
| | 00:08 | the numbers themselves.
| | 00:10 | Let's go to our Sales Rep
Table and open it in Design View.
| | 00:13 | Click on the HomePhone and then
on the Input Mask line down here,
| | 00:21 | click on the Build
button on the right-hand side.
| | 00:23 | Now Phone Number is already highlighted.
| | 00:27 | Click down here to Try It.
| | 00:29 | You'll notice though that you have to
click in the very beginning of the box.
| | 00:32 | Otherwise, your cursor will land
somewhere within the middle of the Input Mask.
| | 00:38 | Try typing just the ten digits
of the Phone Number. Click Next.
| | 00:43 | If you want to change the underscore
to a different character, do so here.
| | 00:46 | We'll leave it as an underscore. Click Next.
| | 00:52 | Now click the top option, so that
the symbols get saved with the Mask.
| | 00:56 | Otherwise, when you include the
Phone Number in Reports, you won't see the
| | 00:59 | parentheses, spaces and punctuation.
| | 01:02 | Click Finish. Now repeat the same
steps for CellPhone, Input Mask > Build
| | 01:09 | button. Go ahead and save the table.
| | 01:11 | We're on Phone Number. Click Next.
| | 01:16 | Click Next again.
| | 01:18 | Choose the top option and Finish.
| | 01:23 | Now click over to the Datasheet View.
The table asks you to Save, please do, and
| | 01:30 | scroll over to the Phone Numbers.
| | 01:33 | Click in HomePhone and type 3104561234,
| | 01:40 | just the numbers without any
characters, and when you press Tab you'll see the
| | 01:44 | phone number properly formatted.
| | 01:47 | We'll test all of these Input
Masks in Chapter 4 of this course.
| | 01:50 | So to save time, when you're entering
numbers that include punctuation from Phone
| | 01:55 | Numbers to Dates, create an Input Mask
so that you only need to type the digits
| | 01:59 | and let Access to care of the rest.
| | Collapse this transcript |
| Setting validation rules| 00:00 | Validation Rules check your data for
accuracy according to your company's
| | 00:04 | standards and the needs of your database.
| | 00:07 | There are two kinds of Validation Rules:
| | 00:09 | Field Validation, ensuring that
the correct data is entered, and
| | 00:12 | Record Validation, ensuring that
multiple fields in the record work
| | 00:16 | together harmoniously.
| | 00:18 | Let's open up the Orders table
and click in the Quantity field.
| | 00:23 | Go up to Table Tools and the Fields tab,
and then over here on the far right is
| | 00:28 | the Validation button.
| | 00:29 | Choose Field Validation Rule.
| | 00:32 | The Expression Builder opens.
| | 00:34 | Type >0 AND <72 and click OK.
| | 00:43 | That means a quantity has to be
entered, and it has to be less than 72.
| | 00:48 | But now that we've restricted what
numbers can be entered into the field, it's
| | 00:52 | important to let your users
know what they can and can't enter.
| | 00:55 | There's nothing more frustrating than
not being allowed to enter what you are
| | 00:58 | trying to type, and not
knowing what you can type.
| | 01:01 | So click on the Validation button
again and choose Field Validation Message.
| | 01:08 | In real English, tell you
user their allowed values.
| | 01:12 | "Quantity must be greater
than 0 and less than 72."
| | 01:21 | Click OK, and then close your
Orders table and Save it, if needed.
| | 01:27 | Now open up the Products table, and
let's do the same thing in Design View.
| | 01:33 | Click on the Size Field and
come down here to Validation Rule.
| | 01:38 | Here is where we are going
to list our bottle sizes.
| | 01:41 | We'll type =8 or =16 or =32 or =64 or =128.
| | 01:58 | It's important that your Equal
signs, spacing, and ors are all right.
| | 02:03 | This works exactly the same with Text values.
| | 02:06 | Access will automatically add
quotation marks around the words.
| | 02:09 | In the Validation box right below,
enter "Bottle ounces must be 8, 16, 32, 64
| | 02:22 | or 128."
| | 02:26 | Go ahead and Close your
Products table, and Save it if it asks.
| | 02:31 | Next, let's enter in a Record Validation.
| | 02:34 | This allows you to compare two field
values and bring up an error message if the
| | 02:38 | combination doesn't work.
| | 02:39 | For example, our Sales Reps
termination date must be after their start date.
| | 02:45 | Open up the Sales Reps table in Datasheet View.
| | 02:49 | Go back to the Fields Ribbon, click
on Validation, and come down to the
| | 02:54 | Record Validation Rule.
| | 02:57 | In the Expression Builder, double-
click on EndDate - you might have to scroll
| | 03:02 | down - and type a greater than sign,
which for dates means after and
| | 03:07 | double-click on StartDate.
| | 03:09 | Notice that Access put square brackets
around the field names for you. Click OK.
| | 03:15 | Now, Save and Close your Sales Reps table.
| | 03:17 | We will test all these Validation
Rules in Chapter 4 of this course.
| | 03:22 | By setting Validation Rules,
you'll greatly reduce human error during
| | 03:26 | data entry.
| | Collapse this transcript |
|
|
4. Building Structure Creating relationships and enforcing referential integrity| 00:01 | Access 2010 is considered a relational
database, meaning that the data is split
| | 00:05 | into tables that are interconnected.
| | 00:07 | In this lesson, we'll create
relationships between our four tables and set the
| | 00:11 | referential integrity.
| | 00:13 | Start by clicking on the Database Tools tab,
| | 00:15 | then on the Relationships button.
Click on the Show Tables button.
| | 00:21 | Then double-click each of the four tables.
| | 00:23 | Now, I have a tendency to click on Add,
thinking I am going to close the window.
| | 00:28 | So, if you do this, it will
accidentally add in a repeat of that same table.
| | 00:33 | You can see the _1.
Go ahead and close the Show Table button.
| | 00:38 | Click on that extra table and hit the Delete
button on your keyboard and it will disappear.
| | 00:43 | Now, hold your cursor over the bottom
edge of the Customers table until you get
| | 00:47 | a double-headed arrow.
| | 00:48 | Then click and drag them down so that
you can see the rest of your fields, and
| | 00:51 | do the same thing on the SalesReps table.
| | 00:54 | So, let's start with our Customers table.
| | 00:57 | One of the fields is SalesRep, and it
gets that info from the SalesReps table.
| | 01:02 | So, let's drag the primary key field,
EmpID, from the SalesReps table and drop
| | 01:08 | it on SalesReps here in the Customers table.
| | 01:13 | It brings up an Edit
Relationships window showing the two:
| | 01:16 | the source field and the destination field.
| | 01:19 | We want to click in the check box
to Enforce Referential Integrity.
| | 01:23 | Then check both Cascade Update
and Cascade Delete Related Records.
| | 01:28 | Cascade Update means if we make any
changes to the EmpID field, it will make the
| | 01:33 | same changes in any other
tables that use that field.
| | 01:36 | In our case, if we change a SalesRep's
ID, it would make the same changes in
| | 01:40 | the Customer table.
| | 01:42 | If we didn't check this, we could
potentially find SalesReps in the Customer
| | 01:45 | table that no longer existed.
| | 01:47 | Those are called orphaned records.
| | 01:49 | We also have a Join Type to set.
| | 01:52 | Click this button here.
| | 01:53 | There are three states:
| | 01:55 | Only include rows where the joined
fields from both tables are equal, Include
| | 01:59 | All records from SalesReps and only
those records from Customers where the
| | 02:02 | joined fields are equal, Include All
records from Customers and only those
| | 02:07 | records from SalesReps where
the joined fields are equal.
| | 02:10 | Now, most of the time we use the first
one, but in cases where we would want to
| | 02:15 | see reports that included the SalesReps,
even if they didn't have any assigned
| | 02:19 | Customers, we would choose the second option.
| | 02:22 | If we wanted to run reports that
included all of our Customers, even if they
| | 02:26 | didn't have any SalesReps,
we'd choose that option.
| | 02:29 | Let's go ahead and choose
option number two and then click OK.
| | 02:32 | Now, notice down here the One-To-Many
designation, indicating that there can
| | 02:37 | only be one of each rep, and that each rep
can have many customers. Then click Create.
| | 02:42 | You'll now see a black line
running between the SalesReps table and
| | 02:47 | the Customers table.
| | 02:48 | There is 1 right here, indicating the
one part of the relationship, and there
| | 02:53 | is an infinity symbol right here, indicating
the many customers that each rep might have.
| | 02:58 | Notice that line goes under the Products table.
| | 03:02 | You can drag the tables and
move them around to your liking.
| | 03:05 | In fact, let's move SalesReps over to
the left and move all of these tables over.
| | 03:12 | Next, the Customers all place orders.
| | 03:15 | So, let's dragged the CustID field over
to the Customer field in the Orders table.
| | 03:21 | Enforce Referential Integrity and
Cascade Update and Delete so that if we delete
| | 03:27 | a customer from the database, all
of their orders will disappear too.
| | 03:31 | We don't need to adjust the Join Type.
| | 03:33 | We won't ever have customers that
haven't place an order, and we won't have
| | 03:36 | orders without customers.
| | 03:38 | Click Create, and you'll see
the One-To-Many relationship.
| | 03:42 | There is no arrow here, the way there
is here, because we did a normal Join
| | 03:46 | Type instead of a Type2.
| | 03:48 | Now, let's do the same for
the SKU in the Product table.
| | 03:53 | Pick it up and drag it to the Product
in the Orders table, Enforce Referential
| | 03:58 | Integrity, but only the Cascade Update.
| | 04:01 | We don't want a Cascade Delete, because
if we discontinue our product, we don't
| | 04:05 | want that product's SKU to
disappear out of an order.
| | 04:08 | We also don't need to change the Join Type.
| | 04:11 | Click Create to finish.
| | 04:13 | Now, click the Close button and Access
will ask to save the changes. Say Yes.
| | 04:20 | Now, all of our tables are linked
together and Access understands which pieces
| | 04:24 | of data interrelate.
| | 04:25 | Relationships will also allow us to
bring data together from the different
| | 04:28 | tables into our forms and reports.
| | Collapse this transcript |
| Viewing subdatasheets| 00:00 | One of the bonuses, now that we've
created relationships, is that we can use
| | 00:04 | SubDatasheets within our tables.
| | 00:07 | Double-click on your SalesReps table to open it.
| | 00:09 | Now, we've taken the time to populate
our database so that you don't have to.
| | 00:14 | Notice these Plus signs to the left of our data.
| | 00:17 | Click on the Plus sign next to Jordan
Hinton, and you'll see all of his customers.
| | 00:22 | Then click on Plus sign next to Blue
Vine, and you can see all of their orders.
| | 00:28 | Click on the Minus sign
again to fold them back up.
| | 00:31 | If you open up the Orders table, you
won't see any Plus signs, because it's
| | 00:36 | always the Many table in
our database, never the One.
| | 00:41 | So, using these SubDatasheets gives us
a convenient, quick glimpse into how all
| | 00:45 | of our data interrelates.
| | Collapse this transcript |
|
|
5. Formatting and AppearanceEntering data into your tables| 00:00 | Once you've created the structure of your
database, it's time to enter data into your tables.
| | 00:05 | In chapters 2 and 3, we built four
tables and set field properties to
| | 00:09 | constrain the data.
| | 00:10 | Let's enter a few records into our
tables and test out our parameters.
| | 00:14 | In this lesson, we are going to refer
back to topics from chapters 2 and 3, if
| | 00:18 | you need to review those concepts.
| | 00:20 | Let's start with our SalesReps.
| | 00:23 | Double-click on the table to open it.
| | 00:25 | EmpID is an auto number so press Tab to
skip over it, and it will become a 1 as
| | 00:29 | soon as we start typing our last name, Pollock.
| | 00:33 | Now, we set both first and
last names to be required.
| | 00:36 | So, let's test this by skipping the
First Name so you can see what happens.
| | 00:41 | Type in the address: 123 Main Street and Ithaca.
| | 00:46 | It already says New York.
| | 00:48 | So, I can skip right over that.
| | 00:51 | The Zip code is 14850.
| | 00:55 | For HomePhone, type in (123) 456-7890.
| | 00:59 | You'll notice, as soon as I start
typing the numbers, that the parentheses,
| | 01:02 | spaces and dashes appear automatically.
| | 01:05 | The CellPhone is (123) 456-7899.
For Email address, type in alicia@lynda.com, and
| | 01:15 | notice that it's already blue
and underlined. It's a hyperlink.
| | 01:19 | If I clicked on it, it would open
up Outlook and address an e-mail
| | 01:22 | automatically for me.
| | 01:24 | For the Start Date, we can either click on
the calendar and use it, or we can type it in.
| | 01:29 | I am going to click on the calender.
| | 01:30 | I'll go back a couple of
months and choose March 1.
| | 01:34 | Now, this employee is still active.
| | 01:36 | But let's test out the record validation
rule we created that specified that the
| | 01:40 | End Date had to be after the Start Date.
| | 01:43 | Enter 2/28/10 and hit Tab.
For Salary, type in 12.
| | 01:49 | When we hit Tab, it will put in
the Dollar sign and the decimals.
| | 01:54 | Now for the Attachment field, let's
put it in the picture of our employee.
| | 01:58 | Right-click on the field
and choose Manage Attachments.
| | 02:03 | Click the Add button and navigate to
your Exercise Files folder and the picture
| | 02:11 | for apollock and click OK.
| | 02:15 | The 0 will become 1, and you can
have several attachments here as well.
| | 02:19 | Now, hit Tab again, and we
are going to skip the Flag.
| | 02:23 | Hit Tab, and it would start a new record,
| | 02:25 | but we got an error message that
says, You must enter a value in the
| | 02:29 | 'SalesReps.FirstName' field.
| | 02:31 | So, let's go ahead and click OK and
go back to the field that we skipped.
| | 02:37 | Now here's a troubleshooting tip for you.
| | 02:39 | If you're ever working with a
table and you get that error message
| | 02:42 | unexpectedly, it's because your cursor is in a
blank record and you triggered a required property.
| | 02:48 | To solve the problem, dismiss the
error message, then press the Escape key to
| | 02:52 | get out of it and delete
the extra unwanted record.
| | 02:57 | Now, if I come down here to start
another record, I get a warning that my
| | 03:00 | Start Date and End Date
validation rules are being tripped.
| | 03:04 | Click OK, go back to End Date and delete it.
| | 03:09 | Then Tab through to start
another record. Congratulations!
| | 03:13 | You've added your first employee.
| | 03:15 | Now, it's important to notice that all
the changes you make to the data in the
| | 03:19 | tables get saved automatically.
| | 03:22 | Well, changes that we make to the
structure of Access objects do need to get saved.
| | 03:26 | Let's close our SalesReps
table and make our first customer.
| | 03:32 | Double-click on the Customers table to
open it, and we'll enter a few customers.
| | 03:36 | Our ID field is two initials from the
company name followed by the ZIP code.
| | 03:41 | This allows us to distinguish between
different branches of the same chain.
| | 03:45 | Type in "GP" and then "14850."
| | 03:50 | Our Company name is Gino's Pizza.
| | 03:55 | The Address is 714, First Avenue.
There is no Address2 for now.
| | 04:01 | The City is Ithaca.
| | 04:03 | It already says New York,
and the Zip code is 14850.
| | 04:08 | It already has the Country filled in,
USA, because we might have customers
| | 04:11 | in other countries.
| | 04:13 | For the Work Phone, type
in (123) 789-1234, no Fax.
| | 04:21 | Now, let's scroll over so we have little bit
of room to look at the rest of our variables.
| | 04:27 | Now, for Type, click on
the on the dropdown arrow.
| | 04:30 | We created this as a multi-value field.
| | 04:32 | So, we can click on one or both of the options.
| | 04:35 | Now, Gino's Pizza is a restaurant.
| | 04:37 | So, I'll put a check mark in front of it
but not in front of Retail. Then click OK.
| | 04:42 | Next, click the dropdown for SalesRep.
| | 04:46 | You'll see the first and last names pulled
off of the Employee table using a lookup field.
| | 04:50 | Now, currently the actual information
stored in the field is the EmpID number.
| | 04:55 | But this field is set to display our
rep's last name to make it easier for
| | 04:58 | people using the database.
| | 05:01 | Let's go over to our Web
Page, www.ginospizza.com.
| | 05:10 | The Contact is Morelli, Gino, and his
e-mail address is gino@ginospizza.com.
| | 05:19 | He doesn't have a DirectPhone.
| | 05:23 | Now, in the Notes field, we can create
any notes that help us with our customer.
| | 05:27 | It has up to 65,000 characters,
so you've got plenty of room.
| | 05:31 | You can also use Rich Text Formatting,
meaning bold, italics and the like.
| | 05:35 | Type in "Restaurant has been in
the family for three generations."
| | 05:45 | Now, let's add an attachment - in
this case, a Resellers Contract.
| | 05:49 | Right-click on Documentation >
Manage Attachments > click Add.
| | 05:55 | In your Exercise Files, you'll see a Reseller
Contract. Go ahead and choose it and click OK.
| | 06:03 | And as before, we'll leave the Flag unchecked.
| | 06:07 | Press Tab again to create a new record.
| | 06:09 | This is another one for Gino's Pizza, but for
their East Ithaca branch. Type in GP14855.
| | 06:18 | Now, I don't have to keep typing in the
same information over and over again.
| | 06:22 | If I hold down the Ctrl key and then
type a quotation mark, it will copy the
| | 06:27 | information directly above it.
| | 06:29 | Let's go ahead and finish this record.
| | 06:31 | Their Address is 9 Penny Lane, East
Ithaca, still NY, 14855, and their phone
| | 06:44 | number is (123) 789-4321.
| | 06:50 | Now, they are also a Restaurant.
| | 06:54 | Their SalesRep is also Pollack.
| | 06:57 | Their Web site is the same.
| | 06:58 | So, I'll do the same
Ctrl+quotation mark to copy it.
| | 07:04 | The Contact's last name is the same,
but this is his brother Vince.
| | 07:09 | And it's vince@ginospizza.com.
| | 07:15 | "Second branch of family restaurant," and
then right-click to add their contract,
| | 07:26 | and there you go, two customers.
| | 07:29 | We'll close the table, and again, it won't
ask us to save because Access saves all
| | 07:33 | changes to the data automatically.
| | 07:38 | Now, let's open up the Product
table and put in our first product.
| | 07:43 | Type in "pur08" in the SKU and notice
that it turns to all caps because we used
| | 07:50 | greater than in the format.
| | 07:52 | The Product is pure. The Size is 8,
The Price is 8.99. The Cost is 6.
| | 08:02 | Now, notice that the Price change
the currency, and the cost will too.
| | 08:08 | Then the Profit will fill in automatically
because we made a calculated field for it.
| | 08:13 | Press Tab to start another
record, PUR16. Also pure.
| | 08:19 | This time let's put in the size that's bad.
| | 08:21 | Let's put in a 12, and we'll get an
alert telling us the Bottle ounces must be
| | 08:26 | 8, 16, 32, 64 or 128, the
validation rule we created.
| | 08:31 | Then go ahead and change that size to
16. The Price is 10.99, the Cost is $8,
| | 08:39 | and our Profit is also 2.99.
| | 08:41 | Now, we also need one bulk oil to start with.
| | 08:44 | So, enter in EV128, Extra Virgin olive
oil. The Size is 128. The Price is 49.99.
| | 08:55 | The Cost is 34.99, with a $15 profit.
| | 09:00 | Press Tab one more time to create
the record and then Close the table.
| | 09:06 | Now, let's put it in order.
| | 09:08 | Double-click on the Orders table.
| | 09:11 | The order number and the
date will fill in automatically,
| | 09:14 | although I can change the data if I need to.
| | 09:17 | Click on Customer and choose the first Gino's.
| | 09:22 | Click on Product and choose
EV128 and enter in Quantity 1.
| | 09:30 | If they want more than one item, you
would simply repeat these steps for
| | 09:33 | each additional item.
| | 09:35 | Since we are done, Close
the table. Congratulations!
| | 09:39 | You've entered data in all of your
tables and successfully tested all of
| | 09:42 | your field properties.
| | Collapse this transcript |
| Formatting tables| 00:01 | You can modify your tables and the
appearance of your data to make them
| | 00:04 | easier to read and use.
| | 00:06 | Let's double-click on the
Customers table to open it.
| | 00:09 | The first thing we can do is
change the appearance of our Fonts.
| | 00:13 | We want to be on the Home Ribbon and looking
over on the right-hand side at Text Formatting.
| | 00:18 | Let's change the font from
Calibri to Bookman Old Style.
| | 00:21 | Now any changes you make up here are
going to affect the whole entire table.
| | 00:27 | We can turn on Bold or Italic.
| | 00:30 | Let's go ahead and turn those off again.
| | 00:32 | When we choose Left, Center or Right though,
| | 00:37 | notice it's only affecting
the one field that were on.
| | 00:41 | Now we can also change the Gridlines. Those
are the lines between the fields and the rows.
| | 00:48 | Right now, they are light gray.
| | 00:49 | If I click on Gridlines, I can make
them Horizontal only, or Vertical only, or
| | 00:58 | take them off altogether.
| | 01:00 | This effect is fairly subtle when you
are looking at it onscreen, but you can
| | 01:04 | see it easily on printouts.
| | 01:05 | We are going to go ahead
and put it back to Both.
| | 01:10 | This button here allows you
to shade the alternating rows.
| | 01:13 | This helps when you have a lot of records.
| | 01:16 | Right now, it's on this color, but
let's go over here to Olive Green, Accent 3,
| | 01:20 | Lighter 80%, and makes them a Lighter Green.
| | 01:23 | I recommend light colors, or it will
obscure the text on your printouts.
| | 01:28 | Now there's a Spell Checker, right
here, but if your data holds a lot of names,
| | 01:33 | like ours does, it will likely find many,
many words that are actually spelled correctly.
| | 01:38 | So we are going to cancel it.
| | 01:40 | Now let's resize our
columns to best fit our data.
| | 01:44 | Notice that the addresses are cut off,
and Country has way too much room.
| | 01:49 | Hold your cursor over the line between
Address and Address 2, and when you're
| | 01:52 | right on the line between the fields,
you will get a double-headed arrow.
| | 01:57 | Click on it and drag larger or smaller
to resize it, or you can double-click and
| | 02:05 | it will make it exactly as
big as the largest data value.
| | 02:10 | Go ahead and do this for all of your fields.
| | 02:14 | You can also click on a Field, for
instance Notes and then click on the
| | 02:18 | More button, and here you have options to
specify your row height, or your field width.
| | 02:25 | Click on Field Width, and let's
make the Notes exactly 60 and click OK.
| | 02:32 | If you'd like to hide a column so it
doesn't show, for example, maybe you don't
| | 02:36 | want to always look at their Web page,
you can click on the More button and
| | 02:40 | choose Hide Fields, and it will disappear.
| | 02:44 | When you want it back again, click on
the More button again. Choose Unhide Fields,
| | 02:49 | and it will actually give you a
checklist with all of the possibilities for what
| | 02:53 | you want to show, and we'll
turn on Webpage and click OK.
| | 02:59 | Now we want to save all the
changes we made to the Customers table.
| | 03:02 | Click on the Save button right here.
| | 03:04 | Now as a reminder, any changes to the
structure of your objects, tables, forms,
| | 03:09 | or reports do need to be saved,
although changes to the data do not.
| | 03:14 | Now let's open up the Orders
table for one more technique.
| | 03:18 | Click on this Totals button up here, and
it will add a Total row down at the bottom.
| | 03:23 | Click in the Quantity, and
there's a dropdown arrow.
| | 03:27 | I can have it Sum up what is sold, or
find the Average number of bottles in an
| | 03:33 | order, or Count how many orders we have,
| | 03:38 | find the maximum that anybody has
ordered, find the minimum that anybody has
| | 03:43 | ordered and also work with a
Standard Deviation and the Variance.
| | 03:49 | We are going to ahead leave that
on sum and then save the table.
| | 03:55 | Modifying your table's appearance
can make your tables more attractive
| | 03:59 | and useful.
| | Collapse this transcript |
| Finding, sorting, and filtering records| 00:00 | Access makes it easy to search your
tables for exactly the data that you're
| | 00:04 | looking for using three techniques:
finding, sorting, and filtering.
| | 00:09 | Double-click on your SalesReps table to open it.
| | 00:12 | Now, first, let's look at how
to navigate around on our data.
| | 00:16 | At the bottom, down here is the navigator.
| | 00:19 | You can see what record
you're on as you click around.
| | 00:22 | Now remember, if you are using an
AutoNumber field, that AutoNumber is not a counter.
| | 00:28 | When you delete a record, these numbers
don't change, but the total down here will.
| | 00:34 | Now there are Previous and Next
arrows to scroll through your data.
| | 00:38 | You can go back to your first
record or to your last record.
| | 00:42 | This arrow with a spark right here
will take you to the bottom row to start a
| | 00:46 | new record, no matter where you are.
| | 00:48 | If you want to search for a specific
value, for example Hodge, you can start
| | 00:53 | typing in here, and it will get
highlighted in your data as you type.
| | 00:57 | Now, let's find all of our
SalesReps who make $8 an hour.
| | 01:01 | Scroll over to the right and
find an $8 and highlight it.
| | 01:06 | When I go up here to Find, because
I highlighted it, it will autofill.
| | 01:11 | I have choices here for looking in the
whole document, or just in this field.
| | 01:17 | It can either be just a part of
the field or the whole entire field.
| | 01:21 | Let me move this out of the way.
| | 01:22 | As I click Find Next, it will
scroll through and find them all.
| | 01:28 | It's finished searching the
records, so I'll click OK.
| | 01:31 | Now, let's say we want to
give all of our $8 reps a raise.
| | 01:36 | We can replace the 8 with 9.
| | 01:38 | I can either click on the Replace tab
right here, or I could also use this
| | 01:43 | Replace button here, if the window is not open.
| | 01:46 | In the Replace With field, type in $9,
and we can either replace them one at a
| | 01:53 | time or go ahead and replace them all at once.
| | 01:56 | Now notice that you cannot undo this
Replace operation, and we do want to continue.
| | 02:02 | Now, all of our $8 reps now make $9.
| | 02:06 | Go ahead and cancel this window.
| | 02:09 | Now, let's work with our
Customers table. Open up Customers.
| | 02:14 | Let's say that one of our customers is
no longer using our olive oils, and we
| | 02:17 | want to delete them from
our database. Go up to Find.
| | 02:22 | It's autofilled this Customer ID,
because that's what I was clicked on when we
| | 02:25 | invoked the Find command.
| | 02:27 | So let's change this to Uni and then
change it to the current document and whole
| | 02:33 | field, and click Find Next.
| | 02:35 | Then cancel the window.
| | 02:37 | It's highlighted Uni.
| | 02:38 | Click on the gray box to the left
of it, and up here, click Delete.
| | 02:43 | Now, because we have a relationship
between our customers and the orders they
| | 02:47 | place, Access notices that we're
going to do a cascading delete.
| | 02:51 | Not only are we going to delete this
one record, but we're also going to delete
| | 02:55 | all of Uni's related orders.
| | 02:58 | Do we want to delete them? Yes we do.
| | 03:01 | That change is permanent.
| | 03:03 | Now, let's do some sorting.
| | 03:05 | Click on the arrow field next to State.
| | 03:09 | It gives us the options to sort it from Z to
A, or from A to Z. Now, we can also filter.
| | 03:17 | If we just want to see certain
values, click on that dropdown again.
| | 03:20 | Turn off the Select All.
| | 03:22 | Let's just look for customers from California,
and scroll down, and Maryland. Click OK.
| | 03:30 | Now, we just see those customers.
| | 03:33 | Notice there is a filter here to
remind us that a filter is applied.
| | 03:37 | Drop it down again, and choose Select All,
then click OK, and everybody comes back again.
| | 03:44 | Click on Company, and now this
time let's do the sort from up here.
| | 03:49 | Click on this Ascending button.
| | 03:52 | Now, go over to the City
field and double-click on Abilene.
| | 03:56 | Click on this Selection button right
here, and it gives us the options of
| | 04:00 | finding everybody in Abilene,
everybody except in Abilene, or as appropriate,
| | 04:06 | containing or not containing Abilene.
| | 04:09 | We'll go ahead and say
Does Not Contain 'Abilene'.
| | 04:13 | Now notice that this Toggle
Filter button is now orange.
| | 04:16 | Click on it repeatedly to toggle
your last filter on and off again.
| | 04:22 | This big Filter button here has the same
choices as clicking on this arrow right here.
| | 04:28 | If you click on Text Filters, you'll
also see Begins With and Ends With.
| | 04:34 | Also notice this Clear filter from City,
which completely removes all of your
| | 04:38 | filters from memory, so that
you see all of your records.
| | 04:42 | Next, go up to Advanced,
and choose Filter By Form.
| | 04:49 | This blanks out your table, and now
you can fill in the blanks to find
| | 04:52 | what you're looking for.
| | 04:53 | This is perfect when you want to search for
records that match more than one criteria.
| | 04:58 | Go to State and choose Maryland.
| | 05:01 | Then tab over to Web Page.
| | 05:03 | We're going to find all the
companies who do have Web pages.
| | 05:08 | The Access programming used for blanks and
not blanks is either Is Null, or Is not Null.
| | 05:16 | Is not Null means that there
will be data in the Web page.
| | 05:19 | So, right now, we're looking for
companies from Maryland who do have Web pages.
| | 05:26 | Go ahead and toggle your Filter on.
| | 05:29 | We see two customers.
| | 05:31 | So, now we can see if they advertise
that they only use the finest ingredients,
| | 05:34 | including TwoTreesOliveOil.
| | 05:37 | Click the Advanced button
again and choose Clear All Filters.
| | 05:42 | The Advanced button also has an
option for an Advanced Filter/Sort, which
| | 05:46 | actually pulls up the Query Builder.
| | 05:49 | We'll explore these in detail in a
later chapter. Close this window.
| | 05:53 | These filtering techniques
also work when using Forms.
| | 05:57 | So, you can see that finding, sorting,
and filtering records gives you a quick
| | 06:02 | and powerful way to locate only
the records you're looking for.
| | Collapse this transcript |
|
|
6. FormsCreating data-entry forms| 00:00 | Now that we've looked at Access tables
extensively, it's time to move onto the
| | 00:04 | next type of object, Forms.
| | 00:06 | Forms show you all the data in your
tables, and they have two advantages.
| | 00:10 | First, they allow you look at just one
record at a time instead of everything
| | 00:14 | all at once, and second to make it
easy to enter data into your table.
| | 00:19 | The first type of Form we are going to
create is a Data Entry form for our SalesReps.
| | 00:23 | Click once on the SalesRep
table, then click on the Create tab.
| | 00:27 | Here aren't the different form options,
and we're going to click on the Form button.
| | 00:33 | Instantly, we have a data entry form.
| | 00:36 | Access automatically made a subtable of
the SalesReps customers, since we have a
| | 00:41 | relationship between the
SalesReps table and the Customers table.
| | 00:45 | As I navigate from one SalesRep to
another, the customers update down below.
| | 00:51 | Click on this datasheet button
right here so we can use the table.
| | 00:55 | Click on this button to go back to the
first record, and any changes I make to
| | 01:00 | the data here in the form will
be change in the table as well.
| | 01:04 | Let me add an O to Phoenix city, and
any changes I make to the data will be
| | 01:09 | change in the table as well.
| | 01:11 | I can create a NewSales rep by
clicking on this button right down here.
| | 01:15 | Let me go back again to the first record,
and I can add a customer to a SalesRep
| | 01:22 | anytime by simply clicking in the
blank line at the bottom of the sub-form.
| | 01:27 | Now close the form and Access
will ask if I want to save it.
| | 01:30 | Say Yes and change the name to Sales
Reps and Customers Form. Then click OK.
| | 01:42 | Now, another type of form is called a Split Form.
| | 01:45 | Click once on the Products table.
| | 01:47 | Click on the Create button and come down to
this More Forms button and choose Split Form.
| | 01:54 | Now we have an instant form that has
both a single view data entry area above
| | 01:59 | and a list of all the
records in the table below.
| | 02:02 | When I click on any of these records at the
bottom, that record appears in the form above.
| | 02:09 | Now let's save this form as a
Products Split Form and click OK.
| | 02:19 | Open up the Products table,
and you can see the difference.
| | 02:23 | Using a form to view and edit your
data is much more attractive and efficient
| | 02:28 | than doing the work in the table.
| | Collapse this transcript |
| Using the Form Wizard| 00:00 | Another way to make a data entry
form is by using the Form Wizard.
| | 00:04 | Click one time on the Customers
table, click on the Create tab, then on
| | 00:09 | this Form Wizard button.
| | 00:11 | You don't have to click on the table
first, but if you do, that table will show
| | 00:15 | up right here automatically.
| | 00:17 | On the left, you have a list
of all the fields in the table.
| | 00:20 | If you only want some of them, you can
click on them in the order you want and
| | 00:24 | click this arrow to move them
over to the right-hand side.
| | 00:27 | If you want all of them, like we do,
click on this double arrow, and they will
| | 00:31 | all move over at once.
| | 00:34 | Drop this down and change it to
Orders, and let's also bring over all of
| | 00:38 | the order information. Click Next.
| | 00:42 | Access automatically recognized the
relationship between the Customers and the
| | 00:46 | Orders and set them up in a sub-form.
| | 00:50 | Next, we are going to leave it on
the default the Datasheet. Click Next
| | 00:55 | again. Let's name the
field Customers Order Form.
| | 00:58 | We will leave the sub-form with the name
Orders:Subform so that when it's listed
| | 01:06 | over here in the forms, it
will be easy to identify.
| | 01:09 | Access now gives us a choice to open the
form, to use it, or to modify the design.
| | 01:15 | We are going to open it and click Finish.
| | 01:18 | So now we have a form with all the
customer information above and their orders
| | 01:23 | down below, although we will need to
do some resizing and realigning of the
| | 01:27 | fields, and we will learn how
to do that in future lessons.
| | 01:30 | Using the Form Wizard takes most of
the work out of creating a form, selecting the
| | 01:35 | fields, and laying them out for you.
| | Collapse this transcript |
| Modifying a form in Layout view| 00:00 | Layout View makes it easy and
intuitive to create and modify your forms.
| | 00:05 | Double-click on the Sales Reps and
Customers Form to open it, and drop down this
| | 00:10 | button to change it to Layout View.
| | 00:14 | When you click on the different words
in the boxes, they get an orange box
| | 00:17 | around them so that you can modify them.
| | 00:20 | These words are called
labels, or unbound controls.
| | 00:23 | The boxes that contain the data are
called bound controls, because they're bound,
| | 00:27 | or attached, to the source data in the table.
| | 00:31 | Let's start by choosing a theme.
| | 00:33 | Click on this button and go down to Foundry.
| | 00:37 | Themes change the colors
and the fonts in your object.
| | 00:40 | We'll click on Foundry,
and it turns our forms green.
| | 00:44 | It makes the font more interesting.
| | 00:46 | You can also come over to this Colors
dropdown and modify the color scheme.
| | 00:51 | If you want to create your own for use
across your entire database, click down
| | 00:55 | here on Create New Theme Colors.
| | 00:57 | For example, let's change the
Background from white to a very light green.
| | 01:05 | Change the Name down here
to Two Trees, and click Save.
| | 01:13 | Now we have a green background.
| | 01:15 | If you don't like it, you can open up
the Color dropdown again, and right-click
| | 01:19 | on your Custom color scheme and edit it,
and change the color to something else
| | 01:25 | instead, and then click Save again.
| | 01:29 | Click on this Plus sign in the corner of
the form to select the whole thing at once.
| | 01:33 | You can then change the font
scheme for the entire form.
| | 01:37 | We're going to go ahead and leave it on Foundry.
| | 01:39 | Now, Layout View is table-based,
| | 01:41 | so, the controls are all
organized into rows and columns.
| | 01:46 | You can resize the fields as a group,
by clicking on one of the cells, holding
| | 01:51 | the cursor over the side you
want to move, and dragging it.
| | 01:54 | For example, HomePhone is cut off, so
I'll click on it, get the double-headed
| | 02:00 | arrow, and make it a little wider.
| | 02:02 | I noticed that my State field is awfully big,
because it's in the same row as my picture.
| | 02:07 | If I rearrange the form, I can also
put CellPhone down here, below HomePhone.
| | 02:13 | So, click on the Arrange tab, then click
on HomePhone, and up here, insert a new
| | 02:19 | row below the HomePhone.
| | 02:22 | Click on the CellPhone label and hold your
Shift key to click the phone number as well.
| | 02:27 | Get a four-headed arrow, pick them up
and drag them down into this cell, and
| | 02:32 | drop, and they'll both move.
| | 02:35 | Resize the bottom to
make it a little bit taller.
| | 02:39 | Let's move these labels and controls up.
| | 02:42 | Click on Email, hold the Shift
key down, and click on the $11.00.
| | 02:47 | Because I held the Shift key,
it highlights all of those cells.
| | 02:51 | Use this Move Up button to
move them all up at once.
| | 02:56 | Click on Flag and Shift+Click on the
check box, use Move Up again, and it will
| | 03:00 | jump above Photo into the blank space.
| | 03:04 | Now, click on the photograph itself
and hold the Shift key down, and click on
| | 03:09 | the third row below the picture, so that
all four of these cells are highlighted,
| | 03:13 | and then click on Merge to turn them into one.
| | 03:17 | Also notice the Split Vertically and
Horizontally buttons, if you want to align
| | 03:21 | controls next to each other.
| | 03:24 | Now, click on the State field,
grab its bottom border and drag it up.
| | 03:29 | There, much better!
| | 03:30 | Now, if I want to see more of my
Customer table, I have a few different options.
| | 03:35 | I can drag my fields bigger and
smaller, or double-click on them.
| | 03:40 | I can also hide fields that I don't want to see.
| | 03:43 | If I right-click on Address, I can
come up here to Hide Fields, and I'll do
| | 03:47 | the same for Address2.
| | 03:50 | I also have the Scroll Bar, so I can
scroll over to the right-hand side.
| | 03:55 | I can also make the sub-form longer
by clicking on the right-hand edge
| | 04:00 | and dragging it longer.
| | 04:02 | If I want to change the alternating row
colors, come up here to Form tools for
| | 04:07 | the Datasheet, and it has
its own specific set of colors.
| | 04:11 | I'll click on Alternate Row Color, and
I'll change it from blue to green so that
| | 04:15 | it matches the above.
| | 04:17 | Next, let's make our labels bold.
| | 04:20 | I'll click on EmpID, the label right
here, and go to the Arrange tab, and I'm
| | 04:26 | going to select the column.
| | 04:28 | Then I'll go to the Format
tab, and choose B, for Bold.
| | 04:33 | Then I'll do the same
thing for this row right here.
| | 04:35 | I'll click on Email, click on Arrange, select
the column, go back to Format and make it bold.
| | 04:44 | If I don't like the appearance of the
boxes, I can change those as well, using
| | 04:49 | Shape Fill and Shape Outline.
| | 04:53 | I'll click on the Plus
sign to select the whole form.
| | 04:57 | Click on Shape Fill, and I'll choose a
slightly lighter green to fill the boxes.
| | 05:03 | If I want to change the outlines of
the boxes, I can change the color of the
| | 05:07 | outline here, and the thickness here.
| | 05:10 | I could also make it
dotted or dashed, if I'd like.
| | 05:16 | Next, I'm going to do some conditional
formatting to highlight some unusual values.
| | 05:20 | For example, maybe I would like all
of the salaries above $15 to turn red.
| | 05:25 | I'll click up on Conditional
Formatting and make a new rule.
| | 05:28 | I am going to use the values in the
current record, and I want the Field Value
| | 05:33 | to be greater than or equal to 15.
| | 05:38 | I'll make those bold and red.
| | 05:40 | Then I'll click OK.
| | 05:43 | If I want to set Conditional
Formatting Rules for more than one field, or if
| | 05:47 | I want to make multiple rules for one field,
I can do that right here in this window.
| | 05:52 | I'll click OK.
| | 05:53 | Now, nothing happened here for Jordan,
but if I go to the next Sales Rep, Lilah,
| | 05:58 | she makes $31 an hour, so hers are bold and red.
| | 06:02 | Let's go back again to Jordan.
| | 06:05 | Now, I can also control the distance
between the words and the edges of the
| | 06:09 | cells, and the distance between the
cells themselves using, on the Arrange tab,
| | 06:14 | Control Margins and Control Padding.
| | 06:17 | First, select the entire
table using this plus right here.
| | 06:21 | Control Margins, let's
try the different options:
| | 06:24 | Narrow, Medium, and Wide.
| | 06:29 | Let's go back to Narrow.
| | 06:32 | Then for the Padding, try
Narrow, Medium, and Wide.
| | 06:41 | We're going to go with Medium.
| | 06:42 | Now that we've made so many changes to
our form, let's go over to the Design
| | 06:49 | tab, and then click on the
Datasheet View to see how it looks.
| | 06:54 | As you can see, using the Layout View
makes it easy to modify your forms so that
| | 06:58 | they are both functional and attractive.
| | Collapse this transcript |
| Using Design view| 00:00 | There is a third view, called Design View,
that gives you complete control over your forms.
| | 00:05 | Use it to make freestyle changes.
| | 00:07 | We're going to start with
our Customers Order Form.
| | 00:10 | Double-click on it, and let's take a look.
| | 00:12 | I see here that several
of our labels are cut off.
| | 00:15 | This Documents does not need to be this
big at all, but our Notes could be bigger.
| | 00:21 | And then here we've got a big space
where there are Orders and a small Orders
| | 00:25 | table so we can rearrange these.
| | 00:27 | To make those changes, go up to the View
button, drop it down and move it into Design View.
| | 00:34 | Let's start over here on the right-hand side.
| | 00:38 | If your Property Sheet is
open, for now, close it.
| | 00:42 | The first thing we're going to do is move our
Documents over down here, and make it smaller.
| | 00:47 | First, I'll make it smaller by
clicking on the Documentation.
| | 00:51 | I'll hold my cursor over this dot to get
a double-headed arrow and shrink it up.
| | 00:55 | I'll put my cursor in the middle of it
to get a four-headed arrow, and I'll drag
| | 00:59 | it down and over, until
its right-aligned with Flag.
| | 01:03 | In fact, I could even move this down a
little bit more and put Documents on top.
| | 01:09 | So drag this down a little bit more to
make room, and there is a gray dot in the
| | 01:12 | corner of Documents.
| | 01:14 | This allows me to move the label
without the bound control, or vice versa.
| | 01:18 | Then click on Document again and
make it longer so that we can see the
| | 01:22 | whole word, Documentation.
| | 01:25 | We want to move all of these controls
over so that there is more room for these
| | 01:29 | words and the labels in the first column.
| | 01:31 | To highlight all of these at once, I
could Shift+Click on them, or I can simply
| | 01:35 | click above and pull down and anything
that it's touching will get selected.
| | 01:41 | I'll put my cursor in the middle of one of
them and drag them to the right to make more room.
| | 01:47 | Then I want to move these labels.
| | 01:51 | I'll highlight just the labels, and
I'll use the resize handle on the left-hand
| | 01:55 | side to make them all a little
bit bigger, until all the words fit.
| | 01:59 | We need a little bit more.
| | 02:01 | Email Address is the longest one.
| | 02:03 | Then scroll over to the left-hand
side, and let's do something similar.
| | 02:08 | First, drag another marquee to
highlight all of those boxes, and then pick one
| | 02:15 | up and move them over until they are
lined up nicely, and then we'll do the same
| | 02:23 | thing to resize these labels.
| | 02:26 | Use that middle handle and
make them long enough to read.
| | 02:30 | Now, I have a little extra room on this side.
| | 02:33 | I'm going to draw a marquee around all of them.
| | 02:36 | And this time, I'm going to use the
arrows on my keyboard to move them over.
| | 02:40 | Sometimes it's easier to use the
keyboard then it is the mouse, so that you can
| | 02:44 | actually line them up carefully.
| | 02:47 | Now, let's scroll down here,
and we'll work with Orders.
| | 02:49 | I'm going to click on this Orders box,
and I don't actually need this label,
| | 02:53 | because it's fairly obvious
that these are the Orders.
| | 02:56 | So, I'll just click Delete to delete it.
| | 02:59 | Then I'll click on the subform, grab
the bottom left-hand handle and make it
| | 03:04 | wider, and then over to Datasheet View.
| | 03:08 | That's looking much better.
| | 03:09 | The last thing I need to do here is
simply double-click to resize my fields, and
| | 03:15 | now we have a finished Customers Order form.
| | 03:16 | Let's go ahead and close it and save that.
| | 03:19 | It will say, do I want to make
changes to the following objects?
| | 03:23 | It's referring both to this top-order form and
to this subform down here, and we'll say yes.
| | 03:29 | Now the fun begins.
| | 03:30 | We're going to build a form completely
from scratch with some advanced controls.
| | 03:34 | Go to the Create menu, and this time
we're going to click the Form Design
| | 03:38 | button, and we have a blank form.
| | 03:41 | This is going to be our Customer Service form.
| | 03:45 | The first thing I want to do is set our
Theme to Foundry, so that it matches the
| | 03:48 | rest of the forms in our database.
| | 03:51 | We're going to add in our logo.
| | 03:54 | Click up here on Logo, and it will ask
where you want to go to get the file, and
| | 03:58 | I'll click on the folder where that file resides,
find the file that I want, and click on it.
| | 04:10 | Notice that it has now separated
into a Form Header and a Detail area.
| | 04:14 | The Form Header is the top of the form,
and the Detail area is the part that's
| | 04:19 | going to repeat for each record in the table.
| | 04:22 | So I'm going to go ahead and pull
down this Design Form, and it's going to
| | 04:26 | make my logo bigger.
| | 04:28 | Now, let's add in a Title.
| | 04:31 | And we're going to call
this Customer Service Form.
| | 04:38 | Let's also add a Date and a Time to it.
| | 04:41 | I click on this Date and Time button.
| | 04:43 | I'll include the Date, and
this is the format that I want.
| | 04:47 | And because it is a Customer Service Form,
I'll include the Time as well, and click OK.
| | 04:52 | It puts fields right here.
| | 04:54 | Let's see how this looks
when I go to Datasheet View.
| | 04:58 | There's how my form looks so far.
| | 04:59 | I'm going to click on the
dropdown to take it back to Design View.
| | 05:04 | I want to adjust this text so
that it's centered in the box.
| | 05:09 | I'm going to go to Format, and I'm going
to center the words "Customer Service Form."
| | 05:15 | Now, we're going to add a control in
the Detail area so that we can pick which
| | 05:18 | customer we're performing
our customer service for.
| | 05:21 | Go to the Design tab, and here are our controls.
| | 05:25 | This one right here is a Combo
Box, which is a dropdown box.
| | 05:29 | I'll click on it, and I'll click
right about the 2-inch mark to place it.
| | 05:34 | And now it's going to give me
the list of values to choose from.
| | 05:39 | And I'm going to do find a record on my
form based on the value I selected in my
| | 05:43 | Combo Box, and click Next.
| | 05:47 | We're going to choose
CustomerID, and Company. Click Next.
| | 05:53 | We're not going to hide the key
column because when we're choosing our
| | 05:55 | customers, we want to both see their
zip code and their location for the
| | 05:59 | companies I have that have more
than one branch, and click Next.
| | 06:04 | We're going to call this
Customer ID and click Finish.
| | 06:10 | The next thing I'm going to do is put
in what's called an Unbound text box, so
| | 06:14 | that I can write text on my screen.
| | 06:16 | And this is going to be the customer
service message that our reps are going
| | 06:19 | to say on the phone.
| | 06:21 | So, I'll click on label right here, and
I'm going to click right underneath the
| | 06:25 | edge of that box right here,
and it asked me to start typing.
| | 06:29 | And I'll type "Customer
service is our highest priority."
| | 06:40 | Hold the Shift key down and
hit Enter to go to the next line.
| | 06:45 | "What can I do for you today?" Okay.
| | 06:51 | Now, notice it has a little
green indicator in the corner.
| | 06:53 | When I click on it, it gives me a smart
tag, and it is an unassociated control.
| | 06:59 | So I'm going to ignore the error.
| | 07:01 | That means that it has nothing to
do with the data in any of our forms.
| | 07:05 | The next thing we're going to do is add
in a Tab Control so we can have multiple
| | 07:09 | windows down on the bottom.
| | 07:11 | That's this one right here
that looks like some file folders.
| | 07:14 | I'll click on it, and I'm going to draw
approximately how big I want it to be.
| | 07:18 | So I'm going to start on the left-hand
side, at this one-inch mark, and I'm going
| | 07:23 | to drag it down to the six-inch mark and about
four inches or so, and let go. Here are two tabs.
| | 07:33 | The numbers on yours
will be different than mine.
| | 07:35 | To change the names on the tabs, come up
here to the Property Sheet, and it says Page 15.
| | 07:42 | And here's where we're going
to write in "Service Request."
| | 07:48 | And then click on Page 16
and change that to "Website."
| | 07:57 | Now click back on Service Request.
| | 08:00 | Here, we're going to drop in our
Service Request table so that we can fill in
| | 08:03 | information for this particular customer.
| | 08:06 | So we're going to use a subform.
| | 08:08 | Drop this down right here, and we
want this icon right here for subform.
| | 08:14 | Click on it, and when I bring my
cursor down over the tabs, it already
| | 08:18 | highlights where it's going to
go. All I have to do is click.
| | 08:22 | It wants to know if I want to use an
existing table or query, or if I want to
| | 08:26 | use an existing form.
| | 08:27 | We're going to go ahead and
use our Service Requests table.
| | 08:31 | So click on the Next button, and that
gives us the place to choose it right
| | 08:35 | here, ServiceRequests.
| | 08:37 | And we want all of our fields, so hit the
double arrow to move it over, and click Next.
| | 08:43 | Now, it's going to recognize that we
have a relationship between our Customers
| | 08:48 | in our table and the
Customer ID that we've chosen here.
| | 08:52 | So go ahead and click Next.
| | 08:53 | We'll leave this as Service Requests
subform, so that we recognize it with the
| | 08:59 | word subform right here, and click Finish.
| | 09:04 | Here's what it looks like in Design View.
| | 09:06 | I don't actually want this label here,
so I'm going to click on it so it has an
| | 09:10 | orange box around it, and hit Delete.
| | 09:12 | I'm going to click back on the subform,
and I'm going to use the arrows on my
| | 09:16 | keyboard to move it so it's
placed a little better inside the box.
| | 09:21 | And let's go and see what that looks like.
| | 09:23 | I'm going to come up to the Datasheet View.
| | 09:25 | And when I pick my customer up here,
| | 09:30 | it will appear properly down here.
| | 09:33 | I do need to change how
my table looks over here.
| | 09:36 | It's going to be easier to do this,
instead of in the Design View where I
| | 09:39 | can't really see it,
| | 09:40 | let's go to the Layout View. And then I
can go ahead and double-click my columns
| | 09:48 | to resize them so that everything fits.
| | 09:51 | Right now, there's nothing in the Request field.
| | 09:55 | I'm going to go ahead and make this
bigger since this is where we're going to type in
| | 09:59 | the information that they are
requesting. So far, so good.
| | 10:03 | Let's go back to the
Design View and do some more.
| | 10:07 | Click on the Website tab.
| | 10:09 | What we're going to do here is put in
what's called a Web browser Control, which
| | 10:13 | is actually going to show us the
contents of a Web site that we specify.
| | 10:17 | In our case, we're going to put in our
own Web site with our customer service
| | 10:21 | phone numbers so that we
have them at our fingertips.
| | 10:24 | Click on Web browser Control, hold your
cursor over the Website tab and click.
| | 10:30 | It wants to know the address,
and we're going to point it to
| | 10:36 | twotreesoliveoil.com/contact.html, and
when I hit Tab, it reformats itself to
| | 10:50 | the base URL and the path.
| | 10:52 | I don't have to do anything to that.
| | 10:54 | I can just go ahead and click Ok.
| | 10:57 | Now, I want to move this up into the
corner and make it absolutely as big as I
| | 11:02 | can, because it is
pulling in a Web site, after all.
| | 11:05 | If I'd like to give it even
more room, I can pull this down.
| | 11:16 | The last thing that I would like to
do is I want to put a nice little line
| | 11:20 | between my Form Header and the data,
so I'm going to use this control right
| | 11:24 | here that says Line.
| | 11:26 | Click on it one time, and I can see a
little crosshair where it's going to start.
| | 11:30 | I'm going to aim right there and
drag it all the way across my form.
| | 11:37 | Be careful, if you see it being thick like that,
| | 11:41 | that means that it's not actually straight.
| | 11:43 | So you wanted to be as
invisible as possible, and let go.
| | 11:48 | If I want to make it thicker, I can
come over to Format, choose Shape Outline >
| | 11:53 | Line Thickness, and make it a little thicker.
| | 11:58 | Let's also change its color to the
greens that we've been using on this Web site.
| | 12:03 | Now, let's take a look at our form.
| | 12:04 | Go to the Design tab and come over to
Datasheet View, and now we have a Customer
| | 12:09 | Service Form, where I can
write in what they need.
| | 12:21 | And let's take a look at the Web site.
| | 12:23 | When I click here, it brings us to Two
Trees Web site, and here's all our contact
| | 12:29 | and customer service information so
that we can give it to the customer.
| | 12:33 | So as you can see, using the Design
View takes much longer to build a form by
| | 12:37 | hand, but you have complete
control over every element.
| | Collapse this transcript |
| Setting tab stops| 00:00 | You can change the order of the
field as you tab through a form.
| | 00:04 | Double-click on the
Customers Order Form to open it up.
| | 00:08 | Press the Tab key repeatedly, so that you
can see yourself tab through the fields.
| | 00:12 | But notice when we get to Documentation,
it tabs there first before going to
| | 00:16 | Flag, and that's because in an earlier lesson,
we switch the order of those two locations.
| | 00:20 | If I press Tab again, it takes me down
to my Orders table, but I don't want the
| | 00:25 | Tab to go down here at all.
| | 00:26 | So I want to take it off of the tabs list.
| | 00:28 | To make these changes, go up to
the Views menu, and drop it down and
| | 00:32 | choose Design View.
| | 00:33 | Come up here to the Tab Order button,
and scroll down to the bottom so we can
| | 00:39 | see Flag and Documentation.
| | 00:41 | We want Flag to be above Documentation,
so click on Flag and then drag it up, so
| | 00:47 | that the two switch, and Click OK.
| | 00:51 | The next thing we want to do is
move the Subform out of the tabs.
| | 00:55 | Click on the Subform, and then open up
your Property Sheet, if it's not already.
| | 00:59 | There is a button right here to toggle it,
and scroll all the way down to the bottom.
| | 01:06 | You'll see Tab Stop. Change that to No.
| | 01:09 | And then let's go to Datasheet
View and give it a try. Perfect!
| | 01:23 | That's how you take control over your Tab Stops.
| | Collapse this transcript |
| Adding buttons to a form| 00:00 | You can add functionality to a
form using preprogrammed buttons.
| | 00:04 | Double-click on the
Customers Order Form to open it.
| | 00:07 | We're going to put a button right
here to allow us to print this order.
| | 00:11 | Go up to the View menu, drop
it down and choose Design View.
| | 00:15 | In the Controls area,
choose the fourth one for Button.
| | 00:19 | Click on it, and you'll get a
crosshair with a little square on it.
| | 00:23 | Go ahead and click right around the five-
inch mark, and a Command Button Wizard opens.
| | 00:28 | There are all different kinds of
commands that will automatically be programmed
| | 00:33 | when you click your button.
| | 00:34 | We want Form Operations, and we want
to Print a Current Form. Click Next.
| | 00:41 | It gives you a choice of putting on a
Picture, or you can Browse and put your
| | 00:45 | own picture on the button, or you can use Text.
| | 00:49 | We're going to have it say
Print Current Form. Click Next.
| | 00:54 | Give the button itself a name.
| | 00:56 | We're going to use PrintForm using
Camel Caps with no spaces, and click Finish.
| | 01:03 | If I want to change the appearance of
my button, come up to the Form Design
| | 01:06 | tools and click on Format,
and then on Quick Styles.
| | 01:10 | Let's choose this green 3D, so
that it matches the rest of our forms.
| | 01:15 | I can also change the Shape by
using this button right here.
| | 01:18 | I've got rectangles, circles.
| | 01:21 | We're going to do a Rounded Rectangle.
| | 01:25 | Click on it, and move it over,
so it's aligned well on the form.
| | 01:31 | Go up to the Design tab, and then over to
Datasheet View, and there is our button.
| | 01:37 | Hold our cursor over it and click,
and it opens up a Printer box.
| | 01:42 | Click OK if you want to
Print, or Cancel if you don't.
| | 01:45 | Access's buttons add
practical functionality to your forms.
| | Collapse this transcript |
| Using navigation forms| 00:00 | The switchboard from previous
versions of Access has received a
| | 00:03 | welcome replacement: Navigation forms.
| | 00:05 | When creating a database for others to
use, or if you're using the Web
| | 00:09 | SharePoint functionality, navigation forms create
buttons to easily view your forms and reports.
| | 00:14 | Click on the Create tab, and
choose the Navigation button.
| | 00:18 | Choose whether you'd like a Horizontal Tab set,
a Vertical one on the Left or Right, or both.
| | 00:25 | If you have a lot of buttons,
choose the Horizontal Tabs 2 Levels.
| | 00:28 | We're going to choose Vertical Tabs, Left.
| | 00:31 | Drag and drop your desired
forms and reports on to the buttons.
| | 00:34 | That's all you have to do.
| | 00:36 | Let's drag down to the bottom of the
Navigation Pane, so we can see our Forms and Reports.
| | 00:41 | Start with your Customers Order Form.
| | 00:43 | Pick it up, and drag it to the top of the list.
| | 00:46 | Now down in the Reports,
Orders by Customer Report.
| | 00:50 | Also from the Reports, choose Sales Reps
and Customers, and the Products Report,
| | 00:59 | the Service Request Form and the
Sales Reps Phone List from the Reports.
| | 01:05 | Now that I have my buttons, we
can now work with the appearance.
| | 01:09 | Click on the first button for Customers
Order Form, and go to the Format menu.
| | 01:13 | We're going to use a Quick Style.
Drop it down, and let's choose this light
| | 01:17 | green with the white outline.
| | 01:19 | We can then change the Shape.
| | 01:21 | Let's make it a Rounded Rectangle.
| | 01:22 | Now let's do the same
thing for the other buttons:
| | 01:26 | Orders by Customer Report, Quick Styles >
the blue with white outline and the
| | 01:31 | Rounded Rectangle; Sales Reps and
Customers, Quick Styles > the tan with the
| | 01:37 | white outline and a Rounded Rectangle;
Products Report, Quick Styles > the light
| | 01:43 | tan and the Rounded Rectangle;
| | 01:46 | Service Request Form, Quick Styles >
make it pink with the white border and
| | 01:51 | change the Shape to the Rounded
Rectangle, and then the Sales Reps Phone List,
| | 01:56 | dropdown the Quick Style > make it the
dark green with the white border, change
| | 02:00 | the shape to rounded rectangle.
| | 02:02 | Now you're not limited to just the Quick Styles.
| | 02:05 | You can also control the
appearance with these buttons here.
| | 02:08 | Change the Shape Fill.
| | 02:09 | Instead of green, let's
make it this gray right here.
| | 02:14 | I can change the white outline
to any other color and width.
| | 02:18 | I can also apply Special Effects:
| | 02:19 | Shadows, Glows, Soft Edges and Bevels.
| | 02:24 | Once you have it, so you like it,
| | 02:26 | go back to the Form Layout Tools > Design tab.
| | 02:29 | Go back to the Form View.
| | 02:31 | Let's close our Navigation Pane,
so we can see the form in full.
| | 02:35 | Click on the different buttons, and it
will change to your different objects.
| | 02:39 | Now note that Navigation forms only
work for Forms and Reports, not for
| | 02:43 | Tables and Queries.
| | 02:45 | This innovative Navigation Form gives
you the ability to easily move from one
| | 02:49 | form or report to another.
| | Collapse this transcript |
|
|
7. QueriesIntroduction to queries| 00:00 | Now that you've learned how to build
tables and to view your data using Forms,
| | 00:04 | it's time to learn how to
analyze your data using Queries.
| | 00:07 | A query allows you to pull exactly the
records you want at any time, so you can
| | 00:12 | view and analyze your data in a variety of ways.
| | 00:15 | They can be created for a one-time use,
or saved to use over and over again.
| | 00:20 | You can make a query to select
records that meet a certain criteria.
| | 00:24 | For example, double-click on
Maryland Orders to see all your orders from
| | 00:28 | a particular state.
| | 00:30 | Double-click on Order Totals, and you
can see a query that calculates the total
| | 00:35 | price for an order, or maybe you want
to analyze if any of your Sales Reps are
| | 00:40 | overpaid or underpaid.
| | 00:42 | Double-click on the Sales Reps
Phone List to see a contact list of your
| | 00:46 | employees, or you can also use a
Query as the basis for a report,
| | 00:51 | like this Sales Reps
Phone List Report right here.
| | 00:56 | You can also do action queries to
Update information within a table, or to
| | 01:02 | Delete all records that match your criteria.
| | 01:04 | Basically, you'll create a query
whenever you want to ask questions of
| | 01:07 | your database.
| | Collapse this transcript |
| Using the Query Wizard| 00:00 | The first query we're going to
make is called a Select Query.
| | 00:03 | That means we'll want to see some
but not all of the fields in the table.
| | 00:08 | We'll use the Query Wizard to do this.
| | 00:10 | To start, click on the Create
tab, and then on Query Wizard.
| | 00:14 | In the dialog box, we want a
Simple Query Wizard. Click OK.
| | 00:19 | So let's say you want to make a
Phone List for your Sales Reps.
| | 00:22 | Drop down the Table and choose SalesReps.
| | 00:25 | Click on LastName and move it over,
and FirstName and move it over, then
| | 00:29 | HomePhone, CellPhone and Email. But wait.
| | 00:34 | Maybe you decide that you'd
rather see the CellPhone first.
| | 00:37 | Move these three over and then
click on CellPhone, HomePhone and Email.
| | 00:45 | Go ahead and click Next.
| | 00:48 | Enter in the Query Name.
| | 00:50 | We're going to change this to
Sales Reps Phone List Query.
| | 00:57 | It gives you the option to either run
the Query or open it up in Design View
| | 01:01 | to further edit it.
| | 01:02 | We want to run it, so
just click the Finish button.
| | 01:06 | You now have a complete list
of all your Sales Reps and their
| | 01:09 | contact information.
| | 01:10 | You can double-click on the columns to
resize them, and then let's close it and
| | 01:18 | save it when it asks.
| | 01:24 | Now, we see Queries with our Sales
Reps Phone List Query on the Navigation
| | 01:28 | Pane, and I can double-click on it
to run it anytime to get a complete
| | 01:32 | up-to-date list of my employees.
| | 01:34 | Later, we can use this Query as
the source to make a printed report.
| | 01:38 | The Query Wizard and Select Queries
make it easy to pull out just the
| | 01:43 | fields you want to see.
| | Collapse this transcript |
| Creating a query in Design view with criteria| 00:00 | Creating Queries in Design View gives
you complete power and flexibility in
| | 00:03 | analyzing your data.
| | 00:05 | Let's start with looking at
the query that we already made.
| | 00:08 | Right-click on the Sales Reps Phone
List Query and choose Design View.
| | 00:13 | The top part of the window is where you
show your tables with their fields, and
| | 00:17 | the bottom is the grid where you
specify the fields you want to see.
| | 00:20 | So here is our Sales Rep table, with
all of its fields, and the grid below, with
| | 00:24 | just the five fields we wanted to see.
| | 00:27 | If it asks if you want to
save the changes, say No.
| | 00:30 | So let's take this a step further.
| | 00:31 | Let's make a Query to
analyze our sales in Maryland.
| | 00:35 | We would need to pull our Maryland
customers and look at their orders.
| | 00:38 | Here's how to do it.
| | 00:39 | Click on the Create tab
and then choose Query Design.
| | 00:42 | We'll get a new blank Query.
| | 00:44 | The first thing we need to do
is add our tables to the query.
| | 00:47 | Now when you're doing this, only add the
tables that contain the fields that you need.
| | 00:51 | If you add extra tables here,
it can distort your results.
| | 00:55 | If you accidentally open up an extra table,
click on it and press Delete to remove it.
| | 01:00 | Double-click on the Customers Table.
To add the Orders Table, click on
| | 01:03 | Orders and click Add.
| | 01:05 | You can add your tables
either way. Click Close.
| | 01:09 | Now, stretch out the bottoms of
the boxes so that you can see them.
| | 01:13 | Hold your cursor over the bottom
border of each box and pull down.
| | 01:17 | You can also resize your grid by
moving this up or down, as is practical.
| | 01:23 | Now let's add fields in a few different ways.
| | 01:26 | One is to double-click. Double-click on
Company and it will appear in the grid.
| | 01:31 | You can also drag the
field down. Try that for State.
| | 01:38 | Click in the third column.
| | 01:40 | You can also click on this dropdown
arrow to pick a field, but because we have
| | 01:43 | two tables showing, that's a
lot of fields to scroll through.
| | 01:47 | So double-click on OrderDate.
| | 01:50 | Next, we want to see the Product Name,
but in the Orders Table, the Product is
| | 01:54 | shown as an SKU number.
| | 01:55 | So if we add Products from the Products Table
instead, we'll get the actual name of the oil.
| | 02:01 | So click on Show Table and double-
click on Products, then click Close.
| | 02:08 | Add Product to the grid and then Price.
| | 02:13 | Before we add in a criteria,
let's test this and see if it works.
| | 02:16 | There are two buttons over here on the left.
View allows us to see the query results.
| | 02:22 | If it's a Query that performs an action, you'd
have to click this Run button to make it finish.
| | 02:28 | Click on the View button, and we can see
the Company who ordered, the State, the
| | 02:33 | Date of their order, the Product
they ordered, and how much they paid.
| | 02:38 | But let's refine it some more.
| | 02:40 | Go back to the Design View.
| | 02:42 | First, let's just see customers from Maryland.
| | 02:45 | Let's reorder the
columns so that State is first.
| | 02:48 | Click one time on this thin gray line
at the top of the column. Then click on it
| | 02:53 | again and drag it before the Company column.
| | 02:56 | You can see a dark line as you move.
| | 02:58 | Now under the State column, in this
Criteria line, type in MD and then hit the Tab key.
| | 03:07 | The cursor moves to the next column, and
Access automatically put quotes around
| | 03:11 | the letters because they are text.
| | 03:14 | This tells Access to find a literal match.
| | 03:17 | Also, let's sort the Company alphabetically.
| | 03:20 | Click on this Sort row.
Drop it down and Sort Ascending.
| | 03:25 | Let's also Sort by the OrderDate as
well so that all of the orders from one
| | 03:29 | company show up in the
order they were purchased.
| | 03:32 | Now I also want to add in the Product's
Size. Pick up Size from the Product Table
| | 03:37 | and drop it on top of Price.
| | 03:40 | Size will appear, and Price will move over.
| | 03:43 | Now let's see how that works. Click on View.
| | 03:45 | I can see all my orders from Maryland,
what companies are there, the dates
| | 03:51 | that they ordered, the products that they
ordered, their bottle size and how much they paid.
| | 03:56 | I notice that Maryland favors 8-ounce bottles.
| | 04:01 | So let's Save this query
and call it Maryland Orders.
| | 04:12 | Using the Design View to create your
queries gives you a lot of control over
| | 04:16 | your database analysis.
| | Collapse this transcript |
| Creating wildcard queries| 00:00 | Wildcards allow us to run queries when we
don't have an exact match for our criteria.
| | 00:05 | For example, I was having a
conversation with one of my customers, and I know I
| | 00:09 | put a comment in the notes about oils, but
I don't remember who it was or what I said.
| | 00:13 | So let's make a Query.
| | 00:15 | Click on Create and go to Query Design.
| | 00:19 | Double-click on Customers.
Close the Show Table window.
| | 00:25 | Let's put up the Company Name, their
Phone number, the person who I was talking
| | 00:32 | to, ContactFirst and
ContactLast, and then the Notes.
| | 00:38 | Now because I know that I mentioned oil,
but I don't know if that was at the
| | 00:42 | beginning or the end of the sentence,
| | 00:44 | I put in Wildcards.
| | 00:46 | Hold the Shift key down and put it an asterisk.
| | 00:50 | This indicates that there may be text
before the word oil in this sentence.
| | 00:55 | Then type the word oil and put in
another asterisk, because I'm not sure if
| | 00:59 | there were words after the
word oil in the sentence.
| | 01:03 | Now I'm going to view the Query and
sure enough, I have three customers where I
| | 01:09 | mentioned oil in the notes.
| | 01:11 | Now this is a one-time query, so
I don't actually have to save it.
| | 01:14 | I won't be looking for this on a regular
basis, but by using Wildcard Queries it
| | 01:19 | allows me complete
flexibility in establishing my Criteria.
| | Collapse this transcript |
| Creating reusable parameter queries| 00:00 | Sometimes when you run a query, you
would like run the same analysis but change
| | 00:04 | the group that you're running it for.
| | 00:06 | You don't have to create multiple queries.
| | 00:08 | A Parameter Query will allow
you to reuse one Generic Query.
| | 00:12 | Let's build on the Maryland Orders
Query we made in the previous lesson.
| | 00:16 | Right-click on it and choose Copy.
| | 00:19 | Then right-click in the white
area at the bottom and choose Paste.
| | 00:23 | Rename it Orders by State. Click OK.
| | 00:32 | Right-click on it and open it in Design View.
| | 00:36 | In the State Criteria, erase MD and
type in a square bracket, then type What
| | 00:42 | State? And then close the bracket.
| | 00:46 | What you're doing is entering in the
question Access is going to ask you.
| | 00:49 | So use real English.
| | 00:52 | Click on the View button, and a dialog
box will open asking you "What State?".
| | 00:57 | Enter in NY for New York and click OK, and
now you can see your orders for New York.
| | 01:04 | Right-click on the tab to close it,
and if it asks you to Save, please do,
| | 01:07 | and let's try it again.
| | 01:10 | Double-click on Orders by State.
| | 01:12 | It asks you What State?
| | 01:14 | This time type in VA, for Virginia, and click OK.
| | 01:18 | Now you've got your orders from Virginia.
| | 01:21 | Parameter Queries allow you to use the
same query over and over, just entering in
| | 01:26 | the desired criteria every time.
| | Collapse this transcript |
| Creating yes/no queries| 00:00 | Yes/No queries allow you to flag
certain records for a particular purpose.
| | 00:04 | Let's say it is summer, and we need a list of
our SalesReps who have asked for time off.
| | 00:08 | If you open up your SalesReps table and
scroll to the right, you can see the Flag field.
| | 00:15 | Let's mark a number of
our reps who want a vacation.
| | 00:18 | Pick about ten, or so.
| | 00:20 | Then close the table.
| | 00:23 | Now click once on our
SalesRep tables, so it's selected.
| | 00:27 | Click on the Create cab and go to Query Design.
| | 00:29 | Add the SalesReps and close the window,.
Stretch the bottom down so you can see
| | 00:36 | as many fields as possible.
| | 00:38 | Double-click on EmpID LastName,
FirstName, StartDate and Flag.
| | 00:46 | Let's sort ascending by StartDate, so
that we can schedule leave by seniority and
| | 00:52 | under Flag, set the criteria to Yes.
| | 00:56 | Yes means a field has a check in it.
| | 00:58 | No would mean the box is empty.
| | 01:00 | View your results, and now you have a
list of employees who would like leave
| | 01:05 | in order by seniority.
| | 01:06 | The Yes/No query allows you to take
advantage of Flags and Either/Or fields
| | 01:11 | in your database.
| | Collapse this transcript |
| Creating "and" and "or" queries| 00:00 | Often your queries will need
to have more than one criterion.
| | 00:03 | Here's how to create And and Or queries.
| | 00:06 | If you need to pull only the records
that satisfied two or more criteria, use
| | 00:10 | what's called an And query.
| | 00:11 | For example, we want to see which
employees were hired before 2003 and are
| | 00:16 | still with the company.
| | 00:18 | Click on the Create tab and on Query
Design, add the SalesReps table, close Show
| | 00:25 | Table and pull the bottom down.
| | 00:30 | Double-click on LastName, FirstName,
scroll down, StartDate and EndDate.
| | 00:38 | In the criteria line under
StartDate type <1/1/2003.
| | 00:47 | When you press Tab or click off of it,
Access will put pound signs before and
| | 00:51 | after to indicate a date.
| | 00:54 | In the criteria line under EndDate, type Is Null.
| | 00:59 | This is database terminology for Is Blank.
| | 01:02 | If you wanted matches that were not
empty, you would enter Is Not Null.
| | 01:07 | By entering both is criterion on the
same row, you're indicating that both must
| | 01:11 | be true for the result to appear.
| | 01:14 | Click on View, and you'll see that you
have four sales reps that have been with
| | 01:17 | you for a very long time.
| | 01:20 | Close this query and save it as
Longest Employed. Then click OK.
| | 01:29 | Our next type of query is an Or query.
| | 01:34 | This allows you to pull records that
meet either one criterion or another.
| | 01:38 | This works the same way, but instead
of putting all the criteria on the same
| | 01:42 | line, we will put them
on lines below each other.
| | 01:45 | Let's make another query that will
pull sales reps who either have extremely
| | 01:48 | high salaries or extremely low ones.
| | 01:50 | Create a query by clicking on the
Create tab, then on Query Design add the
| | 01:56 | SalesReps table and click Close.
Double-click on LastName and FirstName and
| | 02:03 | scroll down to double-click on Salary.
| | 02:06 | Sort by LastName > Ascending.
| | 02:09 | On the First Salary criteria line, enter <=10.
| | 02:16 | We don't need any dollar signs or decimals.
| | 02:18 | Now on the line directly
below, it says "or," and type in >=30.
| | 02:26 | View it, and now you get a list
of employees with unusual salaries.
| | 02:30 | So you can follow up and find out why.
| | 02:33 | Close this query and save
it as High/Low Salaries.
| | 02:42 | If you're wondering whether to use And
or Or to get your desired result,
| | 02:46 | remember that Or gives you more results.
| | 02:49 | In an And, the record has to meet more
than one standard, so fewer will be chosen.
| | 02:54 | In an Or query, the record
can meet two different standards.
| | 02:57 | So it has a better chance of
qualifying. Or gives you more.
| | 03:01 | Together And/Or queries make it
possible to analyze your data using
| | 03:05 | multiple criteria.
| | Collapse this transcript |
| Building calculation queries| 00:00 | It's possible to run
calculations within your queries.
| | 00:03 | Let's make a query that gives
us the total price on each order.
| | 00:07 | Create a new query by clicking on the
Create tab and then on the Query Design
| | 00:11 | button, add the Products table, the
Orders table, the SalesReps table, and the
| | 00:19 | Customers table, and then click Close.
| | 00:22 | Now double-click on the OrderDate and
instead of choosing the company from here,
| | 00:28 | that would give us just their ID
code, we want their whole name,
| | 00:32 | so let's pick it from the
Company field in the Customers table.
| | 00:35 | Same thing for the Product.
| | 00:37 | I could pick the product from here,
but again it would give me the code.
| | 00:40 | So let's pick the product
name from the Products table.
| | 00:43 | Now let's pick the Size and the Price,
which will give us the details about what
| | 00:48 | they ordered, and then from the
Orders table, pull in the Quantity.
| | 00:53 | Now I like to know which sales rep
helps them and since the SalesReps is
| | 00:57 | associated with the Customers, I
could pull the SalesRep from here, but
| | 01:04 | instead I am going to pull their
LastName from the SalesReps tables, so I will
| | 01:07 | get their actual name.
| | 01:08 | Now let's sort this by OrderDate >
Ascending and then by Company also Ascending.
| | 01:16 | Now, let's find the total for their order.
| | 01:18 | Click in the last column and then click on
the Builder button up here in the Ribbon.
| | 01:23 | Type in "Total:"
| | 01:26 | that tells Access that this
will be the field's caption.
| | 01:29 | Now, click the Plus sign next to the
file name down here in the Expression
| | 01:33 | Elements and then click the Plus sign next
to the Table so that we can see our tables.
| | 01:38 | On the Products table, double-click on
Price. The code will appear in the window.
| | 01:45 | Erase the Expr, since we put in our own caption.
| | 01:49 | The table and the field
names are in square brackets.
| | 01:52 | The Table is here. The exclamation
point separates it from the Field Name.
| | 01:57 | Click after the two and type
in an asterisk for multiply.
| | 02:01 | Then go down to the Orders
table and double-click on Quantity.
| | 02:05 | So now we have the total will equal the
Price of the product times the Quantity
| | 02:11 | ordered and click OK.
| | 02:14 | Let's View the results.
| | 02:15 | Click up on the View table.
| | 02:17 | So here's the OrderDate, the Company who
ordered, the Product that they ordered,
| | 02:22 | what Size bottle they got, their Price,
their Quantity, who helped them, and the
| | 02:28 | Total for their order.
| | 02:30 | Let's save this query as Order Totals.
| | 02:38 | Calculation queries allow you to do
any kind of math between any fields in
| | 02:42 | your database.
| | Collapse this transcript |
| Creating statistical queries| 00:00 | If you'd like to run statistical analysis
on your database, you can use a Totals Query.
| | 00:05 | Create a new query by clicking on the
Create tab and then on Query Design.
| | 00:10 | Add the Products table and the
Orders table, and click Close.
| | 00:15 | We sell olive oils in 8, 16,
32, 64 and 128-ounce bottles.
| | 00:22 | We're going to analyze our
products, grouped by size.
| | 00:26 | Double-click on Size, and then on
Price, and then on Profit, and then
| | 00:32 | double-click on Profit a second time.
| | 00:35 | On the Orders table, double-
click on OrderID and Quantity.
| | 00:42 | Up on the Ribbon, there is a Totals button.
| | 00:44 | Click on it, and you'll get an
additional row down here on the grid.
| | 00:48 | It defaults to Group By.
| | 00:50 | We'll leave Size as Group By.
| | 00:52 | This will group the bottles by size,
no matter what kind of oil it is.
| | 00:57 | Under Price, choose Average. Under
Profit, click the dropdown and choose Min.
| | 01:05 | On the second Profit from
the dropdown, choose Max.
| | 01:10 | From the OrderID, choose Count.
| | 01:14 | Under Quantity, select Sum.
| | 01:18 | Now click on the View.
| | 01:20 | We can see, by size: our average price,
our smallest profit, our largest profit,
| | 01:29 | the number of orders that were placed
for each kind of bottle, and the total
| | 01:34 | number of bottles sold for each bottle size.
| | 01:37 | Now, it came up with codes across
the top that are Access programming.
| | 01:41 | We can clean that up a little bit.
| | 01:43 | Go back to the Design View, and
before Price, type in "Average Price:".
| | 01:54 | By typing in a name and a colon, it
tells us that this is going to be the name
| | 01:58 | at the top of the column.
| | 02:00 | Before Profit, type "Smallest Profit:".
| | 02:07 | Before the second Profit, type "Largest Profit:".
| | 02:15 | Before OrderID, type in
"Number of Orders:".
| | 02:23 | Then before the Quantity, type in
"Total Number Ordered:".
| | 02:34 | Now, when I go to View and run it,
when I resize my columns, I have English
| | 02:39 | captions for my statistics, which will
help you understand what you're looking at.
| | 02:45 | Let's go up to the Save, and title
this Order Analysis Query, and click OK.
| | 02:55 | Every time you run this statistical
query, you'll get completely up-to-date
| | 02:59 | numbers for your product line.
| | Collapse this transcript |
| Using update queries| 00:00 | When you need to make changes to the
data in your Table, you can do it quickly
| | 00:03 | using an Update Query.
| | 00:05 | Let's say it's time for me to raise my prices.
| | 00:07 | I don't need to go into my
Products line-by-line to make the change.
| | 00:10 | I can do the whole table at once.
| | 00:13 | Create a new query and click on Query Design.
| | 00:17 | Add the Products Table and click Close.
| | 00:22 | Double-click on Price to add it.
| | 00:25 | And now we're going to change
the query type, up here, to Update.
| | 00:29 | So let's say we're going to do a
10% across-the-board price increase.
| | 00:34 | In this new Update To line, put in
a square bracket and the word "Price."
| | 00:40 | The square brackets, again,
tell it that it's a field.
| | 00:42 | Put in an asterisk for multiply and then 1.1.
| | 00:47 | This 1.1% represents the
same price, plus a 10% increase.
| | 00:54 | When we click on the View button, we
will see the prices that will be updated.
| | 01:00 | Click on the Design again.
| | 01:01 | When we click Run, it will now
actually change the prices in the table.
| | 01:06 | When we run it, it will ask, "You are
about to update 90 rows. Once you click
| | 01:11 | Yes, you can't use the Undo
command reverse the changes.
| | 01:14 | Are you sure you want to update
these records? Yes, we are sure."
| | 01:19 | Now when we go into our Products and
look at our Prices, they are 10% higher
| | 01:22 | than they were a few minutes ago.
| | 01:26 | Now let's clear this
grid and make another query.
| | 01:29 | Click on the gray row at the top of
Price, and then hit Delete to clear it.
| | 01:34 | And click on the Products Table and clear
that as well, by pressing the Delete button.
| | 01:40 | For our second query, let's say that
SalesRep number 5, April Walters, is
| | 01:44 | leaving, and we are going to give all of her
customers to SalesRep number 2, Lila Douglas.
| | 01:49 | Click on the Query tools Design and
click on Show Table, and add Customers to
| | 01:56 | the Wuery window and click Close.
| | 02:01 | Scroll down and double-click on
SalesRep to add it to the Customers table.
| | 02:06 | Make sure that Update is selected.
| | 02:10 | And in the Criteria line, type 5.
That specifies that we are looking for
| | 02:14 | SalesRep number five.
| | 02:16 | In the Update To line,
enter 2. View the results.
| | 02:21 | We are going to have one
customer that's going to change.
| | 02:25 | Click back on Design, and
run the query. Click Yes.
| | 02:30 | Let's open up the
Customers Table to see the change.
| | 02:33 | Double-click on Customers and Bread
Express, who used to be April Walters,
| | 02:39 | will now say Douglas.
| | 02:42 | Close the Customers table.
| | 02:44 | Now most of the time, these queries don't get
saved, because they are made for one time use.
| | 02:49 | Update queries make it convenient to
change your Table data as needed, all at
| | 02:53 | once, instead of one at a time.
| | Collapse this transcript |
| Using delete queries| 00:00 | If you need to delete records in your
table according to certain criteria, you
| | 00:03 | can do it using the Delete Action Query.
| | 00:06 | Let's say one of my customers, Zino
Lettis, has a few oils backordered.
| | 00:13 | But while they're waiting,
they decide to cancel their order.
| | 00:19 | Create a new query by clicking on the
Create tab, and then on the Query Design.
| | 00:24 | Add the Orders table and then
close the Show Table window.
| | 00:29 | Double-click on the Customer field
and then on the OrderStatus field.
| | 00:34 | Then come up to Query Types, and choose Delete.
| | 00:38 | There is a new row down here for
deletion criteria. In the Customer Criteria, type
| | 00:44 | in ZL11722, and under
OrderStatus, enter Backordered.
| | 00:55 | When you click the View button,
you'll see three orders ready to delete.
| | 00:59 | Toggle it back and click on the Run button.
| | 01:02 | "You are about to delete three
rows from the specified table.
| | 01:06 | Once you click Yes, you can't use the Undo
command reverse the changes. Are you sure?"
| | 01:10 | Yes. We are.
| | 01:12 | Now the orders have been canceled.
| | 01:14 | Go back to the Customers table, scroll
down to Zino Lettis, and they're gone.
| | 01:20 | You don't have to save this query.
| | 01:21 | You probably won't have to
delete Zino Lettis Backorders again.
| | 01:24 | Delete Queries make it convenient to
remove records from your tables according
| | 01:28 | to the criteria you specify.
| | Collapse this transcript |
| Creating crosstab queries| 00:00 | Instead of viewing all your data based
on one criteria, you can analyze it on
| | 00:04 | two or more variables using Crosstab Queries.
| | 00:07 | Maybe you're curious about how many
bottles of each type of oil is sold everyday.
| | 00:12 | To do that, you'll use a Crosstab
Query to compare order date to products,
| | 00:16 | and sum the quantity.
| | 00:18 | Click on Create to start a new query.
| | 00:21 | Choose Query Wizard.
| | 00:23 | Choose the second option,
Crosstab Query Wizard, and click OK.
| | 00:28 | First, pick the table you'll be analyzing.
| | 00:31 | We'll choose Orders.
| | 00:32 | It's important to note that you can
also build Crosstab Queries based on the
| | 00:36 | fields from multiple tables.
| | 00:38 | But to do that, you would need to
create a Select Query to join the fields from
| | 00:42 | all your tables into one place.
| | 00:44 | Then you can build the
Crosstab from that one query.
| | 00:47 | Click Next, and choose what
field you want for the rows.
| | 00:52 | For us, that will be OrderDate.
| | 00:54 | Click on it, and click the
arrow to move it over. Click Next.
| | 00:59 | Now, we'll choose our column headers.
| | 01:01 | In this case, we'll analyze
our Products. Click Next.
| | 01:07 | Now is the most important
field: what do you want to know?
| | 01:09 | You can pick a particular field, and you can
pick the kind of statistic that you want to run.
| | 01:15 | We'll choose Quantity and Sum.
| | 01:19 | That will give us the total
bottles sold. Click Next.
| | 01:23 | Let's change the name.
| | 01:25 | Type in "Products Sold by Date
Crosstab." Then click Finish.
| | 01:35 | Let's scroll down and take a look at March 9.
| | 01:39 | We sold 104 bottles.
| | 01:41 | When we scroll over to the right, we see
66 bottles of 8-ounce light oil, and 38
| | 01:52 | bottles of 32-ounce pure oil.
| | 01:55 | By saving this query, we can run it
again anytime we want a fresh analysis.
| | 02:00 | Crosstab Queries help you analyze
your data among two or more dimensions.
| | Collapse this transcript |
|
|
8. ReportsIntroduction to reports| 00:00 | Reports are the Access objects
that format your data for printing.
| | 00:04 | They can be based on your tables and
queries, in order to organize information,
| | 00:08 | and to make it more attractive and readable.
| | 00:10 | Reports are different from Forms.
| | 00:12 | Forms are specifically
created for onscreen use.
| | 00:16 | Reports are designed to be printed on paper.
| | 00:20 | Reports can be used to group your data.
| | 00:22 | They can perform calculations.
| | 00:24 | You can also use them for special
effects, like Conditional Formatting.
| | 00:28 | So, let's go ahead and create our first report.
| | 00:31 | The first report we're going to
make will turn one of our tables into a
| | 00:34 | printable document with just one click.
| | 00:37 | Click on the Products table to tell
Access that that's the one you want to use.
| | 00:42 | Click on the Create Ribbon, and then
over here to Report. Click on this button.
| | 00:48 | The Report comes up in Layout View.
| | 00:51 | I can tell because of the
orange rectangles when I click on it.
| | 00:55 | Come up to the View button, and
toggle over to the Report View. And Voila!
| | 00:59 | An attractively formatted list of
Two Trees products, ready for printing.
| | 01:05 | We've gone from this, open up your
Products table, to this, Products form.
| | 01:12 | Now, this report is completely customizable.
| | 01:14 | We'll do that in future lessons.
| | 01:16 | Let's save our report and
call it Products Report.
| | 01:23 | Click OK. So let's go ahead and make some
more reports for Two Trees Olive Oil.
| | Collapse this transcript |
| Using the Report Wizard| 00:00 | The Report Wizard walks you through the
steps of choosing which fields to print,
| | 00:04 | how they're laid out, and
it applies an overall theme.
| | 00:07 | Let's make a report for the Sales Reps
Phone List we created in an earlier lesson.
| | 00:12 | Close it.
| | 00:13 | Click on the Create tab
and choose Report Wizard.
| | 00:18 | Click on this dropdown list.
| | 00:19 | You can base your report
on any table or any query.
| | 00:23 | Basing it on a query is ideal when you
want to display fields from multiple tables.
| | 00:28 | Make sure you're on Sales Reps Phone List Query.
| | 00:31 | We want all of our fields, so
click on the double-arrow. Click next.
| | 00:36 | The next screen asks for grouping levels.
| | 00:38 | We'll use those in a
future exercise. Click Next.
| | 00:42 | The next window wants to know
if we want to sort our records.
| | 00:45 | First, we'll choose LastName,
and then we'll choose FirstName.
| | 00:50 | That way, it will group all of our
reps with the same last name together and
| | 00:53 | list them in order by first name. Click Next.
| | 00:58 | Here we can choose a general layout.
| | 00:59 | Columnar will run the fields down in columns.
| | 01:04 | Tabular makes it look
something like a datasheet.
| | 01:07 | Justified runs all the fields going
across, like you were filling in a form.
| | 01:11 | We're going to choose Tabular.
| | 01:13 | You can then choose Portrait or Landscape.
| | 01:16 | We're going to do Portrait.
| | 01:17 | There's a check mark here to narrow all
the fields so that they fit on one page.
| | 01:22 | Sometimes this works wonders, but
other times it may truncate your fields.
| | 01:26 | You'll have to experiment with your
data to know for sure. Click Next.
| | 01:31 | Rename the report Sales Reps Phone List
Report. It gives us the option of viewing
| | 01:37 | the report or changing it.
| | 01:39 | We're going to leave it on
Preview and click Finish.
| | 01:42 | Now, right now, some of the fields are cut off,
but we'll learn to fix that in future lessons.
| | 01:48 | Look at the difference between this
and our original query. Pretty nice!
| | 01:55 | The Report Wizard makes it easy to
lay out new reports based on any table
| | 01:59 | or query.
| | Collapse this transcript |
| Formatting reports in Layout view| 00:00 | Once you've created report, you
can modify it in the Layout View.
| | 00:04 | We explored many of the Layout View
features in the chapter about Forms, so
| | 00:08 | we're just going to highlight
the ones most relevant here for our
| | 00:10 | particular reports.
| | 00:12 | Scroll down to the bottom of the Navigation
Pane, and find your Sales Reps Phone List report.
| | 00:18 | Double-click on it.
| | 00:18 | Come up here to the View button,
| | 00:21 | and toggle down to Layout View.
| | 00:24 | Notice that that you can now click on
your labels and your field names, and
| | 00:28 | they'll get orange boxes around them.
| | 00:30 | Let's start by choosing a theme.
| | 00:32 | Click on this button up here
and scroll down to Foundry.
| | 00:37 | It turns our form green and
makes the font more interesting.
| | 00:40 | You can also change the
fonts from this Colors box.
| | 00:44 | You can use the color scheme we made in
the chapter about Form Layout View and
| | 00:48 | use the same steps we followed in
that lesson to modify your colors.
| | 00:52 | Layout View is table-based, so that all the
data are organized in rows and in columns.
| | 00:58 | The headers and the data
move and resize independently.
| | 01:02 | So if you want to alter both at the
same time, you need to click on one and
| | 01:05 | Shift+click on the other.
| | 01:07 | Click on Last Name, hold the Shift key
and click on the list of Last Names so
| | 01:12 | that they're both highlighted.
| | 01:14 | We can resize the fields as a group by
pointing at the right edge, getting a
| | 01:18 | double head arrow and
dragging over to left-hand side.
| | 01:22 | They'll all resize.
| | 01:24 | Now I want to move my First Names over.
| | 01:27 | Click on the First Name header, shift-
click on the First Names themselves, hold
| | 01:32 | your cursor over the middle of the
field and get a four-headed arrow and drag
| | 01:35 | over to the left, and the field will move.
| | 01:38 | Let's also pick up the right-hand side with a
double-headed arrow and make it a little smaller.
| | 01:44 | Next let's do CellPhone.
| | 01:46 | Click on CellPhone, Shift+
click on the phone numbers.
| | 01:49 | This time, use the arrows on your keyboard.
| | 01:52 | Use the Left Arrow, and it
will move your data over.
| | 01:55 | Sometimes this is easier than dragging.
| | 02:00 | Let's resize the right side, so that
we can see all of our CellPhone numbers,
| | 02:05 | and do the same thing to HomePhone.
| | 02:08 | Shift+click on both, use the arrows on
my keyboard to move them over, and place
| | 02:13 | them precisely, make the
right side a little bigger.
| | 02:16 | Now I also notice that CellPhone
and HomePhone don't have spaces.
| | 02:20 | Double-click on CellPhone and click
your cursor right in between the two words
| | 02:25 | and tap the Spacebar.
| | 02:26 | Then do the same thing in between Home and Phone.
| | 02:30 | Next, our e-mail addresses.
| | 02:32 | Click on Email, Shift+
click on the list of addresses.
| | 02:35 | And this time just resize the left
column and pull it over to the left side.
| | 02:40 | Now no matter what we do, our
Email is going to be very large.
| | 02:43 | So let's change the Font Size.
| | 02:45 | Click on one of the Email addresses.
| | 02:48 | And then come up to your Report Layout
tools, and click on the Format Ribbon.
| | 02:52 | Here, where the Font Size is 11,
drop that down and make it a 10.
| | 02:56 | Now they're all fit.
| | 02:58 | Scroll down to the bottom.
| | 03:02 | Our report already has page numbers,
but if I wanted them some more else, I can
| | 03:07 | click on the Page Number and then
delete it by pressing Delete on my keyboard.
| | 03:11 | And then go to the Design Ribbon and
choose Page Numbers, and we get a dialog box.
| | 03:17 | I have the choice between
saying Page 1 and Page 1 of 3.
| | 03:22 | I can Position them at the Top of
the page or at the Bottom of the page.
| | 03:26 | I can change the Alignment.
| | 03:28 | I can have it on the Left, the
Center, or the Right side of the page.
| | 03:31 | If my Report is going to be bound, I
can have the Page Numbers on the inside
| | 03:35 | near the binding, or the
outside near the loose edges.
| | 03:38 | I'll choose Outside.
| | 03:40 | You can also choose whether or not
to show the number on the first page.
| | 03:44 | For now, we'll leave that on. Click OK.
| | 03:47 | Now it put our Page Number in the
header, but it did it on top of Email.
| | 03:52 | Click on Page Number, and let's drag
the left-hand edge smaller so that page
| | 03:57 | number moves over to the right-hand side.
| | 03:59 | When you're ready to print, let's click
up on the Page Setup tab, up under Report
| | 04:04 | Layout tools, and here you can adjust
your Paper Size, your Margins, make them
| | 04:10 | Normal, Wide or Narrow.
| | 04:12 | We'll leave them as they are
by clicking back on the button.
| | 04:17 | I can turn off this Show Margins check mark if
I don't want to see the margins on my screen.
| | 04:22 | But that doesn't actually
affect my printed report at all.
| | 04:25 | I could also have it print the data
only without printing the headers.
| | 04:29 | I can change it here from
Portrait to Landscape and back.
| | 04:34 | If I have a few fields, I can also
organize my page into columns by clicking on
| | 04:38 | this button and changing the
settings in this dialog box.
| | 04:42 | We're going to click Cancel.
| | 04:44 | This Page Setup button takes you into a
dialog box where you can set all of the
| | 04:48 | same options that we can on the Ribbon.
| | 04:51 | Click cancel here as well.
| | 04:53 | Go back to the Design tab and toggle
back to the Report View, and I can see what
| | 04:58 | my report will look like when it's printed.
| | 05:01 | When you're ready to print, click on
the File tab to go to Backstage View, and
| | 05:05 | then come down here to print.
| | 05:09 | Working in Layout View allows you to
modify your report so that data can be read
| | 05:13 | easily, and so the report looks nice.
| | Collapse this transcript |
| Identifying report structure in Design view| 00:00 | Reports also have another view, the
Design View, which gives you complete
| | 00:04 | control over your report. Understanding it,
helps you with designing your reports,
| | 00:08 | and you can use it to make freestyle changes.
| | 00:11 | We explored many of the Design View
features in the chapter about forms.
| | 00:15 | So we're just going to highlight
the ones most relevant here for our
| | 00:18 | particular reports.
| | 00:19 | Scroll down on the Navigation pane, and
double-click on the Sales Reps Phone List Report.
| | 00:24 | Here's what it looks like.
| | 00:25 | Now, come up here to the Views,
drop it down, and choose Design View.
| | 00:30 | Now let's look at the parts.
| | 00:32 | There is a Report Header and a Report Footer.
| | 00:36 | These appear at the top and
the bottom of the report itself.
| | 00:41 | This will be on the first page.
| | 00:42 | This will be on the last page, no
matter of how many pages the report is.
| | 00:46 | The Page Header and the Page Footer
will appear at the top and bottom of every
| | 00:50 | single page in the report,
whether it's one page or ten pages.
| | 00:55 | Then the Detail will repeat itself over
and over again for every record in the table.
| | 01:01 | Click on the Sales Reps Phone List
Report label up here, and let's look at the
| | 01:05 | Report Design tools.
| | 01:06 | These are exactly the same
as working in the Layout View.
| | 01:09 | We have our Design tab, our Arrange
tab - although most of the features are
| | 01:14 | grayed out in the Design View -
Formatting and Page Setup.
| | 01:19 | Let's go to the Design tab.
| | 01:21 | Over here on the right-hand
side, we have the Property Sheet.
| | 01:24 | Now, this gives you granular control
over every single aspect of your report.
| | 01:29 | It's dynamic, and it will change
according to what you're clicked on.
| | 01:35 | In the Property Sheet,
you'll see a Formatting tab -
| | 01:38 | this is everything about the
appearance of those controls - the Data, the
| | 01:42 | information contained in the controls,
Events, things that happen when you use
| | 01:46 | the controls, a few Other features, then
All brings them altogether in one long list.
| | 01:52 | Now, click again on this Sales Reps
Phone List Report, and then come over to the
| | 01:57 | Property Sheet and go down to Special Effect.
| | 02:01 | Right now, it says Flat.
| | 02:03 | Click on it, and use the
dropdown, and choose Raised.
| | 02:08 | Now, let's go take a look.
| | 02:10 | Go back to the View, and
toggle back over to the Report View.
| | 02:13 | Now, our title has a nice 3D look to it.
| | 02:16 | Click back on the Design tab, and go back to
the View button, and click back on Design View.
| | 02:22 | Working in the Design View allows you to
modify your report down to the smallest
| | 02:25 | detail, giving you complete
control over its appearance.
| | Collapse this transcript |
| Adding group and sort capabilities to a report| 00:00 | When you have a lot of data, you can
group repeated info to make the report
| | 00:04 | easier to read and understand.
| | 00:06 | Open up our Order Totals query.
| | 00:09 | This is a query we made earlier,
showing our orders and a calculation of the
| | 00:14 | Total amount for every Order.
| | 00:16 | Let's turn it into a
Report, so that it's easy to read.
| | 00:20 | Click once on Order Totals under our Queries.
| | 00:23 | Click on the Create Ribbon, and on Report.
| | 00:27 | It opens up a formatted report. So far so good.
| | 00:29 | Now notice how all of our
companies are grouped, but also repeated.
| | 00:35 | On the Report Layout tools Design tab,
click here on the Group & Sort button.
| | 00:41 | At the bottom of your screen, a Group,
Sort, and Total area will appear.
| | 00:46 | Click on Add a Group, and click on Company.
| | 00:50 | A new subheading will
appear with the Orders below.
| | 00:53 | The Company has been removed from
every row, immediately making the data
| | 00:57 | more understandable.
| | 00:59 | Click on All Kinds of Taste, and drag the
right-hand border so that it is smaller.
| | 01:04 | I notice that my dates are not chronological.
| | 01:07 | At the bottom, click on Add
a sort and choose OrderDate.
| | 01:13 | I can sort both of these.
| | 01:14 | Click on Company, and I can change it
from A to Z, or if I wished, Z to A. I
| | 01:21 | can click on OrderDate here is where
I can change it from oldest to newest,
| | 01:25 | to newest to oldest.
| | 01:28 | Now click on the More button, and
sometimes I like to change "do not group
| | 01:32 | together on one page" to "keep whole group
together on one page" so that it doesn't
| | 01:37 | cut off a Group in the middle.
| | 01:39 | Let's click on the fields and resize them
until all of our fields fit on the page.
| | 01:43 | Make OrderDate a little smaller,
make Product a lot smaller, Size, Price,
| | 01:55 | Quantity, that's too short, Last Name and Total.
| | 02:07 | Now all the fields fit on one page.
| | 02:09 | Go up to the Views and toggle over to
the Report view, so you can see what it
| | 02:14 | looks like when it prints.
| | 02:16 | Then click over on our original Order
Totals query, and we've gone from this to this.
| | 02:24 | Let's Save the Report and change the
name to Orders by Customer. Click OK, and
| | 02:32 | we will it leave opened for the next lesson.
| | 02:35 | Grouping and sorting controls helped
us to reduce our a repetitious text and
| | 02:39 | sort the order of our fields to
make a report easier to interpret.
| | Collapse this transcript |
| Adding existing fields from other tables| 00:00 | Scroll down to the bottom of your
Navigation Pane and double-click on
| | 00:03 | your Orders by Customer.
| | 00:05 | Since I'm looking at a list of every
customer's orders, it will be helpful to
| | 00:08 | see who their SalesRep is.
| | 00:10 | Access allows you to add existing
fields from other tables to any report.
| | 00:15 | Go up to the Views, and
toggle over to the Layout View.
| | 00:19 | If you still have Group, Sort, and Total
open from our previous lesson, click on
| | 00:22 | this button right here to close it.
| | 00:24 | Go up to the Report Layout tools, and
make sure you're on the Design tab, and
| | 00:29 | then click on this Add Existing
Fields button, and it will open up.
| | 00:33 | Now I'd like to know who the
SalesRep is, but they're not on this list.
| | 00:37 | Click here for Show all tables.
| | 00:40 | Some of the tables will show up
here; others may be down here.
| | 00:45 | We want to know who the
SalesRep is for each customer.
| | 00:49 | Click on SalesRep, and then drag it
over to the left, to this box right here.
| | 00:56 | It creates a SalesRep
label and the field itself.
| | 01:00 | Let's change the appearance of this label.
| | 01:03 | Double-click where it says SalesRep.
| | 01:05 | In between the two words, put in a space and
after Rep put it a colon for proper grammar.
| | 01:11 | Now let's take off the box around the field.
| | 01:14 | Click on the last name, Davenport.
| | 01:16 | Let's go to the Format Ribbon, and
over to Shape Outline, drop it down, and
| | 01:23 | make it Transparent.
| | 01:24 | I also notice a subtle
border at the bottom of this box.
| | 01:29 | Click on Sales Rep:, go back to the
Design Ribbon and click on Property Sheet.
| | 01:35 | Go down to Gridline Style Bottom.
| | 01:39 | Change Solid to Transparent.
| | 01:43 | That line goes away too.
| | 01:46 | Let's go back to the Report View
to look at it. That looks great.
| | 01:50 | Save it.
| | 01:52 | The Add Existing Fields button allows
you to add relevant data to your report
| | 01:56 | from any tables in your database.
| | Collapse this transcript |
| Adding totals and subtotals to a report| 00:00 | When reading a report, it's useful
to be able to summarize the data with
| | 00:03 | mathematical calculations, both for
subsections the entire report itself.
| | 00:08 | Access 2010's Totals button makes it easy.
| | 00:11 | Scroll down in your Navigation Pane, to
your Reports object, and right-click on
| | 00:15 | Orders by Customer and open it in Layout View.
| | 00:19 | We'd like to know how much each customer
has spent with us and how much revenue
| | 00:23 | we've also had for that report period.
| | 00:26 | Click on the Total column, and then up
in the Report Layout tools on the Design
| | 00:31 | Ribbon, click on Totals,
right here, and choose Sum.
| | 00:36 | A subtotal has been added below every row.
| | 00:39 | Now it's a little short.
| | 00:41 | Click on it and pull the bottom down a little.
| | 00:43 | Now scroll down to the
bottom of the whole report.
| | 00:46 | You'll see we have a grand total down here.
| | 00:51 | Make the same adjustment,
so that it's a little bitter.
| | 00:54 | Now go up to the Report Layout
tools, and click on the Format Ribbon.
| | 00:58 | Let's click on this Dollar sign, which will
change our grand total to Currency format.
| | 01:03 | It put on a Dollar sign, a comma for
the thousandths and two decimal places.
| | 01:07 | Click on the subtotal right above
it, and make that Currency as well.
| | 01:12 | To the left, you'll see another
grand total, this time for the prices.
| | 01:16 | Now that's not meaningful, but we
can right-click on it, go to Totals and
| | 01:21 | change it to Average.
| | 01:24 | Now we see the Average for our orders;
| | 01:27 | however, it also gave us the
subtotal average for each customer, and we
| | 01:31 | don't need to see that.
| | 01:32 | Click on that cell and press Delete on your
keyboard to remove it, but that left a line.
| | 01:37 | So click on the cell again, and
go over to your Property Sheet.
| | 01:41 | If your Property Sheet is not open, go over
to the Design tab and choose it from here.
| | 01:47 | Click over on the Format section of
the Property Sheet, and look for Gridline
| | 01:52 | Style Top and change it
from Solid to Transparent.
| | 01:56 | Now it'll also be handy to see the
total Quantity for the bottles ordered
| | 02:00 | by customer as well.
| | 02:02 | So click on one of the customers, and
come up here to Totals and choose Sum.
| | 02:10 | Scroll down to the bottom of the page
and click on the grand total Quantity.
| | 02:17 | Hold the Shift key down and
click on the subtotal Quantity.
| | 02:21 | Go to the Format tab and give them Comma
format, which will put a comma in our thousands.
| | 02:28 | Then come over and Decrease the Decimal
two times, so that it's a whole number.
| | 02:32 | Go back to the Design Ribbon, and toggle over
to the Report View, and scroll up to the top.
| | 02:40 | That looks great.
| | 02:42 | Not only can we see who ordered what,
but we can see the total quantity of
| | 02:46 | bottles they've ordered from
us and their payments, as well.
| | 02:50 | Save your report again.
| | 02:52 | Report Totals allow us to do
statistical analysis right inside our printouts.
| | Collapse this transcript |
| Adding conditional formatting and data bars to a report| 00:00 | When reading a report, it's helpful
to instantly be able to identify any
| | 00:04 | unusual numbers or data.
| | 00:05 | Conditional Formatting allows you to
change the appearance of your results
| | 00:09 | according to the criteria you specify.
| | 00:12 | One method, Data Bars, even gives you
a graphical comparison of your values.
| | 00:17 | Scroll down in the Navigation pane,
right-click on Products Report and
| | 00:22 | choose Layout View.
| | 00:24 | As we look at our products, we'd like
to see our most profitable product lines.
| | 00:28 | Let's turn any profits bold
and green if they're over $7.
| | 00:32 | Click on one of the profits, and go up
to the Report Layout tools and the Format
| | 00:37 | Ribbon, and click on Conditional Formatting.
| | 00:41 | You have the ability to set multiple
rules for multiple fields without ever
| | 00:45 | leaving this window.
| | 00:46 | Let's leave this on what we
clicked, and click on New Rule.
| | 00:51 | Leave the default on top, check
the values in the current record.
| | 00:55 | You can choose between the Field Value
or an Expression, which is a calculation.
| | 01:00 | We'll leave that on Field Value.
| | 01:02 | Change the between to greater than
or equal to, and type in the number 7.
| | 01:07 | We'll change this to Bold, drop down
the Color, and choose this dark Green 5
| | 01:14 | right here, and click OK.
| | 01:18 | Move the window over so that
we can see, and click Apply.
| | 01:24 | I can see two values over
$7 turn green right here.
| | 01:27 | Now, let's try a different Conditional
Format called a Data Bar, to compare the
| | 01:32 | costs of our products.
| | 01:34 | Click on the top dropdown and change
it to Cost, and then choose New Rule.
| | 01:40 | Change the Rule Type to
Compare to other records.
| | 01:43 | If we put a check mark in this box, it would
show the bars and not show us the numbers.
| | 01:48 | Next, we'll set the values for the
Shortest Bar and the Longest Bar.
| | 01:52 | You can pick the Value,
the Number or the Percent.
| | 01:57 | We'll leave those as is on
Lowest Value and Highest Value.
| | 02:01 | Change the Bar color to green.
| | 02:03 | Let's choose Green 4, just
above the last color that we used.
| | 02:08 | Click OK, and then click OK again.
| | 02:12 | Now, you can instantly see which of our
products don't cost us much, and which
| | 02:16 | of them cost us a lot.
| | 02:18 | Click up here to save the report again
Go to the Design Ribbon and toggle the
| | 02:24 | View to Report View.
| | 02:27 | Click on one of the products.
| | 02:29 | As you can see, using Conditional
Formatting with Data Bars in Reports gives
| | 02:34 | you visual clues into interpreting your data.
| | Collapse this transcript |
| Creating multi-table reports| 00:00 | You can also make reports using
more than one table as a source.
| | 00:03 | We'll make a report listing information
about our SalesReps, and their customers.
| | 00:08 | Click on the Create tab and
then on the Report Wizard button.
| | 00:12 | From the dropdown, select the Sales Reps Table.
| | 00:16 | Click on the EmpID and the arrow to
move it over, LastName and FirstName, then
| | 00:21 | drop down the table selector
again and scroll up to Customers.
| | 00:26 | From here, choose CustID, the Company,
their State, and scroll down a little bit
| | 00:33 | to Type.value, which will tell us if
they are a retail establishment or a
| | 00:38 | restaurant, and click Next.
| | 00:41 | Access automatically recognizes the one
-to-many relationship between SalesRep
| | 00:45 | and Customers, so it groups the report for you.
| | 00:48 | On the next screen, we
can also add another group.
| | 00:51 | Go ahead and choose Type.value and move it over.
| | 00:55 | That will group all of the restaurant
customers and all of the retail customers.
| | 01:00 | Click Next, drop it down, and let's sort
by the Company Name. Click Next again.
| | 01:05 | We're going to lay out our report as
a Block report in Landscape view, and
| | 01:12 | we'll leave on the check mark so
that it Adjusts the field width to keep
| | 01:15 | everything on one page. Click Next.
| | 01:17 | We're going to change the name of our report
to Sales Reps and Customers. Click Finish.
| | 01:30 | Now we have a great report, with info
about our Reps, and we can see their
| | 01:34 | customers grouped by
restaurants and retail all in one place.
| | 01:40 | Multi-table Reports show us the
relationships between your data in
| | 01:44 | a hierarchical view.
| | Collapse this transcript |
| Creating mailing labels| 00:00 | It's easy to create a sheet of
mailing labels from any of your tables.
| | 00:04 | Click one time on Customers, go to the
Create Ribbon, and then over here to Labels.
| | 00:09 | It even has its own button.
| | 00:12 | You can select the manufacturer
of the box of labels that you have.
| | 00:15 | I'm going to use Avery, and then from
the list of Avery, I'm going to choose
| | 00:20 | 5160, which is a standard label that you can
find in any Office Supply Store. Click Next.
| | 00:27 | Here, you can change the Font that you want.
| | 00:29 | We're going to leave that on Arial.
| | 00:31 | You can change your Font Size.
| | 00:33 | Let's go ahead and make this 9.
| | 00:35 | You can change your Font Weight,
anything from Thin to Heavy.
| | 00:41 | We're going to make ours Normal.
| | 00:43 | You can change your Text Color to any
color that you'd like, but because we're
| | 00:47 | on an black and white printer, we're
going to stick to Black, and click OK.
| | 00:51 | If you wanted you could also select
Italics and Underline, but we are going to
| | 00:55 | leave those off. Click Next.
| | 00:58 | Now we're going to arrange our label.
| | 01:00 | You have to put in all the commas,
punctuation and spacing yourself.
| | 01:05 | There's two ways to move the fields over:
| | 01:07 | I can click on Company and press this arrow,
tap Enter on your Keyboard to go to the next line;
| | 01:13 | I can also double-click
on a field to move it over.
| | 01:15 | Go ahead and hit Enter again
after Address1 and move over Address2.
| | 01:21 | Hit Enter again to go to the last line.
| | 01:23 | Move City over, and type a comma and a space.
| | 01:28 | Move over State, type another
space yourself, and move over ZIP Code.
| | 01:33 | You could move over Country as well. For the
sake of this demonstration, I'm not going to.
| | 01:39 | Click Next.
| | 01:40 | Now it wants to know how
you want to Sort your labels.
| | 01:43 | If you're going to be sending them by
bulk mail, do choose Zip Code and move
| | 01:48 | that over. Then choose Next.
| | 01:50 | We're going to change the name of our
report to Customer Labels,
| | 01:57 | and let's click Finish.
| | 02:00 | I didn't get an error message, but let's
take a look and see if everything fits okay.
| | 02:05 | Click OK.
| | 02:06 | My labels actually looked pretty good.
| | 02:10 | The Mailing Label Wizard is a welcome
shortcut to laying out Mailing Labels
| | 02:14 | with ease.
| | Collapse this transcript |
| Printing reports| 00:00 | Once you've made your reports,
you will want to print them.
| | 00:02 | Here's what you need to Know.
| | 00:04 | Scroll down to your Navigation Pane,
and double-click on your Orders by
| | 00:07 | Customer Report to open it.
| | 00:10 | Click on the File tab to go to
Backstage view. Click on Print.
| | 00:15 | You have three options:
| | 00:17 | Quick Print will send one
copy straight to the printer;
| | 00:20 | Print will open up a dialog
box to give you printing options -
| | 00:24 | click Cancel. Go back to the File tab
to the Backstage view to print again;
| | 00:30 | Print Preview will send you back to
the same print options that you saw under
| | 00:34 | Report Layout and Design views.
| | 00:36 | Now notice here that you have the
ability to Export into a PDF, e-mail the
| | 00:41 | report directly and more options.
| | 00:45 | When you click Print, either from here
or from the Backstage view, you get a
| | 00:48 | Standard Windows Print dialog box where you
can choose your Printer and number of copies.
| | 00:54 | Click OK, and you've got a copy of your report.
| | 00:57 | Going to Print Preview first gives you
a lot of options for how your printout
| | 01:01 | will look as well, as additional output
formats. Otherwise, printing in Access
| | 01:06 | works just as it does in other applications.
| | Collapse this transcript |
|
|
9. Putting Data to WorkPivotTables| 00:00 | PivotTables allow you to interactively
analyze data along two or more dimensions.
| | 00:05 | Let's take a look at our
Profit Margin by product.
| | 00:08 | Click once on the Products table. Then
go up to the Create tab and then click on
| | 00:13 | the More Forms button.
| | 00:14 | Drop it down, and choose
PivotTable at the bottom.
| | 00:18 | In the Show/Hide group on the left-hand
side, click on Field Lists until you get
| | 00:22 | a PivotTable Field List on the right.
| | 00:24 | You have a screen with several
zones for you to drag your fields.
| | 00:28 | Click on Product and drag it to the left-
hand zone, where it says Drop Row Fields here.
| | 00:34 | This will group all of our
Olive Oils by Olive type.
| | 00:36 | Pick up Size and drag it where
it says Drop Column Fields here.
| | 00:41 | This will now show you all the
different sizes of each bottle, and drag Profit
| | 00:46 | into the center area.
| | 00:48 | You can now see the Profit Margin for
each bottle size in the grid. Also drag
| | 00:52 | Profit over to this Grand Total,
and we can see our Total Profit.
| | 00:56 | Now when I do that, it does open up a
Subtotal and a Grand Total, which we don't
| | 01:01 | need, so come up here to the Hide
Details button, and that part closes.
| | 01:06 | Now not only is this easier to compare
that in the product list, but you can
| | 01:10 | also see which size is profitable,
the 128 ounce bottle, but you can also
| | 01:15 | see which oil itself:
| | 01:17 | the Picholine and the Picual.
| | 01:20 | If you want to hide different rows or
columns, drop down the Header and turn on
| | 01:25 | and off the check marks that
you want to see, and click OK.
| | 01:29 | This arrow here will turn Blue to
show you that you have a filter applied.
| | 01:33 | Click on the Blue arrow again.
| | 01:35 | Click on All, and they will all come back again.
| | 01:37 | Now PivotTables are interactive.
| | 01:40 | You can pick fields up and drag
them around to different places.
| | 01:44 | We're going to show you how we
came to the sum of the profit.
| | 01:47 | Click on Price and pick it up and drag
it next to Sum of Profit, and it will now
| | 01:52 | add Sum of Price under each size.
| | 01:55 | Do the same thing with cost. Drag it up
and put it between Price and Profit, and
| | 02:01 | now I can see, for each of my olive oils,
how much we charge altogether, how much
| | 02:05 | they cost altogether and their Profit Margin.
| | 02:09 | If you want make a PivotTable that
sourced by information from several different
| | 02:12 | tables, first make a query pulling
those particular fields into one place.
| | 02:17 | Then use that query as the source
of the PivotTable. As you can see,
| | 02:21 | PivotTables group your data into a
grid, allowing you to analyze your
| | 02:24 | information along two or more dimensions.
| | Collapse this transcript |
| PivotCharts| 00:01 | Frequently, it's easier to look at
your data visually then it is to analyze
| | 00:04 | numbers in a report.
| | 00:05 | Access 2010's PivotCharts allow
you to make charts of your data in an
| | 00:09 | interactive interface.
| | 00:11 | Let's look at the same
product table, this time as a Chart.
| | 00:14 | Click once on Products, go to the
Create tab, come over to More Forms, drop it
| | 00:19 | down and choose PivotChart.
| | 00:22 | In the Show/Hide Group, on the left-hand
side of the Ribbon, click on Field List
| | 00:26 | until you get a list on the left.
| | 00:28 | Drag Product down across the bottom
where it says Drop Category Fields Here, and
| | 00:34 | then drag Profit here where
it says Drop Data Fields Here.
| | 00:39 | We now see a column chart breaking down the
total Profit margin by type of oil for all sizes.
| | 00:45 | If we right-click on Sum of
Profit, we can also change it.
| | 00:48 | Hold your cursor over
AutoCalc and drag down to Average.
| | 00:52 | Now we're looking at our
Average Profit by Product.
| | 00:56 | If we want to break them out by
size, we can do it in two ways.
| | 01:00 | First, drag Size, down here, next to
Profit, and it will split the products
| | 01:05 | into all five sizes.
| | 01:07 | Now you can see everything at once.
| | 01:10 | But if that's too much info at once,
let's take a different approach.
| | 01:13 | Drag Size from here up to the
Drop Filter Fields Here and let go.
| | 01:19 | Now if nothing appears, click over on
the Design View button and then toggle it
| | 01:23 | back again, and your chart will redraw itself.
| | 01:26 | Click on the Size dropdown, and you
can turn off the check marks for the ones
| | 01:29 | that you don't want to see.
| | 01:31 | The blue triangle indicates
that you do have a filter applied.
| | 01:35 | Click on All to bring them
all back into the equation.
| | 01:39 | You can even switch the two.
| | 01:41 | Drag Size down to the bottom,
and pull Product up to the Filter.
| | 01:47 | Now I can see my bottles by Size and
choose which oil type I would like to see.
| | 01:52 | That's why PivotCharts are interactive.
| | 01:54 | So as you can see, Pivot tables group
your data graphically, allowing you to
| | 01:59 | analyze your information in
a completely flexible manner.
| | Collapse this transcript |
|
|
10. Automating with MacrosCreating macros| 00:01 | Macros are series of actions that can be
triggered by data entered, or buttons on forms.
| | 00:05 | They're incredibly useful
for automating your database.
| | 00:08 | Macros are very complex, and an entire
course could be written just on this topic.
| | 00:13 | In this lesson, I'll introduce you to the
Macro Designer, and we'll create one basic action.
| | 00:18 | We'll create a Macro that will open an order
form and move to the Customer field within it.
| | 00:23 | To create a Macro, click on the Create
button and then click on Macro, on the far
| | 00:27 | right. The Macro Designer window opens.
| | 00:30 | There's one Action field to get you
started, and here's a button to toggle on and
| | 00:35 | off the Action Catalog, which
lists all the available macros.
| | 00:39 | If you click on Show All Actions, you'll see
additional Macros here beyond the basic set.
| | 00:44 | We want the command OpenForm.
| | 00:47 | Instead of searching around to find it,
I can click in Search right here and
| | 00:51 | type Open, and it will bring up all the Macros
that use open in the title. We want OpenForm.
| | 00:58 | I can either drag it into my
Macro or double-click on it.
| | 01:04 | The Macro appears with all the
parameters and arguments it needs to run.
| | 01:08 | The form I want to open is the Orders Subform.
| | 01:12 | Use this dropdown to find it.
| | 01:15 | The view that we want is form.
| | 01:16 | We are not going to add in
any filters or conditions.
| | 01:21 | The Data mode allows you to choose
whether it's going to open up in Add - which
| | 01:26 | will start with the blank record,
| | 01:28 | Edit - which will show you all the
records so that you can change them, or Read
| | 01:32 | Only - so you can view the data
but not make any changes to it.
| | 01:36 | We want Add, and then our
Window mode will be Normal.
| | 01:41 | Now, run the Macro and see what it does.
| | 01:43 | Come up here to the exclamation point.
| | 01:45 | When it asks you to save, say Yes.
| | 01:48 | And we're going to call it, in all
lowercase letters, neworder, and then click OK.
| | 01:54 | So now we have an open orderform, but
it would be handy to have it skip down
| | 01:59 | to the Customer field, since the Order
field will Auto Fill, and the Date will
| | 02:02 | usually be correct.
| | 02:04 | So let's right-click and close this
form and go back to our Macro, and in the
| | 02:10 | dropdown under the first step,
let's create a second step.
| | 02:14 | Click on it, scroll down and choose GoToControl.
| | 02:18 | Remember from the Forms lesson that a
control is a field on the form. Enter in
| | 02:23 | a square bracket, and type
"Customer," the field on the form.
| | 02:30 | Save the Macro again. Go back
to the Design Tab and run it.
| | 02:36 | And it opens up the orderform and
jump straight down to the Customer field.
| | 02:40 | In the next lesson, we'll see how to
activate that Macro in the database.
| | 02:45 | Macros are a complex but powerful way
of forcing Access to do your bidding,
| | 02:49 | allowing you to program desired
actions for a variety of triggers.
| | Collapse this transcript |
| Attaching macros to objects| 00:00 | Once you've made your Macros, you need
them to be triggered in your database.
| | 00:04 | One of the most common methods of activating
a Macro is to assign it to a button in a form.
| | 00:09 | Now that we've built a Macro to open an
Order form, let's use it in our database.
| | 00:14 | Click on the Queries header to
collapse it, so we can see our Forms.
| | 00:18 | We have a Products Split Form.
| | 00:19 | That allows us to look at our products.
| | 00:22 | I can imagine a sales rep looking at
this form to see the prices, and wanting to
| | 00:26 | place a customer's order right from here.
| | 00:28 | We can put a button on
the Form up, in the header.
| | 00:30 | That'll open up a blank Order Form.
| | 00:33 | Click up on the View button to
toggle it to Layout View, and let's make a
| | 00:37 | place for the button.
| | 00:38 | Click one time on the Product name.
| | 00:40 | Now a button will always
fill up the cell that it's in.
| | 00:43 | So we're going to split
this cell into three parts.
| | 00:46 | Go up to the Arrange tab and choose
Split Horizontally, and it splits it in half.
| | 00:53 | Click on the right half, and
Split it Horizontally again.
| | 00:56 | Now we have a perfect place for our button.
| | 00:58 | Go back to the Design tab.
| | 01:01 | Click on the fourth icon for a Button,
and then hold your cursor over that new
| | 01:05 | cell that we created.
| | 01:07 | Click on it, and a Wizard will open up, so
that we can choose the action for our button.
| | 01:11 | Click on Miscellaneous, and
then Run Macro and then click Next.
| | 01:17 | Choose the name of our
Macro, newOrder, and click Next.
| | 01:21 | Now we can either put Text
on our button or a Picture.
| | 01:24 | We can click on Show All Pictures to
show all the different options that we
| | 01:28 | have, but we're going to use Text.
| | 01:31 | Click up on Text, and change the button
name to Place New Order and click Next.
| | 01:39 | Now we're going to change the
button name itself to PlaceOrder.
| | 01:43 | This doesn't show on the form, but
you may see it in the programming.
| | 01:47 | Click Finish, and now we have a Button.
| | 01:50 | If you'd like to change its appearance,
go to the Format tab and choose Quick
| | 01:55 | Styles, and let's choose
this green one down here.
| | 01:58 | We can also choose a shape.
| | 02:00 | Click on Change Shape, and make it a
Rounded Rectangle. Let's try it out.
| | 02:05 | Go back to the Design tab, and to the
Form View, and click on Place New Order.
| | 02:10 | The Order form opens and jumps
straight to the Customer field. You did it.
| | 02:15 | In addition to buttons, Macros can
also be assigned to be triggered when
| | 02:18 | entering data into Forms and Tables.
| | 02:21 | Assigning a Macro to a Button is a
great way to trigger it from any form.
| | Collapse this transcript |
| Using data macros| 00:00 | A Data Macro allows you to perform an action
upon adding, updating, or deleting a record.
| | 00:06 | When a customer's order is marked as
complete, it would be nice to automatically
| | 00:09 | send them an e-mail letting them
know their olive oil has been shipped.
| | 00:12 | To do this, open up the Orders table.
| | 00:15 | Go up to the Table tools
Ribbon and choose Table.
| | 00:18 | Then click on After Update.
A Macro window will open.
| | 00:22 | In the first box, select If and type in
a square bracket to start a field name and
| | 00:28 | start typing OrderStatus.
| | 00:31 | You'll get a little dropdown with
the possible matches in your database.
| | 00:34 | We'll go ahead and double-click on
OrderStatus, and it puts it in there.
| | 00:38 | Type in = "complete".
| | 00:42 | The quotation marks show that it's a text value.
| | 00:46 | In the next box down, choose SendEmail.
| | 00:49 | A box with parameters will open.
| | 00:51 | In the To field, type in square bracket, Customers!,
another square bracket, and EmailAddress and end the bracket.
| | 01:06 | The first one tells us to look in
the Customers table. sSparated by the
| | 01:09 | exclamation point and
EmailAddress refers to the field.
| | 01:13 | Now, let's set up our e-mail.
| | 01:15 | In the Subject line, type in "Your
Two Trees Olive Oil has been shipped."
| | 01:20 | Then tab down to the Body of the message
and type in "Your order has been shipped.
| | 01:26 | Please let us know when it
arrives and how you like it."
| | 01:32 | When you are done typing,
click Save and then Close.
| | 01:35 | Let's test this out.
| | 01:37 | Go to the bottom of your Orders Form
and click the new blank record button.
| | 01:40 | It will start a new record.
| | 01:43 | Tab over, pick a customer, pick
a product, enter in a Quantity,
| | 01:50 | then tab pass Processed to
enter it in the first time.
| | 01:54 | Now, let's say our order has been shipped.
| | 01:56 | We'll drop down
Processed and change it to Complete.
| | 01:59 | When I tab off of the order, it opens
up a box saying, A program is trying to
| | 02:03 | send an e-mail message on our behalf.
| | 02:06 | Click on Allow, and it will now
send an e-mail to our customer.
| | 02:10 | Data Macros are a great way of
automating tasks based on adding, updating
| | 02:14 | or deleting records.
| | Collapse this transcript |
|
|
11. Using Other Applications with AccessImporting Excel and text data| 00:01 | One of the most efficient ways to bring
data into your Access file is to import
| | 00:04 | it from other programs.
| | 00:06 | Access has the ability to
import in several different formats.
| | 00:09 | Two of the most common are Excel
Spreadsheets and text, such as Comma
| | 00:13 | Separated Values, or CSV.
| | 00:16 | Let's create a new table based on an Excel file.
| | 00:19 | Click on the External Data
tab and then click Excel.
| | 00:23 | A new window will appear,
asking you to browse for your file.
| | 00:27 | Click on Browse, go to our Chapter 11
files and double-click on Olive Cost.
| | 00:34 | Now you have the option of creating a
brand-new table from scratch, adding the
| | 00:38 | file's data to an already
existing table, or linking the table.
| | 00:42 | If you add the records to a current
table, you will need to edit the Excel
| | 00:45 | spreadsheet so that the file names are
identical, the number of columns, the
| | 00:49 | spelling, the spacing, everything.
| | 00:51 | If you link to the table, you actually
create a live connection between the two.
| | 00:56 | Any changes made to your data, in either
program, will be reflected in the other.
| | 01:00 | Click OK.
| | 01:02 | If your Excel file has more than one
spreadsheet, Access will ask which one to use.
| | 01:07 | Click Next.
| | 01:08 | Now assuming that your row one contains
your field headings, select this check mark.
| | 01:13 | If it doesn't, it's actually worth
going back into your spreadsheet and
| | 01:16 | deleting the first few rows. Click Next.
| | 01:20 | You now have the opportunity to scan
through your columns and set Data Types right now.
| | 01:24 | Olive is a Text file; Cost/barrel is
Currency; Quantity, we will leave on the
| | 01:31 | default; and Total we will also make Currency.
| | 01:34 | You also have the option of skipping the
field if you don't want it in your Access file.
| | 01:39 | Click Next.
| | 01:40 | If you have a Primary Key field already,
you can choose your own primary key and
| | 01:45 | pick it off of this dropdown.
| | 01:47 | If you don't, Access will
add an auto number field.
| | 01:50 | We will go ahead, and we will
leave that there. Click Next.
| | 01:54 | Let's name our new table
Olive Cost, and then click Finish.
| | 02:00 | If you'll be repeating this exact
import frequently, you can save these steps;
| | 02:04 | otherwise, just click Close.
| | 02:07 | Now, in your Navigation Pane
you can see your new table.
| | 02:10 | Double-click on it to check it out.
| | 02:12 | Now, let's import a Text file.
| | 02:14 | The procedure is almost the same.
| | 02:16 | This time click on the Text File
button, go to the Browse button and
| | 02:21 | choose Olive Inventory.
| | 02:23 | We have the same options of creating
a new table, appending the records to
| | 02:27 | a table, or linking.
| | 02:28 | We are going to create a new table and click OK.
| | 02:31 | Now this file is a CSV, meaning
that commas separate the values.
| | 02:36 | So we'll stay with the first option, Delimited.
| | 02:39 | Fixed Width would be used if each column is
the exact number of characters. Click on Next.
| | 02:44 | Now it recognized that commas
separate our values, but if your data file is
| | 02:49 | different, it could also be tabs, or
semicolons, or spaces, or you can put in
| | 02:54 | your own punctuation mark, but as you
could see that ours definitely was commas.
| | 02:59 | Again, our First Row
Contains Field Names. Click Next.
| | 03:04 | Again, we can choose what Data
Type each column is. Olive is Text;
| | 03:07 | BarrelsOnHand, we'll leave as a Long Integer;
| | 03:11 | BarrelsOnOrder, we'll also leave as the default;
| | 03:13 | Cost, we will change to Currency, and click Next.
| | 03:18 | Again, we will let Access add
a primary key and click Next.
| | 03:22 | And we will leave the table name
as Olive Inventory, so click Finish.
| | 03:27 | We don't need to save our steps, so click Close.
| | 03:30 | And now we have our second table.
| | 03:32 | You can see that it's easy to import
data from other sources and convert it to
| | 03:36 | a table on the fly.
| | Collapse this transcript |
| Exporting data into Excel| 00:00 | You can move any of your data into an
Excel Spreadsheet with a minimum of effort.
| | 00:04 | Let's export our Customers table into Excel.
| | 00:07 | Click on it once. Then go
to the External Data Ribbon.
| | 00:10 | Here is our Export group, and
here is a button for Excel.
| | 00:14 | First, it'll ask you where to save your file.
| | 00:17 | Click on the Browse button to
set the location and the file name.
| | 00:20 | If you want to use it in Excel
2010 or 2007, leave it on this default.
| | 00:25 | But if you want it in versions 97 to
2003, choose that here, or you can choose a
| | 00:30 | version older than that.
| | 00:31 | We will make sure that it
says Excel Workbook. Click Save.
| | 00:36 | If you'd like to keep the same
formatting and layout, click this check box.
| | 00:40 | If you'd like Excel to open and display
the results automatically, click this box.
| | 00:45 | If you only want some records exported,
you can select them before starting the
| | 00:49 | Export, then use this option. Click OK.
| | 00:53 | And Excel opens up, and there it is.
| | 00:56 | See, it's easy to move data
out of Access and into Excel.
| | Collapse this transcript |
| Exporting to PDF| 00:01 | You can share your data with others,
whether they have Microsoft Office or not,
| | 00:04 | by creating PDFs, Portable Document Formats.
| | 00:07 | PDFs preserve all your formatting and layout.
| | 00:10 | Essentially, they are un-
editable snapshots of your object.
| | 00:13 | You can turn any Access objects into
a PDF, whether it's a Table, a Query,
| | 00:18 | a Form or a Report.
| | 00:20 | Let's export our Orders by
Customer report into a PDF.
| | 00:24 | Click on it once, and then
click on our External Data Ribbon.
| | 00:28 | In the Export group, we have a PDF button.
| | 00:31 | XPS, by the way, is a
Microsoft PDF alternative format.
| | 00:34 | Click on the button.
| | 00:36 | It'll ask you where you want to save
the file and what you want to call it.
| | 00:40 | Click Publish, and the file will open
up in Acrobat, Adobe Reader, or Preview,
| | 00:45 | depending which program you have it set for.
| | 00:47 | Creating a PDF is essentially as
simple as performing a Save As, but with
| | 00:51 | its own button.
| | Collapse this transcript |
| Exporting into a Word Mail Merge| 00:01 | If you want to use an Access table as
a data source for Microsoft Word Mail
| | 00:04 | Merge, you can either do that from
inside Word when you start to merge, or you
| | 00:08 | can initiate it from inside Access.
| | 00:10 | First, click on the table you
want to use as the data source.
| | 00:14 | Then click on the External
Data tab and choose Word Merge.
| | 00:19 | It will ask if you want to use an
existing document or start from a blank.
| | 00:23 | Using already existing documents is
perfect in a business setting, but for now
| | 00:27 | we'll choose a blank document. Click OK.
| | 00:30 | Word will open up automatically
and start the Mail Merge Wizard.
| | 00:33 | Let's maximize our window.
| | 00:34 | We will accept the defaults for
the first two steps of the Wizard.
| | 00:38 | On step three, you can see your
table already listed as the data source.
| | 00:42 | When I click on the Insert Merge
Field button, you'll see all the fields
| | 00:45 | from your Access Table.
| | 00:46 | I'll choose Company, and then I'll click on
Preview Results, and there is my first customer.
| | 00:51 | Now we are not going to do
the rest of the Mail Merge now.
| | 00:53 | To learn how, please see our Microsoft
Word 2010 Essential training, but it's
| | 00:57 | wonderfully convenient to start a Word
Mail Merge and select your data source
| | 01:01 | with just one button-click.
| | Collapse this transcript |
| Publishing to a web browser in HTML or XML| 00:00 | You can turn any Access object
into a Web page using HTML or XML,
| | 00:05 | whether it's a table, a form,
a report or a query result.
| | 00:09 | Let's first export a customer's table into HTML.
| | 00:12 | Click on it once, then click on the
External Data Ribbon and then over here to
| | 00:17 | the More button, you'll find HTML Document.
| | 00:20 | It will ask you where you want to save the file.
| | 00:23 | Click on Browse, if you want to
change the location or the name.
| | 00:27 | Let's put a check mark here to export
the data, including the formatting and the
| | 00:30 | layout, and we will put a checkmark here,
so that it opens up the Web browser,
| | 00:34 | so we can see our results, and click OK.
| | 00:38 | If you have a pre-created HTML template,
you can put a checkmark here to use it.
| | 00:41 | We're just going to click OK, and
here is our Web page with our data.
| | 00:46 | Now let's close it.
| | 00:50 | You'll see a window that asks if you
want to save your export steps, but we
| | 00:53 | don't need to do that, so click Close.
| | 00:56 | Now let's do the same
thing to create an XML copy.
| | 00:59 | Click on the Customers table once, and over
in the Export section, you'll see an XML button.
| | 01:04 | Again, if you need to change the name or the
location, you can browse and do so, and click OK.
| | 01:09 | It will ask you for a variety of
settings. Check the ones you need, and don't
| | 01:14 | forget about this More Options button down here.
| | 01:16 | Now, we're going to go with
all the defaults, so click OK.
| | 01:20 | It will ask us if we want
to save our export steps.
| | 01:23 | And we don't, so click Close.
| | 01:25 | Now let's navigate over to our
Documents folder where we have that saved.
| | 01:31 | Here's my XML file.
| | 01:33 | I will it open up, and there it is.
| | 01:36 | Now either of these two formats, the
HTML or the XML, would then need to be
| | 01:40 | integrated into your Web
site and published using FTP.
| | 01:44 | But as you can see, it's not hard at
all to create a Web page in HTML or XML
| | 01:48 | right from inside Access.
| | Collapse this transcript |
| Sharing via email| 00:01 | If you'd like to send any part of your
Access table to someone else over e-mail,
| | 00:04 | it's amazingly easy.
| | 00:06 | You can even choose the format on the fly.
| | 00:08 | Click on the Table or Report you'd
like to send to the other person.
| | 00:11 | We will go down to the bottom and choose
our Products report, go to the External
| | 00:15 | Data Ribbon, and then right
here to the E-mail button.
| | 00:18 | It will ask you what Format to choose.
| | 00:21 | It can't actually send it as an
Access object because of security concerns.
| | 00:25 | You can send it in Excel Format, HTML,
an older version of Excel, a PDF, a Rich
| | 00:31 | Text Format, a Snapshot, a Text file or XPS.
| | 00:35 | We will go ahead and send it as a PDF,
and click OK. Access automatically
| | 00:41 | exports the file, converts the format,
and attaches it to an E-mail message, all
| | 00:46 | in one step. Address it, and
you're ready to go. Click Send.
| | 00:51 | Access makes it as easy as possible to
share your data or objects over e-mail, a
| | 00:55 | common task in today's business.
| | Collapse this transcript |
| Collecting data over email| 00:00 | If you'd like to have your customers
fill in their own information for your
| | 00:03 | database, you can send them an
interactive e-mail that will automatically
| | 00:07 | update the records.
| | 00:09 | It doesn't matter what kind
of e-mail program they have.
| | 00:11 | For example, let's say we want all of
our Sales reps to verify that we have the
| | 00:15 | correct contact info for them.
| | 00:17 | Click once on the SalesReps table.
| | 00:19 | Go to the External Data
tab and on the far right,
| | 00:22 | click on Create E-mail.
| | 00:24 | There is a set of instructions. Click Next.
| | 00:27 | Specify if you want normal HTML
formatting, or InfoPath. We want HTML.
| | 00:33 | Click Next.
| | 00:34 | Choose if you want them to create
new records for your database or update
| | 00:38 | already existing information.
| | 00:40 | We want to update their data. Click Next.
| | 00:42 | Now you can choose what
field do you want them to verify.
| | 00:46 | We will move them all over
but then move back, Flag.
| | 00:50 | If you want them to see labels
other than your actual field names, you
| | 00:54 | can modify them here.
| | 00:56 | I'll change Email to
Email Address and click Next.
| | 01:02 | We want to Automatically process the
replies and add data to the SalesReps
| | 01:05 | table, so make sure you have a
check mark here, and then we do want to check
| | 01:09 | Only allow updates to existing data, so that
new records don't get created. Click Next.
| | 01:15 | Select which field in your table
contains the Email addresses, and click Next, and
| | 01:20 | here you can edit the subject line in
the body of the message accordingly.
| | 01:30 | Click Next, and then click Next again.
| | 01:34 | In the last screen, you can
specify which people get sent the e-mail.
| | 01:37 | You don't need to send it to
everybody. You can turn off this check mark
| | 01:41 | here, and then just find the one that
you want and put a check mark in front of
| | 01:45 | them, and click Send.
| | 01:48 | Now, when we look out it in Outlook,
I have a new Email, Update your
| | 01:51 | Employee Information.
| | 01:53 | I will click Reply, and when I scroll
down, I can edit any of these fields.
| | 02:00 | Let's edit the address and
put in an apartment number.
| | 02:02 | I can see it in blue.
| | 02:04 | Check the rest of my fields, and click Send.
| | 02:09 | I even have an Access data collection
repository here where I can see the status
| | 02:13 | of all of my different messages.
| | 02:16 | Back in Access, I have a section
right here for Manage Replies, and you can
| | 02:21 | use this window to manage your data.
| | 02:22 | We will close it and open up our
SalesReps table, and when we look at Raul's
| | 02:27 | record, here's his apartment
number already updated for us.
| | 02:31 | Access's Email automation is a great
timesaver to make sure your records are
| | 02:35 | up-to-date, or to collect information
automatically instead of having to enter
| | 02:39 | it all manually.
| | Collapse this transcript |
| Using Package and Sign| 00:00 | Package and Sign allows you to share
your database with others and send a
| | 00:04 | digital signature with it.
| | 00:06 | E-mailing a database can be risky.
| | 00:08 | Macros are a known
method of transmitted viruses.
| | 00:11 | If you need to e-mail yours, you
should package it and apply your authentic
| | 00:14 | digital signature, so that the
recipient knows it's safe to open.
| | 00:18 | To do that, go to the Backstage view
by clicking on the File tab and then
| | 00:22 | down to Save & Publish.
| | 00:24 | Click on Package and Sign, and then Save As.
| | 00:27 | It will show you your digital signature, and
click OK, and it will now ask you to save it.
| | 00:34 | Go to the folder where you want to
keep the file, change the name if needed,
| | 00:40 | and then click Create.
| | 00:42 | Now if I go to Outlook and create a new
message, I can attach that file, and I
| | 00:48 | can see, right here in my File
List, that it has a different icon.
| | 00:52 | It has a little ribbon on it
indicating that it's been secured, and the
| | 00:56 | extension is a little bit different.
| | 00:57 | So instead of an Access DB, for
database, it's an Access DC, for Digital
| | 01:02 | Certificate, and then I can
simply address the message and send it.
| | 01:07 | Applying a digital signature to your
file before you share it will demonstrate
| | 01:10 | that it comes from an
authentic source and is not spam.
| | Collapse this transcript |
| Publishing to SharePoint| 00:01 | Now let's publish a database to
the Web using Access's Web services
| | 00:04 | featuring SharePoint.
| | 00:06 | It's a good idea to start with either
a blank Web database, or with one of the
| | 00:11 | sample templates that says
Web Database across the bottom.
| | 00:14 | While you can convert your already
existing database to SharePoint, it will have
| | 00:19 | some considerations.
| | 00:21 | Your ID fields must be autonumbers.
| | 00:23 | There are no cascading updates and deletes,
and you can't use any custom formatting.
| | 00:28 | We're going to go ahead and create a
Contact Web Database for our example.
| | 00:34 | So we have Contacts Web Database,
and go ahead and click Create.
| | 00:39 | It will prepare the template, and it
will look like this. We will have tabs across
| | 00:43 | the top here for our different tables.
| | 00:46 | Let's go ahead and add a
Contact to our Address Book.
| | 00:49 | So I will click on Add New, and I will
enter myself in, and I'll click Save and Close.
| | 00:56 | So now here is my first record in my database.
| | 01:00 | Let's go ahead and publish it.
| | 01:01 | I am going to go up to the Backstage
View by clicking on the Red File tab, and
| | 01:05 | then down here to Save & Publish, and
click on Publish to Access Services.
| | 01:11 | Here's the compatibility checker that
you do want to run before publishing.
| | 01:15 | It's going to ask me to close my
objects, and I'll say Yes, and it says this
| | 01:19 | database is compatible with the Web.
| | 01:21 | If you are converting your own database,
it will probably turn red and give you
| | 01:25 | a list of issues that you will need to resolve.
| | 01:28 | Once it is compatible and ready to go,
down here, type in your SharePoint URL, and
| | 01:35 | my database will be called Contact Web Database.
| | 01:38 | I will go ahead and click
Publish to Access Services.
| | 01:43 | It'll take a minute to create all of
my objects, and set up the Web site.
| | 01:49 | Once it comes up, it may
not be available immediately.
| | 01:51 | You may have to wait a
moment for it to finish resolving.
| | 01:56 | So here's the link for the Web site.
| | 01:58 | I will go ahead and click on it.
| | 02:01 | It wasn't quite ready yet, so I will go
ahead, and I will refresh it, and there it is.
| | 02:09 | So here is my Address Book and here
is my first record, Alicia Katz Pollock.
| | 02:14 | Now what makes this so practical
is any changes that I make here,
| | 02:18 | I'll click on Edit Details, and let me
add in our lynda.com Web site, and I'll
| | 02:27 | click Save and Close, and I
will go back to Access down here.
| | 02:32 | Here is the dialog box
that was open. I'll close it.
| | 02:36 | I'll enable the content, and here I am
back in my contact, and low-and-behold it
| | 02:43 | already says lynda.com down there.
| | 02:45 | The changes were instantaneous.
| | 02:46 | So you can see how practical it is to
use SharePoint as a way of sharing your
| | 02:51 | database over the Internet.
| | 02:52 | Just keep aware that not all standard
database techniques are going to work
| | 02:56 | over the interface.
| | Collapse this transcript |
| Importing and exporting with SharePoint| 00:01 | In the previous lesson, we
demonstrated how to publish a SharePoint Website.
| | 00:05 | Here are several additional
places across Access where you can use
| | 00:07 | SharePoint Publishing tools.
| | 00:10 | When you go to the Create tab and
want to create a Table, you can create a
| | 00:13 | specific SharePoint List.
| | 00:15 | When you go to the External Data tab and
to the Import section, you can click on
| | 00:19 | the More button, and import a SharePoint list.
| | 00:23 | When you Export data, you can Export
directly to a SharePoint list, and in the
| | 00:28 | Database tools there's a function for
moving your data in and out of SharePoint.
| | 00:33 | There are also built-in Data Caching and
Synchronization tools to ensure that if
| | 00:37 | you're offline while working on
your Access database, the files will
| | 00:40 | automatically sync as soon as you
do connect back up to SharePoint.
| | 00:44 | Hosting your database on a SharePoint
Server makes it easy for you to publish
| | 00:47 | content from your database to
anyone in your organization.
| | Collapse this transcript |
|
|
12. Advanced Tools in the DatabaseCompacting and repairing a database| 00:00 | It's important to do maintenance
on your database once in awhile.
| | 00:03 | Compacting Repairing your database keeps it
running smoothly and minimizes the file size.
| | 00:08 | If you find that your database is
slowing down or not functioning smoothly,
| | 00:12 | Access may need a repair, or when you
delete records out of your database Access
| | 00:17 | does not reclaim the disk space, and
your file size does not get smaller.
| | 00:21 | To solve both of these issues,
perform a Compact Repair on it.
| | 00:25 | You can do that from two different
places. One is in the Backstage View
| | 00:29 | under the File tab.
| | 00:30 | When you click on Info, you can
see Compact and Repair right here.
| | 00:34 | The other is on the Database tools Ribbon.
The button is right here.
| | 00:38 | When I click on it, Access will trim down
the file size and keep itself running smoothly.
| | 00:43 | Be sure to do a Compact and
Repair periodically, especially after
| | 00:46 | large deletions.
| | Collapse this transcript |
| Using data analysis tools| 00:00 | When you're designing a database,
it's worth a look at Access's Database
| | 00:03 | Analysis tools to be sure
you're on the right track.
| | 00:06 | On the Database Tools Ribbon,
there's an Analyze Group with three tools
| | 00:10 | you might find useful.
| | 00:12 | The Database Documenter writes out
all the information about your objects.
| | 00:16 | Click on the object you want, or click on
Select All to run the analysis on all at once.
| | 00:22 | When you click OK, you'll get a document
showing all the properties of that object.
| | 00:26 | Click Close Print Preview to close it.
| | 00:29 | The second button, Analyze Performance,
will look for tables with earned field
| | 00:33 | properties and other
nonstandard database elements.
| | 00:37 | Click on one or Select All to
click on all of them, and click OK.
| | 00:41 | When you get suggestions, you can take
them or you can delete them. Click Close.
| | 00:48 | The third button, Analyze Table, will
walk you through an analysis to make sure
| | 00:52 | that your tables are constructed properly.
| | 00:56 | After you've constructed your database,
but before you begin using it, try out
| | 01:00 | the Database Analysis tools to
uncover any issues that need correction.
| | Collapse this transcript |
| Encrypting a database and setting a password| 00:00 | If your database contains confidential
information, it's important to keep it secure.
| | 00:05 | Encryption converts your data into
code to prevent unauthorized access.
| | 00:09 | A password requires the user to
enter a code before it can be used.
| | 00:13 | To use these features, you first have
to open the file in Exclusive mode, so
| | 00:17 | that Access is positive that
you're the only user who has it open.
| | 00:21 | Go to File and close the database.
| | 00:25 | Then use the Open button to open it up again.
| | 00:29 | Click once on the file you want, but
on the Open button, use the dropdown and
| | 00:34 | tell it Open Exclusive.
| | 00:36 | Once it has been reopened, click on the
File menu to go to the Backstage View,
| | 00:41 | and click on Info, and here is
Encrypt with Password. Click on it.
| | 00:46 | You'll enter a password and then
retype it, but be warned, do not forget or
| | 00:51 | lose this password.
| | 00:52 | There is no way to recover it, and your
database will be unusable if it's lost.
| | 00:56 | Enter in the password, lynda,
hit Tab, type it again, and click OK.
| | 01:09 | You may get an error message,
but just go ahead and click OK.
| | 01:12 | Now go up to File again and close the
database, and then go to Recent and choose
| | 01:19 | the top one on your list,
Password ,and it will ask for a password.
| | 01:23 | Try typing in something incorrect, and click
OK, and it tells you, Not a valid password.
| | 01:28 | Click OK, and type in the proper one,
l-y-n-d-a. Click OK, and you're in.
| | 01:35 | If you want to take your password off
your database, you have to reopen it again
| | 01:39 | in Exclusive mode, just as we did before.
| | 01:42 | I'll close the database.
| | 01:44 | We'll click on Open, click once on the
file, go back down to the Open button to
| | 01:49 | the dropdown, Open Exclusive, and
it'll ask for the password again.
| | 01:56 | Then go back to the File menu, back to
the Info Ribbon, and decrypt the database.
| | 02:03 | It'll ask for the password one last time,
l-y-n-d-a. Click OK, and then it will
| | 02:08 | be unencrypted again, and have no password.
| | 02:12 | So to keep your database secure from
unauthorized users, consider using Encrypt
| | 02:16 | with Password, but again, be sure not
to lose it, or you will be unable to open
| | 02:20 | your file ever again.
| | Collapse this transcript |
| Splitting a database| 00:00 | If you have many users of your
database, consider converting to a Split
| | 00:04 | Database Architecture.
| | 00:05 | Your database can be divided into a
front-end database that contains all the
| | 00:09 | application objects, queries, forms,
reports, macros and models, and is linked
| | 00:14 | to tables stored in the Back-end
Share Database containing the data.
| | 00:18 | The back-end database can be stored in the
location shared by many users, such as a File Server.
| | 00:23 | The front-end database is distributed
to each user's desktop and linked to
| | 00:27 | the shared database.
| | 00:29 | Using this design, each user has a copy
of Microsoft Access installed on their
| | 00:32 | machine along with their Application database.
| | 00:35 | This reduces network traffic, because
the application is not retrieved for each
| | 00:39 | use, and it allows the front-end
database to contain tables with data that are
| | 00:43 | private to each user for
storing settings or temporary data.
| | 00:48 | The Split database design also
allows development of the application
| | 00:51 | independent of the data.
| | 00:53 | When a new version is ready, the front-
end database is replaced
| | 00:56 | without impacting the data.
| | 00:57 | Microsoft Access has two built-in
utilities: the Database Splitter and Linked
| | 01:03 | Table Manager to facilitate this architecture.
| | 01:07 | To create the Split Architecture,
click on the Database Tools Ribbon, and
| | 01:11 | then on the Access Database button.
Tell it to Split the database, and in the
| | 01:16 | Save Dialog box, your database will be named
with the BE on the end, short for back-end.
| | 01:22 | Click Split. Click OK.
| | 01:27 | Your database is now the front-end.
| | 01:29 | Notice all the tables have a blue arrow on them.
| | 01:32 | This indicates that they're now
linked tables, meaning that the data is not
| | 01:36 | stored here, but in another
file on your computer or network.
| | 01:40 | If you hold your cursor over each one,
you can see the absolute path to your
| | 01:44 | database back-end File.
| | 01:46 | In a Split database all the changes to
your data are synchronized instantly, but
| | 01:50 | changes you make to the
structure of the tables are not.
| | 01:53 | To update your tables in the back-end,
click on External Data, and there's a
| | 01:57 | button for a Linked Table Manager.
| | 02:00 | When you click it, it'll ask
which tables you want to update.
| | 02:04 | I'll click on Select All, and OK.
| | 02:08 | Once it's been refreshed, you'll have the
latest structure, and so will everyone else.
| | 02:13 | Splitting your database is an ideal way
to share it among multiple users without
| | 02:16 | having to spend precious
hours synchronizing each desktop.
| | 02:20 | In a Split database with a back-end
stored on the server, all your users will
| | 02:24 | have automatic access to the most recent data.
| | Collapse this transcript |
|
|
13. CustomizationCustomizing the ribbons| 00:00 | You can customize the Ribbons to enhance
your workflow, whether it's rearranging
| | 00:04 | buttons on existing Ribbons or
creating Ribbons of your own.
| | 00:07 | To make you work in Access as efficient as
possible, you can create your own Ribbons.
| | 00:12 | Click on the File tab Backstage view
and go down here to Options and then click
| | 00:18 | on Customize Ribbon.
| | 00:20 | Click the New Tab button, and
a New Tab and Group appear.
| | 00:23 | Click on New Tab and rename it, and
I'll call this Alicia, and then I need to
| | 00:30 | add at least one group on that
Ribbon, and I'll rename that one Faves.
| | 00:35 | You can have several groups on a Ribbon.
| | 00:36 | Now let's put our buttons on.
| | 00:39 | It starts with Popular commands.
| | 00:41 | I am going to choose Close Database
and Cut and Copy and Paste, and then I am
| | 00:49 | going to go down to All commands,
and I am going to scroll down and find
| | 00:53 | Conditional Formatting,
and bring that over as well.
| | 00:58 | If you change your mind, you can
also Reset your customizations.
| | 01:02 | Click OK, and here's my new Ribbon
with all of my favorite commands on it.
| | 01:08 | Creating your own Ribbons turns
Access into your own customized database
| | 01:11 | solution, with frequently used
commands all grouped together.
| | Collapse this transcript |
| Setting Access options| 00:00 | While using Access, become a power
user by familiarizing yourself with
| | 00:03 | Access's Option button.
| | 00:06 | To set your own Preferences while using
Access, go to the Backstage View via the
| | 00:10 | File tab, and click Options.
| | 00:14 | I'll walk you through some of
the settings that I find useful.
| | 00:17 | We'll start with General, up here at the top.
| | 00:19 | Live Preview is what changes your
data as you try different settings.
| | 00:23 | If you want to change your color scheme,
you have options beyond the Silver.
| | 00:27 | You can also change it to Blue or Black.
| | 00:29 | Here is the Default file
format for a new database.
| | 00:34 | Now notice there is no Access 2010 on this list.
| | 00:37 | Access 2010 actually uses the
same file format as Access 2007.
| | 00:41 | So this ensures backward compatibility.
| | 00:44 | If you don't want to save your databases
in your Documents folder, you can click
| | 00:47 | on Browse and find a new location.
| | 00:50 | Here, you can set your
Username and your Initials.
| | 00:53 | Now let's look on Current Database.
| | 00:56 | Application Title will change the
name up here, so if you are building a
| | 01:00 | database for a third-party user, you
can actually make it a custom application
| | 01:04 | by writing what you want the bar to
look like up here. An Application Icon
| | 01:07 | changes this icon right here.
| | 01:10 | Display Form is what opens
up when you start Access.
| | 01:12 | For example, we could have it
go to our Navigation Form.
| | 01:15 | That's the form that we created that
have the row of buttons to choose between
| | 01:19 | all of your different forms and reports.
| | 01:21 | That's a great place to start.
| | 01:24 | You can choose to display or not
display the Status Bar at the bottom.
| | 01:28 | Now it's set to Tabbed Documents so
that when you open up more than one object,
| | 01:32 | you get tabs across here.
| | 01:33 | You can also set that as Overlapping
Windows or display those tabs or not.
| | 01:39 | Compact on Close will reclaim disk
space every time you delete a lot of records.
| | 01:44 | If you're worried about Ssecurity, you
can remove your personal information from
| | 01:47 | the file properties every
time you save the database.
| | 01:50 | If you don't use the
Layout View, you can disable it.
| | 01:53 | Likewise, you can disable the design
changes and tables in Datasheet View. That
| | 01:57 | way nobody can change
what your tables look like.
| | 02:02 | Scroll down here to the Navigation
Pane and click on Navigation Options.
| | 02:07 | This is where you can
customize this pane right here.
| | 02:09 | You can make your own groups.
| | 02:12 | I'll click Cancel, and here you have
options for AutoCorrect, particularly
| | 02:17 | around people's names.
| | 02:19 | Now let's go to Datasheet.
| | 02:21 | This is how your tables appear.
| | 02:24 | You turn off either your
Horizontal or Vertical gridlines.
| | 02:27 | Right now, all these cells are Flat,
but you can make them Raised or Sunken.
| | 02:31 | You can make your Default column
width either bigger or smaller.
| | 02:34 | You can even change the Default font,
| | 02:36 | make the font bigger, change its
appearance including Underline, or Italic.
| | 02:41 | Now, let's go to Object Designers.
| | 02:44 | In the Table design view, maybe you don't
want your Default field type to be Text.
| | 02:49 | You can change it to any data type.
| | 02:52 | If you don't want your text fields to
start out at 255 characters, you can make
| | 02:56 | that at any specific numbers you'd like.
| | 02:58 | If you don't want your Default
number to be a Long Integer, you can change
| | 03:02 | that format as well.
| | 03:05 | When you are in the Query designer,
you can show your table names or not.
| | 03:08 | You can set your queries
Output all the fields in the table.
| | 03:12 | I like to leave on Enable Autojoin.
| | 03:14 | That makes sure that all my
relationships are set between my different tables
| | 03:18 | when I am making a query.
| | 03:19 | I can change my Query designed font right here.
| | 03:24 | Down here are error checkers.
| | 03:25 | If you find you get the same error messages
over and over again, you can turn these off.
| | 03:31 | Now let's click on Proofing.
| | 03:33 | This is where Access automatically
formats the contents of your database.
| | 03:38 | Here you have your AutoCorrect
options and your spelling errors.
| | 03:41 | Now these are settings
all the way across Office.
| | 03:43 | They don't just affect
Access. Next is Language.
| | 03:48 | If you use other languages in
addition to English, you can manage them here.
| | 03:52 | Now to the Client Settings.
| | 03:54 | This is the behavior of your database.
| | 03:56 | Right now, when you hit Enter,
it takes you to the Next fields.
| | 03:59 | You can also have it go down to the Next record.
| | 04:02 | Right now, when you click on a
field, it highlights the entire field.
| | 04:05 | You can also have it go to straight
to the beginning of a field, or straight
| | 04:08 | to the end of a field.
| | 04:09 | By default, your arrows take you to the
Next field, but you can also have them
| | 04:13 | go letter-by-letter through
the contents in your field.
| | 04:16 | You can have Access confirm your
Record changes and your deletions.
| | 04:21 | Down under Display, here you can
set your number of Recent Documents.
| | 04:25 | You can show your Status bar, Show
animations, and here you can turn off the
| | 04:31 | tags that pop up when you perform different
actions in the Datasheets and Forms and Reports.
| | 04:35 | Here, you can set your
default margins for your reports.
| | 04:39 | Here's a great check mark that opens up
the last database you're in, the next
| | 04:42 | time you open up Access.
| | 04:46 | Down here you can choose your Default Theme.
| | 04:47 | For example, throughout this
course, we've been using Foundry.
| | 04:52 | I could set it to Foundry automatically.
| | 04:56 | Customize Ribbon, we talked
about in a previous lesson.
| | 05:00 | The Quick Access toolbar, we also
spoke about in the previous lesson.
| | 05:05 | Add-ins are the utilities that
extend the functionality of Access.
| | 05:10 | Acrobat PDFMaker is a great one to have active,
and you can find others as well on the Web.
| | 05:16 | The Trust Center is where
you manage your security.
| | 05:19 | I am going to click right here on the
Trust Center Settings, and we are going to
| | 05:23 | start up here with Trusted Publishers.
| | 05:26 | If you work with different
companies, you can add them right here.
| | 05:30 | Trusted Locations are locations on
your computer, or on your network, where you
| | 05:34 | know that your files are safe.
| | 05:37 | Trusted Documents is where
you manage the security for your
| | 05:39 | individual documents.
| | 05:41 | If you are using documents on a network,
you can tell it never to give you a
| | 05:45 | security warning about those.
| | 05:47 | Also, if you want to clear the Trusted
Documents, you can do that right here.
| | 05:53 | Add-ins allows you to manage
the security for your Add-ins.
| | 05:57 | Here are settings for ActiveX,
Macros, and Data Execution Prevention.
| | 06:04 | This Message Bar allows you to turn
off the yellow bar that appears at the
| | 06:09 | top of some windows.
| | 06:12 | And here are your Privacy Options.
| | 06:15 | This first check mark connects you to
Office.com for updated content. That's
| | 06:19 | particular to your help files.
| | 06:22 | This one downloads a file to your
computer from Microsoft that helps
| | 06:26 | determine system problems.
| | 06:28 | This check marks signs you up for the
Customer Experience Improvement Program, and
| | 06:33 | it sends some of your
computer information to Microsoft.
| | 06:37 | This check mark will detect your Office
components when you go to Office.com.
| | 06:42 | This one will check your office
documents if you got them from suspicious Web
| | 06:46 | sites, and then this one allows the
Research pane to go online to get updates
| | 06:51 | to its Resource tools.
| | 06:53 | Click Cancel, and let's go back to General.
| | 06:56 | So to be as effective and efficient
with Access is possible, personalize your
| | 07:00 | application to work the way you do.
| | Collapse this transcript |
|
|
ConclusionGoodbye| 00:00 | Thanks for watching Microsoft Access
2010 Essential Training, and I hope you've
| | 00:04 | enjoyed building the Two
Trees Olive Oil database.
| | 00:07 | This course gave you a good foundation
of Access skills, and now you are ready
| | 00:10 | to begin building own custom database solution.
| | Collapse this transcript |
|
|
