Access Competency

There is no Sage of Access.

The Project

This is the order for how you should complete the project:

  1. It would be good for you guys to attend a class but you don’t have to.  These are things you should’ve learned after taking the class and before starting the competency:
    • The differences between and reasons you would use One to One, One to Many, and Many to Many Relationships
    • How to create a table, enter data, and sort data
    • The difference between and when you would use Primary and Foreign keys
    • How to choose data types, make input masks, and make validation rules
    • How to make a lookup field
    • How to import data from Excel
    • How to create and enter data into a form
    • How to insert a subform
    • How to create a Navigation form
    • How to create various types of queries (see Day 4 for details)
    • How to create a report
    • How to insert a subreport
    • How to create a basic macro
    • How to create a combo box
    • How to create a buttons
  2. Create the fully working library database from what you learned in class.
  3. Design and create your own database
    • Using your newfound skills, create a new database from scratch. Be sure that you don’t just make the quickest database possible because you won’t be prepared to teach the class. You need to make a database complex enough to show that you understand the concepts and broad functionality of Access. However, feel free to ask for help because this isn’t the type of program you can really learn on your own.  In the beginning stages, it can be very helpful to walk through on paper what you will eventually do in Access.
  4. Create multiple tables linked by appropriate relationships
    • Include one to many relationships and at least one example of a many to many relationship.
  5. Populate your tables with appropriate data
    • Be sure to include input masks, lookup fields, and validation rules.
    • For one table, use the importing feature to import data from Excel.
  6. Create several queries to pull out relevant information—
    • Be sure to demonstrate a working knowledge of criteria (AND, <, BETWEEN, LIKE etc.)  and parameters ([ie What is the name of the book?])
    • Create an update, delete, crosstab query
    • Use the Totals row
    • Use the expression builder
  7. Create a few forms to help users enter information (use the autoform and the wizard)
    • Insert some buttons into your forms
    • Make one macro with the Macro builder.
    • Insert a combo box into a form
    • Insert a subform into a form
  8. Create a few reports based on one or more tables or queries (use auto report and the wizard)
    • Insert a subreport.
    • Add group and sort options
    • Add something from the Totals feature
    • Add your forms and reports to a Navigation form.
  9. When you’re done, pass off what you’ve done with the Access ninja and show your completed lesson plans (half of the days is the requirement). Good luck!