Field & Lookup Table

[rsquared]

I am very new to Access and any help would be appreciated. I have been through the entire Access Bible and many forums but can't find the answer to my specific question.

The project:

I'm setting up a database for tutors and students. Tutors will, via the web, enter data about a specific tutoring session (studentName, subject, sessionLength, etc.) Then at the end of the month a report will be generated detailing hours tutored and total compensation.

The problem:

Students pay varying rates based on subject (math or verbal). So in the Session table each record for each tutoring session has an empty field called payRate. This is the rate the student will pay based on subject. So I need this field to "fill in" automatically based on the 'student' and the 'subject' from the same record. Then I can multiply the field by sessionLength and get a totalPay field for the session. Add these at month's end and pay the tutors.

I've set up a lookup table called 'subject' with three fields (student,subject, payRate) and I have the student and subject combined as the primary key. Am I on the right track here? If so, how do I link the tables to get that Session 'payRate' field to fill in automatically. This is where is breaks down for me. I've tried the lookup wizard but all I seem to get when I open the Session table is an empty PayRate field with a drop down box containing the entire subject lookup table.

Sorry for being so long winded but I need help from someone better at his than me. Thanks in advance.

 

[llkhoutx]

I use recordset queries rather that DLookup, as they are significanty faster.

 

[ScottGem]

First, I don't recommend composite primary keys. They generally are more trouble then they are worth. I recommend using an autonumber as your PK. If you need to insure uniqueness in a combination of fields, then use a unique multi-field index for that.

Second, I would use a DLookup here because you are pulling data from a separate table. What I would do is create a query on your Subject lookup table. In this query, I would set the criteria for Student to the currently selected student info and the same for the subject. To do this I would set the criteria using the following syntax:

=Forms!formname!controlname

This would then filter the query to return just that one record. I would then set the Payrate control using the following code:

Me.txtPayrate = DLookup("[PayRate]","qryPayrates")

You would put this code into an event that makes sure both the student and subject are selected.

I just saw that you posted the same question twice. Please try not to do that. Had I read the other thread I would have answered differently.

Since the payRate is frozen per student and subject, then there is NO need to store the payrate anyplace else. You can pull the payrate by a join to the Subject table.