Help with this (Pat Hartman)

[Dgavilanes]

Hi All,


I have a combo box [Pytable1] with the following values( 01, 02, 09, 15, 23, )

Based on these values when selected I need to update couple other fields [ Position]and [ID_L] provided [EffDate] and [CompDate] are present (Not null)

Now in order for the update to take placed [Lane, II to VI] must be ther to complement the corresponding Paytable.

Hope Pat sees this and helps me out





Lane II and pytble 01 = " Regularly Appointed Teachers" which is letter "A"
Lane II and Pytble 02 = " Full-Time Basis Substitute" which is letter "A"
Lane II and Pytble 15 = " Full-Time Basis Substitute" which is letter "A"

Lane III and Pytable 01 = " Regularly Appointed Teachers" which is letter "B"
Lane III and Pytable 02 = " Full-Time Basis Substitute" which is letter "B"
Lane III and Pytable 15 = " Full-Time Basis Substitute" which is letter "B"

Lane IV and Pytable 01 = " Regularly Appointed Teachers" which is letter "C"
Lane IV and Pytable 02 = " Full-Time Basis Substitute" which is letter "C"
Lane IV and Pytable 15 = " Full-Time Basis Substitute" which is letter "C"

Lane V and Pytable 01 = " Regularly Appointed Teachers" which is letter "D"
Lane V and Pytable 02 = " Full-Time Basis Substitute" which is letter "D"
Lane V and Pytable 15 = " Full-Time Basis Substitute" which is letter "D"

Lane VI and Pytable 01 = " Regularly Appointed Teachers" which is letter "E"
Lane VI and Pytable 02 = " Full-Time Basis Substitute" which is letter "E"
Lane VI and Pytable 15 = " Full-Time Basis Substitute" which is letter "E"

Pytable 09 = " Instructor Assistant" which is letter "F"
Pytable 23 = " Instructor Assistant" which is letter "F"


Hope Pat sees this and helps me out



Dennis

 

[Fizzio]

If you update the fields as shown, you will be duplicating data uneccessarily. How I would achieve this is to use a third table with the combinations already stored (if they are unlikely to change)
You could then store in this third table ony ID's for the combinations eg example 1
store the ID's for Lane II, pytable 01, "Regularly Appointed Teachers" and "A".
If the combinations are too many, you will need to either use an update query or a looping recordset to dynamically update the records but I would still only store the ID's and not the full text.

 

[Dgavilanes]

Re:

thank you


Dennis

 

[Dgavilanes]

Well I understand with his approach and see his point.

The reason I need it to update those fields is because it will populate the query that feeds a monthly report based on a report date.


So the more the codes takes care of it the easier it gets.

So how you would approach this Pat

would like to learn from you.

I'm new when it comes to <code> so anything helps..
Dennis