Total sum to reflect only displayed data on the report

[Guest]

I have four group levels set on a report which gets data from a
table(TABLE1). The
Report is grouped first by project#, then Cap#, PO# and Inv#. Both of Cap#
level and PO# Level has an numeric filed: such as CapAmount in Cap# level,
POAmount in PO# level. One Project# might have multiple Cap#s and one Caps
might have multiple POs. Therefore, the TABLE1 records some repetitive field
information for different rows. However, the report needs to be printed out
the information in group level. If there are three PO#s in one Cap#, the
information such as CapAmount, Capdescriptions related to that Cap# will only
be printed out once in one row instead of three times in three rows.

I understand 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 and report 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.

I tried typing the subquery as below to the sum control in the report footer
and project footer on the report:
SELECT Sum(TABLE1.POAmt) AS SumOfPOAmt
FROM (SELECT TABLE1.[PO#], TABLE1.POAmt FROM TABLE1 GROUP BY TABLE1.[PO#],
TABLE1.[POAmt]);
There is nothing except “#Name?†printed on the final report.

Would anyone please help me out here to get the right sum that only reflect
the displayed data?

Thanks in advance!

Ally

 

[Guest]

Ally:

I, obviously have not given you the answer(s) you were looking for, and I
apologize. Can you zip the db, and email it to me? Even if you just export
Table1 and the report you are trying to build.

I can take a look at it, directly, and see what the problem is.

Again, my apologies. I am sure you are quite frustrated, at this point.
Hopefully, we can get this straightened out, quickly.

Sharkbyte ([email protected])

 

[Larry Linson]

Ally,

I really do not understand what you are describing. In my experience since
Access 1.0, using a Sum in a Group footer sums only and all of what is in
that group. Perhaps I miss a description where you define the "hided lines"
which you believe may be the source of your problem.

One statement you make, that the Cap Amount is included in multiple lines,
would indicate to me that the problem you are having is due to your table
design, not to reporting... that you are indeed, keeping redundant data, and
that likely you need to split that one table into a basic Project Table with
a one to many relationship to a CAP Table, which in turn will have a one to
many relationship to a PO Table, which in turn will have a one to many
relationship to a INV Table.

Grouping and "hide duplicates" in the same report may, indeed, be the source
of the confusion. Certainly, if you sum a column, even if it is not shown in
the lines for some of the details, you can expect those values to be
included in the sum. But, "hide duplicates" is normally used to prevent the
display of duplicate "identifier" or "descriptive" information, not values
subject to being summed.

I haven't been following your exchange in the other thread (and, in fact,
don't know which thread it might be -- that is why it is always best to post
followups to the same thread), but I can suggest that feeding the report
only values that are going to be displayed is always the best approach...
that may require using a Totals Query, or a Unique Values Query.

Sharkbyte is very generous to offer to let you send your database and work
on it for you... few of us who answer questions in the newsgroups can afford
to expend the time and energy to do that on anything like a regular basis.

Larry Linson
Microsoft Access MVP

I have four group levels set on a report which gets data from a
table(TABLE1). The
Report is grouped first by project#, then Cap#, PO# and Inv#. Both of Cap#
level and PO# Level has an numeric filed: such as CapAmount in Cap# level,
POAmount in PO# level. One Project# might have multiple Cap#s and one Caps
might have multiple POs. Therefore, the TABLE1 records some repetitive field
information for different rows. However, the report needs to be printed out
the information in group level. If there are three PO#s in one Cap#, the
information such as CapAmount, Capdescriptions related to that Cap# will only
be printed out once in one row instead of three times in three rows.

I understand 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 and report 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.

I tried typing the subquery as below to the sum control in the report footer
and project footer on the report:
SELECT Sum(TABLE1.POAmt) AS SumOfPOAmt
FROM (SELECT TABLE1.[PO#], TABLE1.POAmt FROM TABLE1 GROUP BY TABLE1.[PO#],
TABLE1.[POAmt]);
There is nothing except "#Name?" printed on the final report.

Would anyone please help me out here to get the right sum that only reflect
the displayed data?

Thanks in advance!

Ally