Microsoft Access Online Video Training - Learn Access Fast Videos:
Video 1 - Intro-1A-What Is Microsoft Access
Microsoft Access 2007 is both a database management system and application development system. This Access video describes how Access provides both the ability to define a database structure and build the application used to work with the database. The important concept is recognizing the separation of the database from the application. This will allow you to plan for sharing the database when the time comes.
Video 2 - Intro-1B-Split A Database
This Access video demonstrates how to split a database. This is an important concept when it comes time to share the database. The video demonstrates the how and also talks about the why. The lesson is included to emphasize the difference between the database and application. It also provides suggestions for naming the two files produced by the split and talks about proper database creation.
Video 3 - Intro-2A-Design Terms
Database design is something most Access classes do not include. This video introduces the topic of database design and provides an overview of how to get started. The terms presented in this Access video give you a good start and make it easier to talk to others about how they might help you in the design process.
Video 4 - Intro-2B-Design Diagram
The design process includes not only terms and diagrams but also subject matter experts. These are people who really know what the database should do and can come together to design the system. This Access video talks about the importance of communication during the design process. It also presents an example of how a group might discuss a specific field and how that field might change as the discussion progresses.
Video 5 - Intro-3A-Create Database and Table
Create a database. Mdb vs accdb file formats. Convert an old format to the new format. Mixed environment with both old and new versions of Access. Create a table using
design view. Surrogate vs. intelligent primary keys. Required field. Field caption. What is the first step in actually creating an Access database? This Access video walks you
through the process. Over the years Microsoft Access, like other programs, has continued to grow and evolve through different versions. The video talks about how to manage
different versions of Access. It also looks at creating tables. A discussion of the different types of primary keys is included to help you decide what is best for your table. Field
properties like required and caption are also discussed.
Video 6 - Intro-3B-Create More Tables
This Access video creates tblUoM and tblIngredient. tblUoM is used to store units of measure for ingredients. To keep units of measure consistent it works best to put them in a table once and then assign them to ingredients as needed. When tblIngredient is built the unit of measure can be looked up in tblUoM using the Lookup Wizard. This makes it easy to just pick the unit of measure instead of having to retype it for every ingredient.
Video 7 - Intro-3C-Finish Creating Tables
Create recipe table. Define a lookup field. Use Date() function to return current date. Max note field size of 255 vs. memo field. Linking table. Foreign key vs. primary key.
Number with decimal places. Compound primary key. The recipe table is one of the main tables in the recipe database. It really is what this database is all about. Since recipes
can be grouped into categories a CategoryID field is added. This field is a long integer datatype but is defined using the Lookup Wizard. This allows us to tell the database where
to get a list of categories to display whenever someone is entering a new recipe. Defining the field as a lookup field in the table means all future forms and reports that include
the CategoryID field will display a combo box in forms and display the lookup value in reports.
Video 8 - Intro-3D-Define Relationships
Relationships window. Referential integrity. Relationship characteristics. Cascading updates and deletes. In this lesson we start by rearranging tables in the relationships window
to make it easier to work with the tables and define relationships. Then we take a look at the properties of a relationship and define what referential integrity is and why it is
important. Cascading updates and deletes are also discussed so you are aware of the important characteristics in a relationship.
Video 9 - Intro-3E-More Relationships
Create new relationships. Combo box lookup details. Turn cascading deletes on. Relationships are defined in the relationships window. This Access video includes more
discussion of referential integrity and why cascading deletes are dangerous!
Video 10 - Intro-3F-Cascading Referential Integrity
Cascade update related records. Cascade delete related records. This Access video demonstrates the affect of turning on cascading updates and deletes. It shows what happens
when cascading updates changes related record field values. It also shows how cascading deletes will automatically delete related records. This is a very important concept to
understand to avoid serious damage to the records in a database.
Video 11 - Intro-4A-Toolbar And TabConfig
Customize quick access toolbar. Move toolbar. Add commands and separators and rearrange the toolbar.. Tabs vs multiple windows. Access provides a number of options for
customizing the user interface. This video looks at a few ways Microsoft Access allows us to add often used shortcuts to the quick access toolbar and control where the toolbar is
located. It also demonstrates how a tabbed interface differs from an overlapping windows interface.
Video 12 - Intro-4B-Recipe Form Types
Switchboard form. Forms for working with records. Enter a record. Delete a record. Record at a time vs. datasheet view for viewing multiple records. Popup and modal form.
Form with a subform. The Microsoft Access recipe template contains different types of forms. This Access video reviews the forms included in the template and what they are
used for. The main use for forms is to insert, update, delete or view existing records. This is done while viewing one record at a time or multiple records.. One record at a time is
called form view and multiple records at a time is called datasheet view. Datasheet view is a built in option that can be used with any form. Forms can also be defined as popup
and many customized versions of forms can be created.
Video 13 - Intro-4C-Main Switchboard
Form uses. Create a switchboard with buttons and hyperlinks. Create a horizontal line to separate form areas.. Align controls. Turn off layout view. Add buttons with hyperlinks.
Using the property sheet. Turn off form options. Use the selection rectangle. Most Microsoft Access applications will start up by displaying some kind of switchboard that
provides quick access to the different areas in the application. This Access video looks at how to quickly create a switchboard that provides access to the different tables, forms
and reports an application might include. This is only one example of how a main switchboard can work. The video also demonstrates ways of working with multiple controls on
a form and how to work with some of the properties of a form.
Video 14 - Intro-4D-New Form
Layout view. Use hyperlinks to hook up buttons to tables. Turn off Add New Field in table datasheet view. Create new ingredients form with the form wizard. Unarrange and
resize controls. Set form caption. In this Access video a couple of the new switchboard buttons are hooked up to tables using the hyperlink subaddress property. The buttons are
then all resized at once to provide more room to display the button caption. A feature that allows adding new fields in datasheet view is turned off so that all new fields are
added in table design view. The form wizard is used to create a new ingredients form. After the wizard is done creating the form some finishing work is done to produce the final
form.
Video 15 - Intro-4E-Form Types
Button wizard and different actions for a button. Macro vs hyperlink. Form view vs datasheet view. Continuous form. Datasheet form. Now that the ingredients form is built it's
time to hook it up to the first form button on our switchboard. Buttons can also be created with the button wizard. The button wizard will walk you through the different options
for creating a button and defining what the button will do. The video demonstrates the difference between form view, datasheet view and a third option called continuous
forms.
Video 16 - Intro-4F-Filter Dialogue Forms
Filter forms. Used to limit the records retrieved from a table. Dialogue forms are used to enter parameters for the system to use in running reports or data processing. A real
application is used to demonstrate how a filter form can be used to limit the number of records retrieved by a form. This can be important if a form retrieves records from a
record source that has millions of rows in it. Dialogue forms are forms where a user enters criteria for the system to use while running a report or doing data processing.
Dialogue forms may also be called parameter forms. The idea is to allow a person to enter all the parameters needed to run a report, review them, edit parameters and print reports.
Video 17 - Intro-4G-Form SubForm
Create a form with a subform using the form wizard. Creating a form with a subform is a good time to use the form wizard. The wizard does a great job of getting all the needed
controls on the parent form and the subform. It doesn't however produce a "finished" form. This Access video looks at how to use the wizard and what to name the two forms
produced. The form produced by the wizard is a real mess which is why the next video looks at how to finish the form produced by the form wizard.
Video 18 - Intro-4H-Arrange Controls
Arrange controls for a form with a subform. Unarrange controls to allow for individual resizing and moving. The recipe form produced by the form wizard is quite a mess because
the wizard only knows what controls are needed. It doesn't know anything about how we really want the form to look. That is why we need to do a lot of "finishing" work to
clean up the mess created by the form wizard.
Video 19 - Intro-4I-Arrange Controls 2
Continue to arrange controls on a form with a subform. Align groups of controls. You might be tempted to think it would be easier to just create the form from scratch. That is
definitely an option so give it a try some time to see which method works for you. This last form video finishes up the recipe form with a subform. It continues to demostrate
techniques for moving and resizing single controls or multiple controls. The selection rectangle is used a lot to select multiple controls by simply clicking and dragging. The
finished product is a clean looking form that will be used to input data and restrict the data a user an input.
Video 20 - Intro-5A-Query Wizard
Review Northwind database structure in relationships window. Using the query wizard. Queries on queries. When working with a new database a good way to get acquainted
with the structure of the database tables is to use the relationships window. Unfortunately in the Northwind database that comes with Microsoft Access 2007 the relationships
window is a mess. I took the time to rearrange the tables into a useable diagram. I have done this with even worse messes and in all cases it was well worth the time it takes.
The end result is a document that is very useful for any discussion of what is in the database.
Video 21 - Intro-5B-Query Design View I
Create a query without the query wizard using design view. Left outer join vs right outer join vs inner join. Does switching from an outer join to an inner join affect the number
of records returned by the query? The query wizard is one way to create a new query. I however never use the query wizard because I think design view is a better way to
create new queries. Design view provides a way to pick the tables and queries involved in the query just like the wizard does and then pick the fields the query should return.
Design view is another example of a query by example interface that was invented by Dr. Moshe Zloof back in the 1970's while working for IBM. This type of interface has been
incorporated into many different queries to select data for review.
Video 22 - Intro-5B-Query Design View II
Outer vs inner join in a query. Understanding joins is critical! Adding criteria to a query. Parameterized query. The previous Access training video introduced the different types
of joins involved in a query. This Access video zeroes in on the difference the join type can make in the records returned by a query. This is a critical difference that is
highlighted with a story of how changing a join type found several million dollars more of inventory in an inventory query. Is Null criteria is presented as a way to find records in
one table that do not have any related records in another table.
Video 23 - Intro-6A-Report Wizard 1A
Using the report wizard. Format a report and add more features. Add horizontal lines. Change date format. Reduce horizontal white space. Increase vertical white space. Resize
controls. The report wizard is a great way to get a report started. Like the form wizard it is a quick way to get all the fields added to a new report. After the report wizard has
done it's work the finishing touches can be added to give the report it's final look. Producing the final look is similar to the work done on the recipe form. It includes moving,
resizing and rearranging the various controls. New elements like horizontal lines help separate the different sections of the report. Respacing controls helps to control printing of
reports on your printer.
Video 24 - Intro-6B-Report Wizard 1B
Continue arranging controls. Add a report footer count control. Bound vs unbound control. Importance of control size. Producing a finished report does take time to resize and
rearrange the controls placed on the report by the report wizard. The end result however is a much better report that is easier to read and will usually require fewer sheets of
paper to print. The report footer section is a good place to add a control that will count the number of records in a report. This is an important piece of information that should
be included on most reports. This Access video shows how to add a separate label and text control.
Video 25 - Intro-6C-Report Wizard 2A
Grouping a report. Rearrange controls. A grouping report adds group headers and footers to group the records by state. The state header is added by the wizard. The group
footer is added later by turning on the report footer area. Like the previous report the first step is rearranging and resizing controls to improve readability and decrease wasted
white space.
Video 26 - Intro-6D-Report Wizard 2B
Add group footers and finish up the report. Convert a label control to a text control with an ex-pression to count group records. Adding a group footer is a lot different in
Microsoft Access 2007 than it was in previous versions of Access. This Access video looks at where to find the Group and Sort options for a report and how to manually turn on a
group footer. Once the group footer section is visible the next step is to add controls that will provide group totals. The controls in a group footer can include ex-pressions that
reference fields. This makes it easier for a person reading the report to know which group the totals are for.
Microsoft Access 2007 is both a database management system and application development system. This Access video describes how Access provides both the ability to define a database structure and build the application used to work with the database. The important concept is recognizing the separation of the database from the application. This will allow you to plan for sharing the database when the time comes.
Video 2 - Intro-1B-Split A Database
This Access video demonstrates how to split a database. This is an important concept when it comes time to share the database. The video demonstrates the how and also talks about the why. The lesson is included to emphasize the difference between the database and application. It also provides suggestions for naming the two files produced by the split and talks about proper database creation.
Video 3 - Intro-2A-Design Terms
Database design is something most Access classes do not include. This video introduces the topic of database design and provides an overview of how to get started. The terms presented in this Access video give you a good start and make it easier to talk to others about how they might help you in the design process.
Video 4 - Intro-2B-Design Diagram
The design process includes not only terms and diagrams but also subject matter experts. These are people who really know what the database should do and can come together to design the system. This Access video talks about the importance of communication during the design process. It also presents an example of how a group might discuss a specific field and how that field might change as the discussion progresses.
Video 5 - Intro-3A-Create Database and Table
Create a database. Mdb vs accdb file formats. Convert an old format to the new format. Mixed environment with both old and new versions of Access. Create a table using
design view. Surrogate vs. intelligent primary keys. Required field. Field caption. What is the first step in actually creating an Access database? This Access video walks you
through the process. Over the years Microsoft Access, like other programs, has continued to grow and evolve through different versions. The video talks about how to manage
different versions of Access. It also looks at creating tables. A discussion of the different types of primary keys is included to help you decide what is best for your table. Field
properties like required and caption are also discussed.
Video 6 - Intro-3B-Create More Tables
This Access video creates tblUoM and tblIngredient. tblUoM is used to store units of measure for ingredients. To keep units of measure consistent it works best to put them in a table once and then assign them to ingredients as needed. When tblIngredient is built the unit of measure can be looked up in tblUoM using the Lookup Wizard. This makes it easy to just pick the unit of measure instead of having to retype it for every ingredient.
Video 7 - Intro-3C-Finish Creating Tables
Create recipe table. Define a lookup field. Use Date() function to return current date. Max note field size of 255 vs. memo field. Linking table. Foreign key vs. primary key.
Number with decimal places. Compound primary key. The recipe table is one of the main tables in the recipe database. It really is what this database is all about. Since recipes
can be grouped into categories a CategoryID field is added. This field is a long integer datatype but is defined using the Lookup Wizard. This allows us to tell the database where
to get a list of categories to display whenever someone is entering a new recipe. Defining the field as a lookup field in the table means all future forms and reports that include
the CategoryID field will display a combo box in forms and display the lookup value in reports.
Video 8 - Intro-3D-Define Relationships
Relationships window. Referential integrity. Relationship characteristics. Cascading updates and deletes. In this lesson we start by rearranging tables in the relationships window
to make it easier to work with the tables and define relationships. Then we take a look at the properties of a relationship and define what referential integrity is and why it is
important. Cascading updates and deletes are also discussed so you are aware of the important characteristics in a relationship.
Video 9 - Intro-3E-More Relationships
Create new relationships. Combo box lookup details. Turn cascading deletes on. Relationships are defined in the relationships window. This Access video includes more
discussion of referential integrity and why cascading deletes are dangerous!
Video 10 - Intro-3F-Cascading Referential Integrity
Cascade update related records. Cascade delete related records. This Access video demonstrates the affect of turning on cascading updates and deletes. It shows what happens
when cascading updates changes related record field values. It also shows how cascading deletes will automatically delete related records. This is a very important concept to
understand to avoid serious damage to the records in a database.
Video 11 - Intro-4A-Toolbar And TabConfig
Customize quick access toolbar. Move toolbar. Add commands and separators and rearrange the toolbar.. Tabs vs multiple windows. Access provides a number of options for
customizing the user interface. This video looks at a few ways Microsoft Access allows us to add often used shortcuts to the quick access toolbar and control where the toolbar is
located. It also demonstrates how a tabbed interface differs from an overlapping windows interface.
Video 12 - Intro-4B-Recipe Form Types
Switchboard form. Forms for working with records. Enter a record. Delete a record. Record at a time vs. datasheet view for viewing multiple records. Popup and modal form.
Form with a subform. The Microsoft Access recipe template contains different types of forms. This Access video reviews the forms included in the template and what they are
used for. The main use for forms is to insert, update, delete or view existing records. This is done while viewing one record at a time or multiple records.. One record at a time is
called form view and multiple records at a time is called datasheet view. Datasheet view is a built in option that can be used with any form. Forms can also be defined as popup
and many customized versions of forms can be created.
Video 13 - Intro-4C-Main Switchboard
Form uses. Create a switchboard with buttons and hyperlinks. Create a horizontal line to separate form areas.. Align controls. Turn off layout view. Add buttons with hyperlinks.
Using the property sheet. Turn off form options. Use the selection rectangle. Most Microsoft Access applications will start up by displaying some kind of switchboard that
provides quick access to the different areas in the application. This Access video looks at how to quickly create a switchboard that provides access to the different tables, forms
and reports an application might include. This is only one example of how a main switchboard can work. The video also demonstrates ways of working with multiple controls on
a form and how to work with some of the properties of a form.
Video 14 - Intro-4D-New Form
Layout view. Use hyperlinks to hook up buttons to tables. Turn off Add New Field in table datasheet view. Create new ingredients form with the form wizard. Unarrange and
resize controls. Set form caption. In this Access video a couple of the new switchboard buttons are hooked up to tables using the hyperlink subaddress property. The buttons are
then all resized at once to provide more room to display the button caption. A feature that allows adding new fields in datasheet view is turned off so that all new fields are
added in table design view. The form wizard is used to create a new ingredients form. After the wizard is done creating the form some finishing work is done to produce the final
form.
Video 15 - Intro-4E-Form Types
Button wizard and different actions for a button. Macro vs hyperlink. Form view vs datasheet view. Continuous form. Datasheet form. Now that the ingredients form is built it's
time to hook it up to the first form button on our switchboard. Buttons can also be created with the button wizard. The button wizard will walk you through the different options
for creating a button and defining what the button will do. The video demonstrates the difference between form view, datasheet view and a third option called continuous
forms.
Video 16 - Intro-4F-Filter Dialogue Forms
Filter forms. Used to limit the records retrieved from a table. Dialogue forms are used to enter parameters for the system to use in running reports or data processing. A real
application is used to demonstrate how a filter form can be used to limit the number of records retrieved by a form. This can be important if a form retrieves records from a
record source that has millions of rows in it. Dialogue forms are forms where a user enters criteria for the system to use while running a report or doing data processing.
Dialogue forms may also be called parameter forms. The idea is to allow a person to enter all the parameters needed to run a report, review them, edit parameters and print reports.
Video 17 - Intro-4G-Form SubForm
Create a form with a subform using the form wizard. Creating a form with a subform is a good time to use the form wizard. The wizard does a great job of getting all the needed
controls on the parent form and the subform. It doesn't however produce a "finished" form. This Access video looks at how to use the wizard and what to name the two forms
produced. The form produced by the wizard is a real mess which is why the next video looks at how to finish the form produced by the form wizard.
Video 18 - Intro-4H-Arrange Controls
Arrange controls for a form with a subform. Unarrange controls to allow for individual resizing and moving. The recipe form produced by the form wizard is quite a mess because
the wizard only knows what controls are needed. It doesn't know anything about how we really want the form to look. That is why we need to do a lot of "finishing" work to
clean up the mess created by the form wizard.
Video 19 - Intro-4I-Arrange Controls 2
Continue to arrange controls on a form with a subform. Align groups of controls. You might be tempted to think it would be easier to just create the form from scratch. That is
definitely an option so give it a try some time to see which method works for you. This last form video finishes up the recipe form with a subform. It continues to demostrate
techniques for moving and resizing single controls or multiple controls. The selection rectangle is used a lot to select multiple controls by simply clicking and dragging. The
finished product is a clean looking form that will be used to input data and restrict the data a user an input.
Video 20 - Intro-5A-Query Wizard
Review Northwind database structure in relationships window. Using the query wizard. Queries on queries. When working with a new database a good way to get acquainted
with the structure of the database tables is to use the relationships window. Unfortunately in the Northwind database that comes with Microsoft Access 2007 the relationships
window is a mess. I took the time to rearrange the tables into a useable diagram. I have done this with even worse messes and in all cases it was well worth the time it takes.
The end result is a document that is very useful for any discussion of what is in the database.
Video 21 - Intro-5B-Query Design View I
Create a query without the query wizard using design view. Left outer join vs right outer join vs inner join. Does switching from an outer join to an inner join affect the number
of records returned by the query? The query wizard is one way to create a new query. I however never use the query wizard because I think design view is a better way to
create new queries. Design view provides a way to pick the tables and queries involved in the query just like the wizard does and then pick the fields the query should return.
Design view is another example of a query by example interface that was invented by Dr. Moshe Zloof back in the 1970's while working for IBM. This type of interface has been
incorporated into many different queries to select data for review.
Video 22 - Intro-5B-Query Design View II
Outer vs inner join in a query. Understanding joins is critical! Adding criteria to a query. Parameterized query. The previous Access training video introduced the different types
of joins involved in a query. This Access video zeroes in on the difference the join type can make in the records returned by a query. This is a critical difference that is
highlighted with a story of how changing a join type found several million dollars more of inventory in an inventory query. Is Null criteria is presented as a way to find records in
one table that do not have any related records in another table.
Video 23 - Intro-6A-Report Wizard 1A
Using the report wizard. Format a report and add more features. Add horizontal lines. Change date format. Reduce horizontal white space. Increase vertical white space. Resize
controls. The report wizard is a great way to get a report started. Like the form wizard it is a quick way to get all the fields added to a new report. After the report wizard has
done it's work the finishing touches can be added to give the report it's final look. Producing the final look is similar to the work done on the recipe form. It includes moving,
resizing and rearranging the various controls. New elements like horizontal lines help separate the different sections of the report. Respacing controls helps to control printing of
reports on your printer.
Video 24 - Intro-6B-Report Wizard 1B
Continue arranging controls. Add a report footer count control. Bound vs unbound control. Importance of control size. Producing a finished report does take time to resize and
rearrange the controls placed on the report by the report wizard. The end result however is a much better report that is easier to read and will usually require fewer sheets of
paper to print. The report footer section is a good place to add a control that will count the number of records in a report. This is an important piece of information that should
be included on most reports. This Access video shows how to add a separate label and text control.
Video 25 - Intro-6C-Report Wizard 2A
Grouping a report. Rearrange controls. A grouping report adds group headers and footers to group the records by state. The state header is added by the wizard. The group
footer is added later by turning on the report footer area. Like the previous report the first step is rearranging and resizing controls to improve readability and decrease wasted
white space.
Video 26 - Intro-6D-Report Wizard 2B
Add group footers and finish up the report. Convert a label control to a text control with an ex-pression to count group records. Adding a group footer is a lot different in
Microsoft Access 2007 than it was in previous versions of Access. This Access video looks at where to find the Group and Sort options for a report and how to manually turn on a
group footer. Once the group footer section is visible the next step is to add controls that will provide group totals. The controls in a group footer can include ex-pressions that
reference fields. This makes it easier for a person reading the report to know which group the totals are for.