Report is making too many numbers

[Jacques]

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?


Thanks beforehand,

 

[Duane Hookom]

I expect it's the fields from A that are too high. If A has a record with a
value of $10 and there are 3 related/child records in B then you are
probably totaling this field to $30. This makes sense.

You can place the fields from A in a group header. Assuming the field is
[Shipping] create a text box like:
Name: txtShippingRunSum
Control Source: [Shipping]
Running Sum: Over All
Visible: No

Then add a text box to your report footer:
Control Source: =txtShippingRunSum

 

[Jacques]

Too add to the below, my query is what's making extra records (and I don't
know why).

 

[Allen Browne]

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]

Duane,
I have the fields from Group A in a header (two separate headers
actually - because its grouped by two pieces of info from table A), and I
need that [Shipping] field to show. Maybe I'm not understanding the
response.
I tried the below but it changes the $'s in the field when viewing the
report.

I expect it's the fields from A that are too high. If A has a record with a
value of $10 and there are 3 related/child records in B then you are
probably totaling this field to $30. This makes sense.

You can place the fields from A in a group header. Assuming the field is
[Shipping] create a text box like:
Name: txtShippingRunSum
Control Source: [Shipping]
Running Sum: Over All
Visible: No

Then add a text box to your report footer:
Control Source: =txtShippingRunSum

--
Duane Hookom
MS Access MVP
--

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?


Thanks beforehand,

 

[Jacques]

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]

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.

Reply to group, rather than allenbrowne at mvps dot org.

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?

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]

Is it possible to Sum a Text box in a report. I've only been able to use
Sum on a query or table name outside of a report. It would probably be fine
if I could make a text box Sum another text box by the text box name.

I expect it's the fields from A that are too high. If A has a record with a
value of $10 and there are 3 related/child records in B then you are
probably totaling this field to $30. This makes sense.

You can place the fields from A in a group header. Assuming the field is
[Shipping] create a text box like:
Name: txtShippingRunSum
Control Source: [Shipping]
Running Sum: Over All
Visible: No

Then add a text box to your report footer:
Control Source: =txtShippingRunSum

--
Duane Hookom
MS Access MVP
--

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?


Thanks beforehand,

 

[Jacques]

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

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.

Reply to group, rather than allenbrowne at mvps dot org.

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?

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

 

[Jacques]

Hey, I didn't do anything wrong did I?

No one has answered me back, and I could really use the help (as this is
already behind schedule).

I expect it's the fields from A that are too high. If A has a record with a
value of $10 and there are 3 related/child records in B then you are
probably totaling this field to $30. This makes sense.

You can place the fields from A in a group header. Assuming the field is
[Shipping] create a text box like:
Name: txtShippingRunSum
Control Source: [Shipping]
Running Sum: Over All
Visible: No

Then add a text box to your report footer:
Control Source: =txtShippingRunSum

--
Duane Hookom
MS Access MVP
--

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?


Thanks beforehand,

 

[Duane Hookom]

Place a running sum text box in the group section that contains the field
based on the proper table.

--
Duane Hookom
MS Access MVP
--

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

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.

Reply to group, rather than allenbrowne at mvps dot org.

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?



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

 

[Jacques]

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.

Place a running sum text box in the group section that contains the field
based on the proper table.

--
Duane Hookom
MS Access MVP
--

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

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.

Reply to group, rather than allenbrowne at mvps dot org.

"Jacques" <Jacques Latoison at hotmail dot com> wrote in message
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?



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

 

[Duane Hookom]

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

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.

Place a running sum text box in the group section that contains the field
based on the proper table.

--
Duane Hookom
MS Access MVP
--

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


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.

Reply to group, rather than allenbrowne at mvps dot org.

"Jacques" <Jacques Latoison at hotmail dot com> wrote in message
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?



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

 

[Jacques]

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.

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

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.

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


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.

Reply to group, rather than allenbrowne at mvps dot org.

"Jacques" <Jacques Latoison at hotmail dot com> wrote in message
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?



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

 

