Access Report Incorrectly Summing Grouped Data

C

Chris Moore

I have data structured as follows:

AppleTable:

Field1: Apple Categories:
A
B
C

Field2: Apple Category Amounts:
$100
$150
$200

Thus the amount for Apple Category B is $150, etc...


OrangeTable:
Field1: OrangeCategories:
aa
bb
cc
Field2: OrangeCategoryAmounts:
$75
$125
$175
Field3: Apple Category:
A
A
C

Thus one type of Apples can equal multiple types of oranges. In the
above example the total for Apple Category A = $100 but the total for
all the Orange Categories associated with Apple Category A is $200.

I have a report that represents this data. It is grouped by Apple
Category. In each Apple Category the Orange Categories are listed as
details. The Apple Category Footer contains a text box summing the
total amount for all the Orange Categories in that Apple Category as
well as a text box containing the amount for that Apple Category (no
summing needed for that).

The underlying query has Orange Category, Orange Category Amount,
Apple Category and Apple Category Amount. The the amounts in the Apple
Category Amount field are repeated given there are multiple Orange
Categories per Apple Category.

The problem is that when I try to sum the Apple Category Amount in the
report footer I get an amount equal to sum of all the repeated values
in the Apple Category Amount in the query. How can I get just the sum
of the Apple Category Amounts?

Thanks!
 
A

a a r o n . k e m p f

wow, if you were using SQL Server Reporting Services, then you would
have a lot more control over stuff like this
 

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