you're going to learn about the exciting features of this great product.
I really enjoyed working with Access 1.1,
but Access 2 has added some great and exciting features.
On this tape, you're going to get an introduction to Microsoft Access.
You're going to learn how to create your own database,
how to enter, edit, and validate data, how to get help when you need it,
how to sort and find, and also how to query.
You're going to create your own forms and reports,
and you're going to also learn how to import data from other sources.
In this first section, you're going to get an introduction to Access.
First, you're going to learn how to start Access.
You're going to learn the parts of the Access desktop
so that you can be more comfortable in working with the product.
You're going to learn how to view a table and a table structure,
and also how to print out the contents of the table.
After this section, you'll be a lot more comfortable in working with Access.
On my machine, the Microsoft Access icon
is within the Microsoft Access program group.
I'm going to come over here to Microsoft Access
and double-click on the icon.
This is loading up Access for me.
When Access first loads, I get this introductory screen
with Microsoft Access cue cards.
From here, I can actually get a quick introduction,
explore a sample database, and so forth.
What I'm going to do is to close this window down right now.
I'm going to double-click on this control icon right here.
At this point, I get two menu options, File and Help.
And I also get a couple of toolbar icons.
Notice if I place my mouse cursor over the toolbar right here,
it says New Database.
And if I come over to here, it says Open Database.
I'm going to go ahead and open a database.
I could also do that through the File menu and Open Database.
I'm going to go ahead and do it through the toolbar right here.
I click on Open Database.
And up comes a listing of any databases that would be in my C colon backslash
Access directory.
I don't have any in this directory.
I'm going to go into Sampp Apps.
This is a directory that you should also have underneath your Access directory
since it's installed along with Access.
I'm going to double-click on Sampp Apps.
And now I get a listing of the sample files
that come with Microsoft Access 2.0.
I'm going to come over here to Northwind and double-click my mouse.
That's going to select the default option.
Notice the blackened option over here, which says OK.
I'm going to come over to Northwind and double-click.
Now I've opened up the database.
Let's take a look at our Access desktop.
On the top, we have the Microsoft Access title bar.
Down below it, we have the Microsoft Access menu bar.
And below that, we have the toolbar.
We also have the database that we opened.
Notice our database has a title, and the title is Northwind.
At this point, I also have some tabs on the left side over here,
table, query, form, report, macro, and module.
We'll learn more about these a little bit later.
Let's learn about the toolbars and tooltips.
Using the toolbar, you can access the most commonly used features
of the product.
All of the features found on the toolbar
can also be found in Microsoft Access's menus.
But you're going to see that you can do things a lot more quickly
and comfortably using the toolbar.
The tooltips tell you what each tool does.
If I bring my mouse up to the toolbar
and place my mouse for a few seconds over any of the tools,
I can see what that tool does.
In this case, I have new database selected.
Now I have open database, attach table, print, and so forth.
We're going to use a lot of these tools today in this tape.
Let's take a look at the database window here.
Earlier, I referenced these tabs that you see on the side
of the database window.
A database is actually a collection of tables, queries, forms, reports,
macros, and modules.
If I take my mouse pointer and click on Query,
I can see all of the queries that are part of this database.
If I click here, I see all of the forms.
Here are the reports, the macros, and the modules.
We're going to start by working with tables.
And later on, we're going to work with queries, forms, and reports.
In other tapes, you'll learn about macros and modules.
I want to view the contents of the customers table.
First, I need to make sure that the Table tab is selected.
So I've clicked on it.
I get a listing of all of the tables in this database.
One of the tables is the customers table.
In order to open up that table, I can either double click on its name
or I'll single click and then click on this Open button right here.
Now I've opened up the table and I'm looking at its contents.
Notice it says Table Customers.
And I've got a bunch of information here.
What I'm looking at is fields and records.
Going across the top here, I can see the names of my fields.
Customer ID, company name, contact name, contact title.
And I've got some other that are hiding over here to the right.
In order to get a better look at this table right here,
I'm going to come up to the Maximize button for the table window,
which is this up arrow right here, and click on it.
Now I'm looking at the customer table,
but it's taking up all of my screen.
Again, I have my fields.
If I want to view the fields further over to the right on this table,
I can come right down to here, to this right here.
I can come right down to here, to this right scroll arrow, and click on it.
Notice I'm seeing the other fields in my table.
I can also come over to this left scroll arrow, and click on it,
and come back over to the left.
I can take this scroll box and click and drag it to any point and let go.
And I've moved over as well.
So there's several different ways, even using my mouse,
to move over to the right or to move back over to the left.
So these are my field names.
They're categories of information.
Going down, I have records.
Each record is an individual entity, a piece of information.
So right here, I have all the information about one customer
with the customer ID of ALFKI.
Notice I have the company name, contact name, contact title, and so forth.
If I want to scroll down and see all the other records using my mouse,
I can use this down scroll arrow right here and click on it.
I'm scrolling down through my table.
I can come up to the up arrow and scroll back up.
And I can also take the scroll box and click and drag it down and let go.
And I'm viewing a different part of my table.
So remember, using your mouse, you can use the horizontal or vertical scroll bar
to move about your database table.
Let's see some other ways that we can move around our table.
Take a look at these buttons right down here.
I'm going to go ahead and click on this right arrow.
I'm moving down a record at a time in my table.
If I click on this button right here, I'm moving up a record at a time in my table.
I can also move to the very last record in my table
or to the very first record in my table.
So those are some ways that I can move around using my mouse.
Let's try some keyboard methods.
I'm going to tap my tab key several times.
And I'm moving over a field at a time to the right.
Shift-Tab, and I'm moving a field at a time to the left.
Down arrow, and I'm moving down a record at a time.
Up arrow, and I'm moving up a record at a time.
Page down, and I move down a screen at a time.
Page up, and I move up a screen at a time.
Let's go ahead and come down to this record right here
and over to the right a little bit.
And I'm doing that with my right arrow this time instead of my tab key.
Now I'm going to tap my home key.
Home brings me to the very first field in the current record.
I'm going to tap my end key, and end is taking me to the very last field
in the current record.
Home, and I'm back at the first field in the current record.
Now I'm going to hold down my Control key and tap my home key.
That's going to take me to the first field of the first record.
I'm going to hold down my Control key and tap my end key, E-N-D key,
and I'm now going to the last field of the last record.
So Control-Home, Control-End.
And now I'm going to do Control-Home again.
I'm back at the first record.
Underlying every table is a table structure.
This is something that you set up before you add any data to the table.
It defines the field names, the field types,
and the field lengths of the table.
There's also a lot of other things that we can define in the table structure.
Let's take a look at the structure of this table.
The easiest way to do this is with this tool right here.
Notice that it's called Design View.
I'm going to click on the Design View tool,
and I go into Design View for this particular table.
Here's the structure for the table we were just looking at.
I've got several fields, customer ID, company name, contact name,
contact title, address, city, region, postal code, country, phone,
and fax.
All of the fields in this particular table are text,
meaning they contain text data.
I also have some descriptions over here.
These are optional but are helpful when you're
entering data into the table.
Down below, I have attributes of this particular field
that I have selected, facts.
These are actually properties.
That's the official name for them, field properties for the field
that I have selected.
Notice I have field size, format, input mask, caption, default value,
and so forth.
We're going to learn a lot about these later in the tape.
If I want to go back and look at my actual data,
I can click on this tool right here, which is Data Sheet View.
I'm going to click on that now, and I'm back looking at my data.
So I can go to Design View and back to Data Sheet View
at any time that I want.
If I don't want this data sheet to take up the entire screen,
I can come up to this Restore button right here.
Notice I'm not on the Restore button for the application,
but instead just for this particular window.
I'm going to click on it, and now my table's
not taking up the entire screen for access.
I can move this window around by grabbing its title bar.
I can size it with a sizing handle.
And no matter where I am, I can go back into Design View
at any time, back and forth from Design View to Data Sheet View.
You're going to see that understanding
the definition of a table is vital when you're
working with Microsoft Access.
So we'll be learning more about it later.
For right now, let's see how to print out the data in this table.
To take a look at what this data would print like,
if I were to send it to the printer,
I can use my print preview icon on my toolbar right here.
I click on it, and I get a zoomed out view of what
the printout would look like.
Notice I have a little magnifying glass.
If I take that magnifying glass and place it
where I want to zoom in and click,
I've now zoomed in to that part of my table printout.
Take a look at the printout, and notice
that I have my field names going across the top.
And I can scroll across using my scroll bars.
And I have all of my data records going down.
There's many ways to print in Microsoft Access,
and this is just the most basic.
We're going to learn other ways to print later.
If I want to come up and just send this out to the printer,
I can come to this tool right here and click on it.
I can print all a particular selection or pages
that I'd like to print right now.
In this case, I'm going to leave it at all and click on OK.
I'm going to close down my preview
by double clicking on the control icon,
and now I'm back at my database window.
After viewing this section, you should have a better idea
of what Access is all about.
If you're not quite sure yet, don't worry.
You'll be getting a lot more practice
in working with Access throughout the tape.
In this section, you learned how to start Access.
You learned about the Access desktop and the database window.
You learned how to view the contents of a table
and how to print it out.
You also took a look at the behind the scenes
structure of the table.
In the last section, you learned how
to work with a database that had already been created for you.
In this section, you're going to create your own database.
You're going to learn about the elements of a database
and what a relational database management system is.
You're going to create your own database and your own tables.
You're going to learn how to work with the properties
of fields and tables and how to add tables to your database.
Let's get started.
We've been viewing this database called Northwind.
I'm going to go ahead and close it down
by double clicking on this control icon right here.
I don't need to worry about saving.
Everything's automatically saved for me.
Now I'm going to start a brand new database.
Notice this icon right here says new database.
I could also select it by coming up to the file menu
and selecting new database.
I'm going to go ahead and use the tool right here.
Click on it.
And now I've got this dialog box for new database.
I want to make sure that my new database is
going to go into my sample apps directory
along with my Northwind database and the other ones
that Microsoft provided.
I'm going to call my new database MyDB.
It's just a temporary one we're going to create so that you can
learn how to create your own database.
I click on OK.
And this is automatically going to get the extension of MDB,
which stands for Microsoft Database.
So my file is actually called MyDB.MDB.
Now I have a database window.
Unlike the one that we looked at, the Northwind database,
if I click on each tab, none of them have contents.
Microsoft Access Databases are actually
a collection of different components.
Tables are a way that we can define
the structure for entering our data
and also a way to enter our data.
Queries allow us to view particular parts of our data.
Reports and forms are ways to view our data
and to print it out.
And forms are different than reports
and that we can enter our data through forms
rather than through the normal table mode.
We also have modules and macros.
Modules and macros are ways to automate our system.
We're going to learn how to create a new table with
and without Microsoft Access's new table wizards.
I'm going to come over here, make sure that table
is selected, and then select New.
Notice that I get the option of creating a table using
the table wizard or just new table.
Let's start by trying to create a table with the table wizard.
The table wizard gives me a lot of help in creating my table.
Notice that I can look at business types of tables
and personal tables.
Once again, this is just a way to facilitate
the creation of my table.
I've got personal selected.
And as I click on each of these different items, friends,
guests, category, household inventory, recipes,
and so forth, I get a different listing of sample fields
over here.
Notice for recipes, I get things such as a recipe
ID, which meal, vegetarian, calories per serving,
and so forth.
Where if I come up to guests, I get a guest ID, first name,
last name, and so forth.
I'm planning a party, so I'm going to go ahead and have
a guests table.
I now need to tell Microsoft Access which fields I
want to include in my table.
Notice that these are just sample fields over here,
and there's no fields currently selected for my table.
If I want to take guest ID and include it
in my new table over here, I can use this button right here
to move that field over so that now it's
going to be included in the table that I'm creating.
If I want first name, I can click on it
and then click on this right over here.
Or like I'm going to do with last name, I can double click.
I double clicked on last name and notice
that it's included over here.
If I want to select spouse name, I'm
going to double click on it.
I'm going to double click on city.
I'm using the scroll arrow.
Double click on state, postal code,
and I also want to know the home phone.
So those are the fields that I've
selected for inclusion in my new table.
Now I'm going to go ahead and click on next.
Wizards walk you through step by step
in creating something, in this case, a table.
I'm being asked, what do I want to name my table?
And I'm being prompted with guests.
And I'm going to call this guest list for anniversary party.
Also, I can let Microsoft Access set a primary key for me,
or I can set a primary key myself.
We're going to learn about primary keys later
in the tape.
For now, I'm going to select set the primary key myself
and select next.
I'm being asked what data will be unique for each record.
I have to have, or it's ideal to have,
a key field in each table that's going to be unique.
In other words, no guest is going to have the same guest ID.
I'm going to go ahead and leave this at guest ID.
And also, it says what type of data
do you want the primary key field to contain?
Consecutive members, Microsoft Access
assigns automatically to new records,
numbers I enter when I add the new records, or numbers
and or letters I enter when I add the new records.
If I leave the selection right here, what this means
is that every time I add a guest to my table,
they will automatically receive the next available number.
So my guests will get guest IDs of 1, 2, 3, 4, 5, and so forth.
I'm going to go ahead, leave guest ID up here,
and allow Microsoft Access to assign the numbers for me
and select Next.
I'm also asked for the moment when Access
is done creating my table, do I want
to modify the table design, enter data right into the table,
or enter data into the table using a form
the wizard creates for me?
I'm going to leave the default selection,
enter data directly into the table,
and go ahead and click on Finish.
Access is creating my table for me, setting up the fields
and so forth.
And notice that now I have a table called Guest List
for Anniversary Party.
It has a counter field.
That's because I've indicated that I want access
to assign the guest ID for me.
And I can enter the first name, last name, spouse name, city,
state, zip, and so forth.
If I want to see the design of this table,
remember that I can come up to this button right here,
the Design View button, click on it,
and now I'm looking at the design of my table.
I have guest ID, and its data type is counter.
I have first name, its data type is text, last name,
spouse name, city, state, postal code, and home phone.
These are all of the fields that I
picked for inclusion in my table.
Also notice that I have some special captions down here.
And I've got some other properties,
such as in this case, index.
And it says yes and no duplicates.
Once again, we'll learn all about properties
in a few minutes.
I'm going to go back over here to my table view
and hit my tab key to come over to first name.
I'm going to invite somebody or put somebody on my guest list.
And the person that I'm going to enter is going to be Mary.
The last name is going to be Jones.
I'm hitting tab to get from field to field.
Mary's spouse name is John.
The city is going to be in Tarzana, California,
913-56.
And the home phone is going to be 818.
Notice that I have up here a special picture for my data
entry, 345-4077.
So I've entered a record.
If I wanted to enter a second record,
I can hit tab right from my last field over here.
And I'm now ready to enter my next record.
Notice that I've got a guest ID of number one
for my first record.
A moment ago, I talked about having a unique key field.
In other words, each of our guests
will get assigned a unique guest ID.
This brings us to an important concept in database management.
It's called relational databases.
When you're creating a database, you probably
are going to have several different tables included
in your database.
You're going to want to relate those tables on key fields.
For example, let's think of our classic business situation.
We've got customers.
Those customers have invoices.
Those invoices have detail line items on them.
We're going to have a customer table.
And we're going to have a field in that customer table,
probably called customer ID.
A customer ID is going to be unique.
No two records in the table will have the same customer ID.
Then we're going to have our invoice table.
On the invoice table, one of the fields
is going to be customer ID.
That field is going to identify which customer our data is
being entered for.
We're also going to have an invoice detail table.
On the invoice detail table, you're
going to have an invoice number field.
And that's going to indicate which invoice number
that detail line is going to be related to in the invoice
table.
So what we're doing is by having unique fields in each table,
we can relate the tables.
And we don't need to include all of our information
in one large table.
This is called relational database management.
We'll learn more about relational databases
and how they work later.
For now, let's go ahead and close down
this table we created, our guest list.
And you're going to create one without a table wizard,
so you can see how that works.
I've got my table guest list listed right here,
because I'm on the table tab.
And I'm going to select new to start a new table.
This time, I'm not going to use a table wizard.
Instead, I'm going to click on new table.
Notice that this is a little bit different than working
with the table wizard.
It's now my responsibility to put in all of my field names,
data types, descriptions, and so forth.
Let's enter a field name called last name.
Field names can contain spaces, and the names
can be up to 64 characters in length.
I've now entered the name for this field.
And now I'm going to hit my tab key to come over to data type.
Let's take a look at all of the data types
that we have available to us.
In order to do that, I'm going to click on this down arrow
with the line underneath it.
The types of fields that are available to me
are text, memo, number, date, time, currency, counter, yes,
no, and ole object.
I use text fields when my entry is
going to contain mostly text.
Memo fields are different than text fields,
and that I can put pages of information.
Number fields contain numbers, so things like prices, costs,
and so forth.
Date and time fields contain anything
that's a date or a time.
Currency fields are similar to number fields,
but they automatically relate to money.
So I might make a number field something like age,
where I might take a price and make it currency.
I also have counter fields.
You learned a little bit about them with the last table.
Counter fields automatically increment for me.
Yes, no fields are used whenever my entry
is going to contain a true or false, a yes or a no.
For example, whether or not someone has paid.
An ole object is the most interesting type of field.
An ole object field can contain pictures, spreadsheets, sound,
video, anything that you'd like that field to contain.
This is a great type of field.
I'm going to go ahead and leave last name as text
and come over here and click in description.
Remember that the description is optional,
but it helps to describe the field.
And it's also displayed on the status bar
when I'm using this field on a form.
I'm going to go ahead and type in friends last name.
I'm going to come down here and click
in field name for my second entry and type in first name.
This is also going to be text and the description
is friends first name.
I can hit Tab again to come down to the field name
for my third field.
For this field, I'm going to go ahead and put in address,
text, and friends address, city, Tab, and put in name of city,
state.
It's also text, name of state, and zip.
So I'm going to put zip code and it's
going to be text and it's going to be the friend zip code.
Notice that I didn't make zip code a numeric field.
It's not actually something that I use in calculations.
And in fact, a zip code can contain leading zeros
and other characters such as dashes.
So I'm going to make my zip code a text field.
Now that I've defined the basic structure
of my table for the moment, I'm not
going to fill in any of those properties yet.
And I'm just going to go ahead and double click
to close down this table.
When I double click, I'm asked if I want to save the changes.
And yes, I do.
I'm now prompted for the name of my table.
And this is going to be listing of my friends.
Once I click on OK, I'm asked, I'm
told there's no primary key defined.
Do I want to create one?
Actually, do I want to have access create one?
I don't.
So I'm going to go ahead and say no.
I have two tables, guest list for anniversary party
and listing of my friends.
If I want to open up and view my table called listing
of my friends, all I need to do is come over here,
double click on it.
And now I'm ready to enter some data.
For example, I can enter in Terry for the last name,
Sue for the first name, 45 Sunstone,
and Westlake Village, California, line 3063.
Hit Tab again.
And now I'm ready to enter my next record.
So I've created a table with and without a wizard.
Remember, if I want to go back to design view
of this particular table, all I need to do is come up here,
click on this icon, and I'm back in design view.
Once you've created the definition for your table,
you're going to want to refine that definition further.
For example, you're going to want
to specify things such as the width of each field,
the format for the field, and so forth.
We're going to learn how to do those things now.
So far, we haven't been paying much attention
to the field properties at the bottom of our screen.
When I move from field to field by clicking in each field,
the properties for that particular field are displayed.
So for example, right now, I'm looking at the properties
for my address field.
I'm going to come down and click on State.
I know that my state is never going
to be more than two characters.
If I want to change the properties for my state,
I look down here under field properties,
click and drag over 50, and change it to the number 2.
And I've now indicated that state is only
going to be two characters.
Now, if I come up to my city and click on my city,
I can indicate that I never want my city
to be more than 25 characters.
So if I'm moving from field to field,
I can see and change the properties
for that particular field.
Let's double click to close down this table.
And I'm prompted and asked whether I want
to save the changes to my table.
This is because I changed some definitions for my table.
I changed some field sizes.
I'm going to go ahead and say yes.
I'm also told that the field size property of one or more
of my fields has been changed to a shorter size.
I'm warned that I might truncate or lose some of my data.
I'm going to say that I want to continue anyways.
I click on OK.
I'm going to go ahead and close down
the database called MyDB.
And this time, to open up another database,
rather than coming over here to the toolbar,
I'm going to come up to the File menu.
I come up to the File menu and take a look right here.
I have a listing of the last four files that I've opened.
There's MyDB and there's my Northwind database.
I actually want to open up my Northwind database.
So I'm going to select it by clicking on it.
Now I'm back in my Northwind database.
And we're going to learn a little further how
to work with properties.
You just got a brief introduction on how
to work with field properties.
You're going to add a table to this existing database.
And then you're going to learn a lot more about working
with field and table properties.
In order to add a new table to this database,
I have the Table tab selected and I click on New.
Now I'm going to click on New Table
because I don't want to use the table wizard right now.
I'm ready to enter my field names, data types,
and descriptions.
For my first field name, it's going to be PAID.
I'm creating a personnel action table
that's going to relate to an employee table I already
have set up.
I'm going to go ahead and hit Tab.
And this particular field is going to be a number field.
All I need to do to select number,
if I know I want a number field, is type an N for number.
And notice that I already have number selected
the moment that I type an N. Now I can hit my Tab key
and type in a description if I want one.
I'm not going to put in any descriptions for right now.
I'm going to tab down to my second field,
and that's going to be PA type.
For my data type on that one, it's going to be text.
And I can tab down ready for the next field.
PA initiated by.
This is who initiated the personnel action.
And that one's also going to be a number field.
So I type in N and tab on down to the next one.
It's going to be a particular ID that's going to be numeric.
Next, I'm going to have PA schedule date.
That's going to be a date field.
So I type a D for date, and I can tab on my way.
PA approved by.
Tab over to the data type.
And that one is going to be a number.
Now remember, if I don't remember my particular names,
remember, if I can't quite recall the different data
types, all I need to do is use this pull down right here,
and I can pick from my list and pick number.
I'm going to go ahead and tab on my way.
PA effective date.
That's also going to be a date field.
PA rating, and that's going to be a number.
PA amount.
That I know is always going to contain money.
So I'm going to go ahead and select C for currency
and tab on my way.
PA comments.
My comments might need to be quite long.
So I don't want to limit to a text field.
Instead, I'm going to pick memo.
So I type an M for memo, and now I've
indicated that I want a memo field.
Now that I've defined my field names and my data types,
I'm going to further refine everything using my field
properties down here.
I'm going to start on my PAID.
I'm going to click up here on PAID
and look down at its field properties.
If I click in my field size, and remember,
this is a numeric field, I get a whole pull down here.
Byte, integer, long integer, double, and single.
You might wonder what these types are.
It's quite easy to forget what each of them is.
If I want to take a look at what each of these means,
all I need to do is access help right from here.
Notice I've already clicked in field size.
So access knows that I want help on my field size.
Now I'm going to hit F1, which is help.
I'm launching access help.
And right down here, I get a listing
of the different types of field sizes that are available
for my numeric field.
Notice that Byte allows me to put in numbers from 0 to 255,
no fractions, and so forth.
Notice also the difference between each of these
besides the information they hold is the amount of space
that each one takes up.
This one occupies one byte, integer,
two bytes, long integer, four bytes, and so forth.
I could make everything double, but I'm
allocating a lot of extra disk space for no reason.
Now that I've gotten some help on what each of these does,
I'm going to close down help.
And now I'm ready to pick what I really want.
I want long integer.
That allows me to only enter integers
within a particularly wide range.
For my format, this is how my data is going to be displayed.
I'm going to pick what's called general number.
Take a look and notice right here,
I actually get a visual display of what
each of these properties does, general number
versus currency versus fixed and so forth.
I'm going to go ahead and pick general number
so that I can have different amounts of decimal points.
And that's all that I'm going to set for my PAID.
Now I'm going to come down to PA type.
Because this is a text field rather than a number field,
notice that my field size is different.
I don't get to pick from short integer, long integer,
and so forth.
Instead, I can indicate a numeric size.
I know that my type should never exceed one character,
so I'm going to type in a 1.
Let's come down to initiated by.
For initiated by, I don't have a very wide range of numbers.
So I'm going to go ahead and pick integer.
Also, for my format, I'm going to pick general number.
For my scheduled date, I'm going to come over here
to scheduled date.
And for my format, take a look at all the options that I get.
Once again, I get help on what each of these looks like.
I'm going to pick short date, this one right here.
For approved by, that's going to be a code.
But I know it's not going to vary very much,
so I'm going to go ahead and pick integer.
And once again, I want general number.
For my effective date, I'm going to pick short date.
For my rating, that's going to be another integer.
And general.
For my amount, notice it's a currency field.
So the format is automatically currency.
What I'm also going to do, though,
is take a look at my different options
and notice that I could, even with a currency field,
pick something that looks a little bit different.
I'm not going to make any changes here.
And for my comments, I'm not going
to add any properties whatsoever.
You just learned how to set some of your properties
for your fields.
Sometimes when you're entering data,
you want something called a default value
to pop up for you.
What this means is, as you're entering data,
a particular value, for example, a state name of California,
will automatically pop up for you.
You use that value to set up a change in your data.
You use default values when a lot of your data entry items
contain a particular value.
The default value for a field is one
of the properties for the field.
That's what we're going to learn how to change now.
First, I'm going to set a default value for the PA type.
To do that, I come up here to the field name, PA type,
and click.
Now I'm looking at the properties
for that particular field.
Notice I have a field size of 1, which I set earlier.
I want to indicate a default value,
so I'm going to click down here in default value.
Most of my PA types are a queue, so I'm
going to go ahead and select or type a queue.
This is going to be an automatic value that comes up
whenever I enter a new record.
I'm also going to put in a default value
for my PA scheduled date.
So I click over here on PA scheduled date,
and come down to default value.
I'm going to put in a special access function
for my default value for this field.
The function is going to be the date function.
I type in date, open paren, close paren,
and this indicates that the current date is automatically
going to be placed in the record that I'm adding.
For PA effective date, I'm also going to have a default value.
I come over to the field name and click,
and then come down to default value.
Click in default value.
The default value for my PA effective date
is going to be today's date plus 28 days.
To do this, I type in date, open paren, close paren.
This gives me the current date.
And then I put in plus 28.
In other words, plus 28 days.
So now I've entered some default values for my data.
I can also enter some validation rules.
Now that you've learned how to work
with the back end of your table, in other words,
you've learned how to enter field properties,
let's enter some data and see how the field properties affect
us.
Instead of double clicking to close down this table,
I'm going to click on this icon on my toolbar,
Datasheet View, to move right to Datasheet View.
I'm told that I must save the table first.
Do I want to save it now?
And yes, I do.
I'm also asked to give my table a name,
since I haven't named my table yet.
I'm going to call this my Personnel Action Table, my PA
table.
Let me go ahead and click on OK.
And I'm warned there's no primary key defined.
Do I want to have access create one?
And I'm going to say no for now.
At this point, I'm looking at my table.
Notice that I'm in my Datasheet View,
and I'm ready to enter some data.
For my PA ID, I'm going to type in the number 1 and hit Tab.
Notice that my PA type is automatically set to Q for me.
I hit Tab again, and I'm going to go ahead
and type in the number 1.
Hit Tab again, and notice that my schedule date is also
automatically set for me.
I hit Tab again, and I'm at PA approved by.
I'm going to enter a 2 here and Tab one more time.
Notice that my PA effective date is 28 days
after my PA scheduled date.
I hit Tab again, and I can enter my PA rating.
I'm going to type in the number 2 and hit Tab.
For the amount, notice that it automatically
has dollar signs and two decimal points.
This is because this is a currency field.
I type in the number 1,000.
I don't need to type in a dollar sign or any decimal points,
and I hit Tab, and it displays as 1,000.00,
including a dollar sign and a comma.
And I'm going to type in this is a great raise.
When I hit Tab, I'd be ready to enter my new record.
And notice once again, I have the Q, the 516, and so forth.
I want to go back into Design View.
I simply click on the Design View icon right here.
And now I'm looking at Design View of my table again.
Each time when Access has asked you whether or not
you wanted Access to create a primary field, we said no.
In this next section, you're going
to learn about relationships, primary keys, and indexes.
I'm going to go ahead and close down this table
by double clicking on the Control icon right here.
I'm now looking at my database window.
This database already has several relationships defined.
If I want to see the relationships that
are already defined, I can come right up here
to this icon, which is called the Relationships icon,
and click my mouse.
I now get a graphical picture of all of the relationships
that have been set up.
Notice that I have a Customers table, an Orders table,
and an Orders Details table.
This is exactly like the example that I
explained to you in an earlier section of the tape.
In my Customer table, I have a customer ID.
Over here, I have my Orders table.
Notice that my Orders table also has a customer ID.
And there's a line indicating that these two tables have
already been related on customer ID.
In my Orders table, I have an Order ID.
Over here, I have my Order Details table.
A relationship has been set up from the Order ID
in the Orders table to the Order ID in the Orders Detail table.
I also have several other relationships
that have been set up for me.
Let's take a better look at these relationships.
Customer ID to my Orders table.
Every customer should have a unique customer ID.
But if I look over here at my Orders table,
I hope that I have multiple orders for a customer.
This is called a one-to-many relationship.
For every one customer in my Customer table,
I hopefully have multiple orders in my Order table.
So the customer ID, we hope, is going
to appear several times in this Orders table.
If I look from my Orders table to my Order Detail table,
my Order ID should be unique within my Order table.
So every order should have a unique Order ID.
But over here in my Order Details table,
I hope that my customer has ordered multiple items.
So I hope that there's multiple Order IDs over here
on my Order Detail table.
Once again, a one-to-many relationship.
But if I look from my Order Details table
to my Products table, in my Products table,
this is a listing of my inventory of all the products
I have, I should, in this table, have unique product
IDs for every product.
So looking from my Product ID over to my Product table,
I should have a one-to-one relationship.
When I look at this Product ID, I
should only find one Product ID in my Product table
that matches.
What we're going to do is set up a relation
between the Employee table, which
was already set up for you.
This is the table of all of the employees in the company
and our Personnel Action table, which you just created.
What we're going to do is relate them
on the Employee ID from the Employee table
to the PAID in the Personnel Action table.
To do this, I'm going to come over here
and click on Add Table to add a table.
At this point, I have my Personnel Action table.
Notice I can view my tables, my queries, or both.
We're going to just leave it at Tables
and select the Personnel Action table.
I click on it and click on Add.
At this point, I'm going to select Close
because I don't want to add any more tables.
If I want to set a relationship between the Employee ID
and the PA table, and I know the relationship is
based on the PAID in this table, all I need to do
is click on the Employee ID and drag my mouse down
on top of the PAID in the PA table and let go.
I get this dialog box right here.
I have Employee ID from the Employee table
and PAID from the PA table.
I'm going to go ahead and click on Join Type right here.
This indicates what kind of a join I want.
What I want to do is pick number three.
I want to include all records from my PA table
and only those records in Employees
where the joined fields are equal.
I'm going to go ahead and click on OK.
I also want to do something called
Enforce Referential Integrity.
I do that by clicking right here on Enforce Referential
Integrity.
What this means is that I cannot add a record to my PA table
unless the Employee ID exists in the Employee table.
And it also means that I cannot delete, if I don't want to,
I cannot delete entries from the Employee table
if there's corresponding entries in the PA table.
Now, my relationship is a one to many.
Employee ID is unique.
But within the PA table, I might have multiple personnel actions.
So I leave this button right here selected.
And notice I can cascade update related fields or cascade delete
related fields.
What this means, if I were to select Cascade Delete
related fields and I were to delete an Employee ID,
it would automatically delete all of the corresponding entries
in the PA table.
If I were to cascade update, what that means
is that if I were to change an Employee ID in the Employee
table, it would automatically change the corresponding
Employee ID in the PA table.
I don't want to cascade delete, but I
do want to cascade update.
So I'm going to go ahead and click on this box right here.
At this point, I'm ready to click on Create,
and I'm creating my relationship.
Notice I have a relationship going on here from my Employee
table to my PA table.
Now, the direction in which I dragged, in this case,
from my Employee ID to the PA ID is very important.
I created a relationship from the Employees table
to the PA table.
This is the one.
This is the many.
That's why I clicked and dragged the Employee ID
down to the PA ID rather than the other way around.
So you need to understand which is the one
and which is the many into your one-to-many relationship.
At this point, I'm ready to close down this window.
I come up to the Control icon and double click my mouse.
I'm asked if I want to save my changes, the relationship
that I've set up.
I do, so I click on Yes.
I've now saved my relationship, and I've learned
how to set up a relationship.
Before we complete this section, you're
going to learn how to create an index.
An index allows you to determine the order of your data.
It allows you to specify that the value in the field
must be unique or not, and it also
can speed up your data access.
We're going to talk more about data access in a little bit.
We're going to add an index and a primary key to our PA table,
and you're going to learn the differences between the two.
I'm going to select my PA table by single clicking on it,
and I want to go directly into Design Mode.
To do that from here, the easiest way
is to click on this Design button right here.
I'm now in Design Mode for my Personnel Action Table.
I want to make my PA ID my key.
In order to do that, all I need to do
is with that field selected, I come up here
and click on Set Primary Key.
By setting this as a primary key,
I'm ensuring that I cannot have any duplicate values
on my Personnel Action ID and that my table is automatically
going to be in order by my Personnel Action Number.
This is very similar to an index.
What we're going to do now is create our index.
An index is something that we can use in addition
to our primary key.
So remember that your primary key is the most important
setting, and index is something that's secondary.
I often look at my data in order by PA Effective Date.
I'm going to go ahead and select PA Effective Date
and look down into the properties right here
and notice that indexed says no.
I'm going to click on the indexed field, use my pull down,
and notice that I have three options, no, yes,
with duplicates OK, and yes, with no duplicates.
I want to allow duplicates.
So I'm going to say yes, duplicates OK.
So what does my index do for me?
Well, I often look at my data in the order of PA Effective Date
and I also often query it by PA Effective Date.
By creating this as an index, I'm
speeding up my searches whenever I
want to look at something by my PA Effective Date.
Although indexes speed up my searches,
it actually slows down my data entry
because every time I enter a record,
it automatically has to update this index.
So it's a trade off.
You only want to set indexes for fields
that you search on frequently.
Once again, remember my key is the most important thing.
This key ensures that I cannot have any duplicates on PAID
and that my records are automatically
ordered by my PAID.
I want to save my changes and I'm
going to go ahead and double click and say,
yes, I want to save my changes to the table.
And I'm back here looking at my database.
You've just learned a lot of information
about working with properties, key fields, and indexes.
You might find this a little bit overwhelming.
But it's important to spend the time now
and learn how to set up your databases properly.
If you don't spend the time now, you'll
find that your database tables will be very disorganized
and won't make a lot of sense.
Although complicated, this information
is very, very important.
Remember the properties of a field
determine what can go into the field and what it looks like.
Remember that a key field limits what
goes into your table in the sense that a key field must
be unique.
It also determines the default order for the table.
An index is a way to speed up your searches
and also at times to eliminate duplicates.
This will be reinforced further as you work with Access.
In this section, you're going to become more comfortable
in working with your data.
You're going to add and edit some records.
You're going to learn about data validation.
And you're going to learn how to change the appearance of your data
sheet.
Let's add some records to our customers table.
To do that, if I click on customers,
I can click on open.
Or remember, I can double click on customers.
I'm going to double click on customers.
And here's my customer table.
If I want to add a record, the best way to do that
is by clicking on this button right here, which
takes me to the end of the table, and then
clicking on this button, which takes me down one record.
I'm going to go ahead and enter a customer ID.
I type in ABCDE and hit my tab key.
I'm now ready to enter my company name.
I'm going to type in the ABC company.
My contact name is Joe Smith.
He's the president of the company.
His address is 123 Main Street.
And the city is going to be Simi Valley.
The region is California.
And the zip is 93063.
My country is USA.
The phone there is going to be 805-255-1212.
And he doesn't have a fax number.
I hit tab, and I can add another record.
Let's enter one more.
For this one, I'm going to put in LONDA.
The name of the company is going to be London Airlines.
The contact there is going to be Sally Hansen.
The contact title is going to be Owner.
And the address there is going to be 879 Stone Street.
The city is going to be London.
The region is going to be NSW.
And the postal code is going to be 07-123.
The country is England.
I don't know the phone or the fax right now,
so I'm going to leave them blank.
So I've added a couple of records.
If I realize I need to make a change, for example,
this isn't the ABC company.
It's just going to be ABC company.
If I get what's called an I bar, notice that my mouse right here
looks like an I. You don't want this form right here.
You want the I bar.
If I click and drag over the word the and let go,
and now hit my Delete key, I might
need to get that extra space right there.
It now says ABC company.
So to edit a record, I simply move up to that record
and make whatever change that I want.
If I want to change Sally's position, contact title,
to Chief Owner, I can come over here,
click to get an insertion point, and type in Chief Owner.
At this point, to really save this change, all I need to do
is move to another record.
To do that, I either click on another record
or I can use my up arrow key or down arrow key.
That's when the record actually gets written to the disk.
Besides that, I don't need to do any saving.
So I've added some records and edited some records.
What if I want to select a record or records?
To select one record, I get this mouse pointer form right here.
It's a right facing arrow over on the gray buttons
on the left side of the table.
I click and I've selected one record.
I want to deselect that record and select another one.
I bring my mouse pointer on top of the record
I want to select and click.
So I'm selecting a record.
You select records so that you can print them or do something
to them, such as delete them.
If I want to delete this one record right here,
I can click on it and hit my delete key.
I'm told I've just deleted one record
and I told click on OK to save my changes.
I click on OK and I've now deleted that record.
And let's go ahead and put in a few new customer IDs.
I'm going to put in LLLLL just because I want
to get something in quickly.
It's the L company.
And I'm just going to click right over here,
ready to put in another customer ID.
I'm going to go ahead for this record and put in MMMM.
It's the M company.
And finally, one more.
Click down on the new record and put in JJJJJJ
and it's the J company.
So now I've put in some records that I really want to delete.
To delete multiple records, I can select them at the same time.
I click and drag to select them.
I've selected the three that I want to delete.
Hit my delete key.
I'm warned that I'm deleting three records.
And then I click on OK.
And those records are gone.
So I've deleted those three records.
Now that we've deleted the records,
let's go ahead and close down this table.
At this point, we're going to learn about data validation.
To do that, let's go into design mode for the PA
table that we created earlier.
I'm going to click on the PA table and click on design.
I'm looking at the structure of my PA table.
In order to add some validation for this table,
I'm going to add a validation rule and validation text.
A validation rule is a rule for data entry.
For example, for a particular code,
you might want to limit entry to an A, C, R, or K.
In that case, you could put in a rule
that only those letters were allowed
to be entered into the table.
A validation text is a message that
comes up when somebody has violated the validation rule
while they're doing their data entry.
We're going to start by entering a validation rule
for our personnel action ID.
To do that, I click right here under validation rule
for my field properties.
And I want to limit the entries in the sense
that they must be greater than 0.
So I'm going to type in a greater than sign and then
a 0 to indicate that this must be greater than 0.
From my validation text, I'm going
to enter the personnel ID must be greater than 0.
Next, I'm going to enter some validation for my PA type.
I click on PA type and come down to the validation rule.
Remember, this is different than the default or automatic value.
This rule actually limits what goes into the field.
I want to limit to the following characters, H, S, Q, Y, B,
or C. Here's how I do it.
I type in H or S or Q or Y or B or C.
So I put in the valid entries separated by the word or.
That's all I needed to do.
I come down here to the validation text
and notice that Access has entered some quotation marks
for me.
You could have entered these, but it's a lot easier
to let Access do it for you.
The message that I want to give if somebody violates the rule
is that only H, S, Q, Y, B, and C can be entered.
Remember, this is the text that will appear
if they violate the rule.
I'm also going to, on PA initiated by,
indicate that it must be greater than 0.
I put in greater than 0 for the rule
and put in must be greater than 0.
Also, for my PA scheduled date, I want a validation rule.
Remember, my default is the current date,
but my rule is going to be the scheduled date must
be between today's date minus 3650.
You're wondering what that is.
That gives me 10 years ago and date plus 365.
This might seem complicated.
Let's take a better look at it.
I'm going to hit my Home key to go to the beginning of this.
It says between today's date minus 3650,
meaning between 10 years ago from today, and date plus 365.
That means one year from right now.
This syntax is important to access.
The keywords are between and end.
So for example, if I wanted to be very simple,
I could say between 1 and 5, meaning
that the number would need to be between 1 and 5.
For my validation text, I'm going
to put in a message saying that my scheduled date cannot be
more than 10 years ago or more than one year from now.
And there we go.
The last validation I'm going to put in
is going to be underneath my rating.
I click in Rating and come down to Validation Rule.
For this rule, I'm going to put between 0 and 9, or is null.
What this means is that my number
needs to be between the number 0 and the number 9,
or it can be blank.
That's what the is null means.
So you just need to learn a little bit
about Access's language.
Let's go back and enter some data into this table,
and let's see how we get affected by these rules.
To do that, I can come up and click on this button right here,
which is my data sheet view.
And I'm asked if I want to save my changes.
I say OK.
And notice my data integrity rules have been changed.
It says existing data may not be valid for the new rules.
But I like my existing data to be tested with the new rule.
If you have a lot of data, this can be lengthy.
You're told what records violate the rules.
I'm going to go ahead and just say yes.
And my data was tested, and apparently all of my data
met the rules.
I'm going to come down to a brand new record right here.
I'm going to enter in an ID, and I'm
going to try to enter in an ID of minus 1 and hit my Tab key.
Notice I get my validation text.
It says my PAID must be greater than 0.
I click on OK, and if I change this to 2 and hit my Tab key,
I passed my data entry rule.
Let's enter some more data.
If I've entered my ID, I'm going to go ahead and enter a type
of A. When I hit my Tab key, I get the message saying only
HS, Q, Y, B, and C can be entered.
I click on OK, and I'm going to change this to a C
and hit my Tab key.
For initiated by, I'm going to enter a 2.
For scheduled date, I'm going to go ahead and enter
5, 1, of 61 and hit my Tab key.
It says the scheduled date cannot be more than 10 years ago
or more than one year from now.
I click on OK.
I'm going to change it to 5, 1, of 94 and no problem.
PA approved by, I'll put in a 2.
And effective date, I'm going to go ahead on that one
and put in 5, 1, of 61 and Tab.
And it says the scheduled date cannot be more than 10 years
and Tab, and it accepted it because I don't have
that validation rule on my effective date.
For my rating, I'll put in 2.
And for my amount, I'll put in 2,000 and nothing
for my comments.
So you can see that you get your validation text.
It automatically comes up whenever
you violate your validation rule.
Let's try something a little bit different.
Here, I'm going to enter a PA ID of 1.
Remember, this record up here already has a PA ID of 1.
When I hit my Tab key, nothing happens.
Let's go ahead and Tab over to enter a PA initiated by
and make it the number 2.
I'm now going to go ahead and tap my down arrow
to try to come off of the record.
Tap my down arrow, and notice now I
get a different type of message, not one that I typed in.
It says duplicate value in the index primary key
or relationship changes were unsuccessful.
What Access is upset about is PA ID is a key field.
It's not allowing me to duplicate that entry.
If I change this to a 3, now I can down arrow
off of the record.
In a little bit, you're going to learn
how to work with fancy data entry forms.
For now, though, we're going to learn how
to enhance our data sheet view.
Let's start by changing some of our widths of our fields.
Now, realize that this is not affecting
the underlying structure of your table.
This is the most confusing concept
of what we're about to do.
We're going to change some column widths and also
some field orders.
But if we go back into design mode right here,
the design will not change.
It's only the appearance of our data sheet
that's going to change.
If I take my mouse pointer between two fields,
notice how I get a line with a double-headed arrow through it.
If I click and drag over to the left, I'm narrowing this field.
I can do the same thing for my PA type.
PA initiated by, if I make it narrower,
notice that I'm cutting off the title up here.
I'm going to go ahead and make that one wider again.
PA schedule date isn't quite showing up,
so I'm going to go ahead and make it a little bit wider.
Same thing with PA approved by.
So now I have all of my field names showing.
I can do other things, such as change the order of my fields
in my view.
For example, I can click right here
to select PA schedule date.
Once I've selected that field, I can click and drag it over.
Notice the black line that's moving.
That black line is now popped in between PA ID and PA type.
When I let go, that field has now moved over
so that it's next to PA ID.
But if I come over here to the table structure,
notice that I've got PA ID still immediately
followed by PA type.
So once again, the field width in the underlying table
has not changed, nor has the field order.
I can do other things, such as change the font in my table.
To do that, if I come up to the format menu and pick Font,
I can, for example, pick another font.
I'm going to go ahead, scroll up in my list, and pick Arial.
And I can make it a little bit bigger, 10,
and then click on OK.
Notice that I've made my font larger.
Now, if I like this and I want to save this
for future views of the table, if I double click
to close down the table, I'm asked
if I want to save my layout changes.
These are the changes to the appearance in Datasheet View.
I'm going to go ahead and click on Yes.
And because I said yes, if I double click and open back up
my table, Access remembers my changes.
If I make this field very small and go ahead
and double click and say no, if I go back in,
the field is back how it was originally.
In this last section, you learned some tricks
for entering and editing your data.
You also learned how to change the appearance
of your data sheet.
These are some handy tips when you're working with Microsoft
Access tables.
Although you'll find as you work with Access,
you'll become more and more comfortable.
Sometimes you need help.
The Microsoft Access Help feature
is very powerful and very friendly.
Let's take a look at it.
Access has this great feature called Cue Cards.
Cue Cards walk you through doing some
of the commonly used tasks.
I'm going to come up to the Help menu
and come up and select Cue Cards.
Over on the right side of my screen, I get a menu.
Notice that I can get a quick overview,
build a database with tables, work with data.
Well, we just worked with data.
Let's see what Cue Cards have to say about working with data.
Come over to this button right here and click.
Now I get a submenu telling me about the different options
for entering and editing my data.
See how data sheets and forms work, opening a table,
viewing data in a data sheet.
Well, I want to learn more about adding, editing,
or deleting data.
I come over here and click.
Again, I get a submenu, add new data, edit data, undo changes,
delete data.
Let's take a look at delete data.
Notice I can delete data in a field
or delete one or more records.
Let's take a look at delete one or more records.
It says select and delete records or design a delete query.
Let's take a look at select and delete records.
Here I have step-by-step instructions
on how to select and delete records.
So select the records you want to delete
by clicking the record selector and dragging the pointer
over all the records you want.
So I get instructions step-by-step on what to do.
I can actually come over here with my cue cards up
on the screen and do exactly what I'm told.
I click and drag and notice that my directions are still
right here.
Now I go to next.
And again, it says to continue, click the cut button
on the toolbar.
That's another way to delete records.
We showed you how to delete them using your delete key.
Up here, it says to use the cut button.
It says OK to confirm your change.
So that's another way to delete records.
The important thing to understand here
is that I can actually perform the functions on my data
while looking at the cue cards.
If I click on next, I'm back here
and I can do something else, such as learn
about editing data.
Under edit data, I can see how to make changes
to individual records one at a time.
Again, I have step-by-step information.
I'm going to go ahead and close down my cue cards
because we're going to take a look at a different kind
of help.
I want to deselect these records.
I can click anywhere and deselect them.
Let's say that I want to know how to validate data.
I can come up to the Help menu and select Search.
At this point, I'm going to type in V for Validate,
and then an A, and then an L. And notice
I've got a bunch of entries for validation.
If I want to learn about validation rules in text,
I could double click right here, and I
get a bunch of different topics on validating rules and text.
If I want to see about making validation rules
and bring them up as cue cards, I can actually
double click right here, and I get cue cards
on validating my text.
At this point, I can follow along.
For example, it says from the Window menu,
choose the name of the table to make it the active window.
Click the Design View button on the toolbar.
Well, I already have my table up over here.
I can change to Design View.
There I am in Design View.
And now click on Next.
And again, I can walk through, for example,
Make Rules for Data.
Here I get a validation rule.
I can even see examples of validation rules.
Here's some examples.
So this is quite helpful.
If I want to see some more, I click on Next again.
I'm going to go ahead and close down my cue cards
and show you one more way to get help.
We did this one earlier, but let's review it.
If I click in Validation Rule, I can see the validation rule
on the toolbar.
Remember, we did this for field size.
But I'm clicking in Validation Rule
and now hitting my F1 function key,
indicating that I want some help on the validation rule.
I'm actually getting Validation Rule Validation Text Properties
right away.
I can scroll down and look at it.
In fact, if I want to print this information out,
I can come up to the File menu and select Print Topic.
Now I'm actually printing this out.
Now I'm ready to exit out of Help.
I can come up here to the Control icon and double click.
A lot of times when I'm working with Access,
I get frustrated, but I forget that Help
is there to give me some assistance.
Remember, whenever you're doing something,
if you get frustrated, you can hit your F1 key
or you can come up to the Help menu.
There's lots of ways to get help.
Remember to use it.
You'll be glad to hear that now we've
covered all of the hard stuff.
When I first learned Access, I found
that I could create forms, create reports, and so forth.
They were quite easy.
What I had trouble with were the basics
of creating the validation rules, creating my tables,
and so forth.
You've learned that now.
So now we're ready to have some fun.
In this section, you're going to learn
how to sort your data and also how to filter your data.
I'm going to come up here and double click
to close down my PA table.
I'm going to pull up my Customers table
by double clicking on it.
Now I'm in my Customer table.
Notice I'm in Order by my Customer ID.
That's because that's my key field.
I may want to be in order by something else.
For example, I may want to be in order by my city.
To do that, all I need to do is come over here,
click on my city, and come over to this button right here.
This sorts me in ascending order, A to Z. I click on it.
And now all of my records are in order by the city.
Now to make this easier to see, I'm going to scroll back over.
And I'm going to actually do something called
lock these two fields, Customer ID and Company Name.
So we can keep them up on the screen as we sort.
I take my mouse pointer, and I get a down arrow, just like this,
and click and drag to select the two fields, Customer ID
and Company Name.
At this point, I'm going to come up to my Format menu
and select Freeze Columns.
If I click anywhere to deselect those two columns,
notice that I have a line indicating that those two
columns are frozen.
What that means is I can now tab over and look at my city,
and my Customer ID and Company Name stay on the screen
at all times.
So now I'm in order by my city.
What if I want to be in order by something such as my country?
I click to select Country, and now I can select Sort Ascending.
And I'm now in order by my country.
If I want to be in order by my country in combination
with my city, but my country is my first level of sort,
first I'm going to select Country.
Once the column is selected, now I
click and drag to bring country to the left of city.
So I've got country and city.
At this point, to make sure that I'm in order by city
within a country, notice I'm not right now, I can select,
I'm going to click over here, come back and select
Country and City, and now pick Sort Ascending.
I'm now in order by country, and within a country,
I'm in order by city.
So for example, in Brazil, take a look
and notice that within Brazil, I'm in order by city.
If I want to go into descending order, for example,
I'm going to now pick company name and come over
and pick descending.
Notice what happens.
My company name is in descending order.
So I can sort in ascending or descending order
and by one field or by a combination of multiple fields.
I'm going to come back, pick customer ID,
and select ascending.
I can also find records that meet particular criteria.
Notice I'm still locked.
I'm going to come over here to my contact title,
and at this point, I'm going to search
for all of my presidents.
To do that, I can come up to this button right here,
which is my Find button.
I'm going to go ahead and type in president.
I'm searching downward from the current record.
I was on the top record, so I'm searching down
from the first record, and I'm searching only
within the current field.
I'm not matching my case, so it doesn't really
matter what case I type this in.
I'm going to go ahead and click on Find First.
Now, Find First actually does start at the top of the table
and find the very first one.
Doesn't look like anything happened.
If I take this dialog box by the title bar
and click and drag it down, I can now
see that I'm on the first president.
If I click on Find Next, it says Microsoft Access has reached
the end of the records.
Do I want to continue searching from the beginning?
I'm going to say no.
What this indicates to me, I'm also going to say OK,
what this indicates to me is that there's no more presidents
in this table.
Let's change this Find What to Owner.
I type in owner, and first I'm going to find first.
It took me to the first owner.
Now I click on Find Next.
This time I did find something, and I went to the next owner.
I can click on Find Next, Find Next, Find Next.
It doesn't look like it's on a record now.
Remember, all I need to do is move this dialog box
by the title bar, and I can find the next one.
If at some point I don't want to find anymore, I click on Close.
Now I'm going to go ahead and do Control Home
to get up to my first record and click back in Contact Title.
Let's try another find.
This time I want to find any records that have associate
in them.
I'm going to go ahead and type in associate.
But if I say Match Whole Field, I'm
not going to find anyone unless their entire title is associate.
So what I'm going to do is use this pull down right here
and pick any part of field.
I'm going to find first so that it starts
at the top of the table, and there's my sales associate.
Now if I say Find Next, I go to my next sales associate.
But remember, this could be any type of associate.
Find Next and so forth.
Close, and I just remain on that particular record.
What if I want to search and replace
as opposed to just search?
I'm going to go ahead, click on my first record again,
and I'm going to change owner to company owner.
To do that, I can't use this button
because this button does not allow me to search and replace.
It only allows me to find or to search.
What I can do is come up to my Edit menu and pick Replace.
What I'm going to search for is owner.
Since associate is all blue, all I need to do is start typing,
and I type in owner.
What I want to replace it with is company owner.
I'm replacing owner with company owner only in the current field,
but I want to match the whole field.
What that means is that I'm going to only replace the records
where the entire field is owner.
Otherwise, it would try to fill in company owner
even where owner is part of some other title.
I'm going to go ahead and select Replace All.
I now get a message saying that access
has reached the end of the records.
Do I want to continue searching from the beginning?
Already was at the beginning, so I'm
going to go ahead and say no.
Also, I'm told I won't be able to undo this replace.
Says to click OK to continue or cancel to undo.
I'm going to go ahead and say OK.
At this point, I'm going to click on Close,
and all of my owners are now company owners.
If I scroll down and look at the rest of my records,
you can see that all my owners are company owners.
To take a more conservative approach,
when you first start to do searches and replaces,
there's two things you can do.
One is you just find the first record
and replace one at a time rather than replace all.
And the other is you can always back up your database.
In case you make a mistake, you can always
go back to the original.
In fact, it's always a good idea to back up
your database frequently.
I'm going to come over to this button right here,
so I can go to my first record again.
And now I'm going to scroll over and look at my data.
Notice that I have a lot of different countries.
I just want to filter my data so that I'm looking at people
that are in the USA.
To do that, I can come up to this button right here.
It says Edit Filter Sort.
Click on it, and I get a grid where
I can indicate my criteria.
My criteria for my customer ID is not what I want.
I'm going to go ahead and click right here on this button
and delete so that customer ID goes away.
Instead, I'm going to scroll down over here
and grab the country.
One of my favorite ways to bring this down to the query grid
is click and drag down.
And there, I've brought country down to the filter grid.
Now I'm going to indicate my criteria.
I click in criteria and type in USA.
Now all I need to do is come up to this button right here,
which says Apply Filter Sort.
I click on it, and now I've applied my filter,
and I'm only looking at the records in the USA.
If I want to look at USA and also look at France,
I can come up here, Edit Filter Sort,
come over to this OR right here, and put in France.
I'm going to go ahead and come up here, Apply Filter Sort,
and let's scroll over and look at our country.
I've got France and USA, but they're not
in any particular order.
Let's go back and edit our filter sort.
And this time, we're going to go ahead and click right here
and pick that we want ascending order by country.
And also, I'm going to go ahead and grab my city.
And this time, rather than clicking and dragging it over,
I'm going to double click and come over and pick ascending
for my city as well.
I'm not going to indicate any criteria for my city.
At this point, I apply the filter or sort,
and I'm looking at just France and USA in alphabetical order
by country in combination with the city.
What if I want to look at all of the records again?
If I come up to this button right here,
it says Show All Records.
I click on it, and now I have all of my records back.
Notice that I'm looking at all different cities and countries.
So that's how I can apply a filter or a sort.
Before I exit out of this table, if I
want to get rid of my freeze right here,
I can come up to the Format menu and pick
Unfreeze All Columns.
Now notice that if I scroll over to the right,
I'm no longer viewing my customer ID and company name.
I'm now going to double click to close down this table,
and I'm asked if I want to save the layout changes I've made.
The layout changes I made were freezing,
and I'm freezing those columns.
I don't want to save anything, so I say no.
Let's go into our Orders Details table.
To do that, I double click.
Let's set up one more filter.
I come over here to my Edit Filter sort,
and I only want to look at the items where the unit price is
greater than 30.
I click over here, and at this point,
I'm going to double click to bring down Unit Price.
For the criteria, I type in greater than 30.
At this point, I can apply it.
And now I'm looking at only those items where
the unit price is greater than 30.
So you can actually use numeric operators.
Let's edit this one more time, because I
want to see the ones that are greater than 30
and are less than 50.
Notice I have some that are actually greater than 50.
Go in and Edit Filter Sort.
Come over and click in the criteria, and say,
and less than 50.
Now realize I'm not getting any that are equal to 30
or equal to 50.
Then I would need to say greater than equal to 30,
less than equal to 50.
I apply it, and now I'm no longer
seeing any records where the price is greater than 50.
We'll learn more about setting criteria in a few moments
when you learn about querying.
For now, I'm going to double click to close down this table.
And I wasn't asked any questions because I
didn't change any of the properties of the actual table.
You just learned how to better manipulate your table data.
You learned how to sort your data on one or multiple fields.
You also learned how to find and replace data,
and finally, how to apply filters so that you
can look at specific records.
In this section, you're going to learn how to create queries.
Queries are similar to filters, except that queries
are used with forms and reports to limit
what you're looking at.
Queries are very useful, and you'll
find that you're using them all the time.
When you want to create a new query,
you can use a query wizard, or you can
create the query from scratch.
To do either, you click on the Query tab,
and then click on New.
The query wizards are actually for some advanced queries.
So we're just going to create our query from scratch.
I click on New Query, and I get a query grid.
In front of it, I get the ability to add a table.
I need to add a table.
This is the table that I want to query on.
I'm going to go ahead and query on the customer table.
I click on Customers, and then click on Add.
I also could have double clicked on Customers.
At this point, unless I want to add more tables to my query,
I click on Close.
At this point, I get a listing of all of my fields
in the customer table.
This looks very similar to the grid we used on our filter,
and it actually is.
I'm going to go ahead and show you how similar it is.
What we're going to do is we're going to pick Country.
I'm going to double click to select Country,
and it moves down to my query grid.
At this point, I'm going to say that my criteria is
going to be USA or Canada or Mexico.
Now, notice that I could have put my ORs going down,
and then I don't need to type the word OR,
or I can put them across on the criteria using the word OR
in between.
So I've said USA or Canada or Mexico.
This is similar to our data validation
when we said Q or R or P. In other words,
the data validation items.
I also want to see my city field.
Remember, I can double click on it,
or I can click and drag it down to the grid.
Another way that I can select a field
is by clicking and using this pull down right over here.
And I can pick any field that I want, for example,
contact title.
I can also select multiple fields at a time
and bring them over to the query grid.
For example, I can pick address, and then holding down
my Control key, I can scroll down and pick phone and facts.
Notice that all three are selected.
That's because I held down my Control key
while I selected them.
Now, I can click and drag all of them over to the query grid
at one time.
Now, all three of them are there.
So I have country, city, contact title, address, phone,
and facts.
I'm going to go ahead and take a look
and see what I have right now.
All I need to do is come up here and click on Datasheet View.
And I've moved to Datasheet View,
and I'm viewing the country, city, title, address, phone,
and facts.
I want to size this a little bit bigger.
Remember, I can do that with the sizing handle
so that I'm viewing all of my data.
Notice that I'm only looking at Mexico, USA, and Canada.
I'm going to go back into Design View by clicking right here.
I also want to add my contact name.
I've scrolled up, and I have contact name.
To bring it to the grid and place it
right after the city, all I need to do is take it, click and drag,
and come over and drop it right on top of the title.
I've moved the title over and inserted contact name.
I've also decided that I don't want the address.
What I'm going to do is click right here and hit my Delete key.
And now I've removed the address.
So I have some fields selected.
I have some criteria selected.
And once again, I'm going to go ahead and go into Data Sheet
View.
Now I'm looking at country and city and contact name,
and I'm no longer looking at the address.
Let's go back to Design View, come over to Sort,
and indicate that I want the country in ascending order,
as well as the city in ascending order.
Again, let's go back to Data Sheet View.
And now I've actually ordered the records in country
in combination with city.
I can actually edit my data while I'm looking
at the results of my query.
By the way, the formal name for the results of a query
is a Dinoset.
So we'll be talking about editing our Dinoset.
When I edit the data looking at my Dinoset,
it actually edits the data in my original table.
Let's see how easy this is to do.
I'm going to come down here to Anna
and change Anna's name to include two N's.
I'm going to click between the N and the A
and type another N. So now I have ANNA.
I'm also going to come over here to Antonio
and change this to Anthony.
Now remember, in order to save my changes,
I need to move off of the record.
So I click on any other record, and now I've saved my changes.
So I have Anthony Marino and Anna Trujillo.
Let's go ahead and close down our query
and save it on the way out.
I'm going to go ahead, double click to close down my query,
and say, yes, I want to save it.
I'm going to call it North America because it includes
my North America records.
And then click on OK.
Let's come back, click on the Table tab,
and look at our customer table.
Notice that I have Anna over here with two N's,
and I also have Anthony.
I'm actually looking at my table rather than my Dinoset.
I'm going to close down my table,
come back over here to my Query tab,
and if I scroll down, there's North America.
I want to run this query.
I double click, and now I'm back in viewing
the results of my query.
Take a look.
There we have Anthony and Anna.
But now we're only looking at Canada, Mexico, and USA,
and I'm looking at my Dinoset, the results of my query
called North America.
There's an important concept to understand
when you're working with queries.
When you save a query, you are not
saving the actual Dinoset or result of the query.
You're only saving the query's definition.
You're saving the definition so that you
can run the query later, or you can edit the definition
at some later time.
I'm looking at my Dinoset or the result of my query.
If I want to go back and change something about the query,
I can go back into Design View right here.
I'm going to go ahead, click on Design View,
and I want to change the titles of my query.
So here I am in Design View.
Let's take one more look at Datasheet View,
and notice this says Contact Name and Contact Title.
I'm going to change these so that they say Name and Title.
Go back to Design View and come over here to Contact Name.
I'm going to click just to the left of See in Contact
and type in the word Name and then a colon.
Anything that precedes the colon is the title for the field,
and anything that follows the colon is the actual field name.
I'm going to do the same thing for Contact.
Just click to the left of the C and type in Title and a colon.
Let's do one more thing.
For Phone, I want it to say Phone Number.
I click to the left of the P and Phone,
type in Phone, a space, and a pound sign, and then a colon.
So it's going to say Phone Number.
Do the same thing for Facts.
Facts, Number, and a colon.
Let's go back into Datasheet View.
And now I'm looking at my data, my Dynaset,
and it says Name, Title, Phone Number, and Facts Number.
I'm going to double click to close down this query
and say, yes, I want to save the changes.
Once again, here's a listing of all of the queries
that exist in this particular database.
If I want to see what this customer list query is,
and this is one we didn't create,
I click on Customer List and I click on Design.
I immediately go into Design View,
and I can see that this query contains two fields, Company Name
and Customer ID, and that Company Name is ascending.
If I want to view the results, I can click on this button
right here, which is Datasheet View.
And there's also another button I can use
that I haven't shown you yet.
This one's called Run.
It just runs my query.
I'm going to go ahead and click on Run,
and notice that there is my listing of company names
and customer IDs.
Even though I've used Run, I'm still in my Datasheet View.
I can go back to Design View.
So what's the difference between this icon, Datasheet View,
and Run?
At this level, there's no difference.
But when you get into advanced action queries,
you're going to use Run instead of Datasheet View,
because action queries actually change your data.
I can close this down by double clicking.
Remember, if I double click on a query name,
I'm actually immediately running it.
If I single click and then click on Design,
I'm looking at the design of that query.
Let's run our North America query one more time
by double clicking on it.
And I can actually print this, just as in the first section,
you printed your data sheet.
To do that, if I want to print preview,
I come up to my Print Preview button,
and I'm in Print Preview mode.
I can now click and zoom in.
I can also send it to the printer with this button
right here.
Instead, I'm going to close out of Print Preview
with this button right here.
I'm back in my Dynaset.
Now I close my Dynaset by double clicking right here.
In this section, you learned how to work with accesses queries.
You learned how to select tables to include in your query,
as well as how to add fields to the query grid.
You learned how to specify criteria
so that you could look at particular records.
You also learned how to change the headers for your fields
on your query grid.
Finally, you learned how to print out
the results of your query.
Hopefully, you're becoming more comfortable
in working with access.
You'll see that it's very consistent.
For example, when we were working with tables,
we could switch from Datasheet View to Design View.
Hopefully, you've noticed that with queries,
it's the exact same thing.
You can easily switch from the Design View
to the Datasheet View.
My favorite part of access is forms.
This is because forms are an easy way to beautifully present
your data.
You're going to learn how to use the Forms Wizard.
You're going to learn how to view forms
and also modify the forms that you've
created using the Forms Wizard.
You're going to learn how to save and close forms,
and then how to reopen them.
You're also going to learn how to edit and add your data
while using forms.
I'm going to come over to the Form menu and select New.
At this point, I'm first asked to select
an existing table or query.
If I use this pulldown right over here,
I have a listing of all of my tables and my queries.
I'm going to come down here and find my Personnel Actions
Table.
I pick that table, and now I'm going
to use a Form Wizard to help to get me started with my form.
I click on Form Wizard, and up comes this dialog box
right here.
I can select a single column form with my field
names going down the side, a tabular form
with my field names going across the top in columns,
and I can also pick a graph, a main and sub form, or auto form.
I'm going to go ahead and pick single column.
I click on OK, and I get my next dialog box.
I'm asked which fields I want to include on my form.
I want to select all my fields.
Now realize I can pick one at a time and add it or remove it,
or I can click on this button right here
and add all of my fields at the same time.
I've now added all of my fields at the same time,
and now I'm going to go ahead and select Next.
I can pick from a variety of styles.
Notice that if you click on each one of these radio buttons
right here on the left side, you get a different look.
I like the embossed look, so I'm going
to go ahead and select Embossed and then click on Next.
I'm now asked for a title for my form.
I'm going to go ahead and type right over what's there,
PA data entry form.
I'm asked if I want to open the form with data in it
or immediately go to the form's design.
I'm going to go ahead and click on Finish.
I want to open the form with my data in it.
Here I am, and I'm looking at my resulting form.
I have my ID, my type, initiated by, scheduled date,
and so forth.
You're going to learn how to edit your data in here
in a little while.
For right now, let's see how we can modify this form.
So we're viewing the form right now in data entry mode.
I have familiar buttons up top.
I have a design view.
I have a special one, which is form view,
and I also have data sheet view.
So I can go to data sheet view.
I can go back to form view, and I can also go to design view.
So here I am in design view.
In design view, I can move things around and do things
to the form that was created through the form's wizard.
This is my form design window.
Right now, I have a toolbox, and this
has tools that I can use in modifying my form.
I also have my familiar toolbar and my menu bar.
If you haven't noticed, my toolbar and menu bar
often change depending on what feature of access I'm in.
I want to move some of my fields around.
In order to do that, I first need
to learn how to select them.
I'm going to select this type field.
The way to do it is to click anywhere
on either the title or the actual field contents over here.
I'm going to click, and notice that I get some handles
around the field.
If I move my mouse around and I get a hand,
I'm going to want to move until you get a hand,
I can click and drag and move this field
to somewhere else on the form.
So I just move this field over to here.
I now can pick a couple of the fields
and do something to them at the same time.
For example, if I want to pick initiated by, scheduled date,
approved by, and effective date all at the same time,
and move them up as a group, one way I can do that
is by holding down my Shift key as I select each one of them.
I've selected them, and now they're all selected.
I make sure that I get a hand, click and drag up,
and now I've moved them up as a group.
If I want to take the rating and the amount,
as well as the comments, and move them up,
another way I can select multiple fields
is by lassoing them.
I'm going to come over to a blank area on my form
over here and click and drag to surround the fields
that I want to select.
I've surrounded them, and now they're all selected.
I can tell they're selected because they all have handles.
I click and drag up, and I've moved them up as a group.
And now I can scroll up and take a look at my form again.
If I want to move the field information separately
from the field title, I have to do something
a little bit special.
I'm going to go ahead and pick this comments field.
First, I'm going to deselect everything
by clicking over on a blank area on the form.
I'm going to scroll down so I get a better look at comments.
And now click on the actual field information over here.
At this point, rather than getting
a hand which would move the field and the title,
I'm going to come up to this corner
to get a hand with a finger pointing upward.
This allows me to move the field contents separately
from the field name.
Watch.
I click and drag, and my field information
is moving without the field name.
I'm going to scroll back up to the top of my form.
I can also affect the alignment of my controls
in relation to one another.
I'm going to click and drag to surround the titles right here.
Now notice, because I surrounded the titles,
I have more handles around the title
than around the attached field.
I'm going to align these to the left side
as opposed to the right side.
Now it's not this button right here.
Watch what happens.
If I pick left over here, all I'm doing
is changing the alignment within the little box.
I'm going to go back to right.
Instead, what I want to do is come up to the Format menu
and Align, and then pick left.
Notice that now all of these titles
are aligned on the left side instead of on the right,
but they're not perfect.
Now I can come over and within each of their boxes,
send them over to the left side so that they are indeed
perfectly aligned.
So these controls right here on the toolbar
are within the box, where the Format menu and Align
is how the boxes are aligned one to the next.
So I'm going to pick right and send them back over
to the right.
At this point, I may want to take a look
at what my form looks like.
I can come back up to this button right here,
go into Form View, and it's looking pretty good.
I've now got my entries.
I look at it in Form View, and I see
I still have a minor problem.
I have to send the titles back over to the right
within their boxes.
I can easily go back into Design View.
I still have them all selected, and then I click on Right.
Now let's take a look.
Back to Form View again, and there I am.
And all my entries are aligned, and everything
looks pretty good.
There's a lot more things that you can do in Design View,
and they're taught in the next tape.
I'm going to click on this button right here
and go back to Design View, and at this point,
try to close my form down.
When I double click to close it down,
I'm asked if I want to save my changes.
I'm going to say yes, and as the name,
type in Personal Action Data Entry Form.
I click on OK, and I've saved my form.
Now that I've saved it and closed it,
I can go ahead and open it back up again.
I find it and double click on it if I simply want to open it.
I double click, and now I'm back and I've opened it.
Let's see how we can use our data entry form to view
our data, edit it, and add records to our table.
To view it, I can simply use these buttons right here
to scroll through a record at a time.
I can go to the last record and the first record,
and I can also tab from field to field
and Shift-Tab to go back.
I can move to the next record using this button,
and at any time, I can edit.
For example, if this record number 2, PAID 2,
was initiated by number 3, all I need to do
is select that type of 3 and tab down.
All of my validation rules still apply.
For example, if I try to change the schedule date to 5-1 of 99
and hit my Tab key, I get a message
saying the scheduled date cannot be more than 10 years ago
or more than one year from now.
So that's the same validation rule that I set in my table.
Once again, this reinforces how important
it is to set everything up at the table level.
I'm going to click it OK, backspace once,
and change this to 91.
Now I can tab out without any problem.
I'm going to go to my last record
and then click one more time on this right arrow right here.
I'm now on a brand new record.
I'm going to put in a PAID of 4.
Come over to the type, which is defaulting to Q,
and make it an S. I hit my Tab key.
That did pass the validation, so now I'm in initiated by.
I type a number 5 and tab.
My scheduled date is going to be 5-16-94.
I'm going to leave it, and approved by is going to be 7.
Come down to my rating and make it 6 and the amount 3,000.
For comments, let's see what happens.
I am typing some comments.
Let's see what happens when I get to the end of a line.
And you can see what happened.
I word wrapped.
But let's see what happens when I get to the end of a box.
See how I was able to scroll down?
So I can come up and down and look at my data.
I can go back a record and come back and look at this record.
And there I am.
If I decide I want to make a change at this point
to the actual form, for example, move my comments up
a little bit, I go into Design View, scroll down on my form,
grab the comments.
I'm going to go ahead and move these independently.
So first, I've got a finger, and I click and drag up.
And I was able to move the title without the field.
Now I come down to Comments, get a finger, and click and drag up.
And now I'm able to move the field without the title.
Scroll back up and go into Form View.
And now my comments are a little higher on the screen.
I can double click to close down the form and say, yes,
I want to save the changes.
And here I am, and I'm back at my database window.
You just entered the exciting world of Access Forms.
You used a Forms Wizard to create a data entry form.
You learned how to view the form and how
to modify the form that the wizard had created.
You also learned how to add data, edit data, and view data
through a form.
Come on.
Now that you've got forms under your belt,
you should find reports quite easy.
We're going to start by learning the differences
and similarities between forms and reports.
You're going to learn the different types of Access
Reports and how to use the Report Wizard.
You're going to learn about groups and totals
and how to modify a report created with the Report Wizard.
You're also going to learn how to save your reports,
reopen them, and print them out.
Let's go back into our PA data entry form.
I'm going to double click on it so that I open it up.
What I didn't show you before is you can actually print out
this data entry form.
I'm going to go into Print Preview right here.
I click on Print Preview.
And if I zoom in, I can see that I'd actually
print out my data entry form.
This is what would actually print out on your printer.
In fact, let's try it.
Come up to this button right here, click on it,
and you're sending the form out to the printer
as soon as you click on OK.
Once you've printed your form and taken a look at it,
go ahead and click on this button right here,
and you'll close the window.
I'm back viewing my data entry form.
I'm back viewing my data entry form.
I'm going to double click to close it down.
So if you can print out a data entry form,
what's the difference between a data entry form and a report?
The first difference is that you can do data entry into a form,
and you can't do data entry into a report.
Also, when you printed out your data entry form,
you probably noticed that the printed copy
looked very similar to the form that you saw on the screen.
With reports, you can pick from various formats
that have nothing to do with the way
that the data appeared when you were entering it.
So let's learn how to create reports.
I'm going to start by clicking on the Report button.
At this point, I want to create a new report,
so I'm going to click on New.
This is one way that I can create a new report.
I'm going to show you another way, so let's cancel out.
I'm going to come back up to my table list up here,
select my table called Customers, which is already
selected for me.
I'm going to come up and take a look at these tools right here.
Notice that this first tool creates a new query.
The second one creates a new data entry form,
and the third one creates a new report.
So I can actually bypass a step because I picked my table,
and I can go directly in and pick that I want a new report.
Remember, you can do this for forms and queries as well.
I pick New Report, and already my customer table is selected.
I'm going to use a Report Wizard.
I pick Report Wizard, and now I get
to pick what type of report I want.
Here's the list.
Single column is going to have my field names going
across the top in columns.
Groups and totals give me subtotals and grand totals
on my report.
Mailing labels allow me to create mailing labels.
Summary, just look at summary data,
and then I get tabular that's like a spreadsheet,
and some other options such as auto report and mail merge.
We're going to go ahead and for the moment,
select single column and click on OK.
I'm going to go ahead and pick the fields that I
want included on my report.
Here's a model of what it's going to look like.
I'm going to pick customer ID and company name
and contact name.
And the only other information that I need
is the phone and the facts.
So these are the fields that I want included on my report.
I'm now going to select Next.
Now I'm asked what field I want to sort by.
I want to sort by the company name.
So I'm going to double click to select the company name
and select Next.
Also, I'm asked what style I want for my report, portrait
or landscape.
Take a look, landscape goes across, portrait up and down.
I'm asked about my line spacing and also
what style executive, presentation, or ledger.
I'm going to go ahead and pick executive and portrait
and say Next.
Now I'm asked for a title for my report.
I'm going to type in customer listing.
I'm asked if I want to print each record on a new page,
if I want to show the title on each page,
and if I want to open cue cards to help with my report.
I want to show the title on each page,
and I want to go immediately to view my report.
So I'm going to click on Finish.
Here's the report that I just created.
I've got my customer ID, my company name, contact name,
phone, and fax.
I can scroll through the pages by clicking right here.
I can go to the very last page by clicking right here.
The very first page by clicking right here.
Notice I'm on page one, and now I'm on page 23.
And I can go back 22 and so forth.
I can zoom out to get an overall view.
I'm going to go ahead and double click to close this down.
And I'm asked if I want to save the changes.
I'm going to go ahead and say Yes.
And I'm going to call this report customer listing.
So now I'm actually naming the report.
Click on OK, and I've closed it.
If I come over to report, there is my customer listing.
Before we rerun this report or take another look at it,
let's see how we can create a Groups and Totals report.
Remember, Groups and Totals reports
allow you to easily create subtotals and grand totals
on your reports.
You'll be very surprised how easy this is to do.
I have my report tab already selected.
I'm going to go ahead and select New this time.
I need to pick a table or a query.
I'm going to use my pulldown.
And I'm going to pick from my list here,
I'm going to pick Orders, Detail.
Got Order Details selected.
And I'm going to select Report Wizard.
At this point, I want a Groups and Total report.
I click on Groups and Totals and click on OK.
Here's the fields in that particular table.
I want all of them.
So I'm going to click on this button right here.
Now I click on Next.
Now I'm asked what I want a group by.
This is very important.
What do I want my subtotals by?
I want my subtotal by my order ID.
I've got order ID selected.
I'm going to click on this button right here
and then click on Next.
I'm asked how do I want to group data within each field?
If I use this pulldown, notice I can group things in tens,
fifties, 100s, and so forth.
I'm going to pick Normal so that it groups
on each individual order ID.
I click on Next.
And now I'm asked what do I want to sort by?
What order do I want to be by?
I'm going to pick Product ID.
So within an order ID, I want to be in order by Product ID.
Now I click on Next.
Once again, I'm asked what style I want for my report.
I'm going to leave it at Executive
and put this one in Landscape.
That way I have more room for my fields going across.
I click on Next.
And I'm asked for a title.
I'm going to go ahead and leave it at Order Details.
Notice I have this little option right here,
which allows me to calculate percentages of the total.
This is a new powerful feature of Access To.
I want to take a look at my report with data in it.
And I do want to calculate my percentages.
I'm going to go ahead and click on Finish.
Let's see what we get.
If I scroll over and take a look, I've got order ID, 10,000.
I've got a product ID, unit price, quantity,
and I've got a percentage.
Zoom out and take a look.
And notice that if I zoom in to this particular order right
here, this particular order, 10,001,
has several different items on it, product IDs.
I've got unit prices, quantities, and so forth.
I do have some problems.
I don't think that I really want to add up my product ID,
nor do I want a percentage for my product ID.
So I'm going to make some changes
by going into the Design View of this particular report.
I'm going to click on this button right here, which
closes the window.
And I immediately go into Design View for the report.
Once again, I have a toolbox.
And I also have a report design window.
This should look pretty similar to when we were
working with our form design.
So now I can modify my report.
First of all, I'm going to come over and look
at the sections of my report.
I have a header for the report, a header for each page,
a header for each order ID.
Then I have a detail section.
Then I have an order ID footer.
And then I have a page footer, and finally a report footer.
What I want to get rid of first is I don't want a total
for my product ID.
So I'm going to click on it and hit Delete.
Now I've gotten rid of the total.
I also have my percentage down here.
Now what I'm going to do is get rid of this line
by clicking on it, hold down my Shift key,
click on this item right here.
So I've selected it, scroll down.
And with my Shift key still held down,
I'm going to pick each of those two lines.
There's actually two lines there, and this right here.
I've now selected all of the totaling for my product ID,
both at an order ID footer level and also a report footer level.
I hit Delete.
And now I've removed my product ID.
I do want my unit price, but for unit price,
I only want to sum it.
I don't want a percentage.
I'm going to go ahead and click on this right here
and hit Delete, scroll down.
And I do want to sum my unit price, so I'm OK over there.
For quantity, I do want to sum my quantity.
For quantity, I do want to sum my quantity,
and I do want my percent of total.
For discount, I also want to sum it,
and I don't want my percent of total.
I click on it and hit Delete.
Let's go in to look at our report
and see where we are right now.
I'm going to come up to this button right here, which
is Print Preview.
And let's take a look and see where we are.
I take a look at this, I notice that my order ID numbers
are pretty far from the title.
And also my product ID numbers are
pretty far from their title.
Also, same problem with unit price and quantity and so forth.
So I just want to make some cosmetic changes
to this report.
Once again, I'm totaling my unit price,
and I'm also totaling my quantity.
And I've got a percentage that each of these
is of the grand total for the report.
Let's go back, click on this Close button right here,
and I'm back in my design view again.
My problem is that order ID, if I look right here,
extends far beyond the title.
I'm going to go ahead, get a mouse pointer that
looks like this, a double headed arrow,
with that control selected.
So I don't want to move it with the hand this time,
I want to size it.
And I click and drag to size it.
I'm going to do the same thing with my product ID.
Now the important thing here is you
don't want to size it so small that your data doesn't fit.
I'm going to do the same thing with the price.
You can find when you view your report,
you'll see if you have a problem.
I'm going to size my sum as well.
I click on the sum and click and drag to size it.
Now that I've sized the sum that's in the order ID footer,
I'm going to scroll down and grab these guys over here.
Don't want to forget about them.
I select it and size it, select and size,
and select this one and size it as well.
Let's scroll back up.
I want to move my quantity over.
I click to select quantity, hold down my Shift key, click again.
I'm going to select each of the controls,
making sure that I don't miss any.
Now that I've done that, I'm going
to click and drag the hand them over as a group,
move them over, and now they're moved over on my report.
Let's go ahead and see what we've got right now.
I click on Preview.
And if we take a look, things are looking a lot better.
I still want to size my discount, so let's go back one more time
and pick the discount.
I'm going to pick the field right here, size it,
and do the same thing with the sum and my line.
And line's down here.
And let's run it.
Now that I like how it looks, I'm
going to go ahead and double click on this icon right here
and say, yes, I want to save the changes.
For the name of my report, I'm going
to go ahead and type in price and cost information.
Click on OK, and now I've saved my report.
If I want to open up an existing report,
you need to find it on the list.
There it is right there.
Now, if I want to run it, all I need to do
is double click on it or click on Preview.
If I want to go directly into Design View,
I can click on this button right here.
I'm going to go into Design, and you
can see I'm back in Design View.
Double click to close it down, and this time,
I'm going to double click on it instead of clicking on Design.
And I actually just run it.
Well, now that I really like my report,
I want to print out a final copy.
All I need to do is come up here to this button right here,
which is Print, and click on it.
I want to print the entire report, so I click on OK.
And this is going to go ahead and go out to the printer.
I can click on Close, and now I'm back at my database window.
So remember, we've learned how to create tables, queries,
forms, and reports.
Our tables are where we initially
define what our data is going to look like.
We can go into Design View, and we can also go into Table View.
By close down this table, I can also pull up
a listing of my queries.
I've got queries listed.
If I want to run one, I can double click on it.
If I want to go into its Design Mode, I click on Design,
take a look at its design, and then I
can go over to Data Sheet View and take a look at my Dynaset,
my query result. I'm going to close this down.
We've also created data entry forms.
If I want to go into this data entry form in Design Mode,
I can click on Design and make changes to it.
I can go right from here into Data Sheet View
and take a look at Data Sheet View,
or I can click on here and go into Form View.
Close my form down.
We've also created reports.
I've created my price and cost information.
I can go into Design View and modify its design
or toggle over to Print Preview and take
a look at what it will look like when it's printed.
And then double click and close it down.
I'm done working with Access for right now,
so I want to close Access down.
I can close my database first, but there's really no need to.
I'm going to come up here to the Control icon for Microsoft
Access and double click.
I've closed down Access, and Access
has closed down my database for me.
I'm back here at my Microsoft Office Program group.
In this section, you learned how to create Access reports.
You saw how similar Access reports and forms
are to one another.
Also included on this tape, you got an introduction
to Microsoft Access.
You learned how to create your own databases and tables
and how to change the properties of your fields.
You learned how to get help when you need it
and also how to query your data so you
can look at exactly what you want to look at.
On the next tape, you'll learn how
to work with advanced queries.
There are several different types
of advanced queries in Access.
You'll learn how to include calculations in your queries,
how to use multi-table queries, how
to create parameter queries, and how to create action queries.
You'll also learn how to work with advanced forms and reports
so that you'll get some great and exciting advanced and report
techniques.
We've enjoyed working with you and hope to work with you
again in the future.
Sure.
Anyway, undermine those long-term goals.
The data that we use to formulate our action plan,
recommendations for changing the organization,
came from three sources.
Work satisfaction questionnaires,
that was an anonymous response to a series of questions.
Personal interviews, which addressed
tasks and people relationships in the organization.
And naturalistic observation, which
was merely observing the organization at work
unobtrusively to see how people operated
in their natural environment.
As planning progressed, the design team
began to consider the specific changes it would recommend.
During this phase, communications
with the rest of the purchasing group was given high priority.
The way we initially designed the process here
was to ensure that we had a number of different feedback
mechanisms available to the organization
so that they could participate in the study
and help direct the study toward its ultimate end.
We took extreme care in making sure
that everyone had the opportunity to tell us
when they felt something was wrong
or we were not getting certain messages.
And we established a number of different methods
for people to do that.
Throughout the planning phase, the design team
was encouraged in its efforts by the knowledge
that their purchasing group was a healthy organization,
doing a good job already, but anxious to improve performance
if the team could find a way.
Security, I think, came from the knowledge
that when this effort was over and we were more productive,
we would be more valuable to the corporation.
And I think that went a long way toward reducing the fear
that there would be forced attrition and the elimination
of certain jobs.
Not all the team's recommendations
were related to technology.
Besides terminals for all users and information management
software, the team also recommended an improved system
for rewarding performance and additional emphasis
on career path planning.
We recently made our series of recommendations
to our vice president's staff.
The recommendations were met with a good reception.
We are presently at the implementation stage.
And my new role is to make sure that all of the detail that
is necessary to get the recommendations in place
is accomplished.
The best plan system is of little value,
if not carefully installed and tuned
to the needs of its users.
At Pfizer Incorporated, an electronic mail system
was piloted with users from a variety of corporate roles,
users with an established need to communicate
with each other.
The object of the installation was
to gain experience with the technology
and to assess the potential for company-wide gains
in productivity.
Planners studied the pilot from several points of view.
How did users' corporate roles affect
their use of electronic mail?
How did users prefer to access the system?
In what ways was their previous experience with technology
a factor?
How did electronic mail contribute
to their productivity?
I think the key to any pilot implementation is diversity.
It is important because when it comes time to expand a pilot,
you want to be able to address all the issues,
all the different factions that may occur
within any large organization.
In Pfizer's experience, support was a critical factor
in the successful implementation of the pilot.
You have to provide the necessary educational and
training support to make sure that when the users come
on board, they can do so effectively.
The secondary in terms of support is implementation.
You have to, again, provide the necessary resources
to ensure that the pilot is implemented successfully.
And by that, I mean the hardware and the software.
The third is out of help.
You have to be able to provide some means by which users
can seek aid when they have a problem.
Support is a major consideration in the implementation
of office systems.
And success or failure may well turn on this critical issue.
Nervous, me?
No.
I've learned a lot about computers in the past two
years, from word processing to records management.
Besides, computer graphics sounds like fun.
Now, if only they'd hold the training sessions by the pool.
Right.
Our vice president's big on efficiency.
He's always pushing the latest in office technology.
All the managers have terminals.
Well, for electronic mail, reports, spreadsheet analysis,
that sort of thing.
Although come to think of it, I hardly ever see my boss use his.
Say, did I tell you I got a new car last week?
Oh, it's wonderful.
A Jaguar.
No.
You know, actually, I haven't seen those projections yet.
You know, these long reports can take forever
when the system is busy.
Bill Hendricks had the numbers nearly done last I knew.
Let's check and see what he has ready for us.
Right.
Tell you what, you try Bill.
I'll get some coffee.
None for me.
Thanks.
Boy, if those projections are as bad as I think they are,
I'm in trouble.
Ah, we're in luck.
Bill's at his terminal.
Yeah, of course Bill's at his terminal.
That's all he ever does.
Hmm.
He says he sent you mail.
I don't know.
You know, he's been on the phone for a while.
He's been on the phone for a while.
He's been on the phone for a while.
He's been on the phone for a while.
He says he sent you mail, summaries of those projections
two days ago.
Yeah, well, I've been pretty busy.
There are only so many hours in the day.
Christine, will you check my mail?
Make a couple of copies of that summary from Bill Hendricks.
Right away?
No problem.
Why do I have to make copies?
He can read that report right off his terminal.
Two days?
What's the point of electronic mail
if he's not going to use it?
He has 207 messages waiting.
206 from Bill Hendricks, I'll bet.
Well, this is discouraging, to say the least.
Still, we're going to have to salvage this quarter somehow.
Uh, all right.
Check Bill's directory for the full report
and copy it into your files.
We'll want to see all the details.
And let's look at the projections of the other departments.
Might as well see how we compare.
Somebody hasn't done his homework.
I wonder how many of our other managers
don't use their terminals.
All right, hit the Return key, and let's
get back to the main menu.
The system will prompt us from there.
Somebody hasn't done his homework.
Clearly somebody hasn't done his homework.
Things are going rather badly in this automated office.
But who hasn't done his homework?
I think the senior manager meant the guy who's in trouble
hasn't done his homework, this man who's
late in reading his mail.
That may not be the whole answer.
It may be that the people who should
be supporting this manager haven't done their homework.
And in fact, it may be difficult for him to read his mail.
The system may be poorly designed.
It may be any number of reasons.
My feeling is that this scenario points up
the critical need for user support
after installation and implementation of the system.
You have to keep coming back to users,
asking them how they're getting on with the system,
finding out what they know how to do
and what they don't know how to do,
and helping optimize that system for their own particular
requirements.
What I would like us to remember from this example
is that the real purpose of office automation,
or I'd rather call it office technology,
is to make people more valuable.
Not necessarily more efficient, but more valuable
to the organization they work for.
And clearly, it didn't happen for this guy.
And in fact, he is making the organization less effective
because he's not working with the system
that everyone else is using.
So something is wrong here.
If he's not being made more valuable,
and if in fact he's hurting the organization's ability
to be effective.
I think the scenario, though, also
points out one of the key fears that people have
about office automation tools, which is, what if our managers
won't use it?
How do we know that these people will actually
sit down at a keyboard?
What if our multimillion dollar investment is just
blown because the right people won't take advantage
of the system?
Yes, it's a big problem.
Any new system has to work in concert with the old one
so that the people who won't use it
can still communicate with the people who are using it.
Yeah, I think it's an important point that people should
be given options that managers, for example,
such as the one in this scenario,
might prefer to work through their secretaries
to get their mail and have the secretary sort the mail,
even if it comes in electronically,
print it out and put it on their desk in a prioritized order
in the way that they're used to working.
I think over time, that kind of manager
will become involved in using the system because
of some of the benefits that we've talked about before.
The fact that if they're not in the loop,
if they don't get the information about the meeting
that's to be held in the next half hour,
they'll find themselves in an embarrassing situation.
I think I agree with John that the people do
resist bad technology.
I don't think that we should think of this guy
as resisting change as such.
Maybe he's resisting something else.
But if there's something valuable enough
inside the keyboard, most managers
will go ahead and take the trouble.
We've tried to understand who uses these systems directly
and who uses it through their secretaries.
It's people who see a value in the system, who
will answer the question, yes, I think it's valuable.
And that's the secret to a successful implementation
is to show people how they can benefit personally
from using the system.
The role of office automation is to give users access
to information, to give them tools
to manipulate that information, and to teach them
how to use those tools effectively.
And I think it's that last part that we often
forget in designing and implementing office system
strategy.
The best evidence we have on productivity
that is controlled before and after studies
suggests that we're looking at 25% to 50% improvements
in people's performance ability.
That means that we're freeing up anywhere from a day a week
to half a week of people's time.
And I think the real challenge for management
is they look at this possibility.
It's not going to be, boy, can I use this to save costs?
But how can I use the extra capacity, the extra talent
that I'm creating for my people with this technology?
I don't know of anyone who's really thought that one through,
but I think that's the next thing we face.
Office automation, improving the way we do business,
improving the business we do.
Implementation, making office technology work.
Success depends on the involvement of users,
thorough planning, careful installation,
and continuous support.
Office automation means technology
for improving your business, but there's a lot more to it
than just the technology.
Implementation starts with understanding your business.
That way you know what you're automating.
Everything has to be integrated.
Technology, management, the requirements of the business.
Implementing office automation means more than just
installing technology.
It's using all our resources to make business and the people
who do business more productive.
Hey, Dad, is there a world magazine come today?
Yeah, this is it.
Look, a story on whales and a super-sized pull-out
from my room.
Sorry, Dad, but you know the rules.
National Geographic World is just for kids.
You know, kids really do like reading
National Geographic World, and you'll love giving it.
World is published especially for kids
eight and older by the world-famous National
Geographic Society.
Every month brings page after page
of fascinating articles and pictures
in National Geographic color on animals,
outdoor adventure, and science, faraway places, sports, hobbies,
and crafts.
World teaches children about so many things.
Well, at the same time, it encourages them to read more
and stimulates their interest in the world around them.
You'll be amazed at the low price for 12 monthly issues,
certainly a lot less than what you'd pay for a single toy
these days.
And with a new issue arriving every month,
you can be sure that a child will never
grow tired of this gift.
And it's so easy to order, no shopping, no wrapping,
no shipping.
Just call now with that special child's name and address.
Call 1-800-453-3300.
You cannot buy World at a newsstand or a bookstore.
You can get it only through the mail
by calling this number now.
Each issue has puzzles to solve, games to play with friends,
stories about kids doing special things,
and frequent supersized pull-outs to put on the wall.
Children even have the opportunity
to compete for a $5,000 scholarship.
And when you call with your order,
we'll also send a personalized gift card from you.
So call or write today with your young reader's name and address
and your name for the gift card.
Call 1-800-453-3300.
That's 1-800-453-3300.
12 colorful issues and a card from you for only $10.95.
This gift lasts a year.
The benefits last a lifetime.
Order it now for the children in your life.
They'll love it.
There you have it.
In this lesson, we'll discuss Phase IV, Detailed Design, Programming, and Procedures Development.
Throughout much of this phase, the computer systems analyst and the business systems analyst
work in parallel on separate tasks.
Each takes responsibility for preparing four deliverables in the phase.
The computer systems analyst prepares the automated system ready for acceptance testing,
the updated automated system design, the conversion package, and the data center procedures guide.
On the other hand, the business systems analyst prepares the user procedures guide, the updated
functional specifications, the acceptance test and implementation plan, and the phase
summary for users.
To begin our work on this phase, Barbara and I met to review the deliverables from Phase
III-A and III-B and to go over the work plan.
We each took specific responsibilities in this phase, but our efforts had to be coordinated,
so we agreed to continue our weekly meetings.
I began my work by planning the preparation of the user procedures guide.
At the same time, I began working on the first deliverable.
The first deliverable of this phase is the automated system ready for acceptance testing.
It is prepared by the computer systems analyst and the programming team.
Work on this deliverable is divided into two major efforts, programming and testing.
The programming effort includes designing the program in detail, writing or updating
the program specifications, and coding the programs.
The testing effort includes several levels of testing that prepare the new system for
acceptance testing.
To begin the programming effort, I met with the programming team to discuss the functional
specs and the automated system design.
These documents were the baseline for all our programming efforts.
The system design outlined the addition to the system and divided it into programs, but
there was still plenty of designing, detailed designing for the programmers to do before
any coding could begin.
The programmer for each program had to write detailed specifications which clearly identified
the file and data elements that would be used and how they would be used.
They were to clearly indicate the further division of programs into processing modules.
After the detailed program and module specifications were completed, they were reviewed by me and
the other members of the programming team.
This review took the form of a walk through in which the programmer who wrote the spec
led the discussion.
This peer review, as it is called, has several benefits.
It keeps all members of the programming team informed about the detailed design of other
programs in the system.
It helps in identifying any design inconsistencies, and it provides a forum for sharing techniques.
After the detailed programming specifications are approved, the programmer finalizes the
unit test plan.
Shortly after completing all these preliminary tasks is the programmer ready to actually
code the program, which is the last step in the programming effort.
Then the testing effort begins.
First is the desk check, an informal test that can include a review of the code by peers
and supervisors.
Once the program is compiled, the more formal testing begins by way of the test plans written
in phase 3B.
The more formal testing starts with the unit test, which is designed to test an individual
program or unit in an isolated environment before combining it with other programs.
Next, integration testing begins as groups of related programs become available.
This test is run to find out if the group of programs works together as designed.
The last test in phase 4 is the system test.
After all groups of programs had passed integration testing, the team was ready to start system
testing.
I reviewed the system test plan and made a few minor changes based on the results of
the integration tests.
Then we finished preparing the test data to verify that I could test the entire new system
against the functional specs.
That was the bottom line for the system test.
Did the new system meet the functional specs?
After a successful system test, I met with Barbara and the rest of the programming team.
We reviewed the results of the testing to determine if they differed significantly from
the acceptance criteria established to test the functional specs.
This time there were no significant inconsistencies, but there were a few minor inconsistencies.
When there are only minor changes in the specifications, the two optional deliverables of this phase,
the updated automated system design and the updated functional specifications, simply
become annotations in the original specifications.
So far we've discussed three deliverables of this phase.
The automated system ready for acceptance testing, the updated functional specifications
and the updated automated system design.
Through the rest of phase 4, the computer systems analyst and the business systems analyst
continue to work in parallel on separate deliverables.
First let's look at the deliverables prepared by the computer systems analyst beginning
with the conversion package.
The conversion package contains programs, test data and procedures used when converting
from the present system to the new system.
The conversion package can be a separate deliverable or if it's a relatively small effort, it can
simply be a part of the total automated system.
You may remember that back in phase 3, Barbara and I decided the conversion package should
be a separate deliverable.
In fact, it was large enough to split off into a separate project.
This didn't mean that the programmers and I had no part in the development of the conversion
package.
We worked very hard with the conversion project team to clearly identify the conversion output
requirements.
The final deliverable prepared by the computer systems analyst is the data center procedures
guide.
This contains information that enables the data center staff to fulfill their responsibilities
for production jobs.
The data center procedures guide has three sections.
First is the system overview.
This explains the business purpose of the new system.
It also contains system diagrams and an index of the programs and files used.
The second section is the data center and data communications documentation.
This is organized according to the production jobs that make up the new system.
It describes each of the jobs in detail, whether it's interactive or batch and how to run it,
how to monitor its operation, how to restart it, how to verify its successful completion
and so on.
The complete contents of this section are listed in your standards reference manual,
which you'll look at later.
The last section of the data center procedures guide is, of course, the approval page.
This is signed by the data center representative after the guide has been reviewed and approved.
Now that we've discussed the deliverables prepared by the computer systems analyst,
let's take a look at the deliverables prepared by the business systems analyst.
They are the user procedures guide, the acceptance test and implementation plan, and the phase
summary for users.
The user procedures guide is written for the user staff who will be working with the new
system.
This means it must be written in the language of the user with no computer jargon.
The user procedures guide has four sections.
The system overview section contains a synopsis of the purpose of the system, the effects
of the system, and the specific responsibilities of the users.
It might be appropriate to extract paragraphs or sections of the functional specifications
for some parts of the system overview.
The second section of the user procedures guide is the discussion of the administrative
procedures.
This provides detailed information about the procedures used to interface with the automated
system, information such as when to run it, how to initiate it, how to prepare and enter
data, how to handle error messages, how to verify the output, and so on.
The third section of the user procedures guide is the glossary of reference materials.
This contains samples of information such as data input formats, screen displays, and
outputs generated by the system.
The final section of the user procedures guide is the ongoing training tools.
These are used for the training that takes place even after the new system has been implemented.
No matter how good the new system is, it will work well only if the people using it every
day are trained and understand the importance of ongoing training.
After I finished a draft of the user procedures guide, I met with Harvey to get his initial
comments before the formal review.
This looks like something my people can use.
No computer file names or program names that were always there before.
We don't have to be computer experts to understand this documentation.
Oh thanks Harvey, that's really good news.
But while you're here Barbara, I've been meaning to talk to you about a couple of changes I'd
like to see.
It's not unusual for the user to ask for a few changes at this phase of the project.
If this happens, the business systems analyst must be able to explain the impact of the
changes to the user.
Harvey wanted to add another bank to the system immediately and he wanted some new information
printed on the employee pay stub indicating whether the employee had taken a personal
holiday.
I knew that adding another bank wouldn't be difficult because the file that held the bank
number was designed to be updated by systems personnel.
But adding it at that time might have invalidated portions of the system test.
I thought it should wait until right after acceptance testing.
However, the new information on the pay stub was a significant programming change at that
phase of development.
If we'd tried to add it, it would have caused a real problem and it certainly would have
delayed implementation of the new system.
Besides, it needed more research.
So adding a new bank's easy, but putting the personal holiday in the pay stub should wait
until after the system's up and running.
And that solves one of my problems.
Let's not delay the new system.
Okay, I'll go along with that.
The next deliverable prepared by the business systems analyst is the acceptance test and
implementation plan.
This plan has six sections.
First is a schedule that lists all the major activities associated with training the user
and data center personnel, conducting the acceptance test, implementing the new system,
and phasing out the old system.
This schedule also identifies the persons responsible for performing the activities.
The next four sections are the finalized versions of the plans that were prepared in phase 3b.
The acceptance test plan, the implementation plan, the training plan, and the conversion
plan.
They are reviewed by the business systems analyst, the computer systems analyst, and
data center support personnel.
The final section is the phase out plan.
In this section, the disposition of the old system is clearly stated.
The final phase four deliverable prepared by the business systems analyst is the phase
summary for users.
In this phase summary, like the others in the phases you've already seen, the business
systems analyst recaps for management any changes made to the system and what the impact
of these changes are.
In this case, it contains the hard costs in time and money of completing the last two
phases of the project.
After preparing all the deliverables for the phase, one final task remains, obtaining the
formal approvals at the user and technical review sessions.
By approving the deliverables of this phase, the user and technical representatives make
two kinds of commitments.
First, there is a commitment to continue the project.
Second, there is a commitment to the schedule of events for phases five and six.
This means that the user and technical representatives commit their people and their computer for
training and acceptance testing.
Now, let's move on to the schedule.
Are there any problems with the dates for training?
Well, Doug and I met with Barbara to coordinate these dates and we'll be ready.
Good.
Now, on the computer schedule, Doug, is it all set for training and testing?
Yes, we have a solid six hours a day on these dates.
The time of day isn't the best because we had to work around the regularly scheduled
payroll runs, but we do have the time.
Well, what's important is getting a solid block of time.
Okay.
Error recovery.
The review sessions are the final tasks in this phase.
This concludes our discussion of phase four, detailed design, programming, and procedures
development.
In this phase, you saw the computer systems analysts prepare, the automated system ready
for acceptance testing, the updated automated system design, the conversion package, and
the data center procedures guide.
You also saw the business systems analysts prepare, the updated functional specifications,
the user procedures guide, the acceptance test and implementation plan, and the phase
summary for users.
Now, return to your student guide to continue this lesson.
life cycle, the system's acceptance test.
In the previous phase, we saw the system built and tested by the project team.
In this phase, the complete system will undergo acceptance testing by the people who must
use it.
This testing is an objective process designed to demonstrate that the system meets the functional
and technical specifications.
When this has been demonstrated, the ownership responsibilities for the system can be transferred
to the user and the data center personnel.
There are five deliverables produced in this phase.
The first three are ownership packages.
There is an ownership package for the user, another for the data center, and a third for
the support personnel who will troubleshoot and maintain the applications programs.
These ownership packages consist of updated documentation and training materials.
The fourth deliverable is the list of proposed enhancements.
This is a collection of all the suggested improvements for the system, which have been
discussed throughout the project, but intentionally postponed until now.
The last deliverable is the updated implementation plan.
This is the plan for the next phase and is similar to the work plans used in previous
phases.
We'll discuss the deliverables in detail later in this lesson.
Now, let's look at the tasks that begin phase five.
The business systems analyst and the computer systems analyst begin by reviewing and making
final the acceptance test plans and the training plans.
When a final test schedule is published, they can begin to compile the checklists which
will be used for the acceptance tests.
These checklists will reflect the test criteria which was agreed upon in phase 3B.
The preparation of the checklists is a job which should be done carefully, for they are
the basis for the actual testing.
Well-designed checklists will speed up both the acceptance testing and the post-test analysis.
I helped payroll write the checklists for the functional part of the tests.
Agreement on the content of these checklists had been reached earlier and was reflected
in the functionality acceptance test plan.
The checklists were designed to show that the system did what payroll requested.
When I helped Doug to prepare the data center checklists, we referred to the production
acceptance test plan.
These checklists were designed to show that the system met technical specs and that it
would operate in a production environment.
When we finished creating the checklists, we were ready to train the personnel who would
be involved in the testing.
Harvey and I trained the payroll personnel.
And I helped Doug train the data center support personnel.
I found this training critical, not only for the success of acceptance testing, but for
the success of the training program itself.
Many of the people we trained before acceptance would become the trainers after the acceptance
stage.
This training serves as a test of the training plans, materials, and documentation.
When it is complete and any necessary changes have been made, the tests can begin.
Acceptance testing has two distinct parts, the production tests and the functional tests.
The production tests are designed to test the system in a real environment.
They must demonstrate to the data center personnel, who must ultimately provide the resources
to run the system, that the system works in a production environment.
The functional tests are designed to demonstrate to the user that the system performs the required
functions, that it meets the functional specifications.
Once we finished training, it was up to the users and the data center personnel to actually
put the system through its paces.
We weren't involved in the actual testing.
The production acceptance tests were run by the data center and data center support personnel.
They had to demonstrate to the data center that the system could be run, and to the support
personnel that the system was built to specs and could be maintained.
The functional acceptance tests were run by the payroll department.
They had to demonstrate to Harvey and the other users that the system was a good functional
tool.
Acceptance testing doesn't always go as smoothly as it did this time, but by the time we had
gone through the checklists, everyone was satisfied that the system could do what it
was supposed to do.
If there are any changes as a result of acceptance testing, it may be necessary to update the
documentation, training, training plans, or implementation plans.
Then, after any necessary changes have been made, the ownership packages are assembled.
We'll return to a discussion of the deliverables of this phase as we focus on the ownership
packages.
There are three ownership packages, user, data center, and application support.
The ownership packages consist of tangible items, such as procedure guides, documentation,
and training materials.
But they also include items that are not really packaged, such as trained personnel.
These support materials and personnel must be in place before a system can be considered
complete.
Most of the documents in the ownership packages are contained in deliverables prepared in
earlier phases.
For the most part, it is only necessary to verify that the information is updated and
then assemble the packages.
The next deliverable is the list of proposed enhancements.
This is a formal way to tell the user and the data center that all the suggested improvements
to the system that were talked about during the development of the system, but not included,
won't be forgotten.
The last deliverable is the implementation plan.
Both the list of proposed enhancements and the implementation plan are the responsibility
of the business systems analyst.
The list of proposed enhancements was simply a way for me to let Harvey and Doug know that
all the improvements to the system that we had talked about during the project, but postponed
until now, wouldn't be forgotten.
The last deliverable that I had to produce in phase five was the implementation plan.
This had originally been done in phase 3B, and it was brought back by the payroll and
data center personnel for final updating after acceptance testing.
Now, it is only necessary to implement the training before final approval marks the end
of this phase.
The training plan is made final, and the personnel who haven't yet been trained receive training
now.
Some training that started much earlier may still be required at this point.
The final task in phase five is the delivery of the ownership packages and the signing
of the written approvals of acceptance.
Sure, I'll sign this.
I know what's in here, and I know that system out there does what I want because my requirements
were taken seriously.
I should be this involved in every project.
Well, I think another thing that the success of the acceptance test shows is that all that
front-end work we put in on the specs and design was well worth it.
When final approval is received from all of the involved parties, the system is accepted
and phase five comes to an end.
Now return to your student guide to continue this lesson.
On the default disk.
In this case, the asterisk allowed DOS to accept any file name regardless of how long
the name was.
Wildcards can be used in almost any file name or extension.
The command ren asterisk dot text asterisk dot doc changes the extension of any text
file to doc.
The command del drive name be asterisk dot dupe deletes every file with an extension
of dupe from the disk drive and drive B. Now for one more example, the disk in drive A
is replaced with the second DOS master disk.
It must be labeled basic or utilities.
The disk in B is replaced with the disk labeled MS DOS 3.3 2 of 2.
The command copy asterisk dot asterisk drive B copies every file from disk A to disk B.
The wildcard designation asterisk dot asterisk refers to all files on a disk.
This is an extremely important word of caution.
If a delete command uses the double asterisk wildcard, all files on the disk will be deleted,
which can really ruin your day.
Before using a wildcard with delete, be sure you know what it's going to do.
Files can be protected physically by using a tab.
It's applied over the right protect notch of the disk jacket.
Specific files can be protected with the attribute command.
It makes a specific file read only, which means the data cannot be changed accidentally.
Before demonstrating this, Diane replaces the DOS working copy in drive A.
The command name is a TRIB and has the option plus R and the file name quick dot doc.
The file is now read only.
That's verified by using the same command without the plus R option.
The R in the display means read only.
To see how effective this is, the line editor command won't even open this file.
To change the file back to read write mode, the minus R option has to be used with the
attribute command.
At times, you may need to look at the contents of a file.
Three different DOS commands let you do this.
To display a text file on your monitor, use the type command.
In this example, type is being used on the sample text file.
For short files, this works well.
Long files need different commands, as we'll see later.
The type command is designed only for text files created with Edline or from console
input.
If used with other files, strange things may appear on your monitor.
The more command also displays a text file on the monitor, but only one screen full at
a time.
It's great for large files.
A simple example will show this.
I'll use the command type plus the name of a long file created just for this demo.
Then comes a vertical bar and the word more.
The screen fills up with text.
This time, however, DOS stopped when it filled the monitor.
To continue, press any key.
The more command also works with the output of other commands.
For instance, when used with the directory command, we see our old friend, the directory
listing of the default disk.
This time, DOS stopped when it filled up the monitor rather than scrolling to the end of
the directory.
The vertical bar is called a pipe and must precede the word more.
More uses something called standard input.
These subjects are more fully discussed in our advanced tape on MS DOS.
The more command is designed for text files created with Edline or from console input.
If used with other files, weird things may appear on your monitor.
The print command sends a text file to your printer using background processing.
That means DOS puts the file in a queue and prints it as time permits while executing
other commands.
The default print queue size is 10 files.
Let's try this on our scrap file.
The printer must be powered on and must be online.
The name of the file to be printed follows the command name.
A prompt may appear to get the printer device name.
That's usually PRN and it's already shown as the default.
When enter is pressed, the file is queued for printing.
The print command is designed for text files created with Edline or from console input.
If used with other files, strange and weird things may be printed.
Many options exist for the print command, but we don't need them for basic operation.
It's time to review the major points about files.
A disk file is the equivalent of a document.
It has a name constructed according to specific rules.
There are commands for copying, deleting, renaming, and protecting files.
Wildcards allow you to work on a whole group of files with a single command.
DOS also has commands to examine the contents of files created only with Edline or from
console input.
Files created with application programs must be printed by that application.
Hard drives are faster and bigger than floppy disks.
If your computer has a hard drive, this lesson will give you a brief introduction to hard
drives.
Do a directory command with the drive name C. That's the usual name for hard drives.
If its directory has entries like this, the hard disk has already been set up by the dealer
and you can continue with this lesson.
Otherwise, skip this lesson and get some experienced help because the hard disk setup is more involved.
To boot up from a hard disk, the drive A door must be open.
Use either a warm boot or a cold boot.
The hard disk starts working and these familiar prompts appear.
Enter the date and the time.
The DOS prompt now shows the hard drive name.
Finally, close the door if the floppy will be used.
The hard disk usually has a tree-like structure of subdirectories.
A subdirector is like a file folder.
It's a subdivision of the disk which holds files on a single subject.
The typical software configuration for a hard disk has a root directory containing a few
special DOS files plus several subdirectories, one for each different application.
Each application subdirectory has the application programs plus document files.
In normal operation, documents are created and updated directly on the hard disk, then
backed up onto floppy disks.
Subdirectories are noted on the directory by the abbreviation DIR in angle brackets.
For example, our hard disk has several subdirectories including one named MS-DOS which contains
files from the DOS master disks and one called EdLine for the DOS line editor.
To move around this structure, the change directory command is used.
Diane will show us how.
Change directory command is abbreviated CD.
The option consists of a backslash and a subdirectory name.
That command put us in the MS-DOS subdirectory.
A directory command only shows the files in a specific subdirectory.
Here's another example.
Change directory to EdLine.
A directory command shows the DOS line editor and text documents here.
They were created for this demo.
If you ever forget what subdirectory you're in, just type the command CD only and it'll
display the subdirectory name.
Once in a subdirectory, you run an application just by typing its name.
The command EdLine followed by the file name new.text starts our familiar friend, the line editor
and creates a new text file.
When you exit an application, you're still in the same subdirectory.
So the procedure for subdirectories is to change the subdirectory containing the application
you want to run and type its name and options.
To get back to the starting directory, which is called the root directory, the change directory
command needs only a backslash.
Backing up and restoring a hard disk presents a problem because of the capacity differences
between floppy and hard disks.
It would take many floppies and much time to completely back up an entire hard disk
that's chock full of data.
And now I'll give you a shortcut that can be used with a single subdirectory.
It uses the backup and restore commands, which are designed for hard disks.
Make sure they exist in the MS-DOS subdirectory before trying this procedure.
Prior to this demo, Diane copied these commands into the MS-DOS subdirectory from the second
master disk in the DOS package.
I also type this command.
It tells DOS which subdirectory has backup and restore.
The command name backup has two options, the drive name C, a backslash, and the subdirectory
name and then the drive name B. This command will cause all files in the MS-DOS subdirectory
to be backed up onto drive B. Formatted floppies are inserted as they are
requested.
Each one is marked with a sequence number.
Otherwise, it might not be possible to restore files from them.
Each file is listed as it's backed up, including its subdirectory name.
Now for a couple of notes on backup.
This shortcut erases any old files on the floppies so they can be reused for each backup.
This shortcut only backs up the contents of a single subdirectory.
Other backup options exist to get different variations.
A restore command does reverse of a backup command.
Usually, a restore is only done when important data has been lost.
For example, you might have accidentally deleted a file or a program may have gotten hung up
and damaged the file you were working on.
Also, on very rare occasions, the hardware might have malfunctioned and wiped out data
on the hard disk.
The following procedure restores a single file in a subdirectory.
The restore command needs the name of a drive containing the floppies.
That's B, and the drive name, subdirectory, and file name to be restored.
We're restoring file mode.com to the MS-DOS subdirectory.
Floppies are inserted as they are requested.
It's very important they be inserted in the same order they were backed up.
That's the demo, Steve.
Note these items about restore.
To restore all the files on a subdirectory, use the double asterisk wildcard for a file
name.
However, if any of those files have been updated since the last backup, those updates will
be lost.
Restore won't work on system files.
These have to be restored with the Sys command.
Since we're done demonstrating hard disk, the default drive should be changed back to
A. If you have a hard disk, remember these points.
Get some experienced help to set up the hard disk.
A subdirectory tree structure is typically used on a hard disk.
Documents are created on the hard disk and backed up to floppies.
To run an application, change to the subdirectory containing it and type its name and options.
Special backup and restore commands must be used with hard disks.
In our final lesson, you'll learn some features and tricks on how to speed up your work.
Many of these features are invoked by special keys or combinations of keys.
When the control key is pressed simultaneously with other keys, it gives special commands
to the computer.
Recall those long directories which scrolled off the monitor faster than you could read
them?
The control S combination stops the scrolling.
Simultaneously pressing control and S during the scrolling halts it and allows the display
to be examined.
Pressing control S again resumes scrolling.
If control and C are pressed while a command is working, they abort the command.
For example, suppose a blank disk is inserted in drive B and the format command is typed.
After it starts, pressing control and C simultaneously stops the formatting.
The control C combination also aborts an application program.
The control P combination enables the printer echo feature.
Anything displayed on the monitor is echoed to the printer.
As an example, if control P is typed and a directory command typed, the directory listing
will be printed as well as displayed.
To stop the printer echo, type control P again.
The final control key combination is control Z. This is used as an end of input signal
in certain cases.
We've already seen it used to terminate input in the EdLine editor.
Certain keys have special editing functions to help speed typing commands.
The last command is automatically stored in a place called a template.
The special editing keys perform their editing functions on the contents of the template.
A simple example is a function key F3.
When pressed, it copies the characters in the template to the command line.
It's an easy way to repeat a command with two keystrokes.
Suppose the command type quick dot doc had just been entered.
Pressing the F3 key causes the entire proceeding command to appear on the command line.
All you have to do is press enter to repeat the command.
Function key F2 has a clever function called a copy up.
When pressed, it copies characters from the template to the command line until it encounters
a specific character which you type.
To see how this works, suppose the command dir asterisk dot com had just been typed.
Now suppose you want to get a directory of all files with an extension of exe.
Pressing F2 and then C causes the previous command up to the letter C to appear on the
monitor.
By typing exe and enter, the new directory is displayed.
If you make a mistake and want to terminate the command line, use the escape key and type
a new command.
Several other special editing keys are described in your reference manual.
I suggest you experiment with them sometime and learn some clever keyboard tricks.
I'll finish up this lesson with two other handy features.
Occasionally you may want to print the contents of the monitor but forgot to enable the printer
echo.
In that case, just use the print screen key.
Hold the shift key then press the print screen key at the same time.
Whatever is on the monitor gets printed.
Now for a demo of how to create a text file from console input.
The command syntax is copy conshort dot txt.
It tells DOS to copy text from the console into the named file.
Each line is terminated with enter.
After typing the last line, control z must be pressed to terminate input and enter must
be pressed one more time.
Remember, to start console input, use the command copy con with a file name.
Then enter the text and terminate it with control z and enter.
Our final lesson has a very brief summary.
Control keys provide extra commands.
Control editing keys speed up typing commands.
Other features exist to help you use DOS.
When you're ready to turn off the computer, make sure your last command is finished processing.
If you're using an application program, make sure you exit from it first.
The DOS prompt should be on the monitor.
Remove all floppy disks, then turn off the computer, monitor, and printer.
So that about wraps it up.
You now know enough about DOS to use it productively.
Both Steve and I thank you for viewing this tape.
And we invite you back for more computer literacy in an easy to learn way.