Help with design of form with multiple tables

[magicdds-]

I have tried numerous ways of setting up this subform and each one has
presented with shortcomings. Maybe someone has a suggestion of a better way
of designing this:

Form: MAIN has each patient's personal information
Subform: Should list the referral sources for that patient
While some patients have one referral source, some patients may have been
referred by multiple sources.

Possible referral sources are listed in the following tables:
InsuranceCompanies
Dentists
Patients - a patient may have been referred by 2 or 3 other patients

The subform is based on a table REFERRALSOURCES. There is a one to many
relationship of patients to referral sources.

The user should be able to go to the subform and select (perhaps from combo
box)
which type of referral source and then which referral source from the
resulting list. There should also be a misc. option so if the referrer is not
an insurancecompany, dentist, or patient, the user can type in their own
(like they saw an advertisement).

Regardless of which table the referrer was selected from, a join needs to be
made from the REFERRALSOURCE table, to the other tables, so if you need to
use the referral source's address (like the dentist's or referring patients'
address') to send a letter to the referral source, the address is readily
available to the lettermerge query. The referral source information will also
be used make a report to display how many patients each referrer referred
during a particular time period.

The reason I am thinking of using a subform on the MAIN form is this way the
user, when looking at the patient's personal information, can also see who
referred them.

The main problems I am having is that since the referrers are listed in 3
tables, I can't figure out how to connect everything together efficiently.
Maybe there is a better setup!

Any ideas would be greatly appreciated.
Thanks
Mark

 

[Armen Stein]

The main problems I am having is that since the referrers are listed in 3
tables, I can't figure out how to connect everything together efficiently.

Check out UNION queries. A Union will allow you to list referrals
from the three different tables.

You must use SQL view to build the Union query, but you can use Design
view to build the three separate Select queries and then paste their
SQL into the Union query. Just make sure your Select statements have
the same column layout. If you need to distinguish between the
different types of referrals, you can specify text in an extra
calculated field named something like ReferralSource.

The Union query won't be updatable, but you can drill down to the
appropriate detail record by opening it in another form.

Hope this helps get you started,

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Evi]

You could use this
TblReferralType
RefTypeID
ReferralType eg Dentist, Doctor

TblReferree
RefID
RefName
RefAddress
RefTypeID (linked from TblReferralType)

TblPatientReferral
PatRefID
PatientID - linked from tblPatient's Primary key
RefID linked from tblReferee
ReferalDate (if a patient can be referred on more than one occasion)

Evi