Microsoft Access

PreRequisite: Windows Basics. Students should have a good understanding of how to use Windows, and a computer in general. It would also be very helpful, although not required, for students to have taken our Word and Excel Basics courses as well. While no prior database experience is required, knowledge of how to use a spreadsheet program, like Excel, would be helpful.

Note: This course is our specialty. The instructor, Richard Rost, has been building databases with Microsoft Access for nearly 10 years. When he is not teaching, he is building databases. If you're looking to really learn Access, this is the course for you. We skip a lot of the theoretical nonsense and get right into building real-life databases.

Topics Covered: Access database components, tables, queries, forms, reports, macros, modules, data access pages, planning your database on paper, what tables will you need, what fields will you need in each table, creating your first database, the Database Window, creating a Customer table, adding fields to your table such as First Name, Last Name, and so on, data types, field descriptions, autonumbers, moving fields, deleting a field, undo, saving your table, primary keys, indexing, entering data into your table, check boxes, resizing columns, saving layout changes, building a query with a single table, adding fields to your query, running a query, switching between design and datasheet views, sorting on a field, sorting on multiple fields, copying a query, filtering query data based on criteria, AND v. OR criteria, interactive parameter queries, data entry forms, specifying a data source for your form, adding fields from the Field List, text boxes and labels, viewing your form, moving and resizing fields, moving a group of fields, working with fonts, colors, and effects, resizing your form, entering data into your form, navigation buttons, reports, creating mailing labels. At the end of this class, we have built a fully-functional customer database, capable of tracking customer information, printing mailing labels, and generating sorted lists of customers by state and other user-specified parameters.

Advanced table properties, field sizes, different types of numbers, format, input mask, custom date formats, captions, default values, data validation rules, required values, indexing and why it's important, relating multiple tables together, creating a contact management table, foreign keys, linking tables together using a query, creating field relationships, using the asterisk, advanced query sorting and filtering, building the contact form, using a combo box (drop-down box), the combo box wizard, creating command buttons to open and close forms, the command button wizard, adding pictures to your buttons, creating a Main Menu form, tab order. By the end of this class, you will have built a contact management system to track correspondence with each of your customers. You will also have built a basic menu system for your database.

Building an Order Entry system, Order Entry tables, Orders and Order Details, creating an order, entering in some products, calculations in queries, formatting a query column, string concatenation in queries, calculating sales tax, creating an employee table, adding a sales rep to your order, building the Order Entry form, building the order details form, form properties, continuous v. single form view, form headers and footers, navigation buttons, hiding fields by making them invisible, adding calculated totals to your form footers, SUM function, unbound text boxes, creating the subform for order details, relationships, default values on forms. At the end of this class you now have an Order Entry system where you can enter in customer orders and the products purchased on those orders.

Creating a product table to store product information, creating a product form, enter in products, adding a Product ID to our order details table and form, creating a combo box to select products to be added to our order, creating a command button to add the product as a new item on the order using a Visual Basic module, build events, the code builder, our first programming: a "Hello World" message box, retrieving combo box data using Visual Basic, GoToRecord command, accessing combo box columns, #Name? error, using an If/Then construct, Refresh method, retrieiving data from tables using the DLOOKUP function, string concatenation in Visual Basic, tracking an address in your order, AfterUpdate events, error handlers. At the end of this class, we've greatly enhanced our Order Entry system. Now, instead of just manually typing products into our order form, we can select products from a Product Table and the information is automatically added to our order: product name, price, and so on. We've also learned our first bit of Visual Basic programming.

Advanced Reporting, building a credit limit report, using the SUM function in a report footer, adding page numbers, horizontal lines, report header/footer, the difference between the report header/footer and the page header/footer, creating a printable invoice/quote for our Order Entry system, adding an IsQuote field to our Order table, adding a quote/invoice toggle button to the order form that changes color and caption when changed, changing object properties using Visual Basic and events, OnCurrent event, building an order query that relates multiple tables together, join types (inner, outer), sorting and grouping in reports, creating a group header/footer, report properties, force new page, page margins, creating a label and changing the caption on the fly based on data in the report (invoice/quotation), report section build events, adding a graphic (logo) to your report, adding totals to the bottom of yoru invoice, can grow/shrink, adding a command button on your order form to open the invoice for that order. At the end of this class, we now have a fully usable Order Entry system complete with a printable invoice/quotation that can br printed out and given to our customer. And, of course, all of the data is stored safely in our database.