Relationship Question??

[Sandra P]

I am working with 3 tables for the current scenario: tblClient,
tblResponsibleParty and tblAppointment.

My form for entering or viewing client information is linked to tblClient
and tblResponsibleParty (with a responsible party ID field - one-to-many
relationship) so when I add a new client, I have a drop down to choose the
responsible party (many clients have the same responsible party, for example
Medicaid, which is why there is a separate Responsible Party table).
Therefore, if I view the form for a client, the responsible party information
is also there.

My form for entering a new appointment is linked to tblAppointment,
tblClient and tblResponsibleParty. When I add a new appointment, I have a
drop down to choose the client and once they are selected, the client
information and responsible party information fills the form.

My question is this: Occasionally a client will temporarily have a
different responsible party from their normal one. For example, medicaid
would pay for medical appointments, therefore the responsible party for that
client would be medicaid. If they want to go shopping however, either the
client themselves or perhaps a relative would be the responsible party. When
that occurs, I need to be able to change the responsible party just for that
appointment without it changing the client file.

I hope that makes sense. I can provide further details if it does not.

 

[StrayBullet via AccessMonster.com]

If the responsible party can be different for each appointment, then you
could link the responsible party to the appointment as well as to the client.
The client form would then have their primary responsible party, and the
appointments would each have the responsible party for that specific one. You
could have the appointment responsible party default to the primary one, then
you would only need to change it as needed.