Re: Report is making too many numbers

Provide for us:
1) the sql view and name of your totals query
2) the sql view of your report's record source
3) the section, name, and control source of your control in your report that
is wrong.

--
Duane Hookom
MS Access MVP
--

"Jacques" <Jacques Latoison at hotmail dot com> wrote in message
news:OzeGEXLOGHA.984@xxxxxxxxxxxxxxxxxxxxxxx
I tried the below. I'm afraid it still gives me the same exaggerated
number from the $'s being listed more than once in the query.



"Duane Hookom" <duanehookom@xxxxxxxxxxxxxxxxx> wrote in message
news:euUITaKOGHA.2036@xxxxxxxxxxxxxxxxxxxxxxx
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