Re: Report is making too many numbers

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?




.