[Duane Hookom]

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

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.

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

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.



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


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.

Reply to group, rather than allenbrowne at mvps dot org.

"Jacques" <Jacques Latoison at hotmail dot com> wrote in message
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?



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

 

[Jacques]

Here ya go. Your first eval of the situation is correct though,

Duane Hookom wrote:
I expect it's the fields from A that are too high. If A has a record with a
value of $10 and there are 3 related/child records in B then you are
probably totaling this field to $30. This makes sense.

---------The above statement is the issue. What you asked for is below.

Provide for us:
1) the sql view and name of your totals query

SELECT LongSheetPrimContInfo.PrimeCont, LongSheetPrimContInfo.Site,
LongSheetPrimContInfo.Classification, LongSheetPrimContInfo.Trade,
LongSheetPrimContInfo.Award, LongSheetPrimContInfo.NOD,
LongSheetPrimContInfo.PercentComplete, LongSheetSubContInfo.MBEWBESub,
LongSheetSubContInfo.SubTrade, Sum(CDbl(Nz([CMA],0))) AS CommMBEAmt,
Sum(CDbl(Nz([CMP],0))) AS CommMBEPer, Sum(CDbl(Nz([CwA],0))) AS CommWBEAmt,
Sum(CDbl(Nz([CWP],0))) AS CommWBEPer, Sum(CDbl(Nz([CDA],0))) AS
CommDSDBEAmt, Sum(CDbl(Nz([CDP],0))) AS CommDSDBEPer, Sum(CDbl(Nz([DMA],0)))
AS DocuMBEAmt, Sum(CDbl(Nz([DMP],0))) AS DocuMBEPer, Sum(CDbl(Nz([DWA],0)))
AS DocuWBEAmt, Sum(CDbl(Nz([DMP],0))) AS DocuWBEPer, Sum(CDbl(Nz([DDA],0)))
AS DocuDSDBEAmt, Sum(CDbl(Nz([DDP],0))) AS DocuDSDBEPer,
Sum(CDbl(Nz([DLA],0))) AS DocuLBEAmt, Sum(CDbl(Nz([DLP],0))) AS DocuLBEPer
FROM LongSheetPrimContInfo LEFT JOIN LongSheetSubContInfo ON
LongSheetPrimContInfo.IDLSPrimeContInfo =
LongSheetSubContInfo.IDLSPrimeContInfo
GROUP BY LongSheetPrimContInfo.PrimeCont, LongSheetPrimContInfo.Site,
LongSheetPrimContInfo.Classification, LongSheetPrimContInfo.Trade,
LongSheetPrimContInfo.Award, LongSheetPrimContInfo.NOD,
LongSheetPrimContInfo.PercentComplete, LongSheetSubContInfo.MBEWBESub,
LongSheetSubContInfo.SubTrade
ORDER BY LongSheetPrimContInfo.PrimeCont, LongSheetPrimContInfo.Site;

2) the sql view of your report's record source

The above query is the report's record source (which pulls from two tables)

3) the section, name, and control source of your control in your report
that is wrong.

Section: ReportFooter
Name: TEXT172
ControlSource: =SUM([Award])

 

[Duane Hookom]

Was I correct in your report structure?
Ithought I was fairly clear in the sql of the first totals query that should
not include detail records. It should only be from the table where the Award
is stored. You included two tables. If you only wanted to total the award
field in the query, why include all those other fields?

You didn't provide the name of the totals query as I asked and you didn't
provide the SQL view of the report's record source which must be different
from the first totals query.

When you have the above set up, you don't Sum the award field since it
should be summed in the query.

--
Duane Hookom
MS Access MVP
--

Here ya go. Your first eval of the situation is correct though,

Duane Hookom wrote:
I expect it's the fields from A that are too high. If A has a record with
a
value of $10 and there are 3 related/child records in B then you are
probably totaling this field to $30. This makes sense.

---------The above statement is the issue. What you asked for is below.

