How to total the numeric number if it's repeating after grouping

[Ally]

I have three group levels in a report which gets data from a table. The
report is grouped first by project#, then Cap# and PO# and there is an
numeric filed in both Cap# group and PO# group. For example, there is
CapAmount related to Cap# and POAmount related to PO#. One Project# might
have multiple Cap# and one Cap# might have multiple PO#. I know that I can
set HideDuplicates property to Yes or simply use grouped level in the report
to prevent duplicate data from being printed on a grouped data. However, the
total sum of the amount in each level (CapAmount and POAmount) was not
correct using =sum([CapAmount]) and sum([POAmount]) at the project footer.
It's way larger than it's supposed to be and it seems that those hided lines
with numeric fields are still be counted, which causing the final result way
larger than what they are supposed to be. Would you please let me know what's
the best way to solve this? How should I only sum the numeric field in the
group level not record level? Thanks a lot!!!

 

[Sharkbyte]

You will need something like this:

SELECT Sum(POAmount) AS SumOfPOAmount FROM (SELECT POAmount FROM Table1
GROUP BY POAmount);

Use the same subquery structure for your CapAmount.

HTH

Sharkbyte

 

[Ally]

Sharkbyte,

Thanks for your prompt response and suggestions! I guess I don't know where
I should put the subquery structure in. Should I put it in the report
somewhere or build a new query. If it's a new query, what should I do next?
how do I use it in the report? Sorry that I am still a pretty new user on
Access.... I would really appreciate it if you could give me more hints and
instructions.

Thanks!

You will need something like this:

SELECT Sum(POAmount) AS SumOfPOAmount FROM (SELECT POAmount FROM Table1
GROUP BY POAmount);

Use the same subquery structure for your CapAmount.

HTH

Sharkbyte

I have three group levels in a report which gets data from a table. The
report is grouped first by project#, then Cap# and PO# and there is an
numeric filed in both Cap# group and PO# group. For example, there is
CapAmount related to Cap# and POAmount related to PO#. One Project# might
have multiple Cap# and one Cap# might have multiple PO#. I know that I can
set HideDuplicates property to Yes or simply use grouped level in the report
to prevent duplicate data from being printed on a grouped data. However, the
total sum of the amount in each level (CapAmount and POAmount) was not
correct using =sum([CapAmount]) and sum([POAmount]) at the project footer.
It's way larger than it's supposed to be and it seems that those hided lines
with numeric fields are still be counted, which causing the final result way
larger than what they are supposed to be. Would you please let me know what's
the best way to solve this? How should I only sum the numeric field in the
group level not record level? Thanks a lot!!!