Get 1 total from repeating values

C

cottage6

I have a query and a report using the query that I need help getting totals
on. The query gets records from several asset protection tables involving
theft, etc. There is 1 record per case # for each suspect involved in the
case, so each case # could have several suspects. The report needs to
display a total suspect count for each banner (company has 3 different
locations) which I have working properly just doing a count in the report,
but I'm dead in the water on getting 1 sum per case #. I've tried a lot of
different things between the query and the report to no avail. Example:
case #22 has 5 subjects, total theft amount = 71.77. The 71.77 is multiplied
5 times in the report and I only need it once. HELP! Please be specific if
you're kind enough to help me; I'm very tired this morning and not the
slickest thing when it comes to Access! TIA
 
M

Marshall Barton

cottage6 said:
I have a query and a report using the query that I need help getting totals
on. The query gets records from several asset protection tables involving
theft, etc. There is 1 record per case # for each suspect involved in the
case, so each case # could have several suspects. The report needs to
display a total suspect count for each banner (company has 3 different
locations) which I have working properly just doing a count in the report,
but I'm dead in the water on getting 1 sum per case #. I've tried a lot of
different things between the query and the report to no avail. Example:
case #22 has 5 subjects, total theft amount = 71.77. The 71.77 is multiplied
5 times in the report and I only need it once.


This sounds like you have used Sorting and Grouping on the
Case# field and are using the group footer to sidplay the
Case# totals. The number of suspects would be calculated by
the text box expression =Count(*).

It also sounds like you have another text box with
=Sum(amount) that's displaying the wrong value. In this
situation, there is no need to Sum the amount as it is
already the correct total so just bind the text box to the
Amount field.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top