Provide for us:
1) the sql view and name of your totals query

SELECT LongSheetPrimContInfo.PrimeCont, LongSheetPrimContInfo.Site,
LongSheetPrimContInfo.Classification, LongSheetPrimContInfo.Trade,
LongSheetPrimContInfo.Award, LongSheetPrimContInfo.NOD,
LongSheetPrimContInfo.PercentComplete, LongSheetSubContInfo.MBEWBESub,
LongSheetSubContInfo.SubTrade, Sum(CDbl(Nz([CMA],0))) AS CommMBEAmt,
Sum(CDbl(Nz([CMP],0))) AS CommMBEPer, Sum(CDbl(Nz([CwA],0))) AS
CommWBEAmt, Sum(CDbl(Nz([CWP],0))) AS CommWBEPer, Sum(CDbl(Nz([CDA],0)))
AS CommDSDBEAmt, Sum(CDbl(Nz([CDP],0))) AS CommDSDBEPer,
Sum(CDbl(Nz([DMA],0))) AS DocuMBEAmt, Sum(CDbl(Nz([DMP],0))) AS
DocuMBEPer, Sum(CDbl(Nz([DWA],0))) AS DocuWBEAmt, Sum(CDbl(Nz([DMP],0)))
AS DocuWBEPer, Sum(CDbl(Nz([DDA],0))) AS DocuDSDBEAmt,
Sum(CDbl(Nz([DDP],0))) AS DocuDSDBEPer, Sum(CDbl(Nz([DLA],0))) AS
DocuLBEAmt, Sum(CDbl(Nz([DLP],0))) AS DocuLBEPer
FROM LongSheetPrimContInfo LEFT JOIN LongSheetSubContInfo ON
LongSheetPrimContInfo.IDLSPrimeContInfo =
LongSheetSubContInfo.IDLSPrimeContInfo
GROUP BY LongSheetPrimContInfo.PrimeCont, LongSheetPrimContInfo.Site,
LongSheetPrimContInfo.Classification, LongSheetPrimContInfo.Trade,
LongSheetPrimContInfo.Award, LongSheetPrimContInfo.NOD,
LongSheetPrimContInfo.PercentComplete, LongSheetSubContInfo.MBEWBESub,
LongSheetSubContInfo.SubTrade
ORDER BY LongSheetPrimContInfo.PrimeCont, LongSheetPrimContInfo.Site;

2) the sql view of your report's record source

The above query is the report's record source (which pulls from two
tables)

3) the section, name, and control source of your control in your report
that is wrong.

Section: ReportFooter
Name: TEXT172
ControlSource: =SUM([Award])

 

[Jacques]

Was I correct in your report structure?
Ithought I was fairly clear in the sql of the first totals query that
should not include detail records. It should only be from the table where
the Award is stored. You included two tables. If you only wanted to total
the award field in the query, why include all those other fields?

The first table contains all the info for the Contractors, name, site
worked, work type, and contract AWARD amount.
The second table contains all the sub-contractors that worked for them on
that site.

The report must total the award amounts per Prime Contractor (totaling all
the sites they worked) and then show a grand total of all Primes combined at
the end of the report (because all the sites and contractors work for one
company).

Everything works BUT the totalling of the AWARD in any group section.

You didn't provide the name of the totals query as I asked

LongSheet-ContractorFull

and you didn't

provide the SQL view of the report's record source which must be different
from the first totals query.

There's only one query (in the original design - not counting the changes I
made - to a copy of the DB -that you asked me to make earlier)
When I go to the report's record source it is

LongSheet-ContractorFull and the SQL is what I sent

