combining 3 tables into one report

[Guest]

I have a table for dental charges that I want the report to print in full. I also have a table for patient payments that are tied to the dental charges that I need to have print if there are payments for that charge. I also have a table for insurance payments tied to the dental charges that need to print if there are payments for the charge. I need to have a grouping on the service date. I also need to have the dental charges print even if there are no associated payments.

I can get the charges to print along with the patient payments, but the charges only print if there is an associated patient payment.

How do I get the dental charges to print even when there are no associated payments?

 

[Fons Ponsioen]

I appears that your query design needs to be fine tuned.
What you need to do is set the relations between the
tables in your query to include all patient-charge records
and only those of the patient-payment records where the
treatment ID is the same. If you need help doing that
give us some more specific information or possibly print
the sql (you can get that when you are in the desing view
for the query and from the view meny select sql.
Hope this gets you going.
Fons

-----Original Message-----
I have a table for dental charges that I want the report

to print in full. I also have a table for patient
payments that are tied to the dental charges that I need
to have print if there are payments for that charge. I
also have a table for insurance payments tied to the
dental charges that need to print if there are payments
for the charge. I need to have a grouping on the service
date. I also need to have the dental charges print even
if there are no associated payments.

I can get the charges to print along with the patient

payments, but the charges only print if there is an
associated patient payment.

 

[Marshall Barton]

I have a table for dental charges that I want the report to print in full. I also have a table for patient payments that are tied to the dental charges that I need to have print if there are payments for that charge. I also have a table for insurance payments tied to the dental charges that need to print if there are payments for the charge. I need to have a grouping on the service date. I also need to have the dental charges print even if there are no associated payments.

I can get the charges to print along with the patient payments, but the charges only print if there is an associated patient payment.

How do I get the dental charges to print even when there are no associated payments?

Change the report's record source query to use an outer join
instead of an inner join.

 

[Guest]

Change the report's record source query to use an outer join
instead of an inner join.

That worked great! I used a left join. The only trouble
now is that since there are some of the dental
transactions that don't have payments associated with
them the patient payment field in the joined record will
come up as null and won't total for me. How should I
handle this?

 

[Marshall Barton]

Change the report's record source query to use an outer

That worked great! I used a left join. The only trouble
now is that since there are some of the dental
transactions that don't have payments associated with
them the patient payment field in the joined record will
come up as null and won't total for me. How should I
handle this?

Depends on how/where you're calculating the total.

If it's in a group footer or the report footer, then you can
use the Sum function, which ignores Null values.

If you're using a RunningSum text box, then set its
ControlSource expression to =Nz(paymentfield,0) and make
sure the text box is named something other than
paymentfield.

If you're doing it some other way, please provide more
information.

 

[Guest]

I am totaling in the group footer. I am using the =sum()
function but still can't seem to get a total in the group
footer when there is a null value in the paymentfield. I
must have something else wrong. I will go back and
review all of my parameters and if it still won't work I
will post again with a more detailed explanation of
exactly what transpires and how I am handling it and see
where it takes us then. Thanks for the help