using data from specific record of subform

[BillHesson]

Hello,
I am trying to get data from a specific record in a sub form. and save a result in the same record.

specifically:
I am using the following fields

"teacher type","Class", "Step", "Hours", "Pay"


I have two teacher types, Contract and hourly. a teacher can be either of these or both. I am taking the class and step info from the subform, looking up the data in a table to determine their pay and saving it to the "pay" field.

this works fine as long as I only have one record. if I add the second record the calculation changes bot of the records "Pay" field value to the same thing. what I want to do is have it only calculate the pay for the contract teachers and have it saved to the proper record and the same for the hourly teachers.
I have seperate routines for the calculation of each type.
Any help will be appreciated!!
Bill Hesson
bhesson@guhsd.net
Tech Specialist II
El Cajon Valley High School

 

[BillHesson]

I am using the dlookup function to get data from the underlying table. the pay scale for the teachers is located in a table and depending on the teachers step and class they would get a certain pay. so what I want is if a change is made to the step or class infor the pay field would be updated. the same is true for the hourly teachers except the infor comes from their step and number of hours worked. I think I need to store the data because there are other objects such as reports and such that will need to access the information. I will look at update queries to see if they will help me
Thanks a bunch!!

 

[BillHesson]

trying to make the query in the gui:

all is well except for the dlookup function

Expr1: DLookUp([class],"tblcontractteacherpay","Step =" & [formstep])

the class is a field in the query(this one does not work) the formstep is another field in the query(this one works)
when I put the field name from the tblcontractteacherpay table in place of the class it returns the correct info. it will not accept the field name from the query(data supplied by the query) to fill in the field name?
Thanks!

 

[BillHesson]

Thanks for getting back to me. I will look at this and see if I can do it. I posted a post in queries forum if you could look at it. Thanks!!

 

[BillHesson]

Looking at your post and don't think it will work as there is no field in the payrate table called class, there is a field for each class ie 1, 2 ,3 ,4, 5...
so I do not see how I would join them. also I am using a join table/subform to allow me to have multiple records in the teachertypejoin table. the 2 choices are hourly or contract and each has a different pay scale table??/!@#

I would like to use an array for the payscale and then use variable to call the array elements but not sure how I would fill the array from a table. Just one of the many thoughts on this. I can't sleep at night ARGHHHH
Thanks for all of your help this is a great site!!

 

[BillHesson]

If I understand you correctly there would be 1 field called payID a seconfd field classstep and a third field named Pay and a record for each intersection of the original payscale table ie.
1 Class1StepA $42,547.05
2 Class1StepB $43,228.25

this sounds like it will work. It makes me think of how the user might populate the table when the new pay scale comes out(usually in an excel spreadsheet)? as I will not work here forever and I hope that this database lives on forever(HaHa)
Thanks again!

 

[BillHesson]

any way to automate the filling of the data when the new pay scale comes out?

 

[BillHesson]

This works great!! I learned about the concept of multiple keys but have not used them in so long that I had completely forgoten that they exist. I can sleep again!!