SELECT LongSheetPrimContInfo.PrimeCont, LongSheetPrimContInfo.Site,
LongSheetPrimContInfo.Classification, LongSheetPrimContInfo.Trade,
LongSheetPrimContInfo.Award, LongSheetPrimContInfo.NOD,
LongSheetPrimContInfo.PercentComplete, LongSheetSubContInfo.MBEWBESub,
LongSheetSubContInfo.SubTrade, Sum(CDbl(Nz([CMA],0))) AS CommMBEAmt,
Sum(CDbl(Nz([CMP],0))) AS CommMBEPer, Sum(CDbl(Nz([CwA],0))) AS CommWBEAmt,
Sum(CDbl(Nz([CWP],0))) AS CommWBEPer, Sum(CDbl(Nz([CDA],0))) AS
CommDSDBEAmt, Sum(CDbl(Nz([CDP],0))) AS CommDSDBEPer, Sum(CDbl(Nz([DMA],0)))
AS DocuMBEAmt, Sum(CDbl(Nz([DMP],0))) AS DocuMBEPer, Sum(CDbl(Nz([DWA],0)))
AS DocuWBEAmt, Sum(CDbl(Nz([DMP],0))) AS DocuWBEPer, Sum(CDbl(Nz([DDA],0)))
AS DocuDSDBEAmt, Sum(CDbl(Nz([DDP],0))) AS DocuDSDBEPer,
Sum(CDbl(Nz([DLA],0))) AS DocuLBEAmt, Sum(CDbl(Nz([DLP],0))) AS DocuLBEPer
FROM LongSheetPrimContInfo LEFT JOIN LongSheetSubContInfo ON
LongSheetPrimContInfo.IDLSPrimeContInfo =
LongSheetSubContInfo.IDLSPrimeContInfo
GROUP BY LongSheetPrimContInfo.PrimeCont, LongSheetPrimContInfo.Site,
LongSheetPrimContInfo.Classification, LongSheetPrimContInfo.Trade,
LongSheetPrimContInfo.Award, LongSheetPrimContInfo.NOD,
LongSheetPrimContInfo.PercentComplete, LongSheetSubContInfo.MBEWBESub,
LongSheetSubContInfo.SubTrade
ORDER BY LongSheetPrimContInfo.PrimeCont, LongSheetPrimContInfo.Site;

If I'm mistunderstanding what you're asking for, or just don't know, I
apologize beforehand.

When you have the above set up, you don't Sum the award field since it
should be summed in the query.

I don't Sum the AWARD field in any section except the Report Footer. I
tried to Sum the Award field in the PrimCont section of the report to see if
the math was correct, but it wasn't

--
Duane Hookom
MS Access MVP
--

Here ya go. Your first eval of the situation is correct though,

Duane Hookom wrote:
I expect it's the fields from A that are too high. If A has a record with
a
value of $10 and there are 3 related/child records in B then you are
probably totaling this field to $30. This makes sense.

---------The above statement is the issue. What you asked for is below.

Provide for us:
1) the sql view and name of your totals query

SELECT LongSheetPrimContInfo.PrimeCont, LongSheetPrimContInfo.Site,
LongSheetPrimContInfo.Classification, LongSheetPrimContInfo.Trade,
LongSheetPrimContInfo.Award, LongSheetPrimContInfo.NOD,
LongSheetPrimContInfo.PercentComplete, LongSheetSubContInfo.MBEWBESub,
LongSheetSubContInfo.SubTrade, Sum(CDbl(Nz([CMA],0))) AS CommMBEAmt,
Sum(CDbl(Nz([CMP],0))) AS CommMBEPer, Sum(CDbl(Nz([CwA],0))) AS
CommWBEAmt, Sum(CDbl(Nz([CWP],0))) AS CommWBEPer, Sum(CDbl(Nz([CDA],0)))
AS CommDSDBEAmt, Sum(CDbl(Nz([CDP],0))) AS CommDSDBEPer,
Sum(CDbl(Nz([DMA],0))) AS DocuMBEAmt, Sum(CDbl(Nz([DMP],0))) AS
DocuMBEPer, Sum(CDbl(Nz([DWA],0))) AS DocuWBEAmt, Sum(CDbl(Nz([DMP],0)))
AS DocuWBEPer, Sum(CDbl(Nz([DDA],0))) AS DocuDSDBEAmt,
Sum(CDbl(Nz([DDP],0))) AS DocuDSDBEPer, Sum(CDbl(Nz([DLA],0))) AS
DocuLBEAmt, Sum(CDbl(Nz([DLP],0))) AS DocuLBEPer
FROM LongSheetPrimContInfo LEFT JOIN LongSheetSubContInfo ON
LongSheetPrimContInfo.IDLSPrimeContInfo =
LongSheetSubContInfo.IDLSPrimeContInfo
GROUP BY LongSheetPrimContInfo.PrimeCont, LongSheetPrimContInfo.Site,
LongSheetPrimContInfo.Classification, LongSheetPrimContInfo.Trade,
LongSheetPrimContInfo.Award, LongSheetPrimContInfo.NOD,
LongSheetPrimContInfo.PercentComplete, LongSheetSubContInfo.MBEWBESub,
LongSheetSubContInfo.SubTrade
ORDER BY LongSheetPrimContInfo.PrimeCont, LongSheetPrimContInfo.Site;

