Totals & subtotals

[Guest]

Hello All
I have one table where i have patients from different surgeries having
particular operations performed on them. Each surgery has a budget applied to
it. When I produce my report I have a count of the number of patients treated
at each hospital. Eg HOSP1, 11 patient treated, cost $15000, budget will show
$1,100,000 when it should show $100,000. The problem i have is that my
query/report is multiplying the budget figure ($100,000) by the number of
patients treated instead of having an individual budget figure. Could
somebody give me some guidance on how to overcome this? TIA

 

[Guest]

It sounds like a problem I had before. From one table I was trying to
retrieve records from other two tables. Like this:

Table2 <- Table1 -> Table3

If I had only 1 record correspondent in Table2 and 11 records in table 3, I
would have the exact problem you are having now.

Assuming you are running an aggregate query (group by) to get the total sum,
I would suggest to change the Sum(Budget) to First(Budget).

I can only suggest, because I don't know your tables or your queries.

Mauricio Silva - 2005