How to get two pieces of selected data in a form to output a certain number?

[PaintTheMoonRed]

I've been working on creating a database for a Physical Therapist that will store patients information and information on past appointments (including the amount of money paid per appointment). I created a form that he can then easily select the patient and their D.O.B. and insurance carrier will show up. But I need to create a way that when he then selects the treatment received for the appointment that the correct payment amount will show up and be recorded as well. The reason this is an issue is because for each insurance there is a different cost per treatment.


I was wondering if this was possible and if so how to do it.
Thanks in advance for any replies.

[June7]

You would need a table of treatments and costs for each insurance. This means multiple records for each treatment.

tblCosts
InsuranceID
TreatmentID
Cost

[pbaldy]

It's certainly possible, but the only way Access can fill in the amount is if you tell it how. Offhand I'm thinking a many-to-many junction table joining tables I presume exist for treatments and insurance carriers. That table would have records for each treatment/insurance combination and have a field for price.

[pbaldy]

Apparently June7 and I had the same idea, I just type slower.