2) the sql view of your report's record source

The above query is the report's record source (which pulls from two
tables)

3) the section, name, and control source of your control in your report
that is wrong.

Section: ReportFooter
Name: TEXT172
ControlSource: =SUM([Award])

 

[Duane Hookom]

Your first totals query should be only:
SELECT LongSheetPrimContInfo.PrimeCont,
Sum(LongSheetPrimContInfo.Award) as SumOfAward
FROM LongSheetPrimContInfo
GROUP BY LongSheetPrimContInfo.PrimeCont
ORDER BY LongSheetPrimContInfo.PrimeCont;
This should calculate all the Awards values for a single contractor/vendor,
right? If you add this query to your report's record source query, you
should have the number [SumOfAward] ready to stick in your contractor header
or footer section.


--
Duane Hookom
MS Access MVP
--

Was I correct in your report structure?
Ithought I was fairly clear in the sql of the first totals query that
should not include detail records. It should only be from the table where
the Award is stored. You included two tables. If you only wanted to total
the award field in the query, why include all those other fields?

The first table contains all the info for the Contractors, name, site
worked, work type, and contract AWARD amount.
The second table contains all the sub-contractors that worked for them on
that site.

The report must total the award amounts per Prime Contractor (totaling all
the sites they worked) and then show a grand total of all Primes combined
at the end of the report (because all the sites and contractors work for
one company).

Everything works BUT the totalling of the AWARD in any group section.

You didn't provide the name of the totals query as I asked

LongSheet-ContractorFull

and you didn't

provide the SQL view of the report's record source which must be
different from the first totals query.

There's only one query (in the original design - not counting the changes
I made - to a copy of the DB -that you asked me to make earlier)
When I go to the report's record source it is

LongSheet-ContractorFull and the SQL is what I sent

