Very Complex Query Problem...

[Gary B]

Patients
PatientsKey, PatientsName


Doctors
DoctorsKey, DoctorsID


Charges:
ChargesKey, ChargesDate


Services:
ServicesKey, ChargesKey, DoctorsKeyKey, PatCharge, InsCharge


Payments
PaymentsKey, PaymentsDate, PaymentAmount, PatOrInsPmtType(1=PatPmt;2=InsPmt)


AppliedPayments
AppliedPaymentsKey, PaymentsKey, ServicesKey, AmountApplied, PatOrInsPmtType



I want to return as follows:

select DoctorsID, PatientsName, PatDue0To30Days, InsDue0To30Days,
PatDue31To60Days, InsDue31To60Days

Thank you

 

[PC Datasheet]

Gary,

First you need some rework of your tables; that is one reason why you are
having trouble.
A patient receives a service on a certain date form a doctor. There is a
charge for this service. Payment is made by the patient, insurance carrier
or both. The payment is applied against charges. So let's translate what
happens here into tables. You need a patient table - what you have is good.
You need a doctore table - Your table is confusing! You need:
Doctors
DoctorsKey
DoctorName
You need a services table something like
Service
ServiceKey
ServiceDescription
ServiceCharge
You need a ServicePerformed table
ServcePerformed
ServicePerformedKey
PatientKey
DoctorKey
ServiceKey
ServiceDate
(You can eliminate your Charges table - the data is in the ServicePerformed
table)
You need a Billing table:
Billing
BillingKey
ServicePerformedKey
PatOrInsBilledType(1=PatPmt;2=InsPmt)
AmountBilled
You need a payments table - what you have is good
You need a payments applied table - you need:
AppliedPayments
AppliedPaymentsKey
PaymentsKey
BillingKey
AppliedAmount
(you can delete your applied payments table)

With the above tables, you should find creating the query you want to be
relatively easy!