Calculations in report that has One-to-Many relationship

[James Newman]

Hey all,

I have a report based on two tables. Prospects has sales information
that includes a salesman and a final sale total. Activities has
multiple records that relate to all activities required to make the
sale.

I have the report working almost working but it has a calculation
problem. The report calculates a total sales amount for each salesman
and so long as the activities table has only one record that relates
to the prospects table then all totals are correct.

If however there are multiple activites that relate to the prospect
record then the sale total is being added multiple times.


Is there a way to keep this from happening? I know I may not have
explained the problem very well and if more info is necessary I would
be glad to provide it.

Thanks,

James Newman

 

[Duane Hookom]

You can create a totals query with your total of final sales by whatever
field you are wishing to use in your final report. This may be for all
records or whatever. Then, include this query in report's record source
query so you can display the total(s) in your report.