SELECT LongSheetPrimContInfo.PrimeCont, LongSheetPrimContInfo.Site,
LongSheetPrimContInfo.Classification, LongSheetPrimContInfo.Trade,
LongSheetPrimContInfo.Award, LongSheetPrimContInfo.NOD,
LongSheetPrimContInfo.PercentComplete, LongSheetSubContInfo.MBEWBESub,
LongSheetSubContInfo.SubTrade, Sum(CDbl(Nz([CMA],0))) AS CommMBEAmt,
Sum(CDbl(Nz([CMP],0))) AS CommMBEPer, Sum(CDbl(Nz([CwA],0))) AS
CommWBEAmt, Sum(CDbl(Nz([CWP],0))) AS CommWBEPer, Sum(CDbl(Nz([CDA],0)))
AS CommDSDBEAmt, Sum(CDbl(Nz([CDP],0))) AS CommDSDBEPer,
Sum(CDbl(Nz([DMA],0))) AS DocuMBEAmt, Sum(CDbl(Nz([DMP],0))) AS
DocuMBEPer, Sum(CDbl(Nz([DWA],0))) AS DocuWBEAmt, Sum(CDbl(Nz([DMP],0)))
AS DocuWBEPer, Sum(CDbl(Nz([DDA],0))) AS DocuDSDBEAmt,
Sum(CDbl(Nz([DDP],0))) AS DocuDSDBEPer, Sum(CDbl(Nz([DLA],0))) AS
DocuLBEAmt, Sum(CDbl(Nz([DLP],0))) AS DocuLBEPer
FROM LongSheetPrimContInfo LEFT JOIN LongSheetSubContInfo ON
LongSheetPrimContInfo.IDLSPrimeContInfo =
LongSheetSubContInfo.IDLSPrimeContInfo
GROUP BY LongSheetPrimContInfo.PrimeCont, LongSheetPrimContInfo.Site,
LongSheetPrimContInfo.Classification, LongSheetPrimContInfo.Trade,
LongSheetPrimContInfo.Award, LongSheetPrimContInfo.NOD,
LongSheetPrimContInfo.PercentComplete, LongSheetSubContInfo.MBEWBESub,
LongSheetSubContInfo.SubTrade
ORDER BY LongSheetPrimContInfo.PrimeCont, LongSheetPrimContInfo.Site;

If I'm mistunderstanding what you're asking for, or just don't know, I
apologize beforehand.

When you have the above set up, you don't Sum the award field since it
should be summed in the query.

I don't Sum the AWARD field in any section except the Report Footer. I
tried to Sum the Award field in the PrimCont section of the report to see
if the math was correct, but it wasn't

--
Duane Hookom
MS Access MVP
--

Here ya go. Your first eval of the situation is correct though,

Duane Hookom wrote:
I expect it's the fields from A that are too high. If A has a record
with a
value of $10 and there are 3 related/child records in B then you are
probably totaling this field to $30. This makes sense.

---------The above statement is the issue. What you asked for is below.

Provide for us:
1) the sql view and name of your totals query

SELECT LongSheetPrimContInfo.PrimeCont, LongSheetPrimContInfo.Site,
LongSheetPrimContInfo.Classification, LongSheetPrimContInfo.Trade,
LongSheetPrimContInfo.Award, LongSheetPrimContInfo.NOD,
LongSheetPrimContInfo.PercentComplete, LongSheetSubContInfo.MBEWBESub,
LongSheetSubContInfo.SubTrade, Sum(CDbl(Nz([CMA],0))) AS CommMBEAmt,
Sum(CDbl(Nz([CMP],0))) AS CommMBEPer, Sum(CDbl(Nz([CwA],0))) AS
CommWBEAmt, Sum(CDbl(Nz([CWP],0))) AS CommWBEPer, Sum(CDbl(Nz([CDA],0)))
AS CommDSDBEAmt, Sum(CDbl(Nz([CDP],0))) AS CommDSDBEPer,
Sum(CDbl(Nz([DMA],0))) AS DocuMBEAmt, Sum(CDbl(Nz([DMP],0))) AS
DocuMBEPer, Sum(CDbl(Nz([DWA],0))) AS DocuWBEAmt, Sum(CDbl(Nz([DMP],0)))
AS DocuWBEPer, Sum(CDbl(Nz([DDA],0))) AS DocuDSDBEAmt,
Sum(CDbl(Nz([DDP],0))) AS DocuDSDBEPer, Sum(CDbl(Nz([DLA],0))) AS
DocuLBEAmt, Sum(CDbl(Nz([DLP],0))) AS DocuLBEPer
FROM LongSheetPrimContInfo LEFT JOIN LongSheetSubContInfo ON
LongSheetPrimContInfo.IDLSPrimeContInfo =
LongSheetSubContInfo.IDLSPrimeContInfo
GROUP BY LongSheetPrimContInfo.PrimeCont, LongSheetPrimContInfo.Site,
LongSheetPrimContInfo.Classification, LongSheetPrimContInfo.Trade,
LongSheetPrimContInfo.Award, LongSheetPrimContInfo.NOD,
LongSheetPrimContInfo.PercentComplete, LongSheetSubContInfo.MBEWBESub,
LongSheetSubContInfo.SubTrade
ORDER BY LongSheetPrimContInfo.PrimeCont, LongSheetPrimContInfo.Site;


