bobrossi56
asked on
MS Access populate a field based on 2 other form fields
MS Access newbie created a simple Access database used to assign horses to student riders. Fields are Student's FirstName, LastName, HorseName, LessonType, Instructor's FirstName, LastName and LessonFee. When the admin creates a record she will fill in the Student's Name, HorseName, LessonType, Instructor's Name which are combo boxes that look up the drop down data in their respective tables. The Fee field is manually filled in by the admin based on whatever agreement was made between the student and instructor. All the fields are saved so reports can be run.
Now my client wants to add some "matrix logic" so that the Fee field is auto populated based on the LessonType and Instructor Name fields. For example the Fee field logic would go something like:
If Instructor = "Hanny" and LessonType="Western" populate Fee with $15.00
If Instructor = "Doe" and LessonType="Hunter" populate Fee with $60.00
and the 'If Instructor and LessonType' logic would be quite long as they have many Instructors and many LessonTypes. Could be as much as 30 IF lines.
How would I go about making this happen? I created a mock database trying to use some suggestions an expert on a prior ticket gave me but I could not figure it out so he told me to post a new ticket with a database attached, and here it is. I put some of the IF matrix on the form for reference in a label field.
Thx Experts...BobR
HorsreAssign.accdb
Now my client wants to add some "matrix logic" so that the Fee field is auto populated based on the LessonType and Instructor Name fields. For example the Fee field logic would go something like:
If Instructor = "Hanny" and LessonType="Western" populate Fee with $15.00
If Instructor = "Doe" and LessonType="Hunter" populate Fee with $60.00
and the 'If Instructor and LessonType' logic would be quite long as they have many Instructors and many LessonTypes. Could be as much as 30 IF lines.
How would I go about making this happen? I created a mock database trying to use some suggestions an expert on a prior ticket gave me but I could not figure it out so he told me to post a new ticket with a database attached, and here it is. I put some of the IF matrix on the form for reference in a label field.
Thx Experts...BobR
HorsreAssign.accdb
Hi
Well, I think before you do that, you need to have a think about what you want to achieve.
Do you want a single rate for a given instructor and a given lesson type? No possibility for more than one cost for a particular combination?
Your current indexes aren't going to allow you to achieve that. (They're too restrictive, preventing any instructor or lesson type from appearing more than once).
A unique index across both would allow the above scenario.
You then just need to simply lookup the value matching both in the form.
For example attached.
If you wanted more than one possibility, then you'd remove the unique index, or extend it to include the Charge field too.
And provide a combo as you originally had for the charge, to filter the list of options.
Cheers
HorsreAssign.accdb
Well, I think before you do that, you need to have a think about what you want to achieve.
Do you want a single rate for a given instructor and a given lesson type? No possibility for more than one cost for a particular combination?
Your current indexes aren't going to allow you to achieve that. (They're too restrictive, preventing any instructor or lesson type from appearing more than once).
A unique index across both would allow the above scenario.
You then just need to simply lookup the value matching both in the form.
For example attached.
If you wanted more than one possibility, then you'd remove the unique index, or extend it to include the Charge field too.
And provide a combo as you originally had for the charge, to filter the list of options.
Cheers
HorsreAssign.accdb
ASKER
Leigh....only one possibility for instructor and lesson type. I will take a look at your example and see how it works and then get back with you. Thanks SO much...
ASKER
OK, this works GREAT Leigh...but I cannot "see" how you are doing this. When I get the properties for the LESSON FEE field I do not see how this field is being populated.....AND...
I added a new row to the tblInstructor rates which should have populated the LESSON FEE box with $500.00 when I select SMITH and JUMPER but it does not populate it with anything...don't know why but I attached the database. Once I understand how this InstructorID and LessonID matrix works I can add all the other ones myself.
thx much
BobR
HorsreAssignEE.accdb
I added a new row to the tblInstructor rates which should have populated the LESSON FEE box with $500.00 when I select SMITH and JUMPER but it does not populate it with anything...don't know why but I attached the database. Once I understand how this InstructorID and LessonID matrix works I can add all the other ones myself.
thx much
BobR
HorsreAssignEE.accdb
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sweet Leigh...thank you so much!! One more detail/question. I just heard back from my barn manager and she informed me that the LessonFee field was to calculate the lesson fee, but the instructor does not get all that money. For example:
If Instructor = "Hanny" and LessonType="Western" populate Fee with $15.00, (which is the Instructor's earnings), the lesson fee to the student is $60.00
Is there a way using the same logic that you got working to populate another field with the other amount? Would I need another table? Looking at the tblInstructorRates it seems like I would need to maybe add another column there?
thank you
If Instructor = "Hanny" and LessonType="Western" populate Fee with $15.00, (which is the Instructor's earnings), the lesson fee to the student is $60.00
Is there a way using the same logic that you got working to populate another field with the other amount? Would I need another table? Looking at the tblInstructorRates it seems like I would need to maybe add another column there?
thank you
Hi
It depends entirely on how you want to work it.
If it's a fixed percentage, then you could just calculate that at insert time (store the percentage in a table and use it in a calculation in the code). If it's a variable proportion of the amount then, yes, I see no definitive reason not to just add another field to your table. You can look up both fields from tblInstructorRates then, the fee and the proportion they receive.
Cheers
It depends entirely on how you want to work it.
If it's a fixed percentage, then you could just calculate that at insert time (store the percentage in a table and use it in a calculation in the code). If it's a variable proportion of the amount then, yes, I see no definitive reason not to just add another field to your table. You can look up both fields from tblInstructorRates then, the fee and the proportion they receive.
Cheers
ASKER
It's a fixed amount of money.
I'm not sure what you mean there. Fixed amount, for each scenario, for every instructor?
All instructors earn the exact same amount? (If not, it would then be stored against the instructor would it not?)
You know this business best of all. You're going to have to make decisions on how everything is calculated. And to whom it relates.
And details are pivotal. Brevity doesn't achieve a whole lot.
Cheers
All instructors earn the exact same amount? (If not, it would then be stored against the instructor would it not?)
You know this business best of all. You're going to have to make decisions on how everything is calculated. And to whom it relates.
And details are pivotal. Brevity doesn't achieve a whole lot.
Cheers
ASKER
Sorry....it's clear in my head so I should have been clearer for others....the rate would be fixed based on the same criteria as the lesson fee to the student. For example:
If Instructor = "Hanny" and LessonType="Western" populate LessonFee with $65.00 and paid to the instructor $15.00
If Instructor = "Doe" and LessonType="Hunter" populate LessonFee with $75.00 and paid to instructor $20.00
So I was thinking if I just made another field in my tblInstructorRates called StudentOwes, then on the form made another field called txtStudentOwes, all I would have to do it repeat the code you put into the form module and change out the first field name and LessonRate for StudentOwes, right?
Me.txtStudentOwes = DLookup("StudentFee", "tblInstructorRates", "LessonTypeID = " & Me.cboLessonType & " AND InstructorID = " & Me.cboInstructor)
So I tried it and IT WORKS !!! You have been SO MUCH help to me I cannot thank you enough. Thank you for sticking with the question and seeing it through. So many others just hit the question with a quick answer and never look at it again.
If Instructor = "Hanny" and LessonType="Western" populate LessonFee with $65.00 and paid to the instructor $15.00
If Instructor = "Doe" and LessonType="Hunter" populate LessonFee with $75.00 and paid to instructor $20.00
So I was thinking if I just made another field in my tblInstructorRates called StudentOwes, then on the form made another field called txtStudentOwes, all I would have to do it repeat the code you put into the form module and change out the first field name and LessonRate for StudentOwes, right?
Me.txtStudentOwes = DLookup("StudentFee", "tblInstructorRates", "LessonTypeID = " & Me.cboLessonType & " AND InstructorID = " & Me.cboInstructor)
So I tried it and IT WORKS !!! You have been SO MUCH help to me I cannot thank you enough. Thank you for sticking with the question and seeing it through. So many others just hit the question with a quick answer and never look at it again.
Hi
>> So I tried it and IT WORKS !!!
I'm very pleased to hear that. Not just that you got it working, but hat you took what you'd had working and applied it elsewhere. And that you attempted it. The very essence of learning.
Just as a by the way, it seems you've accepted your own, earlier, post as the answer.
Cheers
>> So I tried it and IT WORKS !!!
I'm very pleased to hear that. Not just that you got it working, but hat you took what you'd had working and applied it elsewhere. And that you attempted it. The very essence of learning.
Just as a by the way, it seems you've accepted your own, earlier, post as the answer.
Cheers
ASKER
Closed by accident, need to aware points to Leigh and not myself.
ASKER
All set now....many thanks again.
when it is done, upload a copy of the db.