IntroductionWelcome| 00:04 | Hi, I am Alicia Katz Pollock, and I
would like to welcome you to Access
| | 00:08 | 2007 Power Shortcuts.
| | 00:09 | In this course, I will show you dozens of
tips and tricks to deepen your Access expertise.
| | 00:13 | I'll show you navigation shortcuts, from
keyboard commands to hidden right-click
| | 00:17 | options, to speed up your database development.
| | 00:19 | You will learn how to customize the
ribbon in Navigation pane, turning your
| | 00:23 | database into a custom application.
| | 00:25 | I'll demonstrate table techniques and
special field properties to train you in
| | 00:28 | database architecture.
| | 00:30 | You'll learn a variety of tips for
building effective forms and reports, and we
| | 00:33 | will build action queries and event
macros to automate your database. Whether
| | 00:37 | you're new to Access, or an experienced
database designer, I will show you hidden
| | 00:41 | shortcuts and techniques to make you
a pro in Access 2007 Power Shortcuts.
| | Collapse this transcript |
| Using the exercise files| 00:00 | If you are a premium member of the
lynda.com Online Training Library, you have
| | 00:04 | access to the exercise files
used throughout this title.
| | 00:07 | The exercise files are in the exercise
files folder, which I have placed on the desktop.
| | 00:11 | You can start wherever you'd like.
| | 00:13 | There are files for most movies.
| | 00:16 | They reside in subfolders
named according to the chapters.
| | 00:20 | If you don't have access to the
exercise files, you can use files of your own
| | 00:23 | in their place.
| | Collapse this transcript |
|
|
1. Top TipsCustomizing the Navigation pane| 00:00 | The Navigation pane on the left hand
side has a few tricks up its sleeve so that
| | 00:04 | you can use it effectively for your workflow.
| | 00:07 | Let's take a look at its features.
| | 00:08 | First you can click this little double
arrow to collapse and open it to give
| | 00:13 | yourself some more real estate.
| | 00:14 | If you hold your cursor over the right
edge, you'll get a double-headed arrow
| | 00:18 | and you can make it wider or narrower as needed.
| | 00:22 | You can click the Section
headers to open and close them.
| | 00:26 | You can use the search box at the top
to quickly find what you're looking for.
| | 00:30 | For example, if I click in the box and
type the word Customers, you can find all
| | 00:36 | your database objects that
contain the word customers.
| | 00:39 | An added tip is that if you're clicked on any
object in the Navigation pane, pressing
| | 00:44 | Ctrl+F on your keyboard will jump
your cursor up to the search box window.
| | 00:49 | But that only works if you're already
clicked there and not somewhere else on the screen.
| | 00:54 | I'll click this little button to clear
my search and see all my objects again.
| | 00:58 | You can also Sort and Resort the
Navigation pane so it's useful for you.
| | 01:03 | Right now it's sorted by object type.
| | 01:05 | So all my Tables, my Queries, my Forms,
and my Reports are grouped together.
| | 01:10 | If I go to this little dropdown, this
arrow inside the circle, I have more options.
| | 01:16 | I like to use Tables and Related Views.
| | 01:19 | If I get a message, I will go ahead
and click OK and now it resorts itself.
| | 01:25 | Here's my Customers:
| | 01:26 | Table and then here are all the
Queries, Forms, and Reports based on it.
| | 01:32 | Below that is my Orders: Table and
all of its Queries, Forms, and Reports.
| | 01:37 | Now if you have forms and reports that
are based on more than one table, you may
| | 01:42 | see them in more than one place.
| | 01:43 | For example, my Orders by Customer
Report has elements of both my Orders table
| | 01:49 | and my Customers table.
| | 01:51 | So I see it in both places.
| | 01:52 | You can also reorganize by the date
that objects were created or by when
| | 01:57 | they were modified.
| | 01:59 | Things I did today, things I did last week.
| | 02:02 | The bottom half of the
dropdown also allows you to filter.
| | 02:05 | For example, let me change this back to
Object Type, so that I am back to seeing my
| | 02:10 | tables together and my
queries together et cetera.
| | 02:13 | I'll drop this down and now I can filter
it so that I am just looking at my queries.
| | 02:19 | There are all my queries
and everything else is hidden.
| | 02:22 | When I'm done, I'll go back to the
dropdown and choose All Access Objects, and
| | 02:28 | everything is back again.
| | 02:29 | Because the Navigation pane is so
flexible, you can organize it and re-organize
| | 02:34 | it any which way you need
depending on your task at hand.
| | Collapse this transcript |
| Creating new fields with field templates| 00:00 | Access 2007's New Fields button, allows
you to build your tables effortlessly by
| | 00:05 | utilizing common field sets
that work well together.
| | 00:08 | First I'm going to create a brand-new,
empty database, by going to the Office
| | 00:12 | button and choosing New. I'll give it the
file name, fieldtemplate, and click Create.
| | 00:18 | Now that I have a new file, it
puts me automatically in the Table
| | 00:23 | Tools > Datasheet ribbon.
| | 00:25 | On the left-hand side I'll click on
New Field, and a list appears on the
| | 00:29 | right, it starts with the
section of Basic Field types.
| | 00:33 | Single Line of Text refers to a
regular text box, and Multiple Lines of
| | 00:37 | Text refers to memos.
| | 00:38 | Then we have the rest of
our standard data types.
| | 00:41 | Below that are a number of built-in fields
with their own names, datatypes, and field sizes.
| | 00:47 | Go down to the Contacts and double-
click on Last Name and then First Name, next
| | 00:52 | choose City and State and Province.
| | 00:55 | Let's say we wanted to have a set of
start and end dates, for our involvement
| | 00:58 | with this customer.
| | 00:59 | Scroll down to Projects and choose
Begin Date and End Date. Two fields are
| | 01:04 | inserted. They're automatically given
the format Short Date. And the Begin Date
| | 01:10 | even has today's date inserted automatically.
| | 01:12 | By taking advantage of the New Fields
button, you'll save several steps when
| | 01:16 | creating your tables.
| | Collapse this transcript |
| Toggling between Design and Data views| 00:00 | When you are building a database,
you'll frequently switch back and forth
| | 00:03 | between the design view, where you build a
structure of a table form or report, and
| | 00:08 | its data view, where you actually use it.
| | 00:10 | Access gives you a variety of methods
of moving back and forth between the two.
| | 00:14 | When you double-click on an object,
let's say the Products table, from the
| | 00:17 | Navigation pane, it defaults to
opening it in data view, handy for working on
| | 00:22 | your data immediately.
| | 00:23 | If you need to switch to the design
view to work with the structure of that
| | 00:26 | table, go up to the very first button
on the Home ribbon, there's an architects
| | 00:30 | triangle, click on it, and it
takes you to the design view.
| | 00:34 | To go back to the datasheet,
simply click on that button again.
| | 00:37 | You will notice that the
button has a dropdown underneath it.
| | 00:40 | Click on that dropdown and you
also have the options to switch to a
| | 00:43 | PivotTable View and a PivotChart
View. Those are available when you're
| | 00:47 | working with a table.
| | 00:48 | Now let's open up the form.
| | 00:49 | I will go down to my Customers Order
Form and let's see how the dropdown
| | 00:54 | arrow works on forms.
| | 00:56 | The top button toggles me back and
forth between Layout View and Form View, the
| | 01:01 | dropdown also gives me access to Design View.
| | 01:04 | So as you can see, each of the
different object types, Tables, Forms, Queries,
| | 01:10 | and Reports will all have two main
views to toggle with the upper half and a
| | 01:14 | wider variety to choose from the dropdown.
| | 01:16 | But that's just the first
way to move between the two.
| | 01:19 | Here are a few more.
| | 01:20 | Look down in the lower right
hand corner of your window.
| | 01:23 | There are little buttons down here to
toggle between each of your views, Form
| | 01:26 | View, Layout View, Design View.
| | 01:30 | If you don't see these buttons,
right-click on your status bar;
| | 01:34 | make sure there's a check
mark in front of View Shortcuts.
| | 01:38 | If you right-click on your object's tab,
you can also change the view from here.
| | 01:44 | And when you are opening up any
object, if you right-click on it in the
| | 01:48 | Navigation pane, you can open it up,
for instance, straight into Design View. I
| | 01:52 | use that shortcut very frequently.
| | 01:55 | If you're working in the contextual menus
in certain views, the first button will
| | 02:00 | always have a View toggle, and if
you're working on a Form or Report in Design
| | 02:04 | View, here is one more powerful option.
| | 02:07 | See this little box in the corner
between the vertical and horizontal ribbons?
| | 02:11 | If I right-click on that, I
can also switch between my views.
| | 02:14 | Note however, that this only works
in forms and reports from Design View.
| | 02:19 | It doesn't work in Tables or Queries, and it
doesn't work in Datasheet or Layout Views.
| | 02:23 | Knowing a variety of methods to
toggle between your object views will
| | 02:27 | definitely speed up your development time.
| | Collapse this transcript |
| Understanding the "Cannot contain a Null value" error| 00:00 | When I'm adding records to my database,
occasionally an error message pops up that
| | 00:04 | doesn't make much sense.
| | 00:05 | Index or primary key cannot contain a Null
value. And when that happens it won't let you out.
| | 00:12 | Let's re-create the error, I'm in my
Customers table and I'll make a new blank
| | 00:16 | record, but tab over your primary key or
ID field, so that it's left blank, fill
| | 00:23 | in a few more fields of information.
| | 00:27 | Now click off the record, and the error
message pops up. When you click OK and
| | 00:32 | you try and to continue on, it'll return.
| | 00:35 | The only thing you can do to get away
from that message is to press the Escape
| | 00:40 | key in the upper left corner of your
keyboard, but that's going to erase the
| | 00:43 | record that I entered.
| | 00:45 | The error is simple, but not obvious.
As we created the record we left the
| | 00:49 | primary key field blank, we didn't
enter the ID that distinguishes this record
| | 00:54 | from all the others.
| | 00:55 | So let's try again. I'll start with my ID
and then enter in the rest of my data again.
| | 01:03 | This time when I move on to a
new record everything is just fine.
| | 01:07 | I see beginners make this cannot
contain a Null value error all the time and
| | 01:10 | that leaves flustered and frustrated,
because the error message itself doesn't
| | 01:14 | help you identify the underlying
problem, but once you remember that your
| | 01:18 | table's primary key field is
required it all makes sense.
| | 01:22 | If you're not familiar with primary keys,
please see the Access 2007 Essential
| | 01:26 | Training for detailed information.
| | Collapse this transcript |
| Working with AutoNumbers as foreign keys| 00:00 | I probably get more questions about
this technique than any other, when people
| | 00:04 | build their own databases.
| | 00:05 | This lesson does require an
understanding of primary keys, foreign keys, common
| | 00:09 | fields, AutoNumbers and relationships.
| | 00:12 | So if you're not sure what these terms
mean, please watch those videos in the
| | 00:16 | Access 2007 Essential
Training and then return to this one.
| | 00:19 | When you're relating two tables together,
the usual method is to use the primary
| | 00:24 | key of one table as the foreign key
or common field in the second table.
| | 00:27 | For example, let's open up our
Customers table and our SalesReps table.
| | 00:31 | In the SalesReps table, each
employee has his or her own ID number, a
| | 00:36 | unique identifier that Access uses
to make sure that the person isn't
| | 00:40 | listed in the table twice.
| | 00:42 | Now let's look at the ID field in Design View.
| | 00:44 | Much of the time an AutoNumber data
type is used for this primary key, which
| | 00:48 | sequentially numbers the records as
they are created, so that no two are
| | 00:51 | given the same ID number.
| | 00:53 | That includes this table, I'll right-
click on the SalesReps tab and click Close.
| | 00:57 | Let's scroll over to the right in the
Customers table to see the SaleReps field.
| | 01:02 | When we get a new customer and we
assign them a SalesRep, instead of
| | 01:06 | re-keying all the employee's name and
contact information every time, we just
| | 01:11 | use their ID field. If you go to the
Database tools ribbon and then click on
| | 01:16 | the Relationships button, you can see
that Access understands that there's a
| | 01:19 | link between the SalesReps and the
Customers, and that everyone SalesRep can
| | 01:23 | have many Customers, as shown by the line with
the one here and the infinity on the Customer side.
| | 01:29 | When we later run reports, we'll use the
query to bring the Customer information
| | 01:33 | and the SalesRep information together
into one place, using this relationship.
| | 01:38 | I'll Close this window. But where
people get confused is that when they create
| | 01:42 | the SalesRep field here in the
Customers table, they assume that because it
| | 01:46 | was an AutoNumber in the original table it has
to be an AutoNumber here, but that won't work.
| | 01:51 | The purpose of an AutoNumber is to
automatically increment itself, that's not
| | 01:55 | possible here, where I'm supposed to
pick the SalesRep off of the list. So let's
| | 01:59 | look at the Design View of the Customer
table and click on the SalesRep field.
| | 02:03 | Instead of an AutoNumber, this foreign
key field must be a Number data type, and
| | 02:09 | the field size should be Long Integer.
| | 02:11 | According to Access that's the only
field type combination that will work to
| | 02:16 | create this relationship. Setting the
common field to Number and Long Integer in
| | 02:20 | the foreign table will allow Access to
match it to the AutoNumber in the primary
| | 02:24 | key, allowing your database to
merge the information properly.
| | Collapse this transcript |
| Using input masks| 00:00 | Input Masks are a field property that
automatically formats Phone Numbers,
| | 00:05 | Social Security Numbers, and other data, so
that all you have to do is enter the numbers.
| | 00:09 | Let's go to our SalesReps table, right-
click on it and open it in Design View.
| | 00:14 | Go down to your HomePhone and in the Field
Properties at the bottom, click on Input Mask.
| | 00:21 | A Build button will appear on
the right-hand side, click on it.
| | 00:26 | The first Input Mask we want is PhoneNumber.
| | 00:29 | So I'll click Next.
| | 00:31 | Here it offers me an option for
what I want my placeholder to be.
| | 00:34 | An underscore is nice because it looks
like an underline, like a fill in the blank.
| | 00:38 | But if you wanted, you could change it to a
number sign or another character. I'll click Next.
| | 00:44 | Now, it wants to know how
I want to store my data.
| | 00:46 | I like to store it with my punctuation, with
the parentheses, and the space and the dash.
| | 00:51 | If I wanted, I could store it so it's just
numbers, but it will still look like the
| | 00:55 | Phone Number in my table.
| | 00:57 | I'll click Next and then Finish.
| | 01:00 | Let's do the same thing for CellPhone.
| | 01:03 | I'll click in my Input Mask,
click the Build button.
| | 01:06 | This time it asks if I want
to save my table and I will.
| | 01:10 | I want a PhoneNumber. I'll click Next.
| | 01:13 | I'll leave this on the defaults and click Next.
| | 01:16 | Again, I will save the symbols,
and click Next, and then Finish.
| | 01:21 | Let's try it again, but a little differently.
| | 01:23 | Now we'll go to the Social Security Number.
| | 01:25 | Click on Input Mask, click the
Build button, save the table, and this time
| | 01:30 | choose Social Security Number.
| | 01:33 | I'll click Next, and Next.
| | 01:35 | Again, I'll save the number, so that it's
formatted properly in my forms and reports.
| | 01:40 | Click Next and Finish.
| | 01:42 | Now let's take a look at how it works.
| | 01:44 | I am going to go back to my Datasheet
View, I'll save the table when it asks,
| | 01:50 | and let's go enter in new data.
| | 01:51 | I'm going to jump over to my PhoneNumbers.
| | 01:55 | It works well when you tab into the number.
| | 01:58 | That way when you type it, everything is
all lined up exactly where it should be.
| | 02:02 | If I click in the Field, it actually
starts wherever my cursor is and if I start
| | 02:07 | to type, then I'll start
typing in the middle of the field.
| | 02:10 | So I want to make sure
that I start in the beginning.
| | 02:14 | Now, when I go to Social Security
Number, as soon as I start to type, my
| | 02:17 | spaces and dashes show up.
| | 02:19 | So to save time when entering numbers
that include punctuation from PhoneNumbers
| | 02:23 | to Dates, create an Input Mask, so
you only need to type the digits, and let
| | 02:27 | Access take care of the rest.
| | Collapse this transcript |
| Four ways to add fields to a query| 00:00 | When you create a Query there are
several ways to add fields to the Design Grid.
| | 00:04 | Go to the Create ribbon, and on
the far right choose Query Design.
| | 00:08 | Double-click to add the SalesReps table
in the Query and then close the window.
| | 00:12 | Hold your cursor over the bottom of
the SalesReps table and pull down so that
| | 00:16 | you can see more of your fields.
| | 00:18 | I can add fields to my
Query in several different ways.
| | 00:21 | The first is to double-click on
the Field and it will appear in the
| | 00:24 | next available column.
| | 00:25 | I'll double-click on LastName and it appears.
| | 00:27 | You can also click in the Field Row
right in the grid and use the dropdown and
| | 00:32 | pick the field from there.
| | 00:34 | I'll choose Address.
| | 00:35 | A third way is to click from the
window above and drag it down to the grid.
| | 00:39 | You can aim for the next available empty
column, but this method also allows you
| | 00:43 | to add columns you forgot.
| | 00:45 | I can drag FirstName and drop it onto Address.
| | 00:49 | Address will move to the right
and make room for the new column.
| | 00:51 | If you do need to reorder the columns,
you can always click on this thin gray
| | 00:56 | bar right here when you get a Black down-arrow.
| | 00:57 | I'll let go of my mouse button, then click on
that thin bar again, and pick it up and drag.
| | 01:03 | If I want, I can drag it back.
| | 01:06 | Now, for our last way of adding fields to a
Query, first delete all three of these columns.
| | 01:11 | I'll click on the thin bar above
LastName, and drag across to Address, and then
| | 01:15 | let go. And then simply
click Delete on my keyboard.
| | 01:18 | Now, go up to the SalesReps field
list, and double-click on the asterisk.
| | 01:24 | The first column simply says SalesReps.*,
but when I go up to the ribbon and
| | 01:29 | then run the Query, all the
fields in the table display.
| | 01:32 | Interchanging all of these methods
as needed will give you speed as you
| | 01:35 | build your queries.
| | Collapse this transcript |
| Four ways to filter data| 00:00 | Forms are designed to be your most
frequently used tool while working with your data.
| | 00:04 | For that reason there are several ways
of filtering your records so that you're
| | 00:07 | viewing only the ones you need.
| | 00:09 | Scroll down in your Navigation pane
and open up your Customers Order Form.
| | 00:13 | This form is my most frequently used
form, because it has all my information
| | 00:17 | about a customer and I can see all
of their orders all in one window.
| | 00:21 | When I look in the bottom left-hand
corner I can see that I have 32 records.
| | 00:25 | So let's say that I only want to
look at the customers in Nevada.
| | 00:28 | Click in the State field and then in
the Ribbon on the right-hand side click on
| | 00:32 | the Selection button.
| | 00:34 | It gives me four options;
| | 00:35 | Equals Nevada, Does Not Equal Nevada,
Contains Nevada, and Does Not Contain Nevada.
| | 00:40 | I will choose Equals Nevada.
| | 00:42 | When I look down on the bottom left-hand
side, I can see that I have two records now;
| | 00:47 | All Kinds of Taste, and Niche Cuisine.
| | 00:50 | When I want to see all the records again,
I'll go back up to this Toggle Filter button.
| | 00:54 | Now, notice when you look at it that
you can see that it's all lit up in
| | 00:57 | orange, which helps me remember that
my records are filtered, if I can't find
| | 01:02 | what I am looking for.
| | 01:03 | I will go ahead and click on it.
| | 01:04 | You can get the exact same options
by right-clicking on the State field.
| | 01:08 | On the Shortcut list I have Equals, Does
Not Equal, Contains, and Does Not Contain.
| | 01:13 | One of my favorite methods
is called Filter by Form.
| | 01:16 | It's buried up under the Advanced button;
| | 01:19 | Advanced > Filter by Form.
| | 01:21 | Nevada was highlighted.
| | 01:22 | But I will go ahead and hit Backspace
on my keyboard to get rid of it, and
| | 01:25 | then come up to the SalesRep field and let's
look for all our records from our SalesRep Hinton.
| | 01:30 | I can either pick it from the
dropdown list, or I can type it in myself.
| | 01:35 | If I choose to type it, notice that it
auto-fills and once I find it I can press
| | 01:39 | Tab, and Access puts it in
quotation marks automatically.
| | 01:42 | I will click on the Toggle Filter
button to run the filter, and now when I
| | 01:45 | look down at the bottom I can see
that I have six matches, each one are
| | 01:49 | Hinton's customers.
| | 01:51 | Now go back into Advanced
and then Filter by Form again.
| | 01:56 | I can even filter on multiple criteria.
| | 01:59 | If I go to State and pick Alaska,
AK, now I have AK and Hinton.
| | 02:05 | When I toggle the filter, I can see
that Hinton has two customers in Alaska;
| | 02:10 | Blue Vine and Ibila.
| | 02:11 | When I click on the Toggle Filter button
again to turn it off, I am back to all 32 records.
| | 02:17 | One last way to filter is to use
this big Filter button right here.
| | 02:21 | Start by clicking in the
field that you want to filter on.
| | 02:23 | I will click in SalesRep and
then click on the big Filter button.
| | 02:27 | This button gives you check
boxes for every value in that field.
| | 02:31 | The top check mark turns on
or off all of the options.
| | 02:34 | I like to turn off Select All,
and then turn on my desired selections.
| | 02:39 | I will click OK and now I have eight
records for the three SalesReps I just selected;
| | 02:45 | Davenport, Douglas, and Collins.
| | 02:48 | And once again, the Toggle Filter
button will restore all of my records.
| | 02:52 | One of these methods will feel right
to you and become your most utilized
| | 02:55 | filtering technique.
| | Collapse this transcript |
| Selecting multiple form and report controls| 00:00 | When you're designing a formal report,
you'll frequently need to make the
| | 00:03 | same formatting or location change
to several labels or data boxes, which
| | 00:07 | Access calls controls.
| | 00:09 | Instead of making the same change to
each control over and over and over
| | 00:13 | again, you can select multiple objects and
apply the change to all of them at the same time.
| | 00:18 | I have opened my Customers Order Form.
| | 00:20 | I am going to right-click on my Customers
Order Form tab and open it in Layout View.
| | 00:26 | When I click on any of the labels,
which are the field names or the bound boxes
| | 00:31 | which contain our data, they get
an orange highlight around them.
| | 00:34 | If I want to select more than one at a
time, I can hold down my Shift key to add
| | 00:39 | controls to the group.
| | 00:41 | If I select too many and I want to
remove one, I can just keep that Shift key
| | 00:45 | down and click the control a second time.
| | 00:47 | The orange box goes away.
| | 00:50 | If I want to select all the boxes on
the form at the same time, I can press
| | 00:54 | Ctrl+A on my keyboard and
everything will get a highlight.
| | 00:57 | If I click on anything that's not a
control, all my selections will go away.
| | 01:01 | Another way of selecting groups of
controls can be found when I am in Design View.
| | 01:05 | I will right-click on the Customers
Order Form tab and choose Design View.
| | 01:10 | A technique I use a lot is to draw a
marquee around the controls I want.
| | 01:13 | For example, maybe I want to make the
text of my labels a little smaller and the
| | 01:18 | boxes themselves a little shorter.
| | 01:19 | I will start by clicking a little
above and to the left of my first textbox.
| | 01:23 | I will hold down my mouse button and drag
down to touch all the controls in this row.
| | 01:28 | It's not necessary to wrap the
box around the entire control;
| | 01:32 | just touching it is fine.
| | 01:34 | When I reach the bottom I'll let go,
and the whole group will be selected.
| | 01:37 | If I want to add in the labels on
the right-hand side, I can combine the
| | 01:41 | techniques I used above.
| | 01:43 | Hold down your Shift key, click
above onto the left of the next column of
| | 01:46 | labels, and drag down, touching all of them.
| | 01:50 | When I let go of the mouse button, this
group joins the first group and the ones
| | 01:53 | in the middle are not touched.
| | 01:55 | Just be sure to let go of the mouse
button before you let go of the Shift key
| | 01:59 | or this won't work.
| | 02:00 | I can continue Shift+Dragging or Shift+
Clicking until I have all of the labels selected.
| | 02:05 | I will then go to my Form Design Tools'
Design ribbon and change the size to 10.
| | 02:10 | I will then carefully click on the
middle handle on the left-hand side of all
| | 02:14 | of the controls and when I make them shorter,
they will all change size at the same time.
| | 02:20 | Selecting multiple field controls when
you're designing forms and reports will
| | 02:23 | save you hours of time so that you can
apply formatting and layout adjustments
| | 02:27 | to everything all at once,
instead of one control at a time.
| | Collapse this transcript |
| Aligning and distributing controls on forms and reports| 00:00 | When you create forms and reports
from scratch, it will help usability,
| | 00:04 | if you make sure to line up your labels
and controls, so the information looks
| | 00:07 | orderly instead of jagged.
| | 00:09 | I'll demonstrate this by
designing a form from scratch.
| | 00:12 | Click on the Create ribbon, and then on the
Form Design button. We now have a blank form.
| | 00:18 | Up on the right-hand side of the
ribbon click on Add existing fields, this
| | 00:22 | brings up a list of all the
fields you have in your database.
| | 00:24 | You can open and shut the different
tables using the Plus and Minus signs.
| | 00:29 | I'll double-click on Customer ID, Company
name, Address1, Address2, City, State, and Zip.
| | 00:37 | Each of the fields is added to my form.
| | 00:40 | Because all the controls are
aligned on their left edge, the labels look
| | 00:43 | jagged. Sometimes it looks nice to
align them on their right edges instead.
| | 00:48 | So, I'll draw a marquee around
the text labels to select them.
| | 00:51 | I'll click above and to the left, and drag down.
| | 00:54 | You could also click and
Shift+Click if you wanted to.
| | 00:58 | Go up to the Arrange tab, under the
Form Design Tools, and in the center
| | 01:02 | there is a Control Alignment
section, I'll make these Aligned Right.
| | 01:07 | Now the right side edge is smoothed.
| | 01:09 | Because they're all selected I can
also resize them all at one time.
| | 01:13 | I'll hold my cursor over the middle
dot on the right-hand side until I get a
| | 01:17 | double headed arrow, and I'll drag
this over to the right until they line up
| | 01:20 | very close to my bound controls.
| | 01:23 | Now, while this looks nice, maybe I'd like my
form to have an address in a standard format.
| | 01:28 | So I'm going to rearrange this form.
| | 01:30 | First, I'm going to move my company
field up next to my customer ID. To move the
| | 01:35 | individual field without the label
I'll hold my cursor over the box in the
| | 01:39 | right-hand corner until I get this
four-headed arrow, and drag it up.
| | 01:43 | I'll also make it longer so
that my company is not cut off.
| | 01:46 | Now instead of customer ID and company,
I'm going to delete my customer ID label
| | 01:51 | I'll click on it and hit Delete on my
keyboard, I'll click on company, hold my
| | 01:55 | cursor over the box in
the corner, and drag it up.
| | 01:58 | Now I need to move up my address, so
I'll click on this box, I'll get the
| | 02:02 | four-headed arrow. This time I'm going
to click on the edge of the control not
| | 02:06 | the box, because I want both the
control and the label to move together.
| | 02:10 | And I'll go ahead and
move address, make it longer.
| | 02:13 | I'll also move up my address too by
clicking on the edge and dragging it up.
| | 02:18 | Now if I want to make my Address2 the
exact same like as my Address1, I don't
| | 02:22 | have to do this manually in hope and guess.
| | 02:25 | If I Shift+Click on the two fields, up
in the Size, I have the option here to
| | 02:29 | size it to the widest,
and it'll make Address2 match Address1.
| | 02:34 | I'll now move City up under Address,
and I'll pick up State, and instead of
| | 02:39 | having City, State, and ZIP here, I'm
actually going to go ahead and delete
| | 02:43 | these four labels, so that all
it says is Company and Address.
| | 02:48 | So now I'm going to move State up next
to City, make my City a little wider, and
| | 02:54 | my State a little smaller.
| | 02:57 | And then I'll move Zip up next to State.
| | 03:00 | Now I want to make sure that
everything is equally spaced and aligned.
| | 03:03 | I'd like these three fields to be
equally spaced and I'd like them to line up.
| | 03:07 | I'm going to hold my Shift key down and
click on all three, so that they are selected.
| | 03:12 | I'm going to use the Control Alignment
Top to make sure that they're all lined
| | 03:16 | up perfectly, then I am going to come
over here to the right-hand side and I
| | 03:20 | want to space out the three fields.
| | 03:22 | So I'll click on Equal Horizontal, now the
three fields are equally spaced perfectly.
| | 03:27 | Now I do want them to match this
Address boundary, so I'm going to increase my
| | 03:32 | horizontal spacing until
ZIP matches my Address lines.
| | 03:37 | I see here that my Company label is
off with these, so I'm going to click on
| | 03:40 | Company, Shift+Click on the other
two, and align these Top as well.
| | 03:45 | Now I'm going add in all the other
fields, and this time I'm going to
| | 03:49 | distribute vertically.
| | 03:51 | Now there is an equal
space between each of my rows.
| | 03:54 | If I wanted I could also increase my
vertical spacing and decrease my vertical spacing.
| | 03:59 | I'll go back to my Design ribbon and
look at my form, I notice that my city is
| | 04:04 | cut off, so I'm going to go back to
Design View, click off of the fields and
| | 04:08 | back on City to make it a little longer,
Shift+Click on my State and Zip, go
| | 04:13 | back to my Arrange tab and
adjust the horizontal spacing again.
| | 04:18 | Now back to Design, back to my Form, wonderful.
| | 04:23 | Go ahead and Save this
form as Customer Addresses.
| | 04:27 | Using align and distribute will make
your forms and reports look like they were
| | 04:31 | professionally made.
| | Collapse this transcript |
|
|
2. File ManagementOpening recent files| 00:00 | If you work on the same files on a
regular basis, you can open all your recent
| | 00:04 | files without having to go
search for them in Windows.
| | 00:07 | You can do this from several places.
| | 00:09 | First, in Windows itself. In
Windows 7, when I click on the Start button it
| | 00:14 | shows me a Jump List.
| | 00:15 | If I have used Access
recently you'll see it on this list.
| | 00:18 | When I hold my cursor over it, it
will show me a list of my recent files.
| | 00:22 | This list will update as
you open more and more files.
| | 00:26 | If there are certain ones that you
would like to keep at the top of the list
| | 00:29 | even if you use them in frequently,
click on the pin on the right-hand side.
| | 00:33 | It'll jump to the top and say Pinned,
and future databases will show up at the
| | 00:37 | bottom under the headline Recent.
| | 00:39 | If I no longer want it there I can unclick the
pin and it will go back into the Recents list.
| | 00:44 | I can also pin Microsoft Access itself,
if I right-click on it I can tell it to
| | 00:49 | pin to the Start menu and it will
jump to the top above this line.
| | 00:53 | I have another option, if I right-click
on it, I can also pin it to the Taskbar
| | 00:58 | down at the bottom of the screen.
| | 01:00 | This way the Access icon will stay
even if I don't have Access open.
| | 01:04 | If I want to remove it, I'll
right-click on it again and unpin it.
| | 01:08 | Now let's go into Access 2007 and visit our
options there. I'll click on it, and Access opens.
| | 01:14 | On the far right-hand side, I'll have a
list of my open databases, and if I go
| | 01:19 | up to the Office button, I'll also see
a list of my recent Access databases.
| | 01:23 | To customize how many files show here,
if I click on Access Options, and then on
| | 01:28 | the Advanced button, and then scroll
down to the display section, now it says
| | 01:34 | show this number of recent documents,
and it's says nine, which is the maximum.
| | 01:38 | If I wanted I can make that number
smaller, and I'll click okay.
| | 01:44 | Access makes it fast and easy to
continue working on a file you've
| | 01:47 | used recently.
| | Collapse this transcript |
| Opening your most recently used file automatically| 00:00 | If you work on the same Access
database all the time, you don't have to go
| | 00:04 | through the extra step of
opening the file every day;
| | 00:06 | you can set Access up to open up a
specific file automatically by default.
| | 00:11 | To do this go up to the Office button and
then down to the Access options at the bottom.
| | 00:17 | Click on Advanced, then scroll down to
the Advanced Section near the bottom.
| | 00:24 | The first item there is Open last
used database when Access starts.
| | 00:28 | I'll put a check mark in front of it and click OK.
| | 00:32 | Now close your database and open
Access again without choosing a file.
| | 00:37 | I'll just click on it in my Jump menu,
and there it is, my file ready to go.
| | 00:43 | Opening your last used database,
saves a step, and precious seconds, every
| | 00:47 | time you use Access.
| | Collapse this transcript |
| Selecting a startup form| 00:00 | If you work on the same Access data
entry for most of the time, you can have it
| | 00:04 | open automatically when the program opens.
| | 00:07 | To do this, click on the Office button, and
then on Access Options down at the bottom.
| | 00:12 | Click on Current Database on
the left, then look on the right.
| | 00:16 | You'll see Display Form.
| | 00:19 | Drop it down, and you'll see a list
of all the forms in your database.
| | 00:22 | For our database, let's open up the
Customers Order Form, since we spent most of
| | 00:26 | our time in this form filling customer orders.
| | 00:29 | Click OK, and it says you must close
and reopen the current database for the
| | 00:34 | specified option to take effect. Let's do that.
| | 00:37 | I'll click OK, and I'll close Access.
| | 00:40 | Now when I go down to the Start button,
go up to Access and open my file again,
| | 00:45 | it opens straight to that order form.
| | 00:47 | Opening directly to your most used
data entry form saves a step, and precious
| | 00:51 | seconds, every time you use Access.
| | Collapse this transcript |
| Changing the default saving location| 00:00 | By default, Access will save your new
databases in your Documents folder, but if
| | 00:05 | you'd rather save them in a subfolder,
or another location on your computer, you
| | 00:09 | can tell Access to start there instead
of having to change the location every
| | 00:13 | time you create a new file.
| | 00:15 | To do this, click on the Office
button, and then on Access Options at the
| | 00:19 | bottom, click on Popular, and in the
middle you'll see Default database folder.
| | 00:26 | I'll click on Browse.
| | 00:28 | I'm going to go into my
Company folder, Two Trees Olive Oil.
| | 00:33 | If I'd like to make a subfolder for my
databases, I can click on New Folder, and
| | 00:38 | I'll type databases, and
hit Enter to accept the name.
| | 00:42 | I'll double-click on the folder, so that
I can see that I'm in it right here, and
| | 00:47 | down here, and I'll click OK,
and click OK again to accept the change.
| | 00:53 | So now I'll cancel this
window, and come up again;
| | 00:57 | Office button, New.
| | 00:59 | There's my new file path.
| | 01:01 | Saving straight to a specific location
will save you repetitive navigation every
| | 01:06 | time you create a new database.
| | Collapse this transcript |
|
|
3. Ribbon and Quick Access Toolbar TipsExpanding and collapsing the Ribbon| 00:00 | Microsoft Access' Ribbons allow you to
create and work in your database quickly
| | 00:04 | and easily, but sometimes they can
take up valuable screen real estate.
| | 00:07 | You have the ability to expand
and collapse your Ribbons as needed.
| | 00:11 | To do this, right-click on the blue
bar at the top of the Ribbon and in the
| | 00:15 | Shortcut menu that appears,
choose Minimize the Ribbon. It's gone.
| | 00:20 | If you right-click any of the Ribbon
tabs at the top, you can reselect Minimize
| | 00:24 | the Ribbon and it will open again.
| | 00:27 | Another way to expand and collapse the
Ribbon is to double-click on any of the tab names.
| | 00:32 | They'll shrink up and then
double-click again to open them.
| | 00:35 | And last, use the keyboard command
Ctrl+F1 to toggle it open and closed.
| | 00:41 | When you have a lot of data to view at
once, collapsing the Ribbon will gain up
| | 00:45 | to an extra inch of real estate on your screen.
| | Collapse this transcript |
| Three ways to customize the Quick Access toolbar| 00:00 | The Quick Access toolbar appears in the
upper left corner of all the Microsoft
| | 00:04 | Office applications.
| | 00:05 | By default, it has little
buttons for Save, Undo, and Redo.
| | 00:10 | But you can also customize it to
contain your most commonly used commands.
| | 00:14 | The first thing to understand is that the
buttons will appear in the order that you add them.
| | 00:18 | Later on I'll show you how to reorder them.
| | 00:20 | But if you plan ahead, you can
customize the Quick Access toolbar so it's
| | 00:23 | organized right from the get-go.
| | 00:25 | The first way we will customize
this bar is to change its location.
| | 00:28 | You can have it up at the top of the
screen, or if you click the little dropdown
| | 00:32 | arrow next to it, you can
choose Show Below the Ribbon.
| | 00:36 | When I click on that, it appears right here.
| | 00:38 | But that actually takes up extra real
estate, so I am going to click the little
| | 00:42 | dropdown again and choose Show Above the Ribbon.
| | 00:46 | Now, let's add more buttons to it.
| | 00:48 | I'll click the dropdown arrow
and I'll choose Quick Print.
| | 00:52 | It will now appear to the right of Redo.
| | 00:56 | Quick Print sends your Table, Form, or Report
straight to the printer, without a dialog box.
| | 01:01 | Another favorite, if you're working
with a database published to the web or a
| | 01:04 | SharePoint server, is Refresh All.
| | 01:07 | Having that button on your
Quick Access toolbar will make
| | 01:10 | synchronization instantaneous.
| | 01:12 | If you want to remove a button, simply
drop down the arrow and select the item
| | 01:17 | again to remove the check mark,
and it will disappear from the toolbar.
| | 01:20 | You can also add any button to the
Quick Access toolbar from any Ribbon.
| | 01:24 | For example, I go into the
Relationships View frequently.
| | 01:27 | Instead of having to click on Database
tools and then Relationships every single
| | 01:32 | time, I can right-click on the
Relationships button and tell it, Add to Quick
| | 01:38 | Access Toolbar, and there it is.
| | 01:41 | So now it appears after my Print button
and no matter where I am in my database
| | 01:45 | I can get to it instantly.
| | 01:47 | The last and most powerful way to
customize this toolbar is to click on the
| | 01:51 | dropdown arrow and choose More Commands.
| | 01:55 | This takes you into Access's options
into a special area just for this purpose.
| | 02:00 | On the left-hand side is a list
of every single command in Access.
| | 02:04 | The list defaults to popular commands,
but you can use the dropdown to select
| | 02:09 | All Commands or even Commands Not on any Ribbon.
| | 02:14 | Find the commands you want.
| | 02:15 | I'll choose Filter By Selection and I'll
click Add to move it to the right-hand side.
| | 02:21 | If you want to reorder your buttons,
you can choose the Up arrows and Down
| | 02:25 | arrows to put them in the order that you'd like.
| | 02:28 | If you want to reset your Quick Access
toolbar to its default, use this Reset
| | 02:32 | button down here at the bottom.
| | 02:34 | When you're done, click OK to close
the window and go back to your file.
| | 02:38 | There is our new toolbar.
| | 02:39 | The Quick Access toolbar is the
perfect place to create a handy collection of
| | 02:43 | your most frequently used commands.
| | Collapse this transcript |
| Selecting Ribbon buttons using KeyTips| 00:00 | Power users do as much of their work
as they can with their hands on their
| | 00:03 | keyboards instead of
continually reaching for a mouse.
| | 00:07 | For frequently used commands
using key tips will really make those
| | 00:10 | precious seconds add up.
| | 00:12 | Let's say I want to create a
new table in my existing database.
| | 00:15 | To do this using key tips press the Alt
key on the left side of your keyboard.
| | 00:20 | Little letters will appear over each
of the Ribbons with a 1, 2, 3 over your
| | 00:24 | Quick Access Toolbar.
| | 00:25 | So to create a new table I have to go to
Create ribbon. So I'll press C on my keyboard.
| | 00:32 | The Create ribbon appears, and there is
a TN under the table button that I want
| | 00:37 | so I'll type TN and a new table appears.
| | 00:41 | To save my table I'll hold
down the Alt key and then press 1.
| | 00:46 | Since this is a new table,
Access has asked me to name it.
| | 00:49 | I'll call it Inventory, and because the
OK button is outlined in blue, I don't
| | 00:55 | have to click on it, I can
hit Enter on my keyboard.
| | 00:58 | In this way, I can get a lot of
development work done in just a few keystrokes
| | 01:02 | without even taking my fingers off the keyboard.
| | 01:05 | Do note that key tips and keyboard
shortcuts are not exactly the same thing.
| | 01:09 | We'll cover keyboard
commands in a future lesson.
| | Collapse this transcript |
|
|
4. Interface ShortcutsTurning the Navigation pane into a switchboard| 00:00 | When you're designing your database for
others to use you'll want to guide them
| | 00:04 | from where to go and what to do.
| | 00:05 | You can transform the Navigation pane
to turn your database into a customized
| | 00:09 | switchboard by changing the names and even
hiding objects that you don't want to see.
| | 00:14 | In this example, I'll transform this list.
| | 00:17 | So instead of being based on the database
objects, it will focus on the tasks done by the users.
| | 00:22 | I'll reorganize this pane into
groups specific to job functions.
| | 00:26 | Tables and forms used by the customer
service department will be grouped together,
| | 00:29 | as well as forms and queries used by
order fulfillment and the sales reps.
| | 00:34 | I'll start by right-clicking on the
navigation pane heading at the top of your column.
| | 00:38 | Yours may say something different here.
| | 00:41 | Choose Navigation Options off of the pop-up
list and then click on Custom. I'll rename this.
| | 00:50 | Click on Rename Item and I'll call it Two
Trees, the name of my company, and hit Enter.
| | 00:56 | Now on the right-hand side I'll click
on Custom Group 1 and I'll Rename it
| | 01:00 | to Customer Service.
| | 01:05 | Next, I'll click Add Group at
the bottom and enter in Sales Reps.
| | 01:14 | I'll Add a third Group called Fulfillment.
| | 01:21 | Because it's likely that I'll have
some objects that I need myself, but other
| | 01:24 | users don't, I'll leave
Unassigned Objects on the list.
| | 01:28 | I'll need it for the next step,
but afterwards I'll even show you how to hide it.
| | 01:32 | Click the OK button.
| | 01:33 | Now let's look at our Navigation
pane. Drop it down, and choose Two Trees.
| | 01:40 | I see rows here for all my departments,
but everything is still under unassigned.
| | 01:45 | So I'll start moving them around.
| | 01:47 | For customer service, I need to see In
State, so I'll pick it up and I'll drop
| | 01:52 | it in there. My Order totals.
| | 01:55 | Under my Sales Reps, I need to see my
Sales Reps Phone List, my SalesReps table.
| | 02:05 | I have a lot of objects, so I'm
| | 02:07 | not going to sort all of them,
but you see how it works.
| | 02:09 | Now notice that these tables have
little shortcut arrows. That's because we're
| | 02:14 | creating an artificial environment.
| | 02:16 | So these little indicators are
pointing to the real tables behind the scenes.
| | 02:19 | We don't have to be concerned with them.
| | 02:21 | Once I'm done, I can choose to leave
this Unassigned Objects group here if I
| | 02:25 | need to use it myself or I can hide it.
| | 02:28 | One way to hide it is to click on it, so
that you can't actually see what's inside
| | 02:32 | it, but my other employees can get to it.
| | 02:36 | To hide it completely, right-click up
here where it says Two Trees, and go back to
| | 02:41 | the Navigation Options. Click on Two
Trees on the left-hand side, and then turn
| | 02:47 | the checkmark off for
Unassigned Objects; that will hide it.
| | 02:51 | I'll click OK and now it's gone.
| | 02:54 | If I need to see my unassigned
objects again, I can either go back to the
| | 02:58 | navigation options to unhide the group,
or if I resort this according to any of
| | 03:03 | the standard sort types, all of
my objects will come back again.
| | 03:08 | And so anytime I want, I can sort
by function instead of by object.
| | 03:12 | Turning the Navigation pane into
a switchboard is a creative way of
| | 03:16 | manipulating Access's tool to
create a custom database application for
| | 03:20 | your workplace.
| | Collapse this transcript |
| Right-clicking| 00:00 | Power users know that right-clicking
your mouse on almost anything on your
| | 00:03 | screen will give you a
context-sensitive shortcut menu.
| | 00:06 | This allows you to invoke frequently
used commands without having to move your
| | 00:10 | mouse very far around your screen.
| | 00:12 | What right-clicking does
depends on what you click on.
| | 00:15 | If you right-click on the Quick
Access toolbar, or a Ribbon tab you get
| | 00:18 | options to manage them.
| | 00:20 | If I right-click on a button on the
ribbon, I get the option to Add it to the
| | 00:24 | Quick Access toolbar.
| | 00:25 | If you right-click at the top of the
Navigation pane you get options for viewing
| | 00:29 | and sorting your Tables,
Forms, Queries, and Reports.
| | 00:33 | If you right-click directly on one of
those objects, you can choose the View
| | 00:37 | that you want to see it in, you can
Import and Export from it, use the Data
| | 00:41 | Collection with Outlook, Rename the
object, Hide it, Delete it, Cut it, or
| | 00:46 | Duplicate it, and see it's Properties.
| | 00:50 | If you right-click on an objects tab
you can Save it, change the View, Close
| | 00:59 | it, and one of my absolute favorite right-
clicks, Close All your open tabs all in once.
| | 01:07 | If I right-click on a field name, I
get options for sorting it, duplicating
| | 01:13 | it, changing the Width, Hiding it, Freezing it,
and we'll cover these in future chapters;
| | 01:19 | Inserting Columns, Deleting
Columns, and Renaming your Columns.
| | 01:24 | If I right-click right on one of the
data pieces itself, I can Cut it, Copy, and
| | 01:29 | Paste it, Sort on that column,
and even use Text Filters.
| | 01:33 | For example, if I right-click on Nevada
(NV) I can immediately find all of my
| | 01:38 | records that equal Nevada and then if I right
-click on it again I can Clear the filters.
| | 01:45 | Here's one most people don't think about.
| | 01:47 | If I right-click on the scrollbar, I
can jump to the Top or the Bottom, Page Up
| | 01:53 | or Page Down, Scroll Up and Scroll Down, or
even jump to an exact location in my data.
| | 01:59 | If you right-click into the Search
box at the bottom you can paste in some
| | 02:03 | information to search for.
| | 02:04 | For example, maybe I have to look for
a record that someone asked for in an
| | 02:08 | e-mail, I can Copy it, go back to
Access, and Paste it in the Search box at the
| | 02:14 | bottom and jump right to it in my data.
| | 02:16 | If I right-click on the Status bar at
the bottom of the window I can add and
| | 02:20 | delete shortcut buttons to
increase my one click options.
| | 02:25 | If you become a right-click master
you'll save yourself a lot of time with
| | 02:28 | essential functions at your fingertips.
| | Collapse this transcript |
| Using keyboard shortcuts| 00:00 | Power users make good use of keyboard
shortcuts to work without having to take
| | 00:04 | their hands off of the
keyboard and reach for the mouse.
| | 00:06 | Access 2007's ribbons don't make it
obvious how to do this, but here are
| | 00:10 | some things you can do.
| | 00:12 | First, many of the basic functions are
standardized across Microsoft Office.
| | 00:16 | So the commands you know from Word
or Excel will work in Access too.
| | 00:19 | Save, Ctrl+S. Open, Ctrl+O. Cut is Ctrl+X.
Paste is Ctrl+V, and Print is Ctrl+P.
| | 00:26 | Ctrl+F1 will minimize the ribbon.
| | 00:30 | Here are my favorites, specific to Access.
| | 00:32 | When I'm working in my table, pressing
the Tab key will move me from field to
| | 00:36 | field. And holding down the Shift
key as I Tab will take me backwards.
| | 00:41 | Ctrl+Up Arrow will take you to the very
first record and Ctrl+Down Arrow takes
| | 00:46 | you to your very last record.
| | 00:49 | When I'm entering in new data, holding down
the Ctrl key and pressing the Semicolon
| | 00:54 | will insert today's date.
| | 00:55 | And if I hold down the Ctrl key and
press the Quotation Mark it will duplicate
| | 01:00 | the data right above.
| | 01:02 | Now let's look at a few keyboard shortcuts
for working with controls on forms and reports.
| | 01:06 | I'm going to scroll down and open up my
Customers Order Form, I'll right-click on
| | 01:11 | it and open it in Design View.
| | 01:13 | Let's say I wanted to move my
CustomerID up a little higher.
| | 01:17 | I'll click on the bound control,
and I'll hold my Shift key to select
| | 01:21 | the additional control.
| | 01:23 | Now, I can use my Up Arrow and Down
Arrow to move them, but those move in
| | 01:26 | awfully large increments.
| | 01:28 | If I hold down my Ctrl key, you'll
be able to nudge in finer increments
| | 01:32 | for exact placement.
| | 01:34 | If I hold down my Shift key, I can
use my Right Arrow and my Left Arrow to
| | 01:40 | increase and decrease the
length of my control boxes.
| | 01:44 | If I press F4 I can toggle open
and close my Properties Sheet.
| | 01:49 | These are just a very few of the
useful keyboard commands, we'll mention more
| | 01:52 | throughout this course.
| | 01:53 | How do you find out more about
keyboard shortcuts? Use Access's Help.
| | 01:57 | Go up to the blue question mark
| | 01:59 | in the upper right-hand corner, and
in the window that appears, type
| | 02:04 | in Keyboard Shortcuts.
| | 02:09 | Here are my Keyboard shortcuts for Access.
| | 02:13 | When I open it, I can click on any one
of the topics, and it will expand out and
| | 02:18 | show me those keyboard commands.
| | 02:20 | I can click on them again to close them.
| | 02:24 | Up at the top on the right-hand side
there is a link for Show All, this has
| | 02:28 | now expanded all of the categories all
at once, so I can see the entire list
| | 02:33 | of keyboard shortcuts.
| | 02:36 | And when I'm done I can also Hide them
again, so that I can pick them by category.
| | 02:43 | Mastering keyboard commands will allow
you to get a lot of work done without
| | 02:47 | having to constantly reach for your mouse.
| | Collapse this transcript |
|
|
5. Navigation ShortcutsNavigating between records| 00:00 | Access 2007 gives you several ways of
moving around the data in your tables and
| | 00:04 | forms using just your keyboard.
| | 00:06 | Let's start with the simple stuff.
| | 00:07 | Double-click to open up a table.
| | 00:09 | When you press Tab you will move
from field to field across a record.
| | 00:13 | If you hold down the Shift key as you
press Tab, what I'll call Shift+Tab, you
| | 00:17 | will move backwards through the records.
| | 00:19 | Use your Up and Down Arrows on your
keyboard to move up and down your records.
| | 00:23 | Hold down the Ctrl key when you use
these Up and Down Arrows and you will move
| | 00:27 | to the very first record or to
the very last record in your table.
| | 00:33 | If you hold Ctrl+Page Down, you'll
move across the record to the next screen.
| | 00:38 | As you can see, the first time I press
it I can see the entire store type and
| | 00:42 | all the fields that come after.
| | 00:44 | I notice that my Email Address is the
next field that I see, and sure enough if
| | 00:48 | I do Ctrl+Page Down again, Email
becomes my leftmost field and I can see the
| | 00:53 | columns that follow.
| | 00:54 | Ctrl+Home and Ctrl+End move me to
the first and last fields in a record.
| | 01:00 | Let's see how those same
keyboard combinations work in a form.
| | 01:03 | I'll scroll down and open up my
Customers Order Form, Tab and Shift+Tab work
| | 01:07 | exactly the same way, moving
me from field to field and back.
| | 01:11 | Now I'm tapping my Up and Down Arrows,
they actually do the very same thing.
| | 01:15 | Adding the Ctrl key, Ctrl+Up Arrow, and Ctrl+Down
Arrow take me to my first and last records.
| | 01:22 | Ctrl+Page Down and Ctrl+Page Up move
me up and down one record at a time.
| | 01:29 | If I click in a field that has multiple
words in it, for example this address,
| | 01:34 | Ctrl+Right Arrow move me one word at a time
forward and Ctrl+Left Arrow takes me back.
| | 01:40 | If I add in the Shift key, so I'm
holding down Ctrl and Shift at the same time and
| | 01:44 | pressing my Right Arrow, that
actually allows me to highlight.
| | 01:48 | Using these keyboard shortcuts allows
you to move around your data without
| | 01:51 | having to reach for your mouse at all.
| | Collapse this transcript |
| Using Search, Go to, and Find| 00:00 | Access gives you a few quick
ways to jump to a record you need;
| | 00:03 | Find, Go To, and Search.
| | 00:06 | Open up your Customers table and
down at the bottom is the Search box.
| | 00:11 | I'll type in Hinton, one of our
sales reps, and it finds it as I type.
| | 00:17 | When I press Enter repeatedly, it
will scroll through all of my matches.
| | 00:21 | When I get to the bottom nothing will happen.
| | 00:23 | Now, let's open up the Customers
Order Form and try the same thing.
| | 00:27 | I'll come down to the Search, I'll enter
Hinton and again, when I press Enter it
| | 00:33 | will scroll through all the
records for that particular sales rep.
| | 00:37 | Next, come up to the Home ribbon on the
far right side and click on the Go To button.
| | 00:43 | You'll see First, Previous, Next,
and Last and those match the buttons in the
| | 00:48 | lower left-hand corner of the record.
| | 00:50 | So you can use whichever
location is closer to your mouse.
| | 00:54 | You can also use Find to
locate a record you need.
| | 00:57 | Let's say I need to call one of our
customers who runs a deli, but I can't
| | 01:00 | remember the whole name of the store.
| | 01:02 | First, click in the Company field name,
then click on the big Find button on the
| | 01:08 | upper right-hand side of the Home ribbon.
| | 01:10 | I can type deli in the find box.
| | 01:13 | And then come down here and look in the Look In.
| | 01:16 | Right now it's looking at
the Customers Order Form.
| | 01:19 | I could also have it look just in
the Company name. Next is Match.
| | 01:24 | Right now it says Any Part of Field, so it
will find deli anywhere in the Company name.
| | 01:29 | I also have the option for Whole Field,
but we don't have any place that's just called deli.
| | 01:34 | And I don't want to use Start of Field,
because I know that we have Katz's Deli.
| | 01:38 | So I'll leave it on Any Part or Field.
| | 01:40 | The next search allows me to search
Up and Down, or All of my records.
| | 01:45 | Now here is another option.
| | 01:47 | If I have the word deli occurring in any
other field in the former report, maybe
| | 01:51 | I want to change this so that it has
a capital D and then Match the Case.
| | 01:57 | Now, it will only find any occurrences
of Deli that have a capital D. I'll click
| | 02:02 | Find Next and it will start
scrolling through all of my records.
| | 02:07 | When it can't find anymore records I'll get a
warning message and click OK and then Cancel.
| | 02:13 | Making use of Search, Go To, and Find,
gives you several ways of quickly
| | 02:17 | locating the record that you need.
| | Collapse this transcript |
| Selecting fields, rows, columns, and tables| 00:00 | Anytime you want to use or change your
data you need to highlight or select it first.
| | 00:04 | Here are a variety of options
for selecting data in your tables.
| | 00:08 | Let's work from smallest to biggest.
| | 00:09 | First let's start with the field.
| | 00:11 | Open up your Customers table and click
on any piece of data. Then press the F2
| | 00:16 | key at the top of your keyboard.
Toggling this key will switch you between
| | 00:20 | selecting the entire field and
inserting the cursor at the end of the
| | 00:23 | content so you can edit it.
| | 00:25 | To select an entire record, you can
either click on the grayed box to the left
| | 00:30 | of the record, or go up to the Find
group on the upper right-hand side, click on
| | 00:35 | the Select button, and choose Select.
| | 00:38 | I can then do anything I want with
it, copy it, delete it, format it.
| | 00:41 | To select a column click on the
field name above the record and the whole
| | 00:45 | column will highlight.
| | 00:47 | I can then move it, sort it,
highlight it, or take other actions.
| | 00:51 | To select the entire table, click on
this little grayed box in the upper left
| | 00:56 | corner of the data between the field
names and record numbers and the whole
| | 00:59 | table will get highlighted.
| | 01:01 | You can also go back to that Select
button in the upper right-hand corner
| | 01:05 | and choose Select All.
| | 01:07 | This is great when you want to change
the formatting on all the data at once.
| | 01:11 | For example, I can change the
font to Bold with one click.
| | 01:15 | These selection techniques are very
similar to Excel. When you use Microsoft
| | 01:19 | Office a lot, these
techniques become very intuitive.
| | Collapse this transcript |
|
|
6. Data Entry and Editing ShortcutsUndo and Redo tips| 00:01 | The most frequently used command
in my arsenal will make you laugh.
| | 00:04 | It's the Undo button.
| | 00:06 | When I make mistake I don't want to have
to fix the problem, I can just use undo
| | 00:10 | to put it back the way it
was before I botched it up.
| | 00:12 | Undo is available by default on the
Quick Access toolbar in all Microsoft
| | 00:16 | Office applications.
| | 00:17 | Apparently, I'm not the
only one who makes mistakes.
| | 00:20 | It's the Blue arrow pointing back to the left.
| | 00:23 | The keyboard shortcut for undo is Ctrl+Z,
which you'll soon burn it to muscle memory.
| | 00:28 | I have open my Customers table and
here I have a store called Avulon.
| | 00:31 | Now that looks wrong to me.
| | 00:33 | So I'll change it to Avalon and I'll
change the city to Oakland, but then I
| | 00:39 | realize that it really was correct.
| | 00:42 | Instead of going back and making the
edits I can just go up and click that blue
| | 00:46 | arrow or Ctrl+Z and it
will go back the way it was.
| | 00:51 | Something that works differently from
Word and Excel though is that in an Access
| | 00:54 | table all the corrections to one record
count as one undo step, because they're
| | 00:58 | all done before the record
itself has been committed to disc.
| | 01:01 | So even though I changed Avalon and
its city, two changes, when I go up to undo
| | 01:07 | all I see is one undo step and
I'm back to Avulon and Oaklund.
| | 01:13 | Let's see how this works on a form.
| | 01:16 | I'll open up my Customers Order Form
and I'm going to change it to Layout View.
| | 01:21 | Undo also has the ability to
fix errors several steps back.
| | 01:24 | I'm going to make a few changes
to some of the controls in my form.
| | 01:29 | I'll resize some of the boxes.
| | 01:37 | Now, to undo all those changes, if I
click on the little arrow to the right of
| | 01:42 | Undo, I can see that I
made three Sizing changes.
| | 01:46 | I can select one of them
further on down the list.
| | 01:49 | This allows you to go back in
time and undo several steps at once.
| | 01:53 | But note that Access does not allow me
to just pick and choose off of the list.
| | 01:58 | I can't, for example, only do
my change to the company name.
| | 02:01 | I have to undo all the
work that I've done since.
| | 02:05 | If I then change my mind, and I really do
want the change, I can use the Redo button.
| | 02:10 | The keyboard shortcut for Redo is Ctrl+
Y. Because it's much easier to undo a
| | 02:16 | mistake then to go back and redo the work,
| | 02:18 | Undo will be one of your most used
commands. And Ctrl+Z is my second favorite
| | 02:22 | keyboard shortcut to use, after Save, of course.
| | Collapse this transcript |
| Entering data across, not down| 00:00 | When you sit down to enter data into
a table from a piece of paper, it's
| | 00:04 | tempting to work field by field,
entering in all the names, then all the
| | 00:07 | addresses et cetera.
| | 00:09 | It's natural to skim downwards instead
of across, and to work with similar data
| | 00:13 | before moving on to the next field.
| | 00:15 | But this approach is fraught
with danger, let's take a look.
| | 00:18 | Here is an example of what I
frequently observe when I teach Access classes.
| | 00:23 | When keying data off of a piece of paper,
I see my students working down, instead
| | 00:27 | of across and their tables look like this.
| | 00:29 | You can imagine what would happen if you
did this in a business environment. You
| | 00:33 | stop for your lunch hour, or to take a
phone call, and you forget to go back.
| | 00:38 | You have unintentionally created bad
data. Not only will it come back to haunt
| | 00:42 | you and your coworkers as you use
the database, but it'll look very
| | 00:46 | unprofessional in your supervisor's eyes.
| | 00:49 | For all these reasons, be sure to work
across the rows and entering in all the
| | 00:53 | information record by
record, one record at a time.
| | 00:57 | By entering your data one complete
record at a time, you will greatly reduce
| | 01:01 | data entry errors, and
minimize bad data in your database.
| | Collapse this transcript |
| Repeating data from the record above| 00:00 | When you're entering data, you may find
times when it would be helpful to repeat
| | 00:04 | information from previous records.
| | 00:06 | Here are some useful techniques so that you
don't have to type the same information
| | 00:10 | over and over again.
| | 00:11 | I am going to open up my Products table.
| | 00:13 | I am going to go to the
bottom to start a new record.
| | 00:19 | Notice that each of my olive
oils comes in a variety of sizes.
| | 00:22 | I am going to add a new size, a 4
ounce bottle, to my product line.
| | 00:26 | To duplicate my ID number, I will hold
down my Ctrl key on my keyboard and type
| | 00:30 | a quotation mark, the key just
to the left of the Enter key.
| | 00:33 | It will copy the data directly above it.
| | 00:35 | I will press F2 and the insertion
plate will move to the end of the data,
| | 00:40 | then I will backspace 3 times
and enter in my new numbers.
| | 00:43 | I will press Tab and then Ctrl+Quotation Mark
again, and as before my data will be copied
| | 00:49 | from directly above.
| | 00:50 | I can then continue tabbing
across to enter the rest of my record.
| | 00:57 | If a lot of the data is the same, I can
even copy entire records and then just
| | 01:01 | change some of the fields.
| | 01:03 | Let's say I have a new oil and it's
pricing is exactly the same as the virgin oil.
| | 01:08 | Right-click on the gray box, to the
left of the record that I want to copy, and
| | 01:12 | choose Copy off of the shortcut menu.
| | 01:14 | Now, right-click on the start in the
bottom left hand corner of the table,
| | 01:18 | the one that starts a new
record, and choose Paste.
| | 01:22 | An identical record will fill in.
| | 01:24 | Now be careful, because all
of our IDs have to be unique.
| | 01:28 | The field is already highlighted and
ready for your new data, so I will change
| | 01:32 | this to E004 and tab over my oil
name and change it to Estacada.
| | 01:41 | The rest of my numbers are
already entered and ready to go.
| | 01:44 | By utilizing Copy, Paste and Ctrl+Quotation Mark
mark, you can speed up repetitive data
| | 01:49 | entry, relieving the tedium, as well
as reducing possible data entry hours.
| | Collapse this transcript |
| Inserting today's date| 00:00 | There are several ways you can
insert today's date into a field.
| | 00:04 | Let's open up the Orders table.
| | 00:06 | When I create my order, I want it to fill in
today's date without having to type it out.
| | 00:11 | I can enter it simply by holding down my Ctrl
key and tapping the semicolon on my keyboard.
| | 00:17 | I can also click on this little calendar and
choose the date that has the red box around it.
| | 00:22 | Both of these techniques also work
when you're entering data into a form
| | 00:25 | instead of a table.
| | 00:27 | Even easier, I can set up my Order date
field to automatically fill in the data.
| | 00:31 | I am going to hit Escape to get out
of this record and then click on the
| | 00:35 | Design View button.
| | 00:36 | Now I'll click on OrderDate, and I
will come down here to the Default Value,
| | 00:42 | and I'll type in an Equals sign, the word Now,
an open parenthesis, and a closed parenthesis.
| | 00:47 | I will go back to my datasheet view with
the button in the upper left hand corner.
| | 00:54 | It asked me if I want to save
the table, and I will say Yes.
| | 00:58 | When I look down at my new records, I
can see that the date is automatically
| | 01:02 | entered and waiting for me.
| | 01:04 | Now when I start a new record, I
can tab right over it and start with
| | 01:08 | my Customer number.
| | 01:09 | Using these three techniques, you
will never again have to type in today's
| | 01:13 | date by hand.
| | Collapse this transcript |
| Removing automatic hyperlinks| 00:00 | When you enter in web site URLs or e-
mail addresses, Access will automatically
| | 00:05 | underline them, color them blue,
and turn them into hyperlinks.
| | 00:08 | This maybe useful for quickly jumping to
the Internet or making new e-mail messages.
| | 00:12 | But if you're just looking at the
data or needing to print, all the
| | 00:15 | formatting can be distracting.
| | 00:17 | Here is how to remove automatic hyperlinking.
| | 00:19 | I am starting in my Customers table,
and I am scrolled over to the right-hand
| | 00:25 | side so that I can see my Web Page
column and my Email Address field.
| | 00:29 | You'll be tempted to use the
same technique that you use in Word.
| | 00:33 | To right-click on the link, go down to
Hyperlink, and choose Remove Hyperlink.
| | 00:39 | But in Access 2007, for some
reason, this erases the entire field.
| | 00:43 | I will press Escape on my keyboard
so that this error doesn't get saved.
| | 00:48 | The only way to prevent this formatting
is to not use the hyperlink field type.
| | 00:52 | I will go to my Customer's tab, right-
click on it and change to Design View.
| | 00:58 | Under WebPage, I will change Hyperlink to
Text and I'll do the same thing for EmailAddress;
| | 01:05 | Hyperlink, change it to Text.
| | 01:08 | Now when I right-click on my tab and go
back to Datasheet View, it will ask if I
| | 01:14 | want to save my changes and of course I do.
| | 01:18 | I get a message saying some data
maybe lost, but that's okay, I'll say Yes.
| | 01:24 | And now when we scroll over to the
right, I have my Webpages and my Email
| | 01:29 | Addresses, but there is no formatting.
| | 01:31 | But do note that I no
longer have any interactivity.
| | 01:34 | If I want to go to the Webpage or
start an e-mail to that person, I now need
| | 01:38 | to highlight the data, then copy and paste
it into my browser or new e-mail message.
| | 01:43 | Whether you choose to remove the
automatic hyperlinks will depend if it will
| | 01:46 | be more convenient to click on the
links to communicate, or to see the data in
| | 01:51 | plain text.
| | Collapse this transcript |
| Using AutoCorrect| 00:00 | Most people think about using
AutoCorrect in Microsoft Word to fix typos.
| | 00:04 | But in Access you can creatively use it to
create shortcuts for data you type in repeatedly.
| | 00:09 | For example, let's say you constantly
have to type in the same city, product, or
| | 00:13 | company when you enter records.
| | 00:14 | Instead of entering it in full over and
over and over again, you can type in an
| | 00:18 | abbreviation and the full
text will appear automatically.
| | 00:21 | To set this up click on the Office
button and then on Access options, click on
| | 00:28 | Proofing and then on AutoCorrect Options.
| | 00:33 | A window will open up
with a list of common typos.
| | 00:37 | But instead of entering typos, we are going
to enter in an abbreviation and the full text.
| | 00:42 | Let's say you enter San Francisco
for the city on a regular basis.
| | 00:45 | Click in the Replace box and type SanF
and we will replace it with San Francisco.
| | 00:55 | Then click the Add button.
| | 00:57 | Do note that capitalization matters.
| | 00:59 | Also, if the letter combination that I
choose is something that occurs naturally
| | 01:03 | during regular typing, you should
choose an abbreviation that you're not likely
| | 01:06 | to type unless you're
trying to invoke the AutoCorrect.
| | 01:10 | That's why I typed in SanF instead of just SF.
| | 01:14 | Otherwise, I would find myself typing
away and then all of a sudden, I would
| | 01:18 | find San Francisco
unexpectedly zap into my text.
| | 01:21 | I will go head and click OK, and OK to
close the Options, and I'll go down to
| | 01:27 | enter in a new city.
| | 01:29 | When I go to the city field and I
type in SanF and hit tab San Francisco
| | 01:35 | automatically auto fills.
| | 01:37 | By making strategic use of AutoCorrect,
I can make abbreviations for all my
| | 01:41 | frequently typed text and
save myself a lot of typing.
| | Collapse this transcript |
| Using concatenated fields| 00:00 | You will sometimes find it useful to
take the data in two different fields and
| | 00:04 | merge it into one field.
| | 00:06 | For example, a well-designed database has
separate fields for last name and for first name.
| | 00:11 | But there are times when you'll want to
have that person's full name in one field.
| | 00:14 | Here is how to do that using an
expression technique called Concatenation.
| | 00:18 | Concatenation is done in a query, and
then you base your forms and reports on
| | 00:23 | that query instead of on the original table.
| | 00:26 | Another possibility is to use a Make-
Table query to convert your current table
| | 00:30 | to a new table that includes
the new concatenated field.
| | 00:34 | The latter technique is fine for a
one-time conversion, but it's not
| | 00:37 | practical for ongoing use.
| | 00:39 | I'll show you how to do both.
| | 00:40 | The most common technique is to make
a query containing the concatenation
| | 00:44 | expression and to use that new query
as the basis for your formal report.
| | 00:49 | We already have a report based on a query.
| | 00:51 | My Sales Reps Phone List Query pulls
specific fields out of my Sales Reps Table,
| | 00:56 | and this Sales Reps Phone List report
is based on the results of this query.
| | 01:02 | So what we are going to do is modify the
query to include a new field for a full
| | 01:07 | name that has the first name,
space, the last name, all in one field.
| | 01:12 | So I am going to be on Sales Reps Phone List
Query and I am going to change it to Design View.
| | 01:17 | I'll click in the very last cell and then
I am going to click on the Builder button.
| | 01:25 | I'll start by keying in the new
field, full name using camel caps, a
| | 01:31 | capital F and a capital M, but no
space between them. And then I will put in a
| | 01:36 | colon, and then the space.
| | 01:38 | This tells Access what I
want my field name to be called.
| | 01:41 | Next, I'll double-click on
first name in the center column.
| | 01:45 | If expression shows up, go ahead and delete it.
| | 01:48 | I will click back after first name.
| | 01:53 | To continue with the string I need to
put in an Ampersand, which will link
| | 01:57 | the first name with everything that comes next.
| | 02:00 | Now I need to put his space
between first name and last name.
| | 02:03 | I need to type a quotation mark, and then
a space, and then a quotation mark again.
| | 02:09 | The quotation marks tell Access that
whatever is in between them goes in
| | 02:13 | between these fields.
| | 02:14 | I will then put in another ampersand
to continue the string and then I'll
| | 02:19 | double-click on LastName.
| | 02:21 | It's very important that
everything is entered just like this.
| | 02:24 | Here's my new field name.
| | 02:27 | Here's the first source field in square
brackets, and ampersand to link segments
| | 02:32 | together, quotation marks with anything
that I want in the middle, ampersand to
| | 02:37 | continue the string, and LastName
as my last field. I will click OK.
| | 02:44 | If I want to see the whole string,
I can widen this column to see the
| | 02:47 | whole thing at once.
| | 02:48 | Before I continue I want to make
sure that what I typed was correct.
| | 02:52 | So up in the Query Tools' Design
ribbon, the first button allows me to view it
| | 02:56 | without actually running it.
| | 02:58 | I will click View, and it worked.
| | 03:01 | I now have a new column with my
SalesRep's first name and then the last name.
| | 03:07 | Let's save that query.
| | 03:08 | I will come up here to the Save button.
| | 03:10 | Now let's work on our report.
| | 03:12 | I will go back to my report and I will
right-click on it to go into Design View.
| | 03:18 | I'll start by converting this
LastName field into my new FullName field.
| | 03:22 | I will click on LastName and then I'll come
up here to my Property Sheet and open it up.
| | 03:27 | I can also use the keyboard command F4.
| | 03:30 | When I am on the Alt tab or on the Data
tab, I have a field for Control Source.
| | 03:36 | I will drop that down, and because it's
based on my query, here is my new full
| | 03:41 | name field, I will click on it.
| | 03:44 | Now I need to get rid of first name.
| | 03:46 | So I will click on the FirstName
field, and on my keyboard Delete it.
| | 03:49 | I will also click on the
First Name label and delete that.
| | 03:54 | Next, I'll edit my header.
| | 03:56 | Instead of Last name I want it to just say name.
| | 03:59 | So I'll double-click on the word
Last and hit Delete, and delete out that
| | 04:03 | extra space as well.
| | 04:05 | Now I want to resize these controls.
| | 04:07 | So I'll click back on the FullName control,
hold down my Shift key, click on the
| | 04:13 | Name label, and then while my Shift
key is still down, I will grab the middle
| | 04:19 | right-hand handle and extend it out so
that's the same distance from CellPhone
| | 04:24 | as CellPhone is from
HomePhone, and I will let go.
| | 04:27 | Now let's go to the Report View
up in the upper left-hand corner.
| | 04:31 | Now when we look at our report we
have a full name Concatenated Field on
| | 04:36 | our printed report.
| | 04:37 | The second way we can do a
concatenation is to take our Sales Reps Table and
| | 04:42 | convert it into a new table
with a new compound field.
| | 04:46 | Let's create a new query.
| | 04:48 | I will go to the Create menu and
then on the far right Query Design.
| | 04:53 | I will add Sales Reps to my grid and
then close my dialog box. And I'll put my
| | 05:02 | cursor on the bottom edge and pull it down
to see as many fields as I can at a time.
| | 05:06 | We are going to start
adding our fields to the grid.
| | 05:09 | I will double-click on EmpID,
LastName, and then FirstName.
| | 05:15 | But this is where I am going
to want my concatenated field.
| | 05:18 | So I am going to drag Address down,
skip a column, and add it right here.
| | 05:24 | After that I can continue to
double-click on all of my fields.
| | 05:36 | The next step is to create the
concatenation expression. Click in the first row
| | 05:41 | of the blank column, and then click on
the Builder button up in the ribbon.
| | 05:46 | I'll start by keying in the new field
name, FullName, using camel caps and no
| | 05:52 | spaces, followed by a colon, to
distinguish it from the expression.
| | 05:56 | Now I need to pull the fields that I need.
| | 05:58 | I will double-click on Tables, and then
on Sales Reps, and the Sales Reps fields
| | 06:04 | will appear in the middle. I want First Name.
| | 06:08 | This time because I'm
pulling out of the table,
| | 06:10 | it includes the table name in the expression.
| | 06:13 | I'll edit out this expression code right here.
| | 06:18 | Then I will click back after FirstName.
| | 06:21 | Next, click on an ampersand
to continue the string.
| | 06:24 | Now we need a space
between FirstName and LastName.
| | 06:27 | So I type in a quote, a space, and a quote.
| | 06:31 | Then I will add another ampersand
and then I'll double-click on LastName.
| | 06:35 | Again, it includes the
table name and the field code.
| | 06:39 | Then I will click OK.
| | 06:42 | If I would like to widen this
column out to see it, I can drag it or
| | 06:45 | double-click on it to make it the perfect size.
| | 06:53 | Before I continue I want to make
sure that what I typed is correct.
| | 06:57 | So I am going to go up to the
ribbon to the first button to view it
| | 07:00 | without running the query.
| | 07:02 | This will allow me to see the results
of my expression and it looks great.
| | 07:06 | So I will go back to Design View again.
| | 07:08 | Now that I've got the query working, I
need to change the query type to a Make
| | 07:13 | Table query. I'll come up to the
ribbon to the second query type button from
| | 07:17 | Make Table, and it asked what I
want my new table to be called.
| | 07:21 | I will call it SalesReps_
New and then I'll click OK.
| | 07:30 | Now I will actually run the query.
| | 07:32 | I will click on the red exclamation point,
| | 07:34 | and I will get a message telling me the
number of rows that will be in the new table.
| | 07:39 | It should be the same as your number of rows.
| | 07:41 | I will click Yes and now I have a new table.
| | 07:47 | I'll open it and I can see my new
concatenated field. Because this is a one-time
| | 07:53 | conversion, I am going to
end by closing all my tabs.
| | 07:56 | I am going to right-click on one
of the tabs and choose Close All.
| | 08:00 | It asks if I want to save the changes
to several of my objects and I will say
| | 08:06 | Yes to all of those messages,
and call my query concatenate.
| | 08:13 | Now because this is a one-time
conversion you can delete your old table and
| | 08:20 | your query.
| | 08:22 | The drawback is that from now on
whenever you add a new record, you will still
| | 08:31 | have to enter the full name by hand.
| | 08:37 | That's why the first method
is the standard procedure.
| | 08:40 | Using Queries to concatenate fields is
used to combine data from several fields.
| | 08:45 | It could also be used to
add text after a number.
| | 08:47 | For example, the word
inches after a measurement.
| | 08:51 | While concatenating can be awkward
to use, the possibilities are endless.
| | Collapse this transcript |
| Linking Outlook contacts| 00:00 | If you would like to use your Access
database to manage contacts who are in your
| | 00:04 | Outlook, you can create a Link
Table that will pull in your contacts.
| | 00:08 | If you make any changes in Access,
they'll be reflected in your Outlook as well.
| | 00:14 | Go to the third tab for External Data
and drop down the button that says More.
| | 00:20 | On that list you'll find
Outlook Folder, click on it.
| | 00:24 | You have the option of doing a one time
import of your contacts or you can link
| | 00:29 | to the data source, which will create a
live link between the two. Click OK.
| | 00:36 | We're looking at our Outlook, and I will
click the plus sign next to Address Books.
| | 00:40 | I will also click the plus sign
next to Outlook Address Book and then click
| | 00:45 | on Contacts, and click Next.
| | 00:47 | I am going to modify the name here to
Outlook Contacts, that way I know that the
| | 00:56 | people in this file have
a live link to my Outlook.
| | 00:58 | I will click Finish.
| | 01:00 | I get a confirmation message. I will click OK.
| | 01:07 | Here's my new table.
| | 01:09 | The icon shows me that it's pulling from
my e-mail and this little blue arrow is
| | 01:14 | what tells me there's a
live link between the two.
| | 01:15 | I will open up my Contacts,
and there is everybody.
| | 01:23 | I can now work with this
data like I would any data.
| | 01:26 | I am going to sort my Contacts by last
name, so I will click on Last and drop it
| | 01:32 | down and sort it from A-Z.
| | 01:35 | Let's say I find out that Stacie Charles
actually spells her name without the e.
| | 01:40 | I will click in Stacie, delete the e,
but when I click Off, I actually get this
| | 01:46 | deleted, deleted, deleted message,
and that's because the record that had the e
| | 01:50 | is no longer in the table.
| | 01:52 | I will close out the Contacts, Save
the change, and now when I open it again,
| | 01:58 | Stacy is back and she
doesn't have an e on her name.
| | 02:02 | Let's see how this works from Outlook's side.
| | 02:06 | I will click on Outlook, sure enough
the e is gone. And like I said before, the
| | 02:12 | link works both ways.
| | 02:13 | If I open up Stacy's card, and I'll add
the e back in. I'll Save and close the
| | 02:19 | card, go back to my Access,
the old record is deleted.
| | 02:24 | And when I open it up again, Stacie's e is back.
| | 02:31 | Creating a linked Outlook contact
table allows you to use all the power of
| | 02:35 | Access while working with your Outlook data.
| | Collapse this transcript |
| Saving imports and exports for reuse| 00:00 | If you move data in or out of your
database on a regular basis, you don't need
| | 00:04 | to through all the import
and export steps every time.
| | 00:08 | For example, if you receive data
weekly from your company's larger database
| | 00:11 | system but you use Access for your day-
to-day analysis, those weekly imports
| | 00:15 | will always have the same settings, so
you can expedite the process by saving
| | 00:19 | your setup steps for reuse.
| | 00:21 | Let's say, for example, every Monday
I receive an Excel spreadsheet of new
| | 00:25 | products from Corporate and I need to
add them to my database to start the week.
| | 00:29 | So we will add these new records
to our existing Products Table.
| | 00:33 | I will close this file in Excel,
here's my Products Table in Access.
| | 00:40 | I'll close my Products Table, and now
let's go to the third tab for External Data;
| | 00:46 | there is an Import section here
and an Export section right here.
| | 00:50 | I am going to click on the
Excel button in the Import section.
| | 00:54 | First it asks what file I want to import.
I will click Browse and I'll find the file.
| | 00:59 | I want the Productsimport.
| | 01:02 | Next I have the option of creating a
brand new table from scratch, adding the
| | 01:06 | files data to an already
existing table, or linking the table.
| | 01:10 | If you add the records to a current
table, you will need to make very sure
| | 01:14 | that the field names are identical, the
data types, the number of columns, the
| | 01:18 | spelling, the spacing, absolutely
everything has to be exactly the same
| | 01:23 | between the two files.
| | 01:24 | If you link to the table, you actually
create a live connection between the two.
| | 01:29 | Any changes made to your data in Access
will be reflected in Excel and vice versa.
| | 01:33 | We will tell it to Append and we will
choose our Products Table. Click OK.
| | 01:40 | Here's what our data
looks like. I'll click Next.
| | 01:44 | It says it's going to import to the
Products Table, so I'll click Finish.
| | 01:49 | Now, here's an interesting message, it
says that Microsoft Access was unable to
| | 01:53 | append all the data to the table,
but that doesn't actually mean that it didn't
| | 01:57 | successfully import the records.
| | 01:59 | Let's take a look at what it says.
| | 02:01 | The contents of fields in 0 records were
deleted and 0 records were lost due to key violations.
| | 02:08 | If it says that some number of records
were lost due to key violations, look for
| | 02:12 | duplicates in your primary key
values or your ID numbers in the Excel
| | 02:17 | spreadsheet and the Data Table.
| | 02:19 | So it asks if I want to
proceed and I will say Yes.
| | 02:22 | Since I want to repeat this exact
import on a regular basis, I will put a check
| | 02:26 | mark in front of Save import steps.
| | 02:28 | It creates a name for me, it's an Import,
and the file name is Productsimport.
| | 02:33 | I will give it a Description
so I can refer to it later.
| | 02:44 | So I will know that this is my
weekly products import from Corporate.
| | 02:47 | Now, here it gives you the
option of Creating an Outlook Task.
| | 02:51 | If I do this I'll have a Task in
Outlook that will have a Run Import button
| | 02:55 | right in it, and I can simply click
that button and it will run this import
| | 02:59 | automatically for me.
| | 03:01 | You can even open the Task in Outlook
and set is as Recurring, so it shows
| | 03:05 | up every single week.
| | 03:07 | For right now I am going to uncheck the box.
| | 03:10 | I'll click Save Import.
| | 03:13 | Now let's take a look at our table.
| | 03:14 | I will open up Products.
| | 03:16 | Here's my new Alpharaz, and if I scroll
down, here are my new Montoya records.
| | 03:21 | Remember that error message that we got,
it looks like I didn't actually have
| | 03:25 | any errors, so I'm not worried about it.
| | 03:27 | Sometimes you might get an additional
table up here that says something like
| | 03:31 | Paste Errors, if you get that table,
open it up, it will give you some hints for
| | 03:36 | where to look for the
discrepancy between your two files.
| | 03:38 | Now, here's the cool part, the next
time I have to do that same import,
| | 03:43 | instead of clicking on the Import or Export
Excel buttons, I can now go to Saved Imports.
| | 03:50 | First I will close my Products
Table and I'll click on Saved Imports.
| | 03:55 | Here's the import ready to go, but first I
need to replace my old file with the new one.
| | 04:00 | The replaced file has to have the
exact same name and it has to go into the
| | 04:04 | exact same location.
| | 04:08 | So if I go over to my Outlook, here's
my e-mail that I received from Corporate.
| | 04:13 | I'll right-click on the file and choose Save As.
| | 04:16 | I'll go to that exact same location and
I'll click on the original file to save
| | 04:20 | it with the exact same name.
| | 04:22 | Now, note, if you're using our Exercise
Files, I have provided you with a second
| | 04:26 | copy of the Import, but you will
need to do a little file management.
| | 04:29 | You would need to delete this file and
rename this one by taking off the 2, so
| | 04:33 | that it's just called Productsimport.
| | 04:35 | But I am going to go ahead and replace
my original file with the file from my
| | 04:39 | e-mail, and I'll click Save.
| | 04:43 | It says it already exists,
and Yes, I do want to replace it.
| | 04:46 | So now I will go back to Access, I will
select my Saved Import, and I'll click Run.
| | 04:54 | I got that same message I got before,
so I will proceed, and it says, all
| | 04:58 | objects were imported successfully.
| | 05:01 | I'll close this window, look at my
Products Table, and here are my new products.
| | 05:13 | The import was successful.
| | 05:15 | Look how many steps we cut out by saving
our Import, and if we use the Recurring
| | 05:18 | Outlook Task we would save even more.
| | Collapse this transcript |
|
|
7. Table TechniquesCreating a table using table templates| 00:00 | When you create a new table, you could
build it from scratch or you could use
| | 00:04 | Table Templates as a starting point.
| | 00:06 | Table Templates are pre-created tables that
I can further modify to suit my own needs.
| | 00:10 | First, I am going to create a brand-
new empty database by going to the Office
| | 00:14 | button, choosing New.
| | 00:17 | I'll give it the name,
tabletemplates, and click Create.
| | 00:22 | I am going to start by closing the
table that opened up automatically.
| | 00:26 | I will right-click on it, and choose Close.
| | 00:29 | Now, go up to the Create ribbon and
the second button says Table Templates.
| | 00:34 | There are five readymade tables.
| | 00:36 | I'll start by clicking on Contacts.
| | 00:40 | Now I have an empty Contacts table with an
example of any kind of field I would want.
| | 00:45 | Other Table Templates include Task
progress, Issue resolution, and Events.
| | 00:57 | I can delete any unneeded fields from
these tables, modify them, or add my own.
| | 01:03 | But by starting the Table Templates,
my database has been set up with correct
| | 01:06 | data types and field sizes, saving me a
lot of time and ensuring my accuracy.
| | 01:12 | Using pre-built Access tables can save
a lot of time and effort while creating
| | 01:15 | databases for contact and project management.
| | Collapse this transcript |
| Setting data types| 00:00 | When you create a new table you need to
define what types of data each field will hold.
| | 00:05 | There are a variety of ways to do this,
both as you build and on the fly afterwards.
| | 00:11 | Let's create a new table by going to the
Create Tab and then to the Table button.
| | 00:15 | When I double-click on Add New Field
I can enter my field name, LastName.
| | 00:21 | When I press Tab or Enter, it takes me
straight to my New Field and I'll put in FirstName.
| | 00:28 | Now, when I click back and I look up on
my Ribbon, I can see that they came up
| | 00:32 | with a default data type of Text.
| | 00:36 | For my next field, I will enter in
Salary, and I will click below, but I want
| | 00:42 | this to be currency.
| | 00:43 | So I will go up to the Data Type
drop down and change it to Currency.
| | 00:48 | Notice that some of the data types
particularly currency, number, and date and
| | 00:52 | time offer you additional formatting options.
| | 00:55 | There is a dropdown to choose
common formatting styles, and below that
| | 01:00 | some number options.
| | 01:01 | For example, if in the Salary box I
enter in 10.25 and hit Tab, because my data
| | 01:08 | type is currency, it automatically
puts in my decimal and my dollar sign.
| | 01:13 | But if I don't want the dollar sign, I can
click on this comma and it will take it away.
| | 01:18 | I can also adjust my number of decimals.
| | 01:21 | If I want it back as a Standard
Currency, I can drop this down and choose
| | 01:25 | Currency again and then bring back my decimals.
| | 01:29 | Now, let's switch to Design mode
and look at a few more options.
| | 01:33 | I will click on my Design View
triangle and it will ask me to save my table.
| | 01:37 | I will call this Employees and click OK,
and I will go down to my last field
| | 01:44 | name and type-in Email.
| | 01:46 | I have a Data Type column
where I can pick Hyperlink.
| | 01:52 | You have one last method.
| | 01:53 | I am going to enter in the field name Notes.
| | 01:56 | And when I go to set the Data Type,
it will actually auto-fill for me.
| | 02:00 | All I have to do is type the
M for Memo and there it is.
| | 02:03 | I will hit Tab and it accepts it.
| | 02:06 | You will probably have a favorite
method for creating fields, but being able to
| | 02:09 | work in either Data Sheet or Design
view to choose your data types will make
| | 02:13 | you a power user.
| | Collapse this transcript |
| Understanding number field sizes| 00:00 | When you assign the data type number to
a field you have several considerations
| | 00:04 | to take into account.
| | 00:06 | Understanding the different Number Field
Sizes can help keep your file size down.
| | 00:10 | The first question is what is a number?
| | 00:13 | Your phone number or ZIP code, are made up
of numbers but they're truly text fields.
| | 00:18 | You don't add your ZIP code and my ZIP
code together to create a new location.
| | 00:23 | We use the number data type when
you'll potentially perform some sort of
| | 00:26 | mathematical calculation on that field.
| | 00:28 | A good example would be a Quantity
field where you would add and subtract your
| | 00:33 | orders, or your inventories.
| | 00:36 | Let's take a look at our
Orders table in Design View.
| | 00:39 | Click on your Quantity and you can
see that it has a Number data type.
| | 00:43 | Look down in the Field Properties,
and the default Field Size is Long Integer.
| | 00:48 | But if I use this dropdown
arrow I have many different options.
| | 00:52 | Let's take a look at what each of these means.
| | 00:55 | You have different options because it's
important to reserve the space in your database.
| | 01:00 | You need to choose the most
appropriate Number Field Size for your particular
| | 01:04 | data, because each of the number options
reserves a specific amount of space in your data file.
| | 01:10 | That space is reserved even if you don't use it.
| | 01:14 | So if you reserve too much space, then your
file size gets larger than it needs to be.
| | 01:19 | Let's take a look at the different data types.
| | 01:21 | First, we'll look at Integers.
| | 01:23 | The first type is called a Byte, and a
byte is the smallest practical amount of
| | 01:27 | space your computer can
store one piece of information.
| | 01:31 | So if your data is a number from 0 to
255, choose the Data Size Byte, and it'll
| | 01:36 | only require 1 byte to store it.
| | 01:40 | The next size up is Integer and you'll
use this for whole numbers that range
| | 01:44 | from minus 32,000 to positive 32,000.
| | 01:49 | Notice that it doubles the amount of
space needed. A Long Integer is used for
| | 01:53 | numbers ranging from minus 2 billion
to positive 2 billion and the storage
| | 01:58 | requirement is 4 bytes.
| | 01:59 | Now let's take a look at the Decimal options.
| | 02:02 | Single is used for decimals up to 7
significant digits, a double is up to 15
| | 02:09 | significant digits, the Decimal data
type are for decimals that are up to 30
| | 02:16 | significant digits, and it
takes up to 12 bytes to store it.
| | 02:20 | There's also an option on the list
for Replication ID, but that's used
| | 02:24 | for enterprise compatibility and it's rare
that you'll use it with an Access database.
| | 02:29 | The most common number sizes are Long
Integer, which is the database default and
| | 02:34 | Double for decimals.
| | 02:36 | If you're using the Field as part of a
lookup with an auto number in another
| | 02:40 | table, you need to have the Field Size
as a Long Integer in this foreign table.
| | 02:46 | We go into this in detail in the
Working with auto numbers as foreign keys
| | 02:50 | video in this course.
| | 02:51 | Now, let's go back to our data.
| | 02:54 | In our case when a customer orders, it's
possible they'll want more than 255 of a product.
| | 03:01 | Now honestly, it's not likely,
but it's certainly possible.
| | 03:05 | So a byte is not big enough.
| | 03:06 | So Integer is our next option.
| | 03:09 | I don't think anyone is going to order
more than 32,000 of any one of our olive oils.
| | 03:14 | So that's safe, and since they can't
order half a bottle, I don't have to
| | 03:18 | consider any of the
options that allow for decimals.
| | 03:21 | So in this example, Integer is the
smallest data type that suits our data, and
| | 03:26 | by changing Long Integer to Integer
we've saved half of the disk's space
| | 03:30 | reserved in the database to
hold this one piece of data.
| | 03:33 | It's very small, but over
time that can become significant.
| | 03:37 | Understanding these number types
separates the pros from the amateurs in
| | 03:41 | Access database design.
| | Collapse this transcript |
| Setting default field types| 00:00 | By default, when you create a new field in a
table, it will have the field type of text.
| | 00:05 | But if you work with specialized data,
maybe you need most of your fields to
| | 00:08 | have a different data type or
to have different properties.
| | 00:12 | Instead of changing each field each time,
you can change Access's default settings.
| | 00:17 | To show you what I mean, let's
create a table to check stock purchases.
| | 00:20 | Go to the Create tab and choose Table Design.
| | 00:24 | For my first field, I will call it Open,
and I want my data type to be Currency.
| | 00:31 | My second field will be Shares.
| | 00:33 | I will change my Data Type from Text
to Number, and then if I look down in my
| | 00:39 | Field Properties, I can see that the
Field Size says Long Integer but because I
| | 00:43 | need decimals, I will drop
this down and change it to Double.
| | 00:48 | My third field name is going to be
Stock Name. And it comes up with the Data Type
| | 00:53 | Text, but I don't need it to have 255
characters, all of mine are going to be
| | 00:58 | short, so I can make this 25,
saving me one-tenth of the space.
| | 01:04 | So because this is going to be a
financial database, I would like all of
| | 01:08 | these to be my defaults.
| | 01:10 | I don't want to have to set them every time.
| | 01:12 | So let's change those options.
| | 01:14 | Close this table, and don't save it.
| | 01:18 | Go up to the Office button and
choose Access Options at the bottom.
| | 01:22 | Go to the fourth button down,
Object Designers, and click on it.
| | 01:26 | We are going to change our default
Field Type from Text to Currency.
| | 01:30 | We will change our default Text Field
Size from 255 to 25, and we will change our
| | 01:37 | default number size from Long
Integer to Double, and then click OK.
| | 01:43 | Now, let's try making our database again.
| | 01:45 | Go up to Create and choose Table Design.
| | 01:48 | I'll put in Open for my Field Name and
sure enough it comes up as Currency.
| | 01:53 | My next one is Shares and I will change
it to a Number and the Field Size says
| | 01:58 | Double. And when I put in stock name
and make it Text, my default is 25.
| | 02:04 | So when you have a lot of tables to
create that have non-standard data, changing
| | 02:10 | Access's default options will cut down
on the number of settings you need to
| | 02:13 | modify, but do bear in mind that this
is a global setting, not just for this
| | 02:17 | database but for all of them.
| | 02:19 | So if you work in several different
files, you may not really want to make
| | 02:23 | this change, or after you switch
files, do remember to go back and reset
| | 02:28 | these defaults again.
| | Collapse this transcript |
| Using 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:06 | Field validation ensures that the
correct data is entered and restricts the user
| | 00:10 | from entering inappropriate values.
| | 00:12 | Let's open up the Orders table.
| | 00:14 | Let's say none of our customers will
order more than 100 of any item and we want
| | 00:19 | to make sure that no one
accidentally types in more than that.
| | 00:22 | Switch over to the Design View
and go down to the Quantity field.
| | 00:26 | Down in the Field Properties click
next to Validation Rule, and on the far
| | 00:30 | right-hand side there is an
Expression Builder button.
| | 00:32 | Now, you can either type straight into
this field or you can use the buttons.
| | 00:37 | Most of the time I find it easier to
just type, I will put in >0 and < or
| | 00:43 | =100 and I'll click OK.
| | 00:49 | My validation rule appears below.
| | 00:52 | In the row underneath we
will enter our validation text.
| | 00:55 | In real English tell your
user they are allowed values.
| | 00:59 | Quantity must be between 1 and 100.
| | 01:02 | Up in the Ribbon there is a
button to test your validation rules.
| | 01:06 | Go ahead and click on it.
| | 01:08 | It gives me a message, telling me
what it's about to do and I will say Yes.
| | 01:11 | It asks if I want to save the table?
| | 01:15 | And I do, and sure enough, I get a
dialog box telling me that some of my data
| | 01:19 | violates my quantity validation rule.
| | 01:21 | I will have to go back and look for
orders with less than 1 or more than 100
| | 01:26 | bottles, and either fix the error or
change my validation rule, because it's too low.
| | 01:31 | I will click Yes and I
will go back to my datasheet.
| | 01:36 | When I look through my records, sure
enough there is one at the bottom that has
| | 01:40 | no quantity entered.
| | 01:41 | In this case then, my error lay in bad data.
| | 01:43 | When I fix it, let me enter in 101,
and here is the error message that I receive
| | 01:49 | right at the moment I make the error.
| | 01:51 | I can click OK and fix it on the spot.
| | 01:55 | By setting validation rules for your
individual fields, you will greatly reduce
| | 01:59 | human error during data entry.
| | Collapse this transcript |
| Using flag fields| 00:00 | This tip is less of an Access feature
than it is a practical solution that
| | 00:03 | will help your workflow.
| | 00:05 | Have you ever needed to run a report
or a query that only includes specific
| | 00:08 | records, but they vary each time?
| | 00:11 | For example, maybe I'm doing a series
of promotions for my olive oils, but my
| | 00:14 | selection of recipients isn't based on
any of the data in my Customers table.
| | 00:18 | I need a way to mark who will be included.
| | 00:21 | Flags are really nothing
more than a Yes/No field.
| | 00:24 | Open up the Customers table and make sure
you're clicked in the first field for CustomerID.
| | 00:28 | I'll go to my Table Tools > Datasheet ribbon.
| | 00:32 | Click Insert to add a new
field in the first position.
| | 00:35 | Then change the Data Type
to Yes/No. A box appears.
| | 00:40 | Double-click where it says Field1 and
change the name to Flag. Then press Enter.
| | 00:46 | The next step is to make the field
only as big as the check box itself, so it
| | 00:49 | doesn't take up a lot of room.
| | 00:50 | Hold your cursor over the line between
Flag and CustomerID so that you get a
| | 00:55 | double-headed arrow.
| | 00:56 | This can be a little tricky
because of the dropdown arrow right here.
| | 01:00 | So make sure that you have this cursor shape.
| | 01:02 | I'll click and drag the right border of
the column as small as I can around the box.
| | 01:08 | You may want to make it just big enough
to show the letter F in Flag. There it is.
| | 01:13 | Now, when you have to pick and choose
from items in your table, you can just
| | 01:16 | click on the boxes in front of them.
| | 01:19 | Once you have your list of check marks,
you can use the little black dropdown
| | 01:23 | arrow to select them.
| | 01:24 | I'll turn off No and leave
just the Yes and click OK.
| | 01:29 | Now I filter just the records I want to see.
| | 01:32 | To go back to all of my records I'll
click on the Filter again and choose Select
| | 01:36 | All, then OK, and everything comes back.
| | 01:40 | If you want to run a query based on these
flag check marks, that's easy to do too.
| | 01:44 | Here I have a query called Flagged.
| | 01:46 | When I run it, it only
pulls the records that I need.
| | 01:50 | When I look at it in Design view, I can see
that in my Flag column I have the criteria Yes.
| | 01:59 | Using the Flag check box will come in
handy over the years as you work with your
| | 02:02 | data, because you can use
it for anything, anytime.
| | Collapse this transcript |
| Using an index| 00:00 | If you perform a lot of searches on one
field, you can tell Access to index that field.
| | 00:05 | An Index is a stored set of data so
that when you do a search or a filter,
| | 00:09 | Access already knows
where that information lies.
| | 00:12 | This speeds up your search results,
particularly when you're working with rare
| | 00:15 | or unusual pieces of information
within a large number of records.
| | 00:18 | Access's Index works a lot like
looking up a word in the index at the back of
| | 00:22 | the book, so you can find
that term within the chapters.
| | 00:25 | But just like an index in the back of
a book, you don't need to turn on a
| | 00:29 | field's index if much of the data is identical.
| | 00:32 | That would be like looking at
word The, which appears on every page.
| | 00:36 | If the data is everywhere
an Index is of no benefit.
| | 00:40 | Your primary key field is automatically indexed.
| | 00:43 | You don't have to turn it on manually.
| | 00:44 | I am in my Customers table and I'm
going to switch over to the Design View.
| | 00:50 | I'll click in the Company field, and
then down in the Field Properties I see
| | 00:55 | Indexed, and right now it says No.
| | 00:57 | I'll go over to the far
right and drop that down.
| | 01:00 | Now I have two choices
for turning my Index to Yes;
| | 01:03 | Duplicates OK or No Duplicates.
| | 01:06 | In our case we would use Duplicates OK,
because we can have two branches of the
| | 01:11 | same restaurant as customers.
| | 01:13 | But if every one of your entries in
this field needs to be distinct, you can
| | 01:17 | change this to Yes (No Duplicates).
| | 01:19 | We're going to say Duplicates OK.
| | 01:23 | To see all the indexes you have in a
table go up to the Table Tools, Design
| | 01:27 | ribbon, and click on the Indexes button.
| | 01:29 | Here you'll see a list of all
the fields that you have indexed.
| | 01:33 | You can manage them or
even delete them from here.
| | 01:35 | There is one last consideration.
| | 01:37 | You don't want to do too much indexing.
| | 01:40 | Turning on Index will make your database
run more slowly, because every time you
| | 01:44 | add a new record or change a company name,
Access has to update the entire index
| | 01:48 | before it can continue.
| | 01:50 | But if you frequently reference a
field with distinct information in a large
| | 01:53 | table, an Index will shave
precious seconds off of every search.
| | Collapse this transcript |
| Using a datasheet Totals row| 00:00 | If you're working in a table's
Datasheet view, it can be handy to see instant
| | 00:03 | calculations at the bottom of your table.
| | 00:06 | I have opened my Orders table and on my Home
ribbon at the upper-right there's a Sigma
| | 00:11 | with the word Totals after it.
| | 00:14 | Click on it and at the bottom of
your table you'll see a Totals row.
| | 00:18 | It's not part of your data.
| | 00:19 | As you scroll it will
always remain in that position.
| | 00:23 | When I click in a cell at the bottom
of the column, a dropdown arrow appears.
| | 00:27 | Click on it and you'll see a number of
choices appropriate to that type of data.
| | 00:31 | For our dates, I can find out the
Average Date, how many orders there were, the
| | 00:37 | most recent date, or the first order.
| | 00:39 | My first order was on January 10, 2006.
| | 00:41 | When I click underneath Customer, my only
options are to Count the number, and there's 47.
| | 00:48 | The same goes for Product.
| | 00:50 | The only thing I can do is
count how many orders there were.
| | 00:52 | But when I get under Quantity,
that's where it gets more interesting.
| | 00:56 | I can choose Sum to add up
the number of bottles ordered.
| | 00:59 | I can drop that down and find
the Average size of my order.
| | 01:04 | I can Count the orders.
| | 01:06 | Maximum will tell me my largest order,
Minimum will tell me my smallest order,
| | 01:13 | and then there is
Standard Deviation and Variance;
| | 01:16 | two advanced statistics.
| | 01:17 | Standard Deviation finds the mean of my
quantities and tells me how far off of
| | 01:22 | that average each of my orders are.
| | 01:25 | Variance takes that number and squares it.
| | 01:28 | What's nice is that if I toggle off
my Totals, the row disappears, and when
| | 01:33 | I bring it back again, the statistics that
I most recently had will remain in place.
| | 01:39 | Having instant calculations at my
fingertips prevents me from having to run
| | 01:42 | queries or create reports in order
to do basic statistical analysis.
| | Collapse this transcript |
| Filtering by selection| 00:00 | Filter by Selection is the fastest way
to locate records in your table or form
| | 00:03 | that match your criteria.
| | 00:05 | I'm working right now in my Customers
table, and let's say I want to see how many
| | 00:09 | restaurants and stores I have in California.
| | 00:11 | Find an example of California in
the State field and highlight it.
| | 00:14 | Go up to the Home Ribbon and look in
the Sort & Filter group for the button
| | 00:19 | that says Selection.
| | 00:20 | When I click on it I have four options;
| | 00:23 | Equals CA, Does Not Equal
CA, Contains CA, and Does
| | 00:27 | Not Contain CA.
| | 00:28 | I'll choose Equals CA, and I see
that I have two customers in California.
| | 00:33 | I'll click the Toggle Filter
button to go back to all my records.
| | 00:37 | I'll highlight California again, back
up to the Selection button, and this time
| | 00:41 | I'll choose Does Not Equal California.
| | 00:44 | Now I can see all the other states
except California, and to go back to all my
| | 00:48 | records, I'll click Toggle Filter again.
| | 00:51 | I can also filter by partial fields.
| | 00:54 | In the Company field I'll double-
click on the word Deli, then go back up to
| | 00:58 | the Selection button.
| | 00:59 | This time my options are Contains Deli,
Does Not Contain Deli, Ends With Deli,
| | 01:04 | and Does Not End With Deli.
| | 01:06 | I'll choose Contains Deli, and I can see
that I have three customers with Deli in
| | 01:10 | their name that carry my olive oils.
| | 01:13 | Filter by Selection is just a
of our large variety of ways to use
| | 01:16 | Access's Filter tools.
| | 01:18 | Please see our Access 2007 Essential
Training to see more of them in action
| | Collapse this transcript |
| Resizing columns and rows| 00:00 | Access's tables allow you to resize rows
and columns, so they don't cut off any data.
| | 00:04 | Some of these techniques are a lot like Excel.
| | 00:07 | I have opened my SalesReps table in
Datasheet view and you can see that some of
| | 00:10 | my columns are too wide and
some of my columns are too narrow.
| | 00:14 | If I hold my cursor very carefully over
the line between two of my field names,
| | 00:18 | I'll get a double-headed arrow.
| | 00:20 | Click and hold on it and then you can
drag it narrower or you can drag it wider.
| | 00:26 | If you double-click on the line, you
can also best fit your column to the
| | 00:30 | longest data in the field.
| | 00:31 | Note though that as you add new
records to the table, you may need to resize
| | 00:35 | this column again if longer
data gets added in the future.
| | 00:38 | To specify an exact column length,
right-click on the column header, I'll
| | 00:43 | right-click on Address, and I'll
choose Column Width off the shortcut menu.
| | 00:48 | If I put a check mark in front of
Standard Width, it will default to 11.75.
| | 00:54 | I can also change this number to
anything that I would like. I'll click OK.
| | 00:59 | If I right-click on Address again and
choose Column Width again, I also have an
| | 01:03 | option here for Best Fit.
| | 01:05 | The column now resizes itself to
the longest data entered in the field.
| | 01:10 | All of these same options are
available to resize your rows.
| | 01:12 | If I go over to the left-hand side
and hold my cursor between two of the
| | 01:16 | records, I'll get that same double-headed
arrow and if I drag down, I can make them bigger.
| | 01:22 | You can't, however, just
change the height of one record.
| | 01:25 | You do have the ability to
specify an exact row height.
| | 01:28 | If I right-click on one of the boxes
to the left of the records, I'll get a
| | 01:32 | shortcut menu and I'll choose Row Height.
| | 01:36 | If I choose Standard Height,
I'll get 14.25, and I'll click OK.
| | 01:41 | It's important to have control
over your column and row heights.
| | 01:44 | If you didn't, you could cut off your
data or your fields can take up more
| | 01:48 | room than you need.
| | Collapse this transcript |
| Removing gridlines and shading from tables| 00:00 | By default, all tables are created
with hairline blue gridlines, so you can
| | 00:03 | easily follow your records and fields.
| | 00:05 | But there may be times when you want
to remove the vertical grid lines, the
| | 00:08 | horizontal lines, or the alternating colors.
| | 00:12 | I have opened my
Customers table in Datasheet view.
| | 00:15 | Make sure you're on the Home ribbon
and then look in the Font group for
| | 00:18 | this Gridlines button.
| | 00:20 | When I click on it, I can
see that I'm on Gridlines Both.
| | 00:24 | Horizontal removes the vertical lines,
leaving just horizontal lines separating
| | 00:29 | one record from another.
| | 00:30 | This looks very nice and it's very useful.
| | 00:34 | The third option, Vertical, may be
useful, but what's important to you is
| | 00:38 | scanning your data field by
field, up and down your columns.
| | 00:41 | The last option is None, which
removes all of your gridlines.
| | 00:47 | This can be hard to use unless you
have alternate row shading going on.
| | 00:51 | It's on by default and that's why
we can see colors in every other row.
| | 00:55 | If you want to change the color of these
alternating rows, use the next button over.
| | 01:00 | Drop it down and pick any color you'd like.
| | 01:03 | Be sure not to use a dark color, though, or it
will be hard to read when you print.
| | 01:08 | If you want to turn off the
alternating row colors, click on the button and
| | 01:12 | choose No Color at the bottom.
| | 01:15 | Since I have no colors, I'll go
back to my Horizontal Gridlines.
| | 01:19 | Customizing the gridlines and
shading of your table rows can help your
| | 01:22 | users understand your data by
helping their eye follow in the most
| | 01:26 | practical direction.
| | Collapse this transcript |
| Hiding and unhiding table fields| 00:00 | As you are working with your data, you may
have fields that you only need occasionally.
| | 00:04 | Instead of having to tab over them,
every time you enter in a new record, you
| | 00:08 | can also hide them from view.
| | 00:10 | I have opened up my Customers
table and I am going to scroll over to
| | 00:13 | the right-hand side.
| | 00:15 | Find the Fax column and right-click on it.
| | 00:19 | Find Hide Columns, and it disappears.
| | 00:22 | Let's scroll further to the
right and do the same thing to the
| | 00:26 | Documentation field.
| | 00:28 | Right-click on
Documentation and choose Hide Columns.
| | 00:32 | So one warning though, there is no visual
to remind you that you've hidden some fields.
| | 00:37 | So while this maybe handy in the
moment, if you have multiple users of your
| | 00:41 | database, make sure that they
know that you've hidden the fields,
| | 00:44 | or they might not notice. Or worse, they
may think the columns have been deleted
| | 00:48 | altogether, and not enter
in relevant data as needed.
| | 00:51 | When you want to bring back the field
that's been hidden, right-click on any
| | 00:54 | column header and choose Unhide Columns.
| | 00:58 | This brings up a checklist where you
can turn on and off to show and hide any
| | 01:03 | fields that you would like.
| | 01:03 | I will click Close.
| | 01:06 | An additional use for hiding your
table fields is to allow you to view just
| | 01:10 | the data you need at any moment
without having to run a query to exclude
| | 01:14 | certain fields.
| | Collapse this transcript |
| Freezing fields when scrolling| 00:00 | When you have a lot of table fields
and would like to freeze your important
| | 00:02 | columns as you scroll, it's easy to do.
| | 00:06 | Open up your Customers table. And when I
scroll to the right to see my last data
| | 00:09 | columns, I lose track of which
record I want, scroll back to the left.
| | 00:13 | We are going to lock the
CustomerID and the Company fields in place.
| | 00:17 | Click on the CustomerID column and
drag to the right to highlight Company as
| | 00:21 | well, then let go of your mouse and right
-click again and choose Freeze Columns.
| | 00:27 | When I click off, I can see a
dark blue line next to Company.
| | 00:32 | Now when I scroll to the right,
Customer ID and Company stay in place.
| | 00:36 | When I want to turn off the Freeze,
right-click back on one of the two Column
| | 00:40 | headers and choose Unfreeze All Columns.
| | 00:43 | Now you're back to normal.
| | 00:45 | Freezing fields works just like it does
in Excel, allowing you to hold fields in
| | 00:49 | place while other fields move around them.
| | Collapse this transcript |
| Analyzing your table for redundant structure| 00:00 | If you're new to databases are just
beginning to understand how to construct
| | 00:03 | a relational database, you may have created
a table that has what's called redundancy.
| | 00:08 | In other words, you maybe
unnecessarily having to type in the same data
| | 00:12 | repeatedly instead of creating
relationships between separate tables.
| | 00:16 | Let me show you what I mean.
| | 00:18 | Open up this Order table, in it I see
orders from the same customer over and
| | 00:23 | over again, and with each order I had
to type in the same information, and that
| | 00:28 | opened me up to some typos.
| | 00:30 | If I look over on the right-hand
side at each of the orders, I see the
| | 00:34 | OrderDate, the Product's unique
identifier, but then I also typed in the
| | 00:38 | ProductName over and over
again as well as the Size.
| | 00:41 | If you watch our Access 2007 Essential
Training course, we go into detail into
| | 00:46 | how to properly design your database to
prevent this, but at the moment here I
| | 00:50 | am with redundancy, and I want to fix it.
| | 00:52 | This process is called Normalization.
| | 00:55 | To do that, go to the Database tools
ribbon and click on the Analyze Table button.
| | 01:01 | This feature will allow me to split
this table into three separate tables.
| | 01:05 | One containing a list of all my
customers, a second separate table listing all
| | 01:10 | of the orders they placed, and a
third table with my product information.
| | 01:14 | The first two screens of the wizard
give me information which I can read later.
| | 01:19 | Next, it will allow me to pick my table,
I only have one. I will click Next.
| | 01:24 | Access's Wizard will try
and split the tables for you.
| | 01:28 | If you would like to do it manually,
you can click No, you want to decide, but
| | 01:31 | we are going to let the
Wizard decide. I will click Next.
| | 01:35 | Here are the tree tables it came up with.
| | 01:37 | I will move this one over here and I will
lengthen my table so I can see all the fields.
| | 01:43 | This first table contains all of the
information about the order that's placed.
| | 01:47 | So I will double-click on the
table name and I'll call it Orders.
| | 01:52 | Do note that the name you type here has to
be different from the original table here.
| | 01:56 | I will click OK.
| | 01:58 | My second table is information about my company.
| | 02:01 | So I will call this
Customers and I will click OK.
| | 02:05 | My third table holds the information
for my products, so I will double-click on
| | 02:09 | that header, and call this, Products.
| | 02:12 | And I will click OK.
| | 02:13 | Now let's take a look at the
fields that are in each one.
| | 02:16 | This one is about my orders.
| | 02:18 | My ID refers to the OrderID, that's perfect.
| | 02:21 | Address though, belongs with my customer,
so I will pick it up and drag it over
| | 02:25 | to the second table. Same with City.
| | 02:28 | I will bring that between Address 2 and State.
| | 02:31 | Here is my OrderDate, the
Quantity that's ordered.
| | 02:35 | This will lookup what product has
been ordered, and this will lookup what
| | 02:39 | customer ordered it. So that's perfect.
| | 02:42 | Let's look at our Customers table.
| | 02:44 | CompanyID is the unique identifier for
that company, so that should go first.
| | 02:48 | Address, City, State, Zip,
BusinessPhone. Oh, here is Size.
| | 02:53 | Size belongs with the product and then
in my Product table I have the ProductID,
| | 02:58 | the ProductName, and the Size of the product.
| | 03:01 | So now my three tables have been
separated out. I will click Next.
| | 03:05 | The next window allows us to pick a primary key.
| | 03:09 | For my Customers, CompanyID
is already a unique identifier.
| | 03:13 | So I don't need the one that Access created.
| | 03:15 | I will click on CompanyID and click on the key.
| | 03:18 | I will do the same thing for Product.
| | 03:20 | This is my unique identifier, so I will
tell Access to use that as my primary key.
| | 03:25 | Now I am all set, I will click Next.
| | 03:28 | Now, take a look at this.
| | 03:29 | Access found typos in my normalization.
| | 03:33 | Avulon has two separate P.O. Boxes.
| | 03:36 | This first one is correct, so I
will leave that check box there.
| | 03:39 | I will click on Next Key
and look for more records.
| | 03:44 | Delish Food has two different addresses,
and in this case, the second one is correct.
| | 03:49 | So I will keep that record.
| | 03:52 | Next Key is grayed out, so that's all
the typos that I have. I will click Next.
| | 03:57 | It now gives me an option of
creating a query that brings all the fields
| | 04:00 | together like my original table.
| | 04:02 | I will go ahead and I'll create
that as well and I'll click Finish.
| | 04:06 | My Order table was open, so it's going to change
my query to the name Order_NEW. I will click OK.
| | 04:12 | Access's Help opens up, I am
going to go ahead and close that.
| | 04:15 | Here is my new query with a Lookup to my
Customer and all of my order information.
| | 04:22 | Let's go ahead and close that.
| | 04:24 | Here's my original Order table
and I will close that as well.
| | 04:27 | Let's take a look at our new tables.
| | 04:29 | Customers now holds information just
about each customer. Orders now holds
| | 04:35 | information just about what was
ordered. And Products now holds information
| | 04:40 | just about my products.
| | 04:42 | In addition, if I go up to the
Relationships, these have been set up for me.
| | 04:46 | Now I'm missing my Orders table.
| | 04:48 | So I'll click on Show
Table, and Orders, and Close.
| | 04:53 | I will move my original order out
of the way and now I can see that the
| | 04:58 | relationships between my
tables have been set up as well.
| | 05:00 | I will close this and Save the changes.
| | 05:03 | Access's handy Table Analysis tool
will automatically split your existing
| | 05:08 | tables and set up the appropriate
relationships so that you don't have to do it
| | 05:13 | manually.
| | Collapse this transcript |
|
|
8. Relationship TechniquesSetting a starting AutoNumber| 00:00 | When you set AutoNumber as a Fields
data type, it will automatically start at 1
| | 00:04 | and then increment up from there.
| | 00:06 | But after you have assigned records to
a table, it's impossible to change the
| | 00:09 | AutoNumbers that have been assigned.
| | 00:12 | And, sometimes you may need it to
start at a larger number than 1.
| | 00:16 | Here's how to set that up.
| | 00:17 | Let's say, for example, in my SalesReps
table, I want my EmpIDs to start at 1000
| | 00:23 | and then work up from there.
| | 00:25 | After you finish creating your table,
but before you begin populating it, close
| | 00:30 | your Tables and make a
duplicate of the table you want.
| | 00:34 | I'll right-click on SalesReps, and
choose Copy, and then in the space below, I'll
| | 00:40 | right-click on it again and choose Paste.
| | 00:43 | I'll leave this as Copy Of SalesReps table
and I am only going to paste the Structure.
| | 00:48 | I don't want to include any data
that might be in there, I'll click OK.
| | 00:52 | Right-click on the new copy of your
SalesReps table and open it in Design view.
| | 00:58 | Change the data type of the Primary Key
from AutoNumber to just plain Number and
| | 01:04 | change the Field Size from
Double to a Long Integer.
| | 01:09 | Now switch to Datasheet view
and save the Table when it asks.
| | 01:14 | In the first record, make the Primary
Key field the number before the number
| | 01:19 | that you want to start with.
| | 01:20 | So because I want to start with
1000, I am going to enter in 999.
| | 01:26 | Enter in at least one or
two other fields of data.
| | 01:28 | This is just a placeholder record and it will
be deleted in a little bit. Close the table.
| | 01:34 | I'll right-click on the tab and choose Close.
| | 01:38 | Now, let's make a new query in Design view.
| | 01:41 | I'll go to the Create ribbon and on
the far right, choose Query Design.
| | 01:46 | I'll add the copy of my SalesReps
table and close the Table Chooser.
| | 01:52 | Double-click on the star. That will
add all of the fields to the query at once.
| | 01:57 | Up on the Ribbon, change the Query
Type to an Append Query and then use the
| | 02:02 | dropdown to pick your original SalesReps table.
| | 02:06 | What this is going to do is take the
one 999 record that we created in the
| | 02:11 | copy and add it to the SalesReps table
and then any records that come in after
| | 02:16 | that will start at 1000.
| | 02:19 | Up on the Query ribbon, I'll click Run.
| | 02:23 | Access gives me the message,
| | 02:25 | You are about to append
one row. I'll click Yes.
| | 02:28 | Close the query and don't save it.
| | 02:33 | You can also delete your duplicate table,
we're all done with that. And now open
| | 02:41 | up your SalesReps table.
| | 02:44 | There's your dummy record at 999.
| | 02:46 | Go ahead and delete that one as well.
| | 02:51 | And now, when you create your next
new record, it will start at 1000.
| | 02:59 | This slick trick will give you one more
level of customization to your database
| | 03:03 | solutions by allowing you to
control your starting AutoNumber.
| | Collapse this transcript |
| Creating a multi-field primary key| 00:00 | There may be times when you don't have a
field that makes a good Primary Key and
| | 00:04 | you don't want to create a meaningless
ID field. A third option is to combine
| | 00:08 | two fields and together make
them a dual Primary Key combination.
| | 00:12 | In other words, I can combine two
fields that individually may contain
| | 00:16 | duplicates but the unique combination of
the two can only happen once in the table.
| | 00:21 | For example, if I'm offering a series
of Access Classes I need one table to
| | 00:25 | store the information about the
classes and a second table to store the
| | 00:29 | information about the students.
| | 00:31 | My third table contains the attendance
tracking for their participation, and
| | 00:36 | that's where the multi-field Primary
Key comes, any student can only take
| | 00:40 | one class one time.
| | 00:42 | Right-click on the tabs and choose Close All.
| | 00:46 | Now, let's open up
Attendance in the Design View.
| | 00:49 | I am going to set up my first two
fields, Student and Class, with Lookups so
| | 00:56 | that they pull the IDs
from the other two tables.
| | 00:58 | I'll start with student, I'll click on
the Data Type where it says Number and
| | 01:03 | drop that down and change it to Lookup Wizard.
| | 01:06 | We want the Lookup Column to lookup the
values in another table so we'll click Next.
| | 01:11 | We want to get the information from
the Students table, and we're going to
| | 01:15 | use the ID, the Last Name, and the First
Name to identify the student. I'll click Next.
| | 01:22 | We'll sort the students by
Last Name and click Next.
| | 01:27 | We have the option of showing their ID
number or not, I am going to leave this
| | 01:31 | key column hidden and click Next.
| | 01:34 | We want our Field name to be
called Student so we'll click Finish.
| | 01:39 | It wants to save the table, so we'll say Yes.
| | 01:42 | Now let's do the same thing for Class.
The procedure is exactly the same. I'll
| | 01:47 | change the Data Type to the Lookup
Wizard, we'll lookup the values from the
| | 01:52 | Class table and click Next.
| | 01:55 | We'll choose Classes and click Next.
| | 01:58 | This time we'll use the Class ID
and the Class Name and click Next.
| | 02:04 | We're not going to do a
sort so we'll just click Next.
| | 02:07 | We'll leave this on all the
defaults, and click Next, and Finish.
| | 02:14 | It wants to save the table so we'll say Yes.
| | 02:18 | Now that that's done, click on the gray box
to the left of Student and drag down to Class.
| | 02:24 | With both of them turned slightly darker go
up to the Primary Key button on the Ribbon.
| | 02:30 | Now both the fields have two keys,
indicating that together the student in the
| | 02:35 | Class is the unique occurrence in the Table.
| | 02:39 | Changeover to Datasheet View and
save the table, and now let's put some
| | 02:44 | students in Classes.
| | 02:46 | I'll pick a student, Reagan Chan, and
add them to a Class. And we'll put the
| | 02:55 | class on today's date.
| | 02:57 | Now, I'll add a second
student to that same class.
| | 03:06 | Now I'll add the first student to
a different class on a future date.
| | 03:17 | Now if while I am entering I make a
mistake and I put the same student in the
| | 03:21 | same class, for example, I try and put
Chaney back in the beginning class, after
| | 03:26 | I pick the date it gives me an error
message, The changes I requested to the
| | 03:32 | table were not successful because it
will create duplicate values in the Index,
| | 03:36 | Primary Key, or Relationship,
in this case, Primary Key.
| | 03:41 | We'll have to change that data. So
I'll click OK and then change them
| | 03:45 | to a different class.
| | 03:46 | So you can see that a class can be
listed several times, and a student can be
| | 03:53 | listed several, times but the
class and the student together create a
| | 03:57 | unique occurrence.
| | Collapse this transcript |
| Using subdatasheets| 00:00 | One of the bonuses of creating
Relationships is that you can see sub datasheets
| | 00:04 | within your Tables,
without having to run a Report.
| | 00:07 | Open up your SalesReps table.
| | 00:10 | Notice these Plus signs to the left
of my data, click on the Plus sign
| | 00:13 | next to Jordon Hinton and you'll see
all of his customers, then click on the
| | 00:18 | Plus sign next to one of his customers
and you'll see all of that customer's orders.
| | 00:22 | It would be handy to get some statistics
on those orders. Go up to the Ribbon, to
| | 00:26 | the right of center, and find the Totals button.
| | 00:29 | Click on it and you'll see a total row.
| | 00:33 | Now, I can click at the bottom of
each of these cells to see statistics for
| | 00:36 | that field, for example, I can find the
largest order that was placed by that customer.
| | 00:42 | This Totals row is covered in detail
in the Totals Video in this course.
| | 00:45 | I'll turn the Totals off and click the
Minus sign next to Avulon to close it.
| | 00:50 | Now here's a slick trick, this gray
box in the corner of the Sub Datasheet
| | 00:55 | allows you to expand or collapse all
of the subdatasheet at that level.
| | 00:58 | For example, if I click on that gray box
next to CustomerID, both of my row boxes
| | 01:05 | and my fields turn blue,
that's how I can tell that its on.
| | 01:08 | Now when I click a Plus sign all of
the customers open up all at one time.
| | 01:13 | The Totals button will even affect
all of those windows all at once.
| | 01:18 | Click on the gray box again to affect
all of the subdatasheets and then click
| | 01:22 | the Minus sign, and they'll all close.
| | 01:25 | Using these subdatasheets gives as a
convenient quick glimpse into how our data
| | 01:28 | flows in our hierarchy.
| | Collapse this transcript |
| Using relationship reports| 00:00 | When you build a complex database, it
may help to have a printed copy of your
| | 00:04 | relationships that you
can refer to or show others.
| | 00:07 | Go to the Database Tools ribbon and
then click on the Relationships button.
| | 00:12 | Arrange your table so that
you can see all the fields.
| | 00:16 | You can also make the tables wider as well.
| | 00:18 | When you have your tables so you like
them, click on the Relationship Report
| | 00:22 | button on the left side of the ribbon.
| | 00:25 | Here you can change your
paper size if you need to.
| | 00:27 | I like to use Landscape so
I can see more of my tables.
| | 00:31 | You can change the Margins
right here if you'd like.
| | 00:33 | There is a check box here for Print Data Only.
| | 00:36 | If you click on this, it will print just
list of fields without the Page Header,
| | 00:40 | Table Headers, or Relationship Lines.
| | 00:42 | I'll go ahead and uncheck that box again.
| | 00:44 | Now notice that my last table is cut off;
| | 00:48 | I need to do some more adjusting.
| | 00:50 | If I Close Print Preview and if I try
to move my objects while I am here, it
| | 00:56 | actually moves all of the elements as
separate objects, which I don't want.
| | 01:00 | I'll press Ctrl+Z to undo that.
| | 01:03 | On the other hand, if you do want to do
any additional formatting to this report,
| | 01:07 | such as add a logo or any other
text boxes, this is the place to do it.
| | 01:13 | Now notice that my
Relationships viewer is still open.
| | 01:16 | So I can click back on that tab and
then rearrange my tables so that they
| | 01:19 | are closer together.
| | 01:22 | Now, I can go back up to the
Relationship tools Design ribbon and back to
| | 01:27 | Relationships Report.
| | 01:29 | Change it to Landscape
again and now everything fits.
| | 01:32 | When I am ready to print, there is a
Print button on the left side of the ribbon.
| | 01:36 | If you want to share your report with
others, I like to use this PDF button
| | 01:40 | right here, and then I can save a PDF
to my computer that I can then e-mail
| | 01:44 | or upload as needed.
| | 01:46 | Being able to print and save your
Relationship views makes it easy to keep as a
| | 01:49 | reference or to share it with others.
| | Collapse this transcript |
|
|
9. Query TechniquesUsing wildcards| 00:00 | There may be times when you're running
a query, but you don't know the exact
| | 00:03 | spelling of a name, or you want
several variations of a match to appear.
| | 00:08 | In both of these cases, you can take
advantage of wildcard characters to
| | 00:12 | represent the letters or words that may vary.
| | 00:13 | Go up to the Create ribbon and
to the right, choose Query Design.
| | 00:18 | Add your Customers table to the
query and then close the Table window.
| | 00:24 | I'll expand this out to make it longer
and add Company and Notes to the grid below.
| | 00:32 | In the Company column, click on
the Criteria line and type, asterisk
| | 00:37 | deli asterisk.
| | 00:39 | The asterisk represents
unknown letters, any number of them.
| | 00:43 | By putting an asterisk before and after
the word deli, I'm telling Access that
| | 00:47 | deli can appear in the beginning,
the middle, or the end of the name.
| | 00:52 | Run the query and you
have three different matches.
| | 00:55 | Now go back to Design view.
| | 00:59 | Notice that Access has added in the word Like
and quotation marks around my search string.
| | 01:04 | Now let's edit this.
| | 01:06 | Take out the first asterisk.
| | 01:08 | In this case, Deli would have to be the
first four letters of the name and when
| | 01:13 | I run it, my match is Delish Food.
| | 01:15 | If I put that asterisk back and take
out the asterisk at the very end, now I'm
| | 01:23 | telling it that I want Deli at the end of the
name, and here I have Istria Deli and Katz's Deli.
| | 01:31 | There are additional wildcards too.
| | 01:33 | I'll take this criterion out.
| | 01:36 | I have a customer called Avulon,
but it's not spelled the normal way and I
| | 01:40 | can never keep track.
| | 01:41 | So if I type in Av?l?n, the question
mark stands for just one letter, instead
| | 01:50 | of a potential string.
| | 01:53 | When I run the query, I get my company, Avulon.
| | 01:56 | Go back to the Design view and
again Access has put on the like and the
| | 02:00 | quotation marks around it.
| | 02:01 | But if you do a search that
starts with the question mark
| | 02:05 | or the asterisk, when you run that,
you'll actually get an error message
| | 02:11 | saying that the expression you
entered contains invalid syntax.
| | 02:16 | So if I am going to do a query that
contains one of the wildcard characters, I
| | 02:19 | do have to manually type in Like and
the quotation marks myself. There it is.
| | 02:30 | Now what happens when you're searching for
text that includes one of these characters?
| | 02:35 | In that case, you have
to use very specific code.
| | 02:38 | I'll take out the criteria under
Company and let's look at our Notes field.
| | 02:43 | In my database, my customers have
questions that need to be answered that are
| | 02:47 | tracked in the Notes field and every one of
those questions ends with a question mark.
| | 02:52 | So in the Criteria line, under Notes,
I'll type in Like quotation mark, an
| | 02:59 | asterisk, which will start our
text match, and then a square bracket
| | 03:04 | to indicate that we're using a special character.
Then my question, then another square bracket
| | 03:10 | to close it, then my end quotation mark.
| | 03:13 | So that it looks like this.
| | 03:14 | I'm looking for any string
that ends in a quotation mark.
| | 03:20 | When I view my query, I have two
customers with questions that need answering.
| | 03:24 | I'll go back to Design view again.
| | 03:27 | Now note that if I forget to start
with Like and my quotation mark and I just
| | 03:33 | have my brackets, that
creates a Parameter query.
| | 03:36 | Parameter queries are covered in the
Access 2007 Essential Training Course.
| | 03:41 | Now there are a few more
wildcards that you might find useful.
| | 03:44 | We've already seen the
question mark for one character.
| | 03:47 | There is also a number sign
if you want to look for one number.
| | 03:52 | We've also seen the asterisk
when you're looking for a string.
| | 03:55 | Now you can combine these.
| | 03:58 | So if I enter in ?a*,
that's telling Access that I'm
| | 04:03 | looking for one letter, then the
letter a, and then anything else at the end.
| | 04:08 | So therefore, a would be my second letter.
| | 04:11 | Possible matches might be bat or batter.
| | 04:15 | I can also tell it that I want the second
letter to be one of several different options.
| | 04:20 | If I put in my question mark for my
first character, then a bracket, then ae,
| | 04:25 | and then my asterisk,
| | 04:27 | this is telling Access that my second
character can either be an a or it can
| | 04:31 | be an e. Possible matches would be not
just bat and batter, but also bet and better.
| | 04:37 | You can also exclude characters.
| | 04:39 | If in these square brackets I put in
an exclamation point, now I am
| | 04:43 | telling it that the second letter needs
to be anything except an a or an e, in
| | 04:48 | which case I might get bit and bitter.
| | 04:52 | Last but not least, you can also have a
wildcard to select a range of letters.
| | 04:56 | So for example, if we want the second
letter to be an a, a b, a c, a d or an
| | 05:02 | e, it would be ?[a-e]*.
| | 05:09 | So now my matches would be Eat, which
has an a as the second letter, able which
| | 05:14 | has a b as a second letter, ace which
has a c as a second letter, Edit which has
| | 05:19 | the d as a second letter, or feat
which has an e as a second letter.
| | 05:24 | Using wildcards will allow you to run
queries with variables, exponentially
| | 05:28 | increasing your ability to find
matching records in your tables.
| | Collapse this transcript |
| Using IN instead of OR| 00:00 | When you're writing an Or query with
multiple values, sometimes using the In is
| | 00:05 | better than using Or.
| | 00:07 | You can use the In operator when
you need to determine whether a value
| | 00:10 | equals one of many values.
| | 00:12 | Right now I am in the In State
query and let's got to Design view.
| | 00:17 | I'll hold my cursor between State and
WorkPhone to get a double-headed arrow and
| | 00:21 | double-click to expand it out.
| | 00:24 | So here, I'm looking for customers
within a set of states, and this criterion
| | 00:28 | expression uses several Or operators;
| | 00:30 | CA Or NV Or NM Or AZ Or MD or PA.
| | 00:37 | A simple alternative is to use the In operator.
| | 00:40 | I'll click in the line below and I'll
type In, and then an open parenthesis, and
| | 00:47 | then I'll list my states.
| | 00:49 | CA, NV, NM, AZ, MD, and PA, and I'll
| | 00:55 | finish with a closing
parenthesis.
| | 00:57 | When I hit Tab, notice that Access will
even type in the quotation marks for me
| | 01:02 | so that I don't have to do it myself.
| | 01:03 | Notice how much shorter an In
statement is than an Or statement?
| | 01:08 | I'll take out the original Or
statement and when I run the query, my In
| | 01:13 | statement gives me the exact
same result as my Or statement.
| | 01:16 | I'll go back to Design view.
| | 01:19 | This syntax can also be used in
validation rules, SQL statements and macros,
| | 01:24 | anywhere you write code.
| | 01:26 | When you want to check to see if a
field is within a specific data set, an In
| | 01:29 | statement is a nice
alternative to lengthy Or statements.
| | Collapse this transcript |
| Hiding query fields| 00:00 | There'll be times when you'll run a
query based on a specific criterion but you
| | 00:04 | don't want to see it in your result.
| | 00:06 | It's easier to hide those columns, so
you don't have to see the repeated match.
| | 00:10 | For example, right now I am looking at
the Maryland Orders query in Design view;
| | 00:14 | it says State, MD.
| | 00:15 | When I run it, I can see all the
orders that have been placed from that
| | 00:19 | State, MD.
| | 00:21 | But because the query name is Maryland
Orders, may be I don't want to see this
| | 00:24 | repeated list of MD, MD, MD, MD.
| | 00:27 | Go back into Design view and
uncheck this box where it says Show.
| | 00:34 | When I go back and run the query
again, I get the exact same results.
| | 00:38 | They're all orders from Maryland, but I
no longer have to look at the repeated
| | 00:42 | State field in my results.
| | 00:43 | Hiding the Criteria field from the
query streamlines the results so they are
| | 00:47 | less cluttered, and it eliminates redundancy.
| | Collapse this transcript |
| Changing captions| 00:00 | By default, the column names in your
query results will be the same as the field
| | 00:04 | names from your tables.
| | 00:06 | But you have the ability to customize the
column header to say whatever you would like.
| | 00:10 | This is particularly helpful to
translate abbreviated field names into real
| | 00:14 | English with capitalization,
spacing and descriptive titles.
| | 00:18 | Right now I am looking at the Order
Analysis Query and let's explore how this was done.
| | 00:23 | Notice the column headers such as
Smallest Profit and Largest Profit.
| | 00:27 | I know that these are not names of fields
underneath my tables, so how do they get here?
| | 00:32 | Let's take a look at the query in Design View.
| | 00:36 | First, let's resize our columns
so that we can see the data inside.
| | 00:40 | I will find my double-headed arrow;
| | 00:42 | double-click on each of
the columns to resize them.
| | 00:48 | See how each of these columns has real
English words followed by a colon,
| | 00:52 | then a field name from one of the tables.
| | 00:55 | These names are descriptive of
the contents of the query result.
| | 00:58 | For example, this field is
the Average of all of my Prices.
| | 01:04 | So makes sense to title my field
Average Price, instead of just the word Price.
| | 01:09 | Smallest Profit and Largest Profit are both
calculations, so they don't have field names;
| | 01:13 | I need to describe what they actually are.
| | 01:17 | Smallest Profit is the minimum of the
difference between the price and the cost
| | 01:22 | of each of my products.
| | 01:24 | So all you need to do to create a
caption is to click before the field name in a
| | 01:28 | query, key in the column header you'd
like to see, follow it with a colon
| | 01:33 | and a space, and you've got a caption.
| | 01:37 | Making good use of captions will
help your users understand the data
| | 01:40 | that they're looking at.
| | Collapse this transcript |
| Finding duplicate records| 00:00 | Your database is only as good as
the data that's entered into it and
| | 00:04 | unfortunately, we are all
human and we do make mistakes.
| | 00:07 | If you've designed your table
structure well, you can eliminate a lot of
| | 00:10 | incorrect data, but not everything.
| | 00:13 | It's almost inevitable that you'll
eventually find some records entered more
| | 00:16 | than once, with just a few
discrepancies between the fields.
| | 00:20 | Access has a Find Duplicates query
that will allow you to compare records and
| | 00:23 | identify potential repeats.
| | 00:26 | Let's say it's the end of the year
and it's time to clean up my data.
| | 00:29 | I can run this query on my Customers
table to make sure that no one created two
| | 00:33 | accounts for the same entity.
| | 00:35 | Go to the Create Ribbon and then find
the Query Wizard on the right-hand side.
| | 00:40 | Choose the Find Duplicates
Query Wizard and click OK.
| | 00:44 | We're going to base it on our
Customers Table, so I'll click Next.
| | 00:48 | Now we have to select the fields.
| | 00:49 | This is the tricky part.
| | 00:51 | If you pick one that's likely to have
variations like the Address field, you're
| | 00:55 | less likely to find identical records.
| | 00:58 | If you pick too many fields, just one
discrepancy will eliminate the erroneous
| | 01:01 | record from the query results.
| | 01:04 | So think carefully about your data,
and which fields would be likely to be the
| | 01:07 | same, and which would help
you to identify duplications.
| | 01:10 | For example, I am going to choose
City and move it over to the right-hand
| | 01:14 | side, since duplicates will be likely
to be in the same town and it's less
| | 01:19 | likely to have typos.
| | 01:20 | I'll also choose the
WorkPhone, then I'll click Next.
| | 01:26 | Now I want to know which
fields I want to see in the results.
| | 01:29 | I want to be able to see all of them
for the best comparison, so I'll click
| | 01:33 | the double-arrow in the middle of the window
to move all the fields to the right-hand side.
| | 01:38 | I'll click Next and I'll keep the
default name and then click Finish.
| | 01:44 | Sure enough, I have a few duplicates.
| | 01:47 | Kats'z Deli was entered with two
different company names and a typo by two
| | 01:54 | different Sales Reps.
| | 01:56 | I probably shouldn't just delete the
erroneous record, I may delete associated
| | 02:00 | orders or other data.
| | 02:02 | I'll need to talk to my reps and change
the customer number on previous orders.
| | 02:06 | Once that's been done, I can delete the
extra record and run the query again to
| | 02:10 | look for additional duplicate data.
| | 02:12 | Performing this query on a regular
basis will help keep your data error free.
| | Collapse this transcript |
| Moving records with append and delete queries| 00:00 | There are a variety of queries that you
can use to modify your data. These are
| | 00:04 | called Action Queries.
| | 00:06 | When you want to move records from
one table to another, combine an Append
| | 00:09 | Query and a Delete Query.
| | 00:11 | The Append Query will add records that match
your criteria to the end of another table.
| | 00:16 | Then changing that query to a Delete Query
will remove them from the original table.
| | 00:21 | This has some nice practical uses.
| | 00:22 | For example, maybe once a SalesRep
leaves your company, you'd like to move them
| | 00:27 | to a table of Past Employees, instead
of looking at the full list of everyone
| | 00:31 | who's ever worked at your company.
| | 00:35 | The first thing I'll need to do is to
create a new table to move those records in to.
| | 00:39 | It needs to have all the same fields
and field names as the original content.
| | 00:43 | So I am going to close my
SalesReps table, right-click on it in the
| | 00:47 | Navigation pane and copy it.
| | 00:51 | Then I'll right-click on
it again and choose Paste.
| | 00:55 | I'll change the Table Name to SalesRepsArchive.
| | 01:00 | I'll also change the Paste
Options to Structure Only.
| | 01:04 | I want it to be empty, I don't want
any of the data in it. I'll click OK.
| | 01:09 | Now we'll create our query to move
the SalesReps who are no longer with us
| | 01:13 | into that new table.
| | 01:16 | Go to Create Ribbon and go over
to Query Design on the far right.
| | 01:21 | Add the original SalesReps
table and close the chooser.
| | 01:26 | I'll extend the table down to
see as many fields as possible.
| | 01:30 | In the Ribbon, change the Query Type to Append.
| | 01:34 | In the pop-up window, choose the
SalesRepsArchive table from the dropdown.
| | 01:40 | Note that you could also move the
records into an entirely separate database and
| | 01:43 | take them out of this one if you'd
like. We're going to leave them in the
| | 01:47 | Current Database. And click OK.
| | 01:49 | Now we have to add all the fields to
the query, double-click on each one.
| | 02:06 | I'll scroll that to the
bottom and get the last one.
| | 02:08 | Scroll over in the grid
until you can see EndDate.
| | 02:13 | Click in the Criteria line underneath
EndDate and type in Is Not Null. Null means blank.
| | 02:22 | By asking for all the EndDates that are
not blank, Access will bring up all the
| | 02:27 | records that have a date entered,
indicating that the SalesRep's employment with
| | 02:30 | us has already ended.
| | 02:31 | If I go up to the Ribbon and click on
the View button I'll see which records
| | 02:36 | will be moved and I can
double-check them for accuracy.
| | 02:40 | If everything looks right, go back to the
Design View and then click on the Run button.
| | 02:46 | I'll get a message telling me how many
rows will be appended, and I'll click Yes.
| | 02:52 | After the query runs, open up the
SalesRepArchive table and you'll see all the
| | 02:57 | SalesReps who are no longer
with the company. Close this table.
| | 03:03 | Now that the records have been added
or appended to the Archive table, we are
| | 03:07 | ready to delete them from
the main SalesReps table.
| | 03:10 | Go back to the Query Tools Design Ribbon
and change the Query Type to a Delete Query.
| | 03:16 | Now Run the Query again.
| | 03:20 | This time it will tell you that
you're going to delete ten rows from the
| | 03:22 | specified table and that
we cannot undo this action.
| | 03:25 | We'll go ahead and say Yes.
| | 03:28 | Now when we open up our SalesReps table,
all that are left are active employees.
| | 03:34 | When we open up the SalesRepsArchive
table, there are all of our past employees.
| | 03:40 | I'll close both the two tables.
| | 03:43 | You can save this query for future use or
don't save it if you won't need it again.
| | 03:48 | Because I'm probably going to do this
update periodically, I am going to save it.
| | 03:52 | But first, I'm going to
change it back to an Append Query.
| | 03:55 | Go back to the Query Tools and
choose Design, and click on Append.
| | 04:00 | We want it to go to the
SalesRepsArchive table, so I'll click OK.
| | 04:05 | This is important, because if I
saved it as a Delete Query and then I
| | 04:09 | accidentally ran it, I would wipe out
all of my SalesReps before I'd move them
| | 04:13 | to the Archive table, and that's not good.
| | 04:16 | Now that it's an Append Query
again, I'll save it, and give it the
| | 04:21 | name ArchiveSalesReps.
| | 04:26 | Using Append Queries to automate the
move of records from table to table is a
| | 04:30 | very practical feature.
| | Collapse this transcript |
| Using make-table queries| 00:00 | When you want to make a new table using
already existing records, you can use a
| | 00:04 | Make Table Action Query to search for
the records you want, and move them into a
| | 00:08 | separate table, all in one automated step.
| | 00:11 | I have opened my Customers table and
when I scroll to the right to the Type
| | 00:15 | field, I can see that some of them are
Retail establishments, some of them are
| | 00:19 | Restaurants, and some of them are both.
| | 00:22 | This type field is a multi-value field.
| | 00:25 | But let's say I want to separate out
just the customers who retail my olive oils.
| | 00:30 | I'll go to the Create Ribbon and
to the Query Design button on the
| | 00:33 | far right-hand side.
| | 00:36 | I'll add the Customers table to
the grid and close the window.
| | 00:40 | Go up to the Ribbon to the Query Types
and change this to a Make Table Query.
| | 00:45 | It asks what I want to name the
new table and I'll call this Retail.
| | 00:51 | I'll extend out the bottom of the table
so that I can see more fields and I'll
| | 00:55 | double-click on them to add them into the grid.
| | 00:57 | Now when you get down to
the Type field, be careful.
| | 01:05 | This type field contains the
designation we need and it is a multi-value field,
| | 01:09 | which means that it can either hold
Restaurant, or the word Retail, or both.
| | 01:13 | This regular type field won't work.
| | 01:16 | That's what contains all of the options.
| | 01:18 | Instead, I need to choose Type.Value,
which separates the contents into
| | 01:23 | their distinct terms.
| | 01:26 | Now scroll down and add the rest of the fields.
| | 01:34 | Once your fields are added, scroll over
to the right-hand side so that you can
| | 01:38 | see the Type.Value field.
| | 01:40 | If you did just enter in Type, click on it and
use the dropdown and change it to Type.Value.
| | 01:46 | Now click on the Criteria line. Here's
where we type in Retail to pull in all of
| | 01:54 | our retail customers.
| | 01:55 | Now go back upto the Ribbon to
the upper left and choose run.
| | 01:59 | It tells us You are about to place
13 rows into a new table, click Yes.
| | 02:05 | Now over on the left in our
Navigation pane is our new Retail table.
| | 02:09 | I'll open up my Retail table and I can see
that these are all of my retail customers.
| | 02:16 | Using the Make Table Query allows
you to create new tables on the fly,
| | 02:19 | automatically populating them with
fields and records of your choice.
| | Collapse this transcript |
| Creating an update query| 00:00 | When you'd like to change several
records in your table, you don't have to make
| | 00:03 | all those changes by hand. You can
automate it using an Update Action Query.
| | 00:09 | I have open my Customers table.
| | 00:11 | And when I am going to scroll over and
look at my SalesReps, I can see the Sales
| | 00:14 | Rep Number and when I click on
them, I can see the SalesRep Name.
| | 00:17 | Now let's say SalesRep Number eight,
Collins, is leaving, and we are going to give
| | 00:22 | all of her customers to Pearl
Davenport, SalesRep number 10.
| | 00:27 | Let's create an Update Query to do this.
| | 00:28 | I'll go up to the Create Ribbon, and
then to the far right to Query Design, and
| | 00:35 | I'll add my Customers table to the grid.
| | 00:39 | The only field I need to add is the SalesRep.
| | 00:42 | So I'll double-click on
SalesRep to bring it down here.
| | 00:46 | I'll go up to the Ribbon to the Query
Type and change this to an Update Query.
| | 00:51 | Now, I have a new row here that says, Update To.
| | 00:55 | So my Criteria is that the Sales
Rep number 8 is going to be updated to
| | 01:00 | SalesRep number 10.
| | 01:02 | I'll come up to the Ribbon and click Run.
| | 01:05 | It says I am going to update three rows,
I'll say Yes and now when I go look at
| | 01:11 | my Customers table, I can see that I
don't have any SalesReps number 8 anymore,
| | 01:15 | all of them are number 10.
| | 01:17 | Here's another use for my Update Query.
| | 01:19 | I am going to right-click on all my
tabs and Close All and I'm not going to
| | 01:23 | save my SalesReps Query.
| | 01:25 | Open up the SalesReps table and on the
far right, I can see all of their Salaries.
| | 01:31 | So now it's the beginning of the year and
everybody is going to get a 25 cent increase.
| | 01:36 | I'll get back to the Create Ribbon and
create a new query in Design View and
| | 01:42 | I'll add the SalesReps
table to the grid and close it.
| | 01:46 | Again, I'll change this to an Update Query;
| | 01:50 | the field that I want is Salary.
| | 01:53 | Since I am going to apply this to
everybody, I don't need to put in a Criteria.
| | 01:58 | But I do need to build a calculation
to add 25 cents to their Salary.
| | 02:03 | So I'll click in Update To and I'll come
up to the Ribbon to the Builder button.
| | 02:08 | I'll double-click on Tables and then on
SalesReps, so that I can find the Salary field.
| | 02:15 | I'll double-click on Salary,
and then I'll add to it, 25 cents.
| | 02:22 | So it's going to update their current
salary to 25 cents more. And click OK.
| | 02:30 | I'll run my Query, it says I am about
to update 25 rows and it can't undo the
| | 02:35 | action, I'll click Yes. And now when I
look at my SalesReps, all of them have just
| | 02:41 | received a $0.25 raise.
| | 02:43 | You can see how using an Update Query
will allow you to change multiple records
| | 02:46 | in your table in a flash.
| | Collapse this transcript |
| Turning a query into a PivotTable| 00:00 | You'll use Queries extensively to
explore your data and you can actually take
| | 00:04 | your analysis a step deeper by
turning your queries into PivotTables.
| | 00:08 | PivotTables allow you to interactively
analyze your data along more than two dimensions.
| | 00:13 | Let's start by
understanding our scenario and our goal.
| | 00:16 | I'm looking at my Orders Table and I
can see the Date an Order was placed, the
| | 00:20 | Customer who placed the order, the
Product that they ordered, the number of
| | 00:25 | bottles that they ordered, and the OrderStatus.
| | 00:27 | If I go over to my Navigation pane and
down to my Order Totals Query, it takes
| | 00:34 | this same data but writes out the
company and the product, I have the bottle
| | 00:38 | size, the Price, the Quantity
and an extended total amount.
| | 00:42 | If I look at this in Design View in the right
-hand column, I can see the new calculation.
| | 00:48 | When I go over to the View button and
click on the bottom half where it says
| | 00:52 | View, I have some additional options.
| | 00:54 | I am going to choose PivotTable View.
| | 00:58 | Now I have a white grid with
placeholders on the left and the top.
| | 01:02 | There's a Field List on the right.
| | 01:04 | If you don't see the Field List,
click on the Field List button right here.
| | 01:09 | Pick up your company and drag it
where it says Drop Row Fields Here.
| | 01:14 | Now we have a list of our customers.
| | 01:16 | Pick up Product and bring it to the top
where it says Drop Column Fields Here.
| | 01:21 | Now here are all our different olive oils.
| | 01:23 | If I pick up size and bring it right
next to Product, now the olive oils are
| | 01:30 | broken up by the size of the
bottles that customers have ordered.
| | 01:34 | Now pick up your Totals and drag it
into the center where it says Drop Totals
| | 01:38 | or Detail Fields Here.
| | 01:40 | In addition to the previous view where
I can see the Grand Total per order, now
| | 01:44 | I can see the Grand Total
by product for each company.
| | 01:49 | I can also get creative.
| | 01:51 | If I pick up my Quantity field and
drag it into my Totals, I can now also see
| | 01:56 | the number of bottles that each
company has ordered of each product.
| | 02:02 | This new Analysis View
will be saved with the Query.
| | 02:04 | So now I'll be able to switch back
and forth between the Design View, the
| | 02:09 | Regular Query Results, and
this new PivotTable View.
| | 02:14 | Creating a PivotTable from my Query
will allow me to do detailed analysis
| | 02:18 | anytime on multiple dimensions simultaneously.
| | Collapse this transcript |
| Turning a query into a PivotChart| 00:00 | You'll use Queries extensively to
explore your data and you can visualize your
| | 00:04 | data by turning your Queries into PivotCharts.
| | 00:06 | PivotCharts allow you to
interactively view your query results along more
| | 00:10 | than two dimensions.
| | 00:12 | Let's take a look at our scenario and our goal.
| | 00:13 | I am looking at my Orders table where I
can see my OrderDate, and I have lookup columns
| | 00:19 | for my Customer and my Product.
| | 00:22 | Here is my total number of
bottles ordered, and my OrderStatus.
| | 00:26 | If I go over to my Navigation pane
and down to my Order Totals Query, I can
| | 00:32 | see the same order but it's written
out in English, with the Company name, and
| | 00:36 | the Product, and the Size, and the
Price, and I can also see a grand Total for
| | 00:41 | that particular order.
| | 00:43 | If I look at this in Design View, in the
far right column I can see that calculation.
| | 00:49 | But if I go up to my View button and
I click on the bottom half with the
| | 00:53 | dropdown arrow, I have some additional options.
| | 00:57 | We're going to take a
look at the PivotChart View.
| | 01:00 | When I click on it, I get a
blank chart and a Field List.
| | 01:04 | If you don't have the Field List,
there's a button on the upper left-hand
| | 01:07 | corner of the Ribbon.
| | 01:12 | Pick up your Product field and drag it
down to the bottom where it says Drop
| | 01:16 | Category Fields Here.
| | 01:19 | Then click on your Size and
drag it down right next to Product.
| | 01:26 | Pick up your Order Total, and bring it
to where it says Drop Data Fields Here.
| | 01:30 | Now you can see how much of
each product and size you've sold.
| | 01:35 | You can instantly see that your 32 ounce
bottles of light olive oil are far and
| | 01:39 | away your bestseller.
| | 01:41 | This new analysis view
will be saved with the query.
| | 01:43 | So now you can switch back and forth
between your Design View, your Query
| | 01:48 | Results, and your Pivot
Chart View, anytime you want.
| | 01:53 | Look at the difference, before and after.
| | 02:00 | Creating a PivotChart from your Query
will allow you to do a visual analysis
| | 02:04 | anytime, making it easy to
interpret your query results.
| | Collapse this transcript |
| Using SQL statements| 00:00 | Access is designed to be a database
for individuals and small businesses.
| | 00:05 | Larger enterprises will usually turn to
database solutions such as Oracle or SQL Server.
| | 00:10 | What Access has in common with
larger applications is that the underlying
| | 00:14 | structure relies on the same
programming language, SQL which is spelled SQL and
| | 00:19 | stands for Structured Query Language.
| | 00:22 | When your database grows large and
your needs become complex, you may find it
| | 00:26 | easier to program your queries in SQL
instead of using the Design View grid.
| | 00:31 | Let's learn a few fundamentals about SQL.
| | 00:33 | Right now I am looking at my entire
SalesReps table and I can see all of the
| | 00:38 | salaries on the right-hand side.
| | 00:39 | If I go over to my Navigation pane and
down to Queries, I can double-click on
| | 00:45 | the High/Low Salaries Query.
| | 00:48 | It's based on the same information as SalesReps.
| | 00:50 | But it only has three fields,
Last Name, First Name, and Salary.
| | 00:55 | In addition it's only displaying the
highest and lowest salaries, salaries that
| | 00:59 | are above 30, and salaries that are below 10.
| | 01:03 | Let's go up to the View button, drop
down to the lower half, and see what this looks
| | 01:07 | like in the SQL view.
| | 01:08 | Now you can view the code underlying the query.
| | 01:13 | SELECT, tells Access which fields we want
to see, Last Name, First Name, and Salary.
| | 01:19 | Notice the syntax always includes
the table name, SalesReps.LastName,
| | 01:24 | SalesReps.FirstName.
| | 01:26 | FROM, tells us which
table we're drawing the data.
| | 01:30 | WHERE, are our criteria,
SalesReps.Salary is less than 10.
| | 01:35 | Or SalesReps.Salary is greater than or equal to 30
| | 01:40 | The query ends with a semicolon.
| | 01:42 | Now let's add to this query.
| | 01:45 | I'll start by deleting the
semicolon and going down to the next row.
| | 01:50 | I'll type in ORDER BY, and my
field name, SalesReps.LastName.
| | 01:59 | This tells Access that we now
want to sort on the LastName.
| | 02:05 | If I go back to Design View and look at it, I can
now see that my grid contains Sort: Ascending.
| | 02:11 | And when I look at it in Datasheet View, my
SalesReps are now in order by LastName.
| | 02:17 | If you need your Access database to
communicate with enterprise-level databases,
| | 02:21 | becoming familiar with the SQL code view
and programming language, will make you
| | 02:25 | a professional power user.
| | Collapse this transcript |
|
|
10. Form TechniquesUsing the property sheet to work with controls| 00:01 | When you're designing objects in Access,
there are dozens of settings for every
| | 00:04 | label and every control that
you can't get to from the Ribbons.
| | 00:08 | When you're working in Design View or
Layout View, opening up the Property Sheet
| | 00:12 | will give you complete power over
your objects, sections and controls.
| | 00:17 | Open up the Customer Order Form
and change the View to Layout View.
| | 00:22 | On the Form Layout Tools' Arrange
Ribbon, click on the Property Sheet.
| | 00:27 | You can also press F4 on your
Keyboard to toggle it open and shut.
| | 00:31 | I'll click on the Format tab.
| | 00:34 | At the top of the window is a dropdown
where you can choose the label or bound
| | 00:38 | control that you want to work with, or
you can just click on it and the Property
| | 00:43 | Sheet will adjust accordingly.
| | 00:45 | Notice that on this list you can
actually choose the entire Form itself.
| | 00:50 | And another thing to appreciate is that
if you switch over to the Design View you
| | 00:54 | can also use the Property Sheet to fine
tune entire sections, including the Form
| | 00:59 | Header or the Details Area.
| | 01:01 | There are five tabs across the top
containing the features that you can modify.
| | 01:06 | Format, has everything
about the control's appearance.
| | 01:09 | I'll click on CustID,
and you can see all of the options.
| | 01:12 | Data refers to the contents of the control.
| | 01:16 | Events are actions that you
can program for that field.
| | 01:19 | Other, are things that don't
fall into any other category.
| | 01:22 | And if you use the All tab, you can see
all of the options from the other four
| | 01:26 | tabs all in one place.
| | 01:29 | Now I can't go over all of these,
but let's look at a few of them.
| | 01:32 | We'll start the Format tab.
| | 01:34 | At the top you can adjust
the appearance of the data.
| | 01:36 | You can change the Width and Height of
the box itself, the Colors, the Border
| | 01:41 | Styles, the Fonts, Hyperlink controls,
the Gridlines, Margins, Padding, and more.
| | 01:52 | On the Data tab you can work with the
source of the information that shows in the box.
| | 01:57 | If I click on the dropdown, I can
change it to any field from that table.
| | 02:01 | I can even create an unbound
control and fill it with a calculation.
| | 02:06 | The events Tab let's you get
creative with macro actions.
| | 02:09 | You can make just about anything
happen as you click or tab around your form.
| | 02:13 | Becoming familiar with all of the
potential properties will allow you to make
| | 02:17 | your objects bend to your will,
from appearance to functionality.
| | Collapse this transcript |
| Creating option groups| 00:01 | When your user has specific choices of
valid info that they can enter into a
| | 00:04 | field, one way of making it easy is to
give them a series of option buttons
| | 00:08 | to push, like you would on a web page.
| | 00:10 | When you choose one it
eliminates the other choices.
| | 00:13 | This technique is called
creating an option group.
| | 00:17 | Scroll down to the Forms and open
up the Sales Reps and Customers Form.
| | 00:21 | This form is missing the selection for
the representative gender, male or female.
| | 00:26 | So we need to add it in.
| | 00:27 | We'll create radio buttons to
choose between one or the other.
| | 00:30 | Switch over to the Design View.
| | 00:32 | The trick with an Option Group is
that the button is not actually available
| | 00:36 | from the Layout View Ribbons, but if I switch
to Design View, a number of new controls appear.
| | 00:43 | This is because many of them can't
be used within the Layout View's grid.
| | 00:46 | I'll go up to the top row and
choose the third button for Option Group.
| | 00:51 | I'll come down to the empty area below
photo and drag a box to fill the space.
| | 00:56 | When I let go, a wizard opens.
| | 00:59 | First I have to enter my labels.
| | 01:01 | I'll put in Female, and I'll hit
the Tab key, and Male, and click Next.
| | 01:08 | If one of your options is the most
common choice, you could set it as a default
| | 01:13 | so that you don't have to
press the button every time.
| | 01:15 | In our case we're not going to
set a default, I'll click Next.
| | 01:20 | This is where you set the option for
what actually appears in your data table.
| | 01:23 | The values do need to be numeric.
| | 01:25 | So I'll leave one and two, for
Female and Male, and click Next.
| | 01:31 | Now it gives you the
option for storing the data.
| | 01:33 | We're going to store the data in the field
that we have for gender, and I'll click Next.
| | 01:38 | Here is where we get to
choose the shape of our controls.
| | 01:41 | We could have Option buttons, which
allow me to click on one or the other;
| | 01:45 | check boxes, which actually allows you
to choose several options; and toggle
| | 01:50 | buttons, to push in or out.
| | 01:52 | We'll stick with Option buttons.
| | 01:54 | I can also select the style of the
box around it, Etched, Flat, Raised,
| | 02:00 | Shadowed or Sunken.
| | 02:03 | I'll stick with Etched and I'll click Next.
| | 02:07 | The caption is what the box is going to say.
| | 02:09 | I'll type in Gender and click Finish.
| | 02:12 | Now I have my new option group.
| | 02:14 | I'll use the arrows on my keyboard to
move it down a little bit, so that it's
| | 02:18 | lined up with my other controls.
| | 02:20 | When I go back to the Form view, I can now
specify if my Sales Rep is Male or Female.
| | 02:27 | Creating an Option Group gives your
users an easy-to-understand choice to
| | 02:30 | make, and selecting a radio or toggle
button automatically adds that value to
| | 02:34 | your table.
| | Collapse this transcript |
| Using a ComboBox to select a record| 00:01 | When you want to change from record to
record in a form instead of having to
| | 00:04 | perform finds and filters,
there is a quicker way.
| | 00:07 | You can add a Combo Box at the top of
the form that will allow you to pick the
| | 00:11 | record from a dropdown list and the
entire form will update accordingly.
| | 00:15 | Scroll down in the Navigation
pane to the Forms and double-click on
| | 00:19 | Customers Order Form.
| | 00:21 | This form shows us all the data for
our customers along with their orders,
| | 00:25 | but to pick a customer I have to go to the
Filter button or the Advanced Filter by Form.
| | 00:30 | To make that easier we're going to add a
dropdown box right at the top of the window.
| | 00:35 | Switch over to the Design View.
| | 00:36 | I'm going to use the little
button in the bottom right-hand corner.
| | 00:40 | First I need a place to put my box.
| | 00:42 | So I'm going to move my Print
button over to the far right-hand side.
| | 00:47 | Now here's the crucial detail to make this work.
| | 00:50 | You have to click in this little gray
box in the upper left corner of the form
| | 00:54 | between the rulers, so that it
has a little black box in it.
| | 00:57 | This relates your next step to the
entire data set in the form, instead of
| | 01:01 | allowing it to be an independent control.
| | 01:04 | Now, go up to the Ribbon and
choose this button for a Combo Box.
| | 01:08 | Come down to your header and
draw where you want the box to go.
| | 01:12 | When I let go, a Wizard opens.
| | 01:14 | Choose the third option, Find a
record on my form based on the value I
| | 01:18 | selected in my combo box.
| | 01:20 | If you don't have this third option, it
means that you didn't do this step right
| | 01:23 | where we clicked in this little square.
| | 01:25 | So then you would cancel the window,
click again, and come back to this point.
| | 01:29 | Now that I have my third
option, I'll click Next.
| | 01:33 | Here we choose what fields will have
the source of the data that we want to
| | 01:36 | see in the dropdown.
| | 01:37 | In our case we want Company, so I'll
the click the arrow to move it to the
| | 01:40 | right-hand side, then I'll click Next.
| | 01:43 | Now, double check to make sure none of
your data will be cut off. I'll scroll down.
| | 01:48 | Sure enough, there is one.
| | 01:49 | I'll hold my cursor at the edge of the
column and double-click on it to best fit.
| | 01:53 | If you'd like to see the CompanyID as well,
turn off this check mark that hides it.
| | 01:57 | I'll go ahead and show the ID number as well.
| | 02:00 | Because I can imagine times when I'll want
to make my selection using this ID, in
| | 02:04 | addition to the company name.
| | 02:06 | I'll resize this column and I'll click Next.
| | 02:09 | Last, we'll enter in the Label name.
| | 02:11 | I'll call this Choose a Company and put
a colon at the end of it, click finish.
| | 02:20 | If my control location needs adjusting,
I'll use the arrows on the keyboard to
| | 02:23 | move it where I want it to go.
| | 02:25 | If I'd like to move my label closer or
further away, I'll click and drag on the
| | 02:29 | square in the upper left-hand corner.
| | 02:31 | When I have them where I like them,
I'll switch back to the Form View and I'll
| | 02:34 | use the little button in the
bottom right-hand corner again.
| | 02:38 | Now I have a new option for Choose a Company.
| | 02:40 | I'll drop it down, and any company
I choose will appear in the form.
| | Collapse this transcript |
| Creating tabbed form layouts| 00:01 | If you have a lot of fields on a Form,
you can organize and consolidate them by
| | 00:04 | putting them on tabbed layout.
| | 00:06 | Scroll down in your Navigation pane
to the Forms and open up the Sales Reps
| | 00:10 | and Customers Form.
| | 00:11 | There's a lot of information here,
from the Sales Reps' personal information, to
| | 00:15 | their employment history,
to their company accounts.
| | 00:18 | If you'd like all this information to
appear on one screen, so that you can read
| | 00:21 | everything without scrolling, we can add tabs.
| | 00:25 | Right-click on the object header where
it says Sales Reps and Customers Form and
| | 00:28 | let's change to Design View.
| | 00:31 | Scroll down to the bottom.
| | 00:32 | Let's make some more room.
| | 00:33 | I'll hold my cursor over the form
footer till I get a double-headed arrow and
| | 00:37 | drag down to make some more room.
| | 00:39 | Scroll back up until you can see your Subform.
| | 00:42 | Look on the Form Design Ribbon and
find the Tab Control and click on it.
| | 00:46 | From down below your Subform, click and
hold your cursor down and drag several
| | 00:52 | inches down and across to
make a space for our Tab Control.
| | 00:56 | Once we're done moving all our
fields onto this tab, we'll move the whole
| | 00:59 | object up to the top.
| | 01:01 | Two tabs appear, with coded names, that
start with the word Page, although your
| | 01:04 | numbers might be different.
| | 01:06 | Click on the first tab until you
get an orange box inside the square.
| | 01:10 | If you don't get this exact orange box,
try again, because otherwise our next
| | 01:13 | technique won't work.
| | 01:15 | Once you have the orange square,
go over to your Property Sheet.
| | 01:18 | If your Property Sheet is not open, there is a
toggle button right here to open and close it.
| | 01:24 | Go over to the 5th tab where it
says All and here's our tab name.
| | 01:28 | I'll change Page113 to Sales Rep
and press Enter to accept the name.
| | 01:35 | Now, click on your second
tab and change its name to HR.
| | 01:40 | Now, we need a third tab.
| | 01:41 | Right-click on HR and choose Insert Page
from the shortcut menu and let's change
| | 01:48 | the name of our third tab to Accounts.
| | 01:50 | Now that we have our three
tabs it's time to move our fields.
| | 01:54 | Let's start with the
closest one, our customer Subform.
| | 01:58 | Right-click on it and choose Cut. Then
come down to the Accounts tab, click on
| | 02:04 | it, make sure you have that orange box,
right-click on it, and choose Paste.
| | 02:10 | Your Subform should now be on your Accounts tab.
| | 02:13 | And if you'd like, you can make it a
little larger by holding your cursor over
| | 02:16 | the dot in the bottom-
center and pulling it down.
| | 02:18 | The next thing we want to
do is move our Salary fields.
| | 02:21 | Click on the Start Date label, then
hold your Shift key down and click on the
| | 02:25 | StartDate bound control.
| | 02:26 | Now do the same for End Date, Salary, and Flag.
| | 02:31 | Now on my keyboard, I'll cut using the
keyboard command Ctrl+X. I'll come down
| | 02:37 | to my tabs, click on the HR tab, make
sure I've got the orange box, and then
| | 02:41 | I'll press Ctrl+V and paste.
| | 02:44 | If I'd like to move these fields
around, I can either use the arrows on my
| | 02:48 | keyboard or I can get this four-headed
arrow and drag them around that way as well.
| | 02:54 | Now let's do our last fields.
| | 02:56 | I am going to move my Photo boxes up.
| | 02:59 | I'll click on the Photo label, hold my
Shift key and click on the Photo bound
| | 03:02 | control, get my four-headed arrow by
holding my cursor over the top edge and
| | 03:07 | drag them up, so that
they are alongside address.
| | 03:09 | I'd even like to make my photo a
little larger, so I'll click off, click back
| | 03:14 | on the Photo control, hold my cursor over the
middle dot and make my photo a little larger.
| | 03:21 | Now that I like the alignment of my
fields, I'll select them all by drawing a
| | 03:25 | marquee. That means that I'll start above
into the left of the fields, click and
| | 03:29 | drag, and draw a box that
touches all of the controls.
| | 03:34 | When I let go, they are all selected.
| | 03:36 | I'll cut these as well, come down to my
Tab Control, click on of my Sales Reps
| | 03:41 | tab and press Ctrl+V to paste. There they are.
| | 03:46 | Now that my tab control is populated,
click to the right of the three tabs and
| | 03:50 | you'll get a box all the way around to
the control, get a four-headed arrow,
| | 03:54 | click and drag, and pull the entire
tab control up to the top of the form.
| | 03:59 | Now, before you let go, make sure
that you are down in the detail area.
| | 04:03 | If your cursor is up above in the
form header, then this entire tab will
| | 04:07 | appear in the header.
| | 04:08 | So I'll make sure I'm
down in Detail and let go.
| | 04:11 | Last, I'll scroll down to the bottom
to my form footer, hold my cursor over
| | 04:15 | the top edge of it to get a double-
headed arrow, and pull that up to the
| | 04:18 | bottom of my tab control.
| | 04:20 | There, we are done.
| | 04:21 | Right-click on the Sales Reps and
Customers Form header and change it back to
| | 04:25 | Form View, and now we have our tabbed control.
| | 04:27 | Here is all my SalesReps information.
| | 04:30 | Here's all of his employment
information. And here are all of the accounts.
| | 04:34 | Tabbed controls are the perfect way to
organize and consolidate large amounts of
| | 04:38 | information onto one small screen.
| | Collapse this transcript |
| Inserting charts| 00:00 | It may be a cliche to say a picture is
worth a thousand words, but when it comes
| | 00:04 | to analyzing your data, a
chart is worth at least 10 minutes.
| | 00:08 | It's possible to put charts right in
your forms to give you instant visual
| | 00:10 | analysis into your data.
| | 00:12 | Scroll down in the Navigations pane
and open up the Customers Order Form.
| | 00:17 | Here you see all the necessary
information about each customer, including a
| | 00:20 | list of all the orders they've placed.
| | 00:22 | We can enhance this form by
including a pie chart over on the right that
| | 00:26 | demonstrates what products they've ordered.
| | 00:28 | Right-click on the Customer Order
Form header and change to Design View.
| | 00:33 | Note that we're in Design View
because charts are not actually available
| | 00:36 | from the Layout View.
| | 00:38 | Scroll over to the far right.
| | 00:40 | We need to make some room for our chart.
| | 00:42 | I'll hold my cursor over the edge of
the form until I get a double-headed arrow
| | 00:46 | and I'll move it out to about the 10 inch mark.
| | 00:49 | Now, go up to the Ribbon and
choose the Insert Chart button.
| | 00:52 | When I bring my cursor back down to the
form, I have a little crosshair and I'll
| | 00:56 | click and drag to the size that I want my Chart.
| | 00:59 | When I let go, a wizard appears.
| | 01:02 | We are going to base our
chart on the customer orders.
| | 01:05 | So I'll click on the
Orders table and click Next.
| | 01:08 | We want to know what products each
customer ordered, so use the Right Arrow to
| | 01:11 | move Product over to the right-hand
side and we also want to know how many they
| | 01:15 | ordered, so do the same
thing for Quantity, click next.
| | 01:20 | We'll choose a 3-D Pie Chart to compare
the quantity of the products ordered and
| | 01:24 | give it some visual interest. Click Next.
| | 01:28 | Access did exactly the right
thing in laying out this chart.
| | 01:31 | It's going to show us all the
different products and the pie slices will be
| | 01:34 | based on the sum of the quantity of
each product ordered. I'll click Next.
| | 01:39 | This window helps us make sure of
which fields are linked in our tables.
| | 01:42 | CustID in the Customer
table is the common field, with Customer
| | 01:47 | in the Orders table. I'll click Next.
| | 01:49 | In the next screen, I'll title the
chart Olive Oils Ordered, and I do want
| | 01:57 | to include a legend. I'll click Finish.
| | 01:59 | A sample chart will appear that just has
filler data because we are in Design View.
| | 02:04 | Change the view back over to the
Form View and now we have a Chart Box.
| | 02:10 | If your Chart doesn't appear
automatically, click in the box.
| | 02:14 | I can see my products ordered, and
that PND016 has been ordered about three
| | 02:18 | times as often as PCH008.
| | 02:22 | I'll scroll through again.
| | 02:24 | If you click on the box and it doesn't appear,
try also clicking on one of the other fields.
| | 02:29 | Now, if I'd like to do further this
chart, maybe I'll make it a little wider so
| | 02:32 | my letters aren't so smushed.
| | 02:34 | I'll go back to Design View, scroll
over to see my chart, right-click on it,
| | 02:40 | choose Chart Object and then Edit.
| | 02:44 | You now have a few of the tools that
you would see in Excel, since Access uses
| | 02:47 | the Microsoft graph
engine to produce this chart.
| | 02:50 | I can right-click on the title to change
its color or font, I can right-click on
| | 02:54 | the legend to adjust it, I can even
right-click on the pie slices, and if I want
| | 02:58 | to change one of the colors, I can
click on that slice again until I get dots
| | 03:02 | all the way around it, right-click on it,
format the data point, and since I am
| | 03:07 | working with olive oil, so let me
change this to green. I'll click OK.
| | 03:10 | Now because my letters look a little bit
crunched, I want to make my chart a little wider.
| | 03:15 | First, I need to make my Form wider, so
I'll hold my cursor over the edge of the
| | 03:18 | form, get a double-headed
arrow, and drag to the right.
| | 03:22 | Then I'll grab the right
handle and drag that as well.
| | 03:25 | That lettering looks much better.
| | 03:27 | And when I go back over to my
Form View, that chart looks great.
| | 03:32 | Inserting charts into your Access
forms is an unusual, but powerful tool that
| | 03:36 | you can use to enhance your data analysis.
| | Collapse this transcript |
| Creating your own smart tags| 00:00 | If you're designing your database for
others to use, you'll find times when
| | 00:03 | you'd like to provide them with instructions.
| | 00:05 | One convenient tool utilizes screen
tips and status bar info comments.
| | 00:10 | Scroll down in your Navigation pane
and open up the Customers Order Form.
| | 00:14 | Notice that when I hold my cursor over my
Notes field, it tells the user what to do.
| | 00:18 | This is a screen tip and if I click in
the field and look in the lower left-hand
| | 00:22 | corner at the Status Bar, I can see
more thorough instructions to follow.
| | 00:26 | You can train your users to look in
these two places when they have questions.
| | 00:30 | Let's add a screen tip reminding the
user that they don't have to type in
| | 00:33 | the parentheses and the dashes in the
phone numbers, that Access will enter
| | 00:36 | those automatically.
| | 00:37 | Click in the Work Phone control,
change the Form to the Layout View.
| | 00:41 | You could also do this in Design View.
| | 00:43 | Go over to your Property Sheet.
| | 00:44 | If it's not open, press F4 on your
Keyboard and you can toggle it open and closed.
| | 00:49 | Go to the Other tab and where it says
ControlTip Text, type in Enter numbers only.
| | 00:59 | Then go down to the Status Bar Text
and type in more thorough instructions.
| | 01:03 | Access will automatically
format phone numbers for you.
| | 01:09 | Now go back to the Form View, hold
your cursor over the Work Phone and
| | 01:13 | there's your screen tip.
| | 01:14 | Click in the box and there are
your instructions in the Status Bar.
| | 01:17 | Using Screen Tips and Status Bar Text
can be an excellent training and help
| | 01:21 | tool for your users.
| | 01:22 | Just be sure they know to look for these hints.
| | Collapse this transcript |
| Using the CanGrow and CanShrink properties| 00:00 | When you print out a form, there are
times when you'll have text in a field that
| | 00:03 | may get cut off, or the box is
too big for the text that's in it.
| | 00:07 | There are two field properties that
come in handy to change the size of a bound
| | 00:10 | control box as needed, Can Grow and Can Shrink.
| | 00:14 | Note that these properties don't
affect how the form looks on the screen.
| | 00:17 | It only makes the adjustment on your printouts.
| | 00:20 | Scroll down in your Navigation pane
and open up the Customers Order Form.
| | 00:24 | Then go up to the Office button, and
down to Print, and across to Print Preview.
| | 00:29 | Here's how our form looks when it's printed.
| | 00:32 | Notice that my notes are cut off,
and my Subform is holding space for
| | 00:37 | records that don't exist.
| | 00:38 | Let's get these fixed.
| | 00:40 | Close the Print Preview and now
change your View to the Design View.
| | 00:45 | Open up the Property Sheet either
using the button on the upper right of the
| | 00:48 | Ribbon or F4 on your Keyboard.
| | 00:51 | Click on the Notes field.
| | 00:53 | Make sure that you're on the Format
tab in the Property Sheet and then
| | 00:56 | scroll down to the bottom.
| | 00:58 | We want to change Can Grow to
Yes, and Can Shrink, also to Yes.
| | 01:04 | Then click on your Subform.
| | 01:07 | Make sure it says Order Subform up here.
| | 01:09 | If you're in Layout View it might say
OrdersID, which is why we came to the
| | 01:13 | Design View to do this step.
| | 01:15 | Can Grow is already Yes,
but let's make Can Shrink, Yes, also.
| | 01:20 | Change back to the Form view.
| | 01:22 | Let's go back up to the Office button, and
down to Print, and across to Print Preview.
| | 01:28 | Now our Notes field is no longer cut
off, the box grew and our Orders, while
| | 01:34 | there's still a gap here,
| | 01:35 | there's no box holding space
for orders that don't exist.
| | 01:39 | This is the Can Shrink property.
| | 01:41 | Can Grow and Can Shrink are excellent
fields to use to make sure that your
| | 01:44 | printouts look as good as your on-screen forms.
| | Collapse this transcript |
| Hiding fields in printouts| 00:00 | You may have fields in your database
that you need for usage, but they don't
| | 00:03 | belong on your printouts.
| | 00:05 | You can specify your fields to appear
just on screen, just in a printout, or both,
| | 00:10 | which is the default.
| | 00:12 | Scroll down to the Forms in your
Navigation pane and open up the Customer Order Form.
| | 00:17 | Up in the corner, we have a Flag
field that we use to mark records to take
| | 00:20 | action on them, but whether they're checked
or not, they're not necessary on the printout.
| | 00:24 | In fact, they could be confusing.
| | 00:26 | So let's hide them on our printouts.
| | 00:28 | Switch to the Design View.
| | 00:29 | I am going to use the little
button down here at the bottom.
| | 00:33 | Open up your Property
Sheet if it's not already open.
| | 00:36 | I'll use the Property Sheet button
here or you can use F4 on your keyboard.
| | 00:40 | Click on the word Flag and then hold
your Shift key down and click on the little
| | 00:44 | check mark, so that both the
two fields are highlighted.
| | 00:47 | Go to the Format tab and down at
the very bottom it says Display When.
| | 00:52 | Right now it says Always, which means it's
going to show both on-screen and on printouts.
| | 00:57 | If I click on the dropdown and change it
to Print Only, it wouldn't show when we
| | 01:02 | look at this form on-screen.
| | 01:04 | But you would see it on printouts.
| | 01:06 | We're going to change it to Screen Only.
| | 01:08 | Now, use the Shortcut button at the
bottom to switch back to Form view.
| | 01:13 | Go up to the Office button,
over to Print and to Print Preview.
| | 01:19 | If you look up in the upper right-
hand corner, the Flag field is gone.
| | 01:23 | Display When is a great field to use.
| | 01:25 | When you want a form to look slightly
different on-screen and on paper, you can
| | 01:28 | hide fields on one or the other, as needed.
| | Collapse this transcript |
| Setting tab stops| 00:00 | A power user presses the Tab key on
their keyboard to move through their fields,
| | 00:04 | instead of clicking from
field to field with their mouse.
| | 00:07 | By default, Access sets the order of
the fields in your form according to the
| | 00:11 | order that they were in the underlying table.
| | 00:12 | But as you create your own forms,
you need the tabs to follow.
| | 00:16 | Let's see this in action.
| | 00:17 | We'll create a fast form and rearrange it.
| | 00:20 | I'll right-click on this table to close
it and I'm going to click one time on my
| | 00:23 | Customers table to select it.
| | 00:25 | I'll go to my Create ribbon and
then click on the Form button.
| | 00:28 | Now we have an instant form.
| | 00:31 | If I switch to the Form View and I tab
through, you can see that it tabs down
| | 00:35 | through the columns.
| | 00:36 | But when I customize this form and move
the fields around, the tab stops will change.
| | 00:41 | So let's switch to the Layout
view and move our fields around.
| | 00:44 | If your Property Sheet is open, close
it so you have some more room to work.
| | 00:47 | I'm going to set up this form so that my
initial fields work across and then the
| | 00:52 | fields down below work down.
| | 00:54 | I'll click on the Company name, hold my
Shift key down and click on the Company label.
| | 00:59 | While I have this four-headed arrow,
I'll pick it up and move it above SalesRep.
| | 01:03 | You can see that dark orange line
where the field will get dropped.
| | 01:06 | Next, I'll click on my SalesRep
and its label and move them down
| | 01:10 | underneath CustomerID.
| | 01:12 | Next, I'll take my Store Type, Shift+
Click on the label, and move the two of
| | 01:18 | these below the Company Name.
| | 01:20 | Next, I'll take my Contact's last name,
Shift+Click on the lab,l and move them
| | 01:24 | above the Address. And then my
Contact's first name, and with the label, move
| | 01:30 | them above Web Page.
| | 01:32 | Now I do notice, that the changes that I
made have changed some of my field sizes.
| | 01:36 | So I am going to click on CustomerID
and Shift+Click on the rest of the bound
| | 01:40 | controls in my form, hold my cursor
over the right-hand edge until I get a
| | 01:44 | double-headed arrow and make it
smaller, so that I can see my labels again.
| | 01:49 | Now, let's test this out and see how
it doesn't do what I want it to do.
| | 01:53 | I'd like this to go from CustomerID
to Company, to SalesRep, to store type, to
| | 01:58 | contact last, to contact first.
| | 02:00 | But it goes down the column, so let's fix this.
| | 02:05 | I am going to switch back to the Layout View.
| | 02:07 | Go to the Form Layout Tools, and Arrange,
and there's a button here for Tab Order.
| | 02:13 | When the window comes up, there are
Form sections on the left, Form Header,
| | 02:17 | Detail, and Form Footer.
| | 02:18 | We need to be clicked on the Detail
area to see all of our Form fields.
| | 02:22 | When I look at the list, I can see
that they're going down, instead of across
| | 02:27 | like I'd like them to.
| | 02:28 | The first thing I need to do is move
Company up under CustomerID, but I want to
| | 02:32 | point out something that's
a little counterintuitive.
| | 02:34 | Your inclination is going to be to
click and drag on Company, but notice that
| | 02:38 | what that does is select multiple fields at a
time, which is not the effect I am looking for.
| | 02:43 | So I have to click Next to Company one
time, let go off my mouse button, and then
| | 02:48 | drag it up under CustomerID.
| | 02:51 | SalesRep is the next field I want and
after that I'd like Type, so I'll click
| | 02:55 | one time on the box next to
Type, and drag it up below SalesRep.
| | 02:58 | Contact Last is correct, and let's
bring Contact First up under that.
| | 03:04 | The rest of the fields I will
want to work down in columns.
| | 03:07 | If I need to start over again, pressing
Auto Order will reset the fields working
| | 03:12 | down in columns again. I'll click OK.
| | 03:14 | I'll right-click on my Customers object tab
and change it to Form View, and now when I tab,
| | 03:19 | it will go from CustomerID to Company,
to SalesRep, to Type, to Contact Last,
| | 03:24 | Contact First, and then work
it's way down the columns.
| | 03:27 | Now there is one more thing
you can do with tab stops.
| | 03:31 | You can skip fields altogether.
| | 03:33 | For example, most of our customers are
in the United States, so I could have it
| | 03:38 | skip from the ZIP Code
straight down to the Work Phone number.
| | 03:41 | In the same way, I rarely use
Documentation and Flag, so I can have my tab skip
| | 03:46 | from the Notes field, down to my Orders.
| | 03:49 | To do this, go back to the Layout
View, and turn on your Property Sheet,
| | 03:53 | I'll use my F4 key.
| | 03:55 | Click on the Country bound field and go
to the Other tab in the Property Sheet.
| | 04:00 | Tab Index tells us what
number tab it is on the form.
| | 04:04 | Tab Stop is whether our tab will stop there as
we tab through the form, and we'll tell it No.
| | 04:10 | Let's do the same thing for Documentation.
| | 04:13 | We wanted to skip over Tab Stop number 18,
and the same for the little check box for Flag.
| | 04:19 | We won't have it stop there either.
| | 04:21 | Now when I go back to my Form view, let
me start up on City, and I'll press Tab,
| | 04:26 | State, Zip, right down to Work
Phone, and from Notes, down to my Orders.
| | 04:34 | Our form is now behaving exactly as
we wanted to when we tab through it.
| | 04:38 | When you're working with form data,
efficient workflow is essential.
| | 04:41 | Setting Tab Stops allows you to
create a natural and practical order for
| | 04:44 | your data entry.
| | Collapse this transcript |
| Clipping, stretching, and zooming images| 00:00 | When you place images in OLE fields,
you have a choice for how they appear.
| | 00:04 | You can clip them if they're too large,
stretch them to fill the size of the
| | 00:07 | field, or zoom them to best fit.
| | 00:10 | Scroll down in the Navigation pane, and in
the Forms, choose Sales Reps and Customers.
| | 00:15 | On the right you can see a field
containing the pictures of our Sales Reps.
| | 00:18 | Our picture looks great, but how did I do that?
| | 00:21 | This technique works in
either Design View or Layout View.
| | 00:24 | I'm going to switch to Layout View,
so that I can see my changes in action.
| | 00:28 | Open up your Property Sheet if it's
not already open and make sure that
| | 00:32 | you're on the Format tab.
| | 00:33 | Click on your picture and
then look for Picture Size mode.
| | 00:37 | Right now it's on Zoom which resizes
your original pictures so that it best fits
| | 00:41 | the size of your field.
| | 00:42 | Let's try the other options.
| | 00:44 | Change it to Clip and the original
picture dimensions are maintained, but the
| | 00:49 | picture gets cut off where the field ends.
| | 00:52 | If I change it to Stretch, the picture
will fill up the entire field box even if
| | 00:56 | the image gets distorted.
| | 00:58 | Depending on your circumstances one of these
three will be the best option for your image.
| | 01:03 | In our case Zoom was indeed the optimal
picture size option, so I'll put it back.
| | 01:07 | When you're inserting images into
your database, finding a balance between
| | 01:11 | your field dimensions and your choice
of picture size will make your pictures
| | 01:14 | look great.
| | Collapse this transcript |
| Viewing single or continuous forms| 00:00 | When you create a new form, by
default it will show one record at a time.
| | 00:05 | Scroll down to the Forms and
open up your Products Form.
| | 00:10 | But because this form has very little
information on it, it might be nice to see
| | 00:13 | all of my products in one continuous list.
| | 00:17 | Go up to the View button, drop it
down and change this to Design View.
| | 00:22 | Go to your Property Sheet.
| | 00:23 | If it's not already open,
click on this button to open it.
| | 00:27 | Make sure this dropdown says Form.
| | 00:30 | The second item down says Default
View and right now it says Single Form.
| | 00:34 | Drop it down and change it to Continuous Form.
| | 00:39 | Now when you go back to look at it in Form
View, you can see all of your products in a row.
| | 00:46 | Note that if your form does have a
Subform on it, this technique doesn't work.
| | 00:50 | But it's nice to know when you're
looking at your forms that you can see either
| | 00:54 | one record at a time or all
of them in one continuous list.
| | Collapse this transcript |
| Changing a form's default view| 00:00 | When you open up a form, it by
default opens up in Form View.
| | 00:04 | But you'll be interested to know
that you can actually change the Default
| | 00:07 | view on your forms.
| | 00:08 | I'll scroll down in my Navigation
pane and open up my Products Form.
| | 00:13 | Right now it shows one product at a time.
| | 00:15 | Notice when I click on the View dropdown,
| | 00:17 | I only have three options here,
Form View, Layout View, and Design View.
| | 00:23 | But if I go to Design View and then
open up my Property Sheet, select Form from
| | 00:30 | the dropdown here if it's
not already showing Form.
| | 00:33 | Here you have Default
View and it says Single Form.
| | 00:37 | In another video in this course I show you
Continuous Form, so I'll skip that for now.
| | 00:43 | But notice that I can also choose Datasheet.
| | 00:45 | To make this work, close
this form and then save it.
| | 00:50 | The next time I open it, it
opens up looking like a table.
| | 00:54 | But it really is a form.
| | 00:57 | Now, go back to Design View again and
change the Default View to PivotTable.
| | 01:02 | Close and save the form and when you open
it again, now you will see a PivotTable.
| | 01:12 | Go back to Design View again.
| | 01:15 | Change your Default View to a PivotChart.
| | 01:19 | Close it and save it, and now open
up Products again, and now it opens up
| | 01:26 | automatically as a PivotChart.
| | 01:28 | Last but not least, go to Design View again
and change the Default View to a Split Form.
| | 01:35 | Close it, save it, and open it again.
| | 01:40 | Now you see all your products on top
and when you click on them, you can see
| | 01:43 | just that one product on the bottom.
| | 01:45 | Let's go back to Design view one more time, and
change the Default View back to Single Form.
| | 01:52 | Close it and save it.
| | 01:59 | When you're working with your forms,
it's nice to know that you can actually
| | 02:02 | choose its appearance when you
open it up from the Navigation pane.
| | Collapse this transcript |
|
|
11. Report TechniquesInserting page breaks in reports and forms| 00:00 | When you print out a report or a form,
sometimes you'll want to design it so
| | 00:04 | that each section begins on a new page.
| | 00:06 | For example, if I scroll down to my
Reports and open up the Sales Rep and
| | 00:10 | Customers report, it gives me a list of all
of my Sales Reps and all of their accounts.
| | 00:14 | If I go over to the View button and
drop it down, I have an option for Print
| | 00:20 | Preview, and here they all are.
| | 00:22 | Now there may be times when I want each
sales rep to be listed on their own page.
| | 00:27 | To do this, close Print Preview and
then view your report in Design View.
| | 00:33 | This technique doesn't
actually work in Layout View.
| | 00:36 | Find the section header where
you want each page to start.
| | 00:38 | In our case it's the EmpID Header.
| | 00:41 | Before each change in EmpID,
we'll tell it to perform a page break.
| | 00:45 | Open up your Property Sheet with this
button right here and go to the Format tab.
| | 00:51 | At the bottom, you'll see Force New Page,
and I'll change this to Before the Section.
| | 00:56 | So before each page and EmpID,
it will force a new page.
| | 01:00 | When I go back to Report View, it
looks exactly the same, but when I drop
| | 01:04 | down the button and look at it in Print
Preview, now here's one page with Jordan
| | 01:08 | Hinton, and when I go to the next one
theres Lilah Douglas, and then Karyn Reese.
| | 01:13 | Now this works very similarly for forms.
| | 01:16 | Open up your Customers Order Form.
| | 01:18 | Now notice that there's no Print
Preview off of the View button and that's
| | 01:22 | because forms aren't actually designed
to be printed, they're designed to be
| | 01:25 | viewed on-screen, but there will be
times when you want to print them.
| | 01:28 | I'll go up to the Office button, over
to Print, and across to Print Preview.
| | 01:34 | When I look at this form, it has the
customer and all of their orders, here's
| | 01:39 | the next customer, but their
orders aren't until the next page.
| | 01:42 | So this information gets orphaned.
| | 01:44 | That's not going to work at all.
| | 01:45 | I'll close the Print Preview and
switch over to the Design View.
| | 01:50 | This time we're going to want to force
the page break in our Detail section, but
| | 01:54 | instead of forcing it before the Detail
section let's make it after the section.
| | 01:58 | Now when I go up to the Office button
to Print and Print Preview, each customer
| | 02:03 | has their own page. And when I
scroll, I can see each customer.
| | 02:06 | Using Force New Page on your reports
and your forms is a very simple technique,
| | 02:10 | but not if you don't know where to look for it.
| | Collapse this transcript |
| Creating headers and footers| 00:00 | When you create new reports, mastering
headers and footers will allow you to
| | 00:04 | create understandable printouts.
| | 00:05 | If I go to the Create Ribbon and then
start a new report using either this
| | 00:09 | button or one of the wizards, my report
will automatically have headers and footers.
| | 00:13 | But if I start with a blank report, my
headers and footers may or may not be turned on.
| | 00:18 | I'll create a blank report and then
switch over to Design View to look at it.
| | 00:22 | This blank report has a Page Header,
a main Detail area, and a Page Footer.
| | 00:27 | The Page Header and Page Footer are what
will appear at the top and bottom of every
| | 00:31 | page in the report, whether there is
only one page or there are hundreds.
| | 00:34 | The Detail area will contain the
fields that will repeat for every record.
| | 00:39 | If you right-click on the gray Page
Header line, I also have a choice to view
| | 00:43 | the Report Header and Footer.
| | 00:44 | Turn this on and now you have two
new sections at the top and the bottom.
| | 00:48 | The Report Header will only appear at
the very top of the very first page.
| | 00:52 | The Report Footer will only appear
at the very bottom of the last page.
| | 00:56 | I can resize any of these sections by
holding my mouse over the bottom edge,
| | 01:00 | getting a double-headed arrow,
and making it bigger or smaller.
| | 01:04 | I'll go ahead and make my
Detail area much smaller.
| | 01:06 | I'll hold my cursor at the top of the
Page Footer line, which is also the bottom
| | 01:10 | of the Detail area, and pull up
until Detail is about a half inch tall.
| | 01:14 | Now click on the gray box
| | 01:16 | in the upper left-hand corner of the
Report between the two rulers, and come over
| | 01:20 | to the Ribbon and open up the Property Sheet.
| | 01:23 | If you have repeated information in
both your Report Header and your Page
| | 01:27 | Header, you can go to the Format tab
and then down at the bottom, you can
| | 01:31 | specify that the Page Header,
instead of showing on all pages, will be
| | 01:35 | suppressed on the first page.
| | 01:37 | This same goes for the footer.
| | 01:39 | If you have redundant information in
your Page Footer and your Report Footer,
| | 01:42 | you can set it so that the Page Footer does
not show up on the last page of the report.
| | 01:47 | I'll leave both the views on All Pages.
| | 01:49 | Now let's start our form by adding a logo.
| | 01:51 | I'll click up here on the Logo button.
| | 01:55 | I'll navigate in Windows to where I know
my file is and I'll insert the TwoTreesLogo.
| | 02:01 | Next, I'll go up to this tiny little
button that says Title and it will drop
| | 02:05 | a title into place.
| | 02:07 | This text box is preformatted with the
larger size, colors and styles applied.
| | 02:12 | I'll type in Two Trees Sales Reps Report.
| | 02:15 | Now Page Headers are a great place to
put your column labels, since you'll want
| | 02:18 | them at the top of every page,
but not repeated throughout the page.
| | 02:22 | Let me show you what I mean.
| | 02:23 | Because I am creating a report from
scratch, I need to click on this button here
| | 02:26 | to Add Existing Fields.
| | 02:28 | I'll pick up my Company field and drag
it into my Detail area, about where I want
| | 02:32 | the Control box to go.
| | 02:33 | Now if I look at this in
Report view, I can see Company:
| | 02:36 | All Kinds of Taste. Company: Avulon. Company:
| | 02:39 | Blue Vine. But I don't want to
Company, Company, Company, I'd rather have
| | 02:43 | Company up at the top and then all
the companies listed down below it.
| | 02:46 | So let's go back to the Design View,
| | 02:48 | click on the Company label, and cut it.
| | 02:51 | Then click on the Page Header and paste it.
| | 02:54 | Now I'll click on my Company
control box and put it right underneath.
| | 02:59 | And I'll shrink up my Detail
area so it's a little smaller too.
| | 03:02 | Now when I look at my report, I see Company:
| | 03:05 | All Kinds of Taste, Avulon, Blue Vine.
| | 03:07 | For more information on how to work
with the Detail area and to format these
| | 03:11 | controls please visit the Access
2010 Essential Training Course.
| | 03:15 | Now let's turn our attention
to our page and report footers.
| | 03:19 | I'll go back to Design View.
| | 03:21 | In the Ribbon, there is a tiny
little button for Page Numbers.
| | 03:24 | I'll click on it and I'll get this dialog box.
| | 03:27 | I can choose just Page 1, or Page 1 of 3.
| | 03:31 | I'll leave it on just Page 1.
| | 03:32 | I can position my Page Numbers at the top
of the page or at the bottom of the page.
| | 03:37 | I'll move them to the footer.
| | 03:38 | I can specify their alignment and I'll
choose to put them on the right-hand side.
| | 03:42 | And if you don't want to show the page
number on the first page, you can uncheck
| | 03:45 | this box, but I'll leave it on.
| | 03:47 | I'll click OK and now I have a page number
right here in the page footer of my report.
| | 03:50 | There is another button right here
to insert the current date and time.
| | 03:54 | This is useful, so that every time you
run the report, it will have automatically
| | 03:58 | the current date and time on it, so that
you don't have to edit it and update it
| | 04:02 | each time you use the report.
| | 04:03 | I'll click on it, and it gives me some options.
| | 04:06 | I can include the date
and I'll choose this format.
| | 04:08 | I am going to select not to
include the time, and I'll click OK.
| | 04:12 | My date appeared in the upper right-
hand corner, but I'm going to click on it,
| | 04:16 | cut it, come down to the
Page Footer and paste it.
| | 04:19 | Now I'll go back and view my
report, and here is what it looks like.
| | 04:22 | I have a header, the Company label, all
of my companies listed, and down at the
| | 04:27 | bottom, the date of the report
and the page number of the report.
| | 04:30 | There are two additional header and
footer techniques that you'll want to master.
| | 04:33 | Grouping on a field, which turns that
field into its own header, and totals,
| | 04:38 | which will allow you to add subtotals
at the bottom of a page and grand totals
| | 04:42 | to the bottom of an entire report.
| | 04:44 | Both of these techniques are covered
in detail in the Reports chapter of the
| | 04:47 | Access 2010 Essential Training Course.
| | Collapse this transcript |
| Hiding duplicates in reports| 00:00 | If you create a Report that contains
repeated data fields, it's possible to hide
| | 00:04 | all the duplicated values, so your
Report is cleaner and easier to read.
| | 00:08 | Scroll down to the bottom of your
Navigation pane and open up the Products Report.
| | 00:12 | Say you have it repeats Alberquina,
Alberquina, Alberquina for all five bottle
| | 00:15 | sizes, and then Extra Virgin, Extra
Virgin, and First Cold Press, First Cold
| | 00:19 | Press, First Cold Press.
| | 00:20 | Let's change this Report, so that you
see Alberquina and Extra Virgin for their
| | 00:24 | first occurrence,
but then it hides it after that.
| | 00:26 | Switch over to the Layout View
and then open up the Property Sheet.
| | 00:31 | If it's not open, press F4 on your keyboard.
| | 00:34 | Click on the Product field and make sure
you're on the Format tab and scroll all
| | 00:38 | the way down to the bottom,
look for Hide Duplicates.
| | 00:41 | I'll change that to Yes.
| | 00:42 | Now go look at your Report.
| | 00:44 | You'll immediately see all
the repeated values disappear.
| | 00:46 | Hiding Duplicates removes
redundant data from your report, making it
| | 00:50 | much easier to read.
| | Collapse this transcript |
|
|
12. Formatting ShortcutsUsing the Format Painter| 00:00 | Microsoft Office has an often
overlooked feature called Format Painter that
| | 00:04 | allows you to reuse all of the formatting
applied to a field in your form or report.
| | 00:08 | Once you've formatted a control exactly
as you like it, you can apply all of the
| | 00:12 | settings at once to one
or several more controls.
| | 00:16 | Let's see how it works.
| | 00:17 | Open up the Customers Order Form in Layout View.
| | 00:20 | You could also do this in
Design View if you wished.
| | 00:25 | Click on the label for CustomerID
and then up on the Ribbon, change the
| | 00:29 | Font Size from 11 to 12.
| | 00:32 | Let's also make it bold, and drop down the
color, and change the color to Dark Green.
| | 00:38 | Now that's three changes I
made to this one control.
| | 00:42 | I don't want to have to apply all three
of them over and over and over again to
| | 00:46 | all of my other labels, so that's
where the Format Painter comes in.
| | 00:50 | The first step is to click on whatever
has the format that you want to copy.
| | 00:54 | Believe it or not, this is the first
mistake that most people make when
| | 00:57 | trying the Format Painter.
| | 00:59 | Clicking on it pulls in the
specific formatting that you want to use.
| | 01:02 | Now note that if were in Word, you
would either click in or highlight the text,
| | 01:07 | but because this is Access and I want
to apply this to controls, I am going to
| | 01:11 | click off of my CustomerID and then on
it again to make sure that I've selected
| | 01:15 | the entire box and I can tell
because of the orange square around it.
| | 01:19 | Now I'll go up to my Format Painter up
on the Ribbon, and click on it, and then
| | 01:23 | click on the Company label, and
it's taken on that same formatting.
| | 01:27 | It's now 12, Bold, and Green, all in one click.
| | 01:32 | Now I do want to apply that to all the
rest of my labels, so another way to use
| | 01:36 | the Format Painter is to double-click on it.
| | 01:39 | Now, the formatting sticks to
the cursor and I can keep clicking.
| | 01:43 | See that little paintbrush next to it?
| | 01:45 | So I'll click on all my labels and
now they're all 12, Bold, and Green.
| | 01:53 | Now it's important to remember to
turn off the Format Painter, otherwise the
| | 01:57 | next thing that I click on would also take on
that formatting, and I don't want that to happen.
| | 02:01 | To turn off the Format Painter, either
click back on the Paintbrush again or
| | 02:06 | press Escape in the upper left
hand corner of your keyboard.
| | 02:09 | Using the Format Painter will save
you from repetitive steps whenever you're
| | 02:12 | formatting multiple controls in
the same way on any form or report.
| | Collapse this transcript |
| Setting default appearance for new databases| 00:00 | If you customize most of your Database
Tables with the same formatting, you can
| | 00:03 | set Access's options, so that many of
these preferences are set by default.
| | 00:08 | I'll go up to the Office
button and I'll make a new Database.
| | 00:12 | On the bottom right-hand side I'll set
the name to defaults and click Create.
| | 00:16 | Now let's go take a look at Access's options.
| | 00:20 | Go up to the Office button, and down
at the bottom, choose Access Options.
| | 00:25 | Go down to the Datasheet button and now
we can customize the way your datasheet
| | 00:29 | tables look in Access.
| | 00:31 | Now, for the sake of this demonstration,
I am going to make these look a little
| | 00:35 | bit extreme, so that you
can see the differences.
| | 00:37 | In a regular database, they
would be much more subtle.
| | 00:41 | I can change my Font color.
| | 00:43 | I'll make it Dark Blue.
| | 00:45 | The Background color is the main color
of the rows, and I'll go ahead and I'll
| | 00:48 | make that Light Blue.
| | 00:50 | For my alternating rows,
I'll choose Light Green.
| | 00:55 | I'll make my Gridlines lines Orange.
| | 00:58 | Now by default, your tables have
Horizontal and Vertical gridlines.
| | 01:01 | I, myself, am a big fan of just the
Horizontals, so I'll turn off the Verticals.
| | 01:06 | You could also set your tables to
have a Raised appearance or a Sunken
| | 01:09 | appearance for all of the cells,
but because I want you to see all of my colors,
| | 01:12 | I am going to leave this as Flat.
| | 01:15 | By default, Tables have 1" columns.
I am going to increase this to 1.5.
| | 01:19 | I can also change my Default font;
| | 01:21 | instead of Calibri I'll make this Candara.
| | 01:25 | Maybe 11 is too small for my
eyes, I'll increase it to 12.
| | 01:29 | And maybe I don't want my Weight to be Normal.
| | 01:31 | I can make it Lighter or Darker as needed.
| | 01:34 | I also have the option of making
everything Underlined or Italicized, but I am
| | 01:38 | going to leave those empty
right now. I'll click OK.
| | 01:42 | Now, when I make a new table, I'll go
to the Create Ribbon, create a New Table,
| | 01:47 | and when I start entering in
data, I can see how this looks.
| | 01:51 | I'll type in my name right there.
| | 01:52 | I can see the blue, I can see the
green, the font, the color, the size, all
| | 01:58 | sorts of changes to it.
| | 01:59 | Now note that when you change these
settings, they will get carried over for all
| | 02:03 | additional tables and all additional
databases that you create, until you go back
| | 02:08 | in to the Options and reset them again.
| | Collapse this transcript |
|
|
13. Macro and Event TechniquesUsing a Before Change macro to create a time stamp| 00:00 | A Before Change Macro allows you to
perform an action upon adding, updating,
| | 00:04 | or deleting a record.
| | 00:06 | For example, it's a great idea to have
date and time modified fields in your
| | 00:09 | database, so that you know when
changes were made to your significant data.
| | 00:13 | For example, open up the Products table.
| | 00:16 | Here's a list of all the products we
sell, with their prices and their costs.
| | 00:21 | If we update either a retail price
or our wholesale cost, it would be very
| | 00:25 | useful to have a historic record of the last
time any changes were made to each product.
| | 00:30 | To do that, right-click on the
Products tab and change to Design View.
| | 00:35 | Now we'll add the fields that we need.
| | 00:37 | Under Active, I'll add in date modified.
| | 00:41 | I'll change the data type to date by
pressing the D and it will auto fill.
| | 00:45 | I'll press Tab again and then come
down to the Field Properties to the Format
| | 00:50 | row and change this to Short Date.
| | 00:54 | Now, add in TimeModified.
| | 00:57 | Also give it the Data Type Date/Time,
and this time the Format will be Short
| | 01:02 | Time, which will put it in military time.
| | 01:06 | I'll right-click on Products again,
change it to the Datasheet View, and when it
| | 01:10 | asks to save the table, say Yes.
| | 01:13 | Now, it's good database design not to
do your data entry and tables, but in
| | 01:17 | forms instead, and this is a perfect example why.
| | 01:20 | When I'm working in the table, I'd have
to tab past these two forms, and we want
| | 01:24 | them to auto fill by default.
| | 01:27 | We can't do that in a table,
but we can do it in a form.
| | 01:30 | So scroll down in your Navigation pane
on the left hand side and open up the
| | 01:34 | Product Split form. We'll add Date and
Time Modified fields down here at the
| | 01:39 | bottom and we'll even set them.
| | 01:41 | So as we tab through the form, after we
hit cost, it'll skip our two automated
| | 01:45 | fields and go straight to the next record.
| | 01:48 | Right-click on the Product Split
Form and change this to Layout View.
| | 01:53 | Go up to the Ribbon on the right-hand
side and choose Add Existing Fields.
| | 01:57 | Click on DateModified and then hold down
your Shift key and click on TimeModified.
| | 02:02 | Then drag them onto your form.
| | 02:03 | They will probably appear up
in the upper left-hand corner.
| | 02:07 | Notice that there's a little
table selector in the very top left.
| | 02:11 | I'll hold my cursor over it, click on it and
drag these two fields down to bottom-center.
| | 02:16 | Now I want to set them to
be skipped in the tab order.
| | 02:19 | I'll click off of them to deselect them.
| | 02:21 | Then I'll click on the DateModified
bound control and Shift+Click on the
| | 02:26 | TimeModified control.
| | 02:28 | Notice that the Orange line
is just around these two boxes.
| | 02:31 | Press F4 on your keyboard and that
will open up your Property Sheet.
| | 02:35 | Go over to the Other tab and where
it says Tab Stop, change this to No.
| | 02:40 | Now your form is set to
skip these two altogether.
| | 02:44 | The next thing that we
need to do is make our macro.
| | 02:46 | Go up to the Create Ribbon and go to
the far right-hand side and choose Macro.
| | 02:52 | Click on the Show All Actions button.
| | 02:55 | If we don't, not all of the
actions show on our macro list.
| | 02:59 | Scroll down to the bottom and find Set Value.
| | 03:03 | Down at the bottom of the
screen is where we set our arguments.
| | 03:06 | Under Item, I'll put in DateModified, and
the expression will be now, with an open
| | 03:13 | and closed parentheses. That way when
the macro is called, whatever that very
| | 03:17 | moment is, is what will get
filled in the DateModified field.
| | 03:21 | Go up to the next action line, and this
time type in SetValue, and notice that as
| | 03:26 | you type, it auto fills.
| | 03:28 | Now, under item we want TimeModified,
and the expression again will be Now.
| | 03:32 | Now something significant about this.
| | 03:35 | Notice that we're not attaching the
DateModified and TimeModified fields
| | 03:39 | specifically to the Products table.
By just using a generic Date and Time
| | 03:44 | Modified, that will allow us to use
this macro with any table or any form.
| | 03:50 | Go ahead and close the macro.
| | 03:52 | When it asks to save it, call it
DateTimeStamp, and click OK or press Enter.
| | 03:59 | Now we want to attach that macro to this form.
| | 04:02 | Up in the Property Sheet, use the
dropdown and chose Form off of the list.
| | 04:08 | We want the macro to be attached to
the whole thing, not a specific field.
| | 04:13 | Go to the Event tab and under Before Update,
drop it down, and choose our new DateTimeStamp.
| | 04:20 | Now let's test it out.
| | 04:21 | I'll right-click on the Product
Split Form tab and choose Form View.
| | 04:26 | I'll make a change to my Price;
| | 04:28 | let's say it's now going to be 729.
| | 04:30 | I'll make the change and when I hit
the Tab it's going to skip from Cost,
| | 04:35 | down to my next product. And notice down
below, the date and time that I made that change.
| | 04:41 | Using a Before Update Macro to track
when changes are made to your data brings a
| | 04:46 | new level of data management to your database.
| | Collapse this transcript |
| Triggering a Before Delete warning message| 00:00 | A Before Delete macro allows you to
perform an action right before you try
| | 00:04 | and delete a record.
| | 00:06 | For example, right now I'm
in my Products Split Form.
| | 00:09 | I want to make sure that no one
tries to delete any of my products.
| | 00:13 | If they do, then anyone who's ordered
one of those in the past will have errors
| | 00:17 | in their order history.
| | 00:18 | Instead, we want our users to uncheck this box
to show that the product is no longer active.
| | 00:24 | To set up is Before Delete event,
switch over to the Layout view.
| | 00:29 | Open up your Properties
sheet if it's not already open;
| | 00:31 | you can use your F4 key to
toggle it open and closed.
| | 00:34 | Now we want this event to be attached
to the form not, to a specific field.
| | 00:39 | So in this dropdown, make
sure that you find Form.
| | 00:43 | Now go to the Event tab.
| | 00:46 | The event that we want is Before Delete Confirm.
| | 00:49 | Now note that there are two
approaches we could take to this macro.
| | 00:53 | I can either call in a stand-alone macro
or I can build it right into this form.
| | 00:58 | Since what we are going to do is bring
up a warning message to tell the user to
| | 01:02 | deactivate the product instead of deleting it,
| | 01:05 | we need it attached to this form.
| | 01:07 | None of my other tables have a
deactivation, but if all of my tables are
| | 01:11 | constructed the same way, I could make
this a stand-alone macro by going to the
| | 01:15 | Create ribbon and creating the
macro here under the Macro button.
| | 01:20 | But because this is specific to this form,
I am going to use my Build button and
| | 01:24 | build the macro right into the form.
| | 01:26 | I will click on Macro Builder and click OK.
| | 01:30 | Before we start, click on this Show All
Actions button because the actions that
| | 01:34 | we need wouldn't show here by default.
| | 01:36 | What we are going to do
is bring up a message box.
| | 01:40 | So scroll down and find MsgBox.
| | 01:43 | Come down here to the bottom.
| | 01:45 | The message that we want the user to
see says Please inactivate instead.
| | 01:51 | We do want the dialog box to beep.
| | 01:53 | Now the type refers to the graphic
that's going to show inside the box.
| | 01:57 | There are question marks,
exclamation points, I's for information.
| | 02:01 | We want Critical, to make sure
that we get their attention.
| | 02:04 | Title is what appears in the
blue bar at the top of the window.
| | 02:07 | I will type in Do Not Delete This Record.
| | 02:12 | Now after we get the dialog box
telling the user what to do, we also want to
| | 02:16 | cancel the deletion so that it doesn't happen.
| | 02:18 | I am going to drop down my actions
and I am going to tell it, cancel event.
| | 02:22 | Now close your macro and save it.
| | 02:27 | Here it is under Before Delete Confirm.
| | 02:30 | Let's go back to the Form view and try it.
| | 02:32 | Now down here at the bottom, I will click
in the gray box next to one of my products.
| | 02:38 | I'll come up to the ribbon and choose Delete.
| | 02:40 | I get my warning message;
| | 02:42 | do not delete this record,
please inactivate it instead.
| | 02:45 | I will click OK, and my deletion is canceled.
| | 02:48 | The Before Delete event is a handy
way of taking an action before deleting
| | 02:53 | content from your database.
| | Collapse this transcript |
| Creating a user interface macro| 00:00 | A User Interface macro is one that gets
triggered when you click somewhere on a form.
| | 00:04 | In the Access 2007 Essential Training,
you learn how to attach a macro to a button.
| | 00:09 | In this lesson, you will see how to
trigger a macro just by clicking in a form field.
| | 00:14 | Open up your Customer Order Form.
| | 00:16 | Let's say you're looking at your
customer, and it would be handy to have
| | 00:19 | quick access to the SalesRep contact
information, just by clicking in the SalesRep field.
| | 00:24 | There are a few steps to this process.
| | 00:26 | First, I have to create the little pop-up form.
| | 00:29 | Then I have to attach it to the
SalesRep field using an Event macro.
| | 00:33 | So click once on the SalesRep
table and go to the Create ribbon.
| | 00:37 | Drop down more forms and choose the Form wizard.
| | 00:41 | I'll pick the fields that I want on the form;
| | 00:43 | EmpID, LastName, FirstName, CelPhone,
and E-mail address. I will click Next.
| | 00:53 | I'll leave this as a columnar form and
I am going to pick the style Foundry off
| | 00:57 | of the alphabetical list. Now I'll click Next.
| | 01:00 | I am going to change my form title to
SalesRepsPopup and then I am going to go
| | 01:06 | directly into the form's design.
| | 01:07 | I will click on this button
right here, and then click Finish.
| | 01:11 | Now I am going to want this
dialog box to be as small as possible.
| | 01:14 | So I am going to right-click on the Form
Header and turn it off from the shortcut list.
| | 01:20 | It says it's going to delete
all the controls, that's fine.
| | 01:23 | I am also going to highlight all of
these fields, and use the arrows on my
| | 01:27 | keyboard to move them up a little bit, and
then bring up the bottom of the form as well.
| | 01:32 | Now that it's done, I'll right-click on the
tab to close it and I will save it when it asks.
| | 01:38 | Now it's time to create the Event macro.
| | 01:40 | I will switch over to the Layout
view and open up my property sheet.
| | 01:45 | If you don't have your property
sheet open, press F4 on your keyboard to
| | 01:48 | toggle it open or closed.
| | 01:50 | Click on your SalesRep field, make sure it
says SalesRep in this dropdown at the top.
| | 01:55 | Now I have lots of different choices
for what's going to trigger the User
| | 01:58 | Interface macro, and I don't really want
to use something like On Click because I
| | 02:04 | am going to be using this dropdown
down to pick the SalesRep, or even just
| | 02:07 | clicking in the box to type in it.
| | 02:08 | So I am going to choose On Dbl Click.
| | 02:11 | Now these two buttons indicate that
there are two different approaches I
| | 02:14 | could take to this macro.
| | 02:15 | If I am going to want to invoke the
SalesRep pop-up several places across my
| | 02:19 | database, I would now go to the Create
Ribbon and then to this Macro button here
| | 02:25 | and build the same macro here.
| | 02:29 | But in this case, I'm only going to want to
call my SalesRep pop up from this one location.
| | 02:34 | So I am going to use the Builder
button right here. It asks me what kind of
| | 02:38 | builder, and I want a macro
builder, so I will double-click on it.
| | 02:41 | The action that I want when
I click is to open up a form.
| | 02:44 | So I will drop this down,
scroll down, and choose OpenForm.
| | 02:50 | At the bottom, there's a list of arguments.
| | 02:52 | First, it wants to know what form
the macro is going to open, and I'll
| | 02:56 | choose SalesRepsPopup.
| | 02:57 | I will leave it in Form view and
we are not going to filter anything.
| | 03:01 | But, very important, is the condition.
| | 03:04 | This is where we set the criteria that
when we click on a particular SalesRep,
| | 03:08 | it opens up that sales
rep's contact information.
| | 03:11 | This is kind of complex.
| | 03:13 | So I need the field from the
SalesRepPopup form that identifies that employee.
| | 03:18 | So I will type in the [EmpID],
then I'll put in an equals sign.
| | 03:25 | Now this next part is kind of complex
and I could just straight type it out.
| | 03:29 | But I also want to show
you the Expression Builder.
| | 03:31 | So I am going to click on the little
Build button on the far right-hand side.
| | 03:35 | We need to find out the SalesRep
field on our Customer Order Form.
| | 03:39 | So I will open up Forms and All Forms,
I will click on our Customer Order Form,
| | 03:46 | and I will scroll down
and find the SalesRep field.
| | 03:49 | I will double-click on it and it
pops into place. I will click OK.
| | 03:56 | Next is the Data mode.
| | 03:57 | I am going to change this to Read-Only,
because I don't want anybody to change
| | 04:01 | the SalesRep contact information from
this this window. And last, I want to
| | 04:06 | change the Window mode to a dialog box,
instead of taking up the full screen.
| | 04:10 | I will close the Macro Builder and I will
save it when it asks, and now let's test it out.
| | 04:16 | I will go over to my Form view, and as
before, if I click on the dropdown, it
| | 04:22 | drops down my SalesRep chooser.
| | 04:24 | But if I double-click in the box, up
pops my SalesRep's contact information.
| | 04:30 | This is just one example
of a User Interface macro.
| | 04:33 | You can get creative with what
happens wherever you click, extending the
| | 04:37 | usability of your Access database.
| | Collapse this transcript |
| Using AutoExec macros| 00:00 | An Autoexec macro is one that runs the
very moment that you open up a database.
| | 00:04 | So if you have certain administrative
tasks that you do every single morning, or
| | 00:08 | there's certain forms that you use all
day long, you can have them open up and
| | 00:12 | execute as soon as you open up your database.
| | 00:15 | Let's make that happen.
| | 00:16 | I'll go to my Create ribbon and
choose Macro on the far right.
| | 00:19 | Now every morning, I always like to run
a report and see how my company is doing.
| | 00:24 | So I'll drop down my
Actions and choose OpenReport.
| | 00:29 | Down at the bottom, I will choose the
report that I want to see, and I will open
| | 00:33 | up my Orders by Customer Report.
| | 00:35 | If I want, under View, I can change it
from the Report view and I can send it
| | 00:40 | straight to the printer. Or, I can put it
in Print Preview, and that way, I can see
| | 00:45 | what it would look like, and I
can choose to print it or not.
| | 00:48 | I also use my Customers Form every
morning, so I'll go up to my Actions again,
| | 00:53 | and this time I'll choose OpenForm. And the
form that I'll open is my Customers Order Form.
| | 00:59 | I also want to keep tabs on my Sales Reps.
| | 01:02 | So I will choose OpenForm again and
choose the form name, Sales Reps and Customers.
| | 01:07 | I will close my macro and save it.
| | 01:10 | Now here's the crucial part.
| | 01:12 | It's giving the macro the name AutoExec,
which is what tells Access to trigger it
| | 01:16 | the moment you open the file. I will click OK.
| | 01:21 | So now I am going to go up to the Office
button and close my database, and then I
| | 01:26 | will open it up again. And
immediately, three different things happened.
| | 01:30 | Here's my Sales Reps and Customers Form.
| | 01:33 | Here's my Customers Order Form. And
here's that report already in Print Preview
| | 01:38 | and ready to be printed.
| | 01:40 | Creating in Autoexec macro will save you
precious time every morning to automate
| | 01:44 | your key repeating tasks.
| | Collapse this transcript |
|
|
14. Database Management TechniquesExporting to PDF| 00:00 | You can share your data with others,
whether they have Microsoft Office or not,
| | 00:04 | by creating PDFs, Portable Document Formats.
| | 00:08 | PDFs preserve all of your formatting and layout.
| | 00:10 | Essentially, they're
uneditable snapshots of your object.
| | 00:14 | You can turn any Access objects into a PDF,
whether it's a table, form, report or a query result.
| | 00:21 | Scroll down to the Navigation pane and
open up the Orders by Customer Report.
| | 00:24 | Go to the External Data Ribbon.
| | 00:28 | Over here in the Export
group is the PDF or XPS button.
| | 00:32 | XPS is Microsoft's alternative PDF format.
| | 00:36 | First it will ask you
where you want to save the file.
| | 00:38 | I'll move it to my Desktop.
| | 00:40 | Here, it tells you Save as type, and it says PDF.
| | 00:43 | This check mark gives you the option
of opening it automatically in Adobe
| | 00:47 | Reader, after you have published it.
| | 00:48 | You have two options here for file size.
| | 00:52 | Standard gives you a higher quality
document, but it can also have a very large
| | 00:55 | file size, depending on the contents.
| | 00:58 | Minimum size would be much smaller.
| | 01:01 | If I go under the Options button, I can
also select a certain page range if I'd
| | 01:05 | like, and there are two more check
marks here that might be relevant for you.
| | 01:10 | I'll click OK and then I'll click Publish.
| | 01:13 | Adobe Reader opens with a
copy of my order's report.
| | 01:18 | Now it's ready to be saved, e-mailed,
or anything else I'd like to do with it.
| | 01:23 | My next window asks if I want to save those
export steps, and I am not going to save them.
| | 01:27 | Now there is another way
of saving a PDF as well.
| | 01:31 | If I go up to the Office button and
over to Save As, I have an option to save a
| | 01:37 | PDF right here, and it
works exactly the same way.
| | 01:41 | Saving your Access objects as PDFs
is a perfect solution when you need to
| | 01:45 | share your data with other people,
but you don't want to give them the Access
| | 01:49 | file itself.
| | Collapse this transcript |
| Compacting and repairing a database| 00:00 | It's important to do maintenance
on your database once in a while.
| | 00:03 | Compacting and repairing your
Access file keeps it running smoothly and
| | 00:06 | minimizes your file size.
| | 00:08 | If you find that your database is
slowing down or becomes corrupted, Access
| | 00:12 | could use a repair. Or, when you delete
records out of your database, Access does
| | 00:17 | not reclaim the disk space and your
file size does not get any smaller.
| | 00:21 | To solve both of those issues,
perform a Compact and Repair on it.
| | 00:25 | Go up to the Office button, and down to
Manage. Choose Compact and Repair Database.
| | 00:32 | Access will trim down your file
size and keep itself running smoothly.
| | 00:36 | Be sure to do a Compact and
Repair periodically, especially after
| | 00:39 | large deletions.
| | Collapse this transcript |
| Locking files| 00:00 | When you look at an open Access data
in Windows, you'll see that there are
| | 00:04 | actually now two files;
| | 00:06 | one is your original database, but you
have a second file that has a little lock
| | 00:10 | on the icon, and the file extension
also has an L at the beginning of it.
| | 00:15 | This is called a lock file.
| | 00:17 | Access creates this file because it
automatically saves your data as you work.
| | 00:21 | If you are going to be doing any
database management and moving your database,
| | 00:25 | make sure you move the original file.
| | 00:28 | Don't move the lock file accidentally.
| | 00:30 | If you try and open the lock file,
you will get an error message.
| | 00:35 | Being aware of lock files will help
you with your database management.
| | Collapse this transcript |
|
|
ConclusionGoodbye| 00:00 | Now that we have covered close to 100
power tips and shortcuts, you are well on
| | 00:04 | your way to becoming an Access Pro.
| | 00:06 | Here are three things to remember.
First, in this course, we built on all
| | 00:10 | the topics covered in the
Access 2007 Essential Training.
| | 00:13 | If at any point in this course, you
felt like I glossed over something
| | 00:16 | significant, take a look at the
Essential Training for in-depth
| | 00:19 | foundation knowledge.
| | 00:20 | Second, Access is very exact.
| | 00:23 | If your procedure isn't working, check
to make sure that you are clicked on the
| | 00:26 | right control, and that your coding
is precise. And third, even with all we
| | 00:30 | covered, we are still scratching
the surface of Access's capabilities.
| | 00:34 | Learning to program macros and SQL will truly
allow you to bend your database to your will.
| | 00:39 | Thanks for watching, and
may your reports be with you.
| | Collapse this transcript |
|
|