2) the sql view of your report's record source

The above query is the report's record source (which pulls from two
tables)


3) the section, name, and control source of your control in your report
that is wrong.

Section: ReportFooter
Name: TEXT172
ControlSource: =SUM([Award])

 

[Jacques]

Duane,
Am I supposed to attached the new totals query to the already existing
one

i.e.

NewQuery <-> TableA -> TableB <==forces other records to be cut off in
TableA and TableB

or

NewQuery <-> TableB <=This will not give me all the information.

Your first totals query should be only:
SELECT LongSheetPrimContInfo.PrimeCont,
Sum(LongSheetPrimContInfo.Award) as SumOfAward
FROM LongSheetPrimContInfo
GROUP BY LongSheetPrimContInfo.PrimeCont
ORDER BY LongSheetPrimContInfo.PrimeCont;
This should calculate all the Awards values for a single
contractor/vendor, right? If you add this query to your report's record
source query, you should have the number [SumOfAward] ready to stick in
your contractor header or footer section.


--
Duane Hookom
MS Access MVP
--

Was I correct in your report structure?
Ithought I was fairly clear in the sql of the first totals query that
should not include detail records. It should only be from the table
where the Award is stored. You included two tables. If you only wanted
to total the award field in the query, why include all those other
fields?

The first table contains all the info for the Contractors, name, site
worked, work type, and contract AWARD amount.
The second table contains all the sub-contractors that worked for them on
that site.

The report must total the award amounts per Prime Contractor (totaling
all the sites they worked) and then show a grand total of all Primes
combined at the end of the report (because all the sites and contractors
work for one company).

Everything works BUT the totalling of the AWARD in any group section.

You didn't provide the name of the totals query as I asked

LongSheet-ContractorFull

and you didn't

provide the SQL view of the report's record source which must be
different from the first totals query.

There's only one query (in the original design - not counting the changes
I made - to a copy of the DB -that you asked me to make earlier)
When I go to the report's record source it is

LongSheet-ContractorFull and the SQL is what I sent

SELECT LongSheetPrimContInfo.PrimeCont, LongSheetPrimContInfo.Site,
LongSheetPrimContInfo.Classification, LongSheetPrimContInfo.Trade,
LongSheetPrimContInfo.Award, LongSheetPrimContInfo.NOD,
LongSheetPrimContInfo.PercentComplete, LongSheetSubContInfo.MBEWBESub,
LongSheetSubContInfo.SubTrade, Sum(CDbl(Nz([CMA],0))) AS CommMBEAmt,
Sum(CDbl(Nz([CMP],0))) AS CommMBEPer, Sum(CDbl(Nz([CwA],0))) AS
CommWBEAmt, Sum(CDbl(Nz([CWP],0))) AS CommWBEPer, Sum(CDbl(Nz([CDA],0)))
AS CommDSDBEAmt, Sum(CDbl(Nz([CDP],0))) AS CommDSDBEPer,
Sum(CDbl(Nz([DMA],0))) AS DocuMBEAmt, Sum(CDbl(Nz([DMP],0))) AS
DocuMBEPer, Sum(CDbl(Nz([DWA],0))) AS DocuWBEAmt, Sum(CDbl(Nz([DMP],0)))
AS DocuWBEPer, Sum(CDbl(Nz([DDA],0))) AS DocuDSDBEAmt,
Sum(CDbl(Nz([DDP],0))) AS DocuDSDBEPer, Sum(CDbl(Nz([DLA],0))) AS
DocuLBEAmt, Sum(CDbl(Nz([DLP],0))) AS DocuLBEPer
FROM LongSheetPrimContInfo LEFT JOIN LongSheetSubContInfo ON
LongSheetPrimContInfo.IDLSPrimeContInfo =
LongSheetSubContInfo.IDLSPrimeContInfo
GROUP BY LongSheetPrimContInfo.PrimeCont, LongSheetPrimContInfo.Site,
LongSheetPrimContInfo.Classification, LongSheetPrimContInfo.Trade,
LongSheetPrimContInfo.Award, LongSheetPrimContInfo.NOD,
LongSheetPrimContInfo.PercentComplete, LongSheetSubContInfo.MBEWBESub,
LongSheetSubContInfo.SubTrade
ORDER BY LongSheetPrimContInfo.PrimeCont, LongSheetPrimContInfo.Site;

