Problems Summing

[Caryn]

I have a database with a few relationships in it. The patient and their
dependants are in two separate tables and a rate of Fee is in another. I
have a report which lists the patient and fee with their dependants listed
underneath. However, when try to put a sum field in the report header it
adds the fee for each dependant. How do set it up to add the fee once per
patient?

 

[golfinray]

Sum it in the footer.
controlsource=sum([patient])

 

[Caryn]

It didn't work. It's still mutiplying the fee against the dependants instead
of the patient.

Sum it in the footer.
controlsource=sum([patient])
--
Milton Purdy
ACCESS
State of Arkansas

I have a database with a few relationships in it. The patient and their
dependants are in two separate tables and a rate of Fee is in another. I
have a report which lists the patient and fee with their dependants listed
underneath. However, when try to put a sum field in the report header it
adds the fee for each dependant. How do set it up to add the fee once per
patient?

 

[Duane Hookom]

If your report displays only a single patient then you just bind the field to
a text box with no summing. If you need to sum multiple values from a "group"
of records, I would suggest you create a query that sums the value by group
and then add the totals query to the report's record source query.

 

[Jeff Boyce]

Please post the SQL statement you are using to "feed" your report.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Adam Simpson]

You could put a new text box called RS in the the patient header section of
the report and make it a running summ and make it =Fee. Then in the report
footer put a text box called say RST and make the control source =RS. Then
in the report header the controle source of your total field should be =RST.

Alternatively you could create a second query that just summs the fees for
the patients and reference that in the report header. This could be a DSum
if the criteria needed are simple.

Adam