Access Assignment 3
Newspaper Event Query

---

Schedule | Syllabus | Printable Version

You may have found that your Access queries don't exactly do what you expect. Here are some examples of what can happen. We will start with the premise that your database had three tables: Instructors, Courses and CourseDates. Instructors and Courses are related to Course Dates through foreign keys.

EXAMPLE 1: GET COURSE NAME LIST WITH JUST THE COURSES TABLE

Create a Query called JustCourseNames with just the Courses table. If there are three Course records, your query should show three Course names when you run the query.

Notice that you don't need the CourseID to get this information. This is an example of pulling out a field and showing the results with a Query. The resulting dataset looks very much like the Table, but with only one column.

 

It would be great if you could do a "Save As" on your Queries for these exercises, but the new Access doesn't seem to have that ability.

EXAMPLE 2: GET COURSE NAME LIST WITH THE COURSES AND THE COURSE DATES TABLES

Create a Query called CourseNamesTwoTables. Add both the Courses and the CourseDates Tables to the Query. Get the CourseNames field from the Courses Table.

Notice that your Query picks up a Course for every CourseDate, not just a list of Courses. It is still picking up the CourseNames, but not the "unique" list that you might have wanted.

 

EXAMPLE 3: CREATING A "UNIQUE" QUERY

You can get a list of "unique" values from the list by choosing Yes from the Unique Values Property. To do that, highlight the field, right click and choose Properties.

That will produce a list that is the same as Example 1.

EXAMPLE 4: ADD THE COURSE DATE FIELD TO YOUR QUERY

Drag the StartDate Field to the next column in your Query and run the Query. Notice that you are beginning to ge the kind of list that you need for the first Query in Access Assignment 3.

Add a new date in your form and see how it goes into your Query automatically.

EXAMPLE 5: ADD INSTRUCTOR FIRST AND LAST NAMES

Add the Instructor's Table to the window. Add the First and Last Name fields.

EXAMPLE 6: ADD THE DATE CONSTRAINTS

In the StartDate criteria, type >#12/31/08#. The # allow Access 2007 to recognize the entry as a date. Run the query and see how it does not show classes before Jan 1, 2009. However, the newspaper wants to list the Courses in Jan - March. Courses after March 31, 2009 should not show. Use the Builder with the And operator to build a range function on the Start Date field.

Now you should have most everything you need to create your Newspaper Query!