Microsoft Access Class #3 (Forms, Reports, Queries)

1. Get the data file
  • Go to the web site, www.ualr.edu/gblane - download the files from today's page
  • Store the files in My Documents
  • Open the database named 13A_Instructors_and_Courses
  • Open each table, examine the data, then close the table
3. Creating a Form (data input)
  • Go to the Create tab - Click on"Form"
  • Save Form with the default name and close the Form
4. Creating a Report (printed output) with a Wizard
  • Go to the Create tab - Click on "Report"
  • Save the Report with the default name and close the Report
  • Examine the other options for reports
5. Creating a Relationship between the 2 Tables
  • Close any open Tables, Forms, or Reports
  • Click on the Database Tools tab, then on Relationships
  • Click on "Show Table", then on 13A Courses, then on Add
  • Click on 13A Instructors, then on Add, and close the Show Table window
  • Stretch the tables out, if needed, to see all the Field Names.
  • Drag the Instructor ID form onto the table,to the Instructor ID in the other
  • When the Edit Relationships window appears, click Enforce Referential Integrity, and Cascade Update
6. Create a Simple Select Query and save
  • To create a Query: Go to the Create Tab; click Queries Wizard or Design
  • Choose Section Number, Dept Name, Course Number, Course Name, Instructor ID,
  • Then from the Instructors table, choose Instructors Last Name
  • Modify the Query Design - OR if you miss that option, go to VIEW under the Home tab, and choose Design View.
  • Under Dept Name, on the Criteria row, Enter "Business" and click the "RUN" button.
7. Complex Query (Modify the Query)
  • Go back to Design View - Use the "OR" line to enter another criteria ("Accounting") under "Business"
  • To run, Click on View-DataSheet (or the RUN button)
8. Other Notes on the Query Criteria that can be entered in Queries
  • Date - Dates can be written as: Between #1/5/95 and #1/10/95
  • Last Name - To get Last names starting with letters beyond "N":    > N
  • Last Name - Can also use "S*"    to mean anything starting with an S (* is a wildcard)
  • Is Null - finding records with no data in a field