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!

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





On Nov 5, 7:40*pm, "Allen Browne" <AllenBro...@SeeSig.invalid> wrote:
> Access does indeed sum every record in the report to get the total. That may
> not be what you expect, though it does make sense.
>
> Can you group the report by FruitType? If so, you can create a group header
> (or footer.) In this section, add a text box bound to the Amount field. Set
> its Running Sum property to Over All. Set its Name to something like
> txtAmountRS. This text box will accumulate the amount. Since it does not
> repeat for every detail record, you don't get the duplications.
>
> Allen Browne - Microsoft MVP. Perth, Western Australia
> Tips for Access users -http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Chris Moore" *wrote in message
>
> news:7dc7c89e-b943-4bea-8c24-(E-Mail Removed)...
>
> 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!