Quick reference

• 00:04 This is lesson 40, the last lesson
• 00:07 in Part 1 of Microsoft Access 2013.
• 00:12 We will add a logo to the main menu
• 00:14 and review what has been covered.
• 00:16 Hello, this is Crystal
• 00:19 Go to the design view of the main menu form.
• 00:22 Move the buttons that open tables over to the left,
• 00:26 and resize the rectangle around them.
• 00:29 Drop the Controls list and choose the Image control.
• 00:33 Drag the size you want the image to be
• 00:36 and select the logo file from your lesson folder.
• 00:40 Make the border around the image transparent.
• 00:44 Save the form and look at it.
• 00:46 Set the form to maximize when it is opened.
• 00:50 Select the form object and choose Event on the Property Sheet.
• 00:56 If the Property Sheet is not showing, press Alt-Enter.
• 01:00 In the Open event, type [ and click the Builder button, ...
• 01:05 In the code, type:
• 01:07 'the date you are writing code [Enter]
• 01:12 [Tab] DoCmd.Maximize
• 01:17 From the menu, choose Debug, Compile then save.
• 01:21 Close the form and filter the Navigation Pane objects
• 01:25 for anything containing men
• 01:28 When you double-click the main menu to open it,
• 01:31 we have a logo and it is maximized.
• 01:35 In these lessons, we have built an Access desktop database
• 01:38 to manage information for customers and services.
• 01:43 The Services database has been designed generically
• 01:47 so that it can be used for any service business,
• 01:50 whether it is mowing lawns, plumbing, electrical,
• 01:53 or for other professional services and consultants such as
• 01:57 architects, programmers, engineers, accountants, and lawyers.
• 02:03 While the database examples are for Joe's Handyman Services,
• 02:07 it could be used for any business that provides services to customers.
• 02:12 By defining structures generically,
• 02:15 you can use an Access database for many things.
• 02:19 There are two types of databases you can build with Access.
• 02:23 These lessons have focused on Access desktop databases.
• 02:27 Access can also create databases that run on the web in a browser.
• 02:32 The web databases you can build with Access are called Access Apps,
• 02:37 which have a backend stored in SQL Azure and managed by SharePoint.
• 02:43 With either type of database, the basics are the same.
• 02:47 An Access Desktop database has 6 main types of objects.
• 02:52 You learned about the Navigation Pane to view and manage objects in your database,
• 02:58 and how to get it to display the way you want to see it.
• 03:02 Tables are the foundation for everything else
• 03:05 since tables are where data is stored.
• 03:08 Here is the Datasheet View of the Customers Table, showing some of the fields.
• 03:14 Each row is a record.
• 03:16 Each column represents a field.
• 03:18 Each record can have a value in each field.
• 03:22 Tables are defined by fields and properties.
• 03:26 When you look at the Design View of a table,
• 03:29 you can see the definitions that are set up.
• 03:32 Indexes can be defined to help Access find information faster.
• 03:37 Indexes for tables are like indexes in the back of a book.
• 03:42 CustID is the Primary Key and is an AutoNumber,
• 03:45 which means it gets its values automatically
• 03:48 as new records are created and is a number.
• 03:51 On the left, you see a column from the Datasheet View.
• 03:54 The right shows properties of the field from the Design View.
• 03:58 A Primary Key is a combination of one or more fields
• 04:01 that is unique for every record.
• 04:04 The Primary Key is denoted by a key icon
• 04:07 to the left of the fieldname in the selector box.
• 04:10 Customer is a text field for storing the Customer name
• 04:13 and is what will be displayed to the user instead of the primary key field.
• 04:18 In most cases, this field will also have a unique index.
• 04:23 IsActiv is a Yes/No field to determine if a customer is active
• 04:28 and is is displayed as a checkbox.
• 04:31 Tracking Fields are added to almost every table.
• 04:34 dtmAdd and dtmEdit track the date and time
• 04:37 that records are added and edited.
• 04:40 They both start out with a Default Value of Now(),
• 04:44 which is a function to return the current date and time.
• 04:48 dtmEdit is updated on form BeforeUpdate events
• 04:52 when records are changed.
• 04:54 This is the Datasheet view showing some of the fields from the Work Table.
• 04:59 The Primary Key is WorkID.
• 05:01 CustID is a Foreign Key that relates to a record in the Customers table.
• 05:06 We covered data types in great detail
• 05:09 because it is important to get them right.
• 05:12 These are the Simple Data Types.
• 05:15 Each data type shows an example value, range, type of value,
• 05:19 type declaration character, and size in bytes.
• 05:24 These are the Special, Complex, and Other Data Types.
• 05:29 As a general rule, complex and other data types should be avoided.
• 05:34 To link records in one table to records in another table,
• 05:38 create a Foreign Key in a related table that
• 05:41 relates to a Primary Key in the main table.
• 05:44 The Relationships Diagram is a tool you can use
• 05:47 to define and display relationships.
• 05:50 When the relationship line is labeled with 1 and infinity sign,
• 05:55 referential integrity is enforced,
• 05:58 which should always be done unless you have a specific reason not to.
• 06:03 Referential Integrity ensures that the related table
• 06:07 will not be allowed to have a value in a foreign key
• 06:10 that is not first in the main table as a primary key.
• 06:14 Queries can pull information from more than one table, or query,
• 06:19 with criteria to limit the records, and sort however you like.
• 06:23 You can define calculated fields and assign aliases to fields.
• 06:27 The SQL Statement is what Access actually stores for each query.
• 06:32 When a query is opened or used, the current data from tables is displayed.
• 06:38 Tables and Queries can be used as sources for bound Forms and Reports.
• 06:43 Forms are used for updating information, menus, and dashboards.
• 06:47 Forms can be used to add, edit, find, and display data.
• 06:52 You learned how to create combo boxes
• 06:55 so that a number can be stored for efficiency
• 06:58 while displaying friendly text from another table.
• 07:01 We discussed colors, formatting, and the Property Sheet.
• 07:05 Reports are for printing and viewing information on the screen,
• 07:09 and this is often what you give other people.
• 07:12 You've learned how to use the wizard to create reports and labels,
• 07:16 and build a complicated report from scratch,
• 07:19 group and sort, use running sums, repeat sections,
• 07:23 hide duplicates, get totals and subtotals,
• 07:25 and line up controls so everything looks good.
• 07:29 Many of the things you learned for forms work for reports, and vice-versa
• 07:35 For instance, the equation to show totals in reports
• 07:38 works in form headers and form footers too.
• 07:42 To automate, macros and VBA, which is stored in modules, can be used.
• 07:47 If you did not know VBA, you have learned,
• 07:50 and seen how you can use just a couple lines of VBA
• 07:54 to get Access to do exactly what you want.
• 07:58 We created a main menu so that what you build in Access
• 08:03 is easy for others to get to and use.
• 08:06 This course was designed to get you on the road
• 08:09 to becoming a master with Access.
• 08:12 To test your knowledge, do the quizzes.
• 08:14 There are more than 150 pages in the Quick Reference Guides.
• 08:19 Be sure to read them and print them as they will be a valuable reference
• 08:23 as you work with Access in the future.
• 08:26 You now have a great database you can customize for your own business too.
• 08:31 Thanks for joining me. I've enjoyed teaching you.
• 08:35 Your next assignment is to build great things with Microsoft Access.

Lesson notes are only available for subscribers.