Appointment Report with 4 Source Tables

[ehk]

Crystal 8.5
Data Source: Advantage SQL

I'm designing Appointment Scheduler output for medical offices. Primary table is APPT.DBF for Date, Time, Appt Reason. Have linked by Patient ID with equal join to PATIENT.DBF to display patient name and date of birth.

I would like to display data from 2 additional tables that may or may not have an associated record. Linked both to APPT.DBF by Patient ID with Left Outer Join.

INSURED.DBF should display insurance carrier name for record with ID "1" only, indicating primary carrier. {INSURED.CO_NAME} if {INSURED.ID}='1'. I am getting the primary carrier name on one record and duplicate records with empty carrier name for patients with multiple insurance records.

PATREF.DBF should display referral authorization number from the most recent (last) referral record, if one exists, {PATREF.REF_AUTH}.

Thanks for any help you can provide,

Ellen

 

[Turkbear]

Hi,
Maybe you could try changing the linking to
Patient =>Appt
"" LO>Insured
"" LO>Patref

Group on Patient and the other records should come in to the details, or add a group for each appointment, in case the other info about referrals or insurance changes between appts.






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[ehk]

Thanks for the speedy reply.

I may need to change the join between APPT.DBF and PATIENT.DBF to Left Outer. Had overlooked the fact that some of our users do not connect every Appointment booked to a Patient record.

Example: Patient has not been seen in the office yet. Call to schedule an appointment. Some offices will wait until the patient shows up to build their patient record - thus avoiding 'dead' records because of no-shows. There is, in this case, a record in APPT with no associated records in PATIENT, INSURED or PATREF.

Is what I'm after do-able, still using APPT as my primary table?

 

[Turkbear]

Hi,
If no record exists in APPT for a Patient ID, and that is the 'driving' table, only those patients with an appointment record will be returned.
That is one reason to try using the Patient as the driver..
( You can then use Left Outer for all joins)





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[ehk]

Hello,

That does seem cleaner but I need to include those appointments booked with no associated Patient record in my output since these slots are also scheduled.

Think that APPT needs to remain my 'defining' table.

Thanks, though.