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

 

[Pat Hartman]

You have two problems:
1. How to update the pay field of existing rows so that all pay fields contain the correct value.
2. How to update the pay field when a new row is saved or a change is made to an existing row.

First make sure that you actually need to store the calculated value. Most times, you do not. Since the Pay rate may change over time, storing this calculation is usually permissable.

To solve the problems, each takes a separate technique:
1. This is best done with an update query. I get the impression that you have written code that you are using to step through the recordset by stepping though the records of a form. This is not appropriate as well as taking a lot of effort. Just create an update query. If the calculations for the different teacher types are too complicated you can create two queries. One that selects contract teachers and updates their pay and the other selects employee teachers and updates their pay.
2. In this case, you will want to put the calculation in the form's BeforeUpdate event. This way, every time the record is changed, the pay field is updated as the record is saved.

 

[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!

 

[Pat Hartman]

Since Pay depends on Class and Step, there is no need to store it. Change the query you use as the RecordSource for the form or report so that it joins to the pay rate table on Class and Step. You can then select Pay from the pay rate table whenever you need it and you won't have to worry about updating the teacher records if the pay rate changes for a given Class and Step. Make sure you set the locked property of Pay to Yes since it will be bound to the pay rate table rather than the employee table and you don't want to accidentally change it. Don't forget to remove the Pay column from the employee table since it is not needed there.

 

[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!!

 

[Pat Hartman]

there is a field for each class ie 1, 2 ,3 ,4, 5...

- that is the problem.

I would like to use an array for the payscale

- if you normalize the pay table properly, NO code is required. All you need to do is to join to the table on the two fields.

Rebuild the table so that instead of having n columns for class, it has n rows. So currently if you have 2 rows - one for contract teachers and a second for employees and each row has 15 columns for class containing the rate for that class, the new table would have 30 rows. Each row would contain a SINGLE pay rate identified by its Class and Step.

 

[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!

 

[Pat Hartman]

That's it. You will have 2 or 3 fields defined as the primary key for this table. I was just guessing what the key would be but the end result is 1 pay rate per row. You need enough key fields to join to the teacher table to obtain the correct rate. Make sure to properly define the primary keys. You should also include an effective date so it is easy to see when a pay rate was last updated. Also make sure you define the relationship in the relationship window and check the box to enforce Referential Integrity but do not select Cascade Delete or Update.

 

[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!!

 

[Pat Hartman]

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

You'll need to write the code to do it. You can import the spreadsheet and create a union query that converts the columns to rows. You can then use that union query to update the existing rate table. Lookup this article on how to update and append in the same query. Make sure to update the last update date with the current date and time.