Query for subsets of data

[Reckonize]

Hi, Access n00bie here. I've got four tables in a database for "Golf
Courses," "Instructor Types," "Instructors," and "Lesson Types."

My goal is the ability to compile user ratings of each Lesson Types.

I'd like to set up a query that gives me a list of the Golf Courses
with dial buttons on the side forcing me to choose only one.

After I make that choice, I'd like it to list the Instructor Types,
again with the dial buttons.

I'd then choose the Instructor Type, then it give me a the list of
Instructors of that Type (at that Course).

It then gives me a list of Lesson Types provided by that Instructor,

I would then choose a Lesson Type, then it would provide with the
average rating (if one is available) for that Lesson, along with a
box for me to enter my own rating (1-5).

I'd like my rating to be stored and impact the average rating for the
next time that Lesson Type is chosen.

I've got the tables set up and am having a hard time with the
queries. At best I've set up a query with some parameters, but I
have to type in my choices (instead of choose them from a list so I
have to know how to spell each choice exactly) and I have to do them
all before I get to a list of Lesson Types. I haven't even begun the
ratings part yet. That's going to be a new table right?

This is within Access 2007. I hope I've given enough info and framed
my question accurately enough to get a response.

Thanks!

 

[Dale Fye]

Without knowing your data structure, it is a little difficult to provide
specific advice. I'd post back here with the names of the fields in each
table.

1. Personally, for what you are attempting, I'd recommend using a list box
or combo box for your Courses. Then, in the afterupdate event of the courses
list or combo box, I would:
a. requery the Instructor Types, based on the value of lstCourses
b. set the value of lstInstrTypes to NULL
c. requery the Instructors list or combo based on the value of
lstCourses and lstInstrTypes.
d. set the value of lstInstructor to NULL
e. requery the Lesson Types list
f. set the value of lstLessonTypes to NULL

2. Then, in the AfterUpdate event of lstInstrType, I'd redo steps c, d, e,
and f from
Step 1.

3. Then, in the AfterUpdate event of lstInstructors, I'd redo steps e and f

4. Finally in the AfterUpdate event of lstLessonTypes, I'd display the
average rating in a textbox or in the caption of a label.

5. Lastly, I'd have a text box for you to enter the value of your rating of
the lesson, and a command button that when you click it, it stores the value
of the new rating for that particular combination of Course, Instructor type,
Instructor, lesson type.

Not sure how you are going to implement this, but you probably also need to
have a way to prevent people from providing multiple ratings for someone, so
that they only get one evaluation per lesson received.

Another issue you might want to consider is that if you are rating on a
scale of 1-10, one person may only use the values 3-7, and another may only
use values of 5-10, so you need to consider some way to standardize these
ratings. As an example, if one of the instructors gets rated by 3 people
that all give the instructor 7's, but 7 is the highest rating they ever give,
and another instructor is rated by 3 people who all give him/her 7's but they
use the rankings from 5-10, then in the first case, that instructor is
probably really good. But in the second case, the instructor is probably
only average.

 

[Reckonize]

Dale, thanks for the help. I think I understand what you're saying.
However, I've got a couple of issues. Is this going to require me using
actual code? I'm not comfortable with that yet. Can it be done without
typing in code?

Fields are as follows:

Golf Course Table:
- ID
- CourseName
- Address
- City
- State
- Zip
- Phone

Instructor Type Table:
- ID
- CourseName
- InstructorType (a text field w/ "Pro," "Apprentice," "Training" as records)

Instructor Name Table:
- ID
- InstructorType
- InstructorLastName

Lesson Type:
- ID
- InstructorLastName
- Lesson Type (a text field w/ "Group," Individual," "Free" as records)

As for the fifth table, I'm trying to construct that with the info from this
query. I think it would be:

Lesson Ratings Table:
- ID
- InstructorLastName
- LessonType
- LessonRating

The average would be calculated in the query, right?

I feel a bit over my head. Hope this makes sense and is doable/fixable.

Thanks!