If I'm mistunderstanding what you're asking for, or just don't know, I
apologize beforehand.

When you have the above set up, you don't Sum the award field since it
should be summed in the query.

I don't Sum the AWARD field in any section except the Report Footer. I
tried to Sum the Award field in the PrimCont section of the report to see
if the math was correct, but it wasn't

--
Duane Hookom
MS Access MVP
--

"Jacques" <Jacques Latoison at hotmail dot com> wrote in message
Here ya go. Your first eval of the situation is correct though,

Duane Hookom wrote:
I expect it's the fields from A that are too high. If A has a record
with a
value of $10 and there are 3 related/child records in B then you are
probably totaling this field to $30. This makes sense.

---------The above statement is the issue. What you asked for is
below.

Provide for us:
1) the sql view and name of your totals query

SELECT LongSheetPrimContInfo.PrimeCont, LongSheetPrimContInfo.Site,
LongSheetPrimContInfo.Classification, LongSheetPrimContInfo.Trade,
LongSheetPrimContInfo.Award, LongSheetPrimContInfo.NOD,
LongSheetPrimContInfo.PercentComplete, LongSheetSubContInfo.MBEWBESub,
LongSheetSubContInfo.SubTrade, Sum(CDbl(Nz([CMA],0))) AS CommMBEAmt,
Sum(CDbl(Nz([CMP],0))) AS CommMBEPer, Sum(CDbl(Nz([CwA],0))) AS
CommWBEAmt, Sum(CDbl(Nz([CWP],0))) AS CommWBEPer,
Sum(CDbl(Nz([CDA],0))) AS CommDSDBEAmt, Sum(CDbl(Nz([CDP],0))) AS
CommDSDBEPer, Sum(CDbl(Nz([DMA],0))) AS DocuMBEAmt,
Sum(CDbl(Nz([DMP],0))) AS DocuMBEPer, Sum(CDbl(Nz([DWA],0))) AS
DocuWBEAmt, Sum(CDbl(Nz([DMP],0))) AS DocuWBEPer,
Sum(CDbl(Nz([DDA],0))) AS DocuDSDBEAmt, Sum(CDbl(Nz([DDP],0))) AS
DocuDSDBEPer, Sum(CDbl(Nz([DLA],0))) AS DocuLBEAmt,
Sum(CDbl(Nz([DLP],0))) AS DocuLBEPer
FROM LongSheetPrimContInfo LEFT JOIN LongSheetSubContInfo ON
LongSheetPrimContInfo.IDLSPrimeContInfo =
LongSheetSubContInfo.IDLSPrimeContInfo
GROUP BY LongSheetPrimContInfo.PrimeCont, LongSheetPrimContInfo.Site,
LongSheetPrimContInfo.Classification, LongSheetPrimContInfo.Trade,
LongSheetPrimContInfo.Award, LongSheetPrimContInfo.NOD,
LongSheetPrimContInfo.PercentComplete, LongSheetSubContInfo.MBEWBESub,
LongSheetSubContInfo.SubTrade
ORDER BY LongSheetPrimContInfo.PrimeCont, LongSheetPrimContInfo.Site;


2) the sql view of your report's record source

The above query is the report's record source (which pulls from two
tables)


3) the section, name, and control source of your control in your
report that is wrong.

Section: ReportFooter
Name: TEXT172
ControlSource: =SUM([Award])