Avatar of bobrossi56
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
DatabasesMicrosoft Access

Avatar of undefined
Last Comment
bobrossi56

8/22/2022 - Mon
Rey Obrero (Capricorn1)

I suggest that you complete first the entries in your table "tblInstructorRates"
when it is done, upload a copy of the db.
Leigh Purvis

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
bobrossi56

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...
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
bobrossi56

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
ASKER CERTIFIED SOLUTION
Leigh Purvis

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
bobrossi56

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
Leigh Purvis

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
bobrossi56

ASKER
It's a fixed amount of money.
Leigh Purvis

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
bobrossi56

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.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Leigh Purvis

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
bobrossi56

ASKER
Closed by accident, need to aware points to Leigh and not myself.
bobrossi56

ASKER
All set now....many thanks again.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.