Re: Report is making too many numbers

Solution that doesn't involve any running sums:

Assuming you have groups in your report like
Vendor
Project with field [Amt] to sum
Project details

And you want to display the sum of [Amt] in the Vendor header or footer
section:

Create a totals query based on the project table that
groups by vendor and sums the [Amt] field.
Add this query to your report's record source and join
the vendor field
Add the [SumOfAmt] field to the record source
Display the [SumOfAmt] in the vendor header or footer.

--
Duane Hookom
MS Access MVP
--

"Jacques" <Jacques Latoison at hotmail dot com> wrote in message
news:OAaozzJOGHA.916@xxxxxxxxxxxxxxxxxxxxxxx
Duane,
You'll have to explain that one. My report is based on a query. The $
amount field sits in the Project group section, which I'm pretty certain
is the right group section. I put the running sum (overall) text box in
there. Is that correct?

I just tried making a new group for the $ amount, and placed the running
sum field in the footer. I then tried the header. Afterwards I change
the group section order about three times (last, in between the other two
sections, then first).

Though they all came closer (only about 1 million dollars off) I'm afraid
that none of them gave me the correct amount.



"Duane Hookom" <duanehookom@xxxxxxxxxxxxxxxxx> wrote in message
news:OnYAncJOGHA.3788@xxxxxxxxxxxxxxxxxxxxxxx
Place a running sum text box in the group section that contains the field
based on the proper table.

--
Duane Hookom
MS Access MVP
--

"Jacques" <Jacques Latoison at hotmail dot com> wrote in message
news:%23hAtLiIOGHA.3936@xxxxxxxxxxxxxxxxxxxxxxx
Maybe I'm not understanding.

My report is grouped by Vendor, then Project (both fields from table A),
then the details section which pulls from table B. The $ amount given
to the Vendor to work at the Project is in the same grouping with the
Project. Doing the below gives me less than the actual total.

I tried to make another sub grouping in the report doing the below, to
the letter, and it gives me that exact same amount as before (both less
than the total). I put the ID (PK) in the group and then the new sub
group which didn't change anything. The running total is still less
than the actual total.

What am I missing? I'm assuming the report is not counting duplicate $
amounts twice, but the ID is in there so I can't say that (I put it in
the source query before putting it in the report so every record shows).


"Allen Browne" <AllenBrowne@xxxxxxxxxxxxxx> wrote in message
news:uRNQ6SIOGHA.1180@xxxxxxxxxxxxxxxxxxxxxxx
Re #1.
You have not chosen the right field to group on. The group footer must
print the section for every record in table A.

Re #2.
A Running Sum text box accumlates its total. It does not make sense to
sum the accumulated values.

--
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.

"Jacques" <Jacques Latoison at hotmail dot com> wrote in message
news:%231255MIOGHA.2472@xxxxxxxxxxxxxxxxxxxxxxx
The query was already set to Total everything but that doesn't make a
difference because both tables have unique values in other fields.

I did what you said in the last paragraph, but two things:
1) I kept the box visible so I could see the running total and
the last instance is far less than the total should be.
2) I am not able to Sum any text box in a report. I can only
Sum the field name of a query or table outside the report. How do I
write =SUM([ReportTextBox]) without it prompting me like a parameter
query?



"Allen Browne" <AllenBrowne@xxxxxxxxxxxxxx> wrote in message
news:%23wuPQ7HOGHA.3144@xxxxxxxxxxxxxxxxxxxxxxx
One record in table A can can many matching records in table B. If
you join them in a query, you will see the record from table A
repeating for each record in table B. If you then sum the column
where the table A records are repeating, you get the sum of the
entire column: it is much more than the sum of the values from table
A, because the table A records occur many times, and so the column
total is way too high.

To avoid that, you need to prevent these repeating records. If you do
not actually need all the B records displayed--just a total for those
records--you could create a Totals query by depressing the Total
button on the toolbar in query design. Access adds a Total row to the
grid. In this Total row, accept Group By under the fields from Table
A, but change all the table B records to Sum. The query now gives you
*one* row for each value in table A, and so the report total will be
correct.

If you do want to list all the B records as well, you might create a
main report based just on table A, and use a subreport to list the
record from table B. Again, the total is correct because it does not
have repeating copies of the record.

Another option is to add a group footer for the primary key of table
A. Use the Sorting and Grouping dialog, on the View menu, and set the
Group Footer to Yes in the lower pane of this dialog. Then add a text
box to this ID Group Footer section, and set the text box's Running
Sum property to Over All. The text box will accumulate the total for
each record in table A, so when you get to the end the running sum
will be showing the right total.

"Jacques" <Jacques Latoison at hotmail dot com> wrote in message
news:OyItJxHOGHA.1124@xxxxxxxxxxxxxxxxxxxxxxx
Hello all,
I've got a problem with Sum Totals at the end of a report, but I'm
unclear if the issue can be resolved in the query or the report.

I have two tables, A and B. A has a one-to-many relationship with
B, but not every record in A has corresponding records in B.

My query has A and B in it and the relationship there states to
Include ALL records from A and only records from B that are equal.
This is giving me every record in both A and B.

My problem is that, a field in A holds Currency, and some records in
A have the same $ amount. B holds several currency fields as well.

In the corresponding report, the grand total in the report footer is
giving me a sum that's way larger that if I summed all the records
in the table by hand (coping the whole $ amount field from the table
into Excel sums to a smaller number, which is correct).

If I generate a report for just table A and not show table B the
grand total in that report footer is correct.

How do I get the report or query to give me all the info, but not
sum extra $ amounts that it is creating out of thin air?










.

Loading