Total is not calculating correctly?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Here is the setup:

In ClassID header I have: ClassDate, ClassName, CompetencieID, Credithours
In the detail I have:Competency name(each class could have multiple
competencies)

For my report total: If a class had 2 credit hours but 3 competencies in
the detail the Total Credit hours is reporting 6 instead of 2. The way I
have the calculation in the report total is =sum([credithours])

Any advice would help

JZ
 
Yes, if you look at the query that feeds the report, you will see that
Access repeats the 2 credit hours for all 3 records (with the different
competencies), and so when you add up the total you get 6.

To avoid that, use a Running Sum in the group header to sum the hours for
the header, but not repeat them in the detail section. You can then show the
total hours in the report footer like this:

1. Add a text box to the ClassID group header, and set these properties:
Name: txtSumOfHours
Control Source: =[CreditHours]
Running Sum: Over All
Visible No
(Add this, even if you already hae a ClassHours text box.)

2. In the Report Footer section, add a text box with Control Source of:
=[txtSumOfHours]
 
Dear Allen,

I have a similar issue.

record layout

Table: IPAWS - contains shipments, pcs, kgs

Table: Marks - contains markings

Relationship- Shipment to Markings one to many

i.e one shipment has multiple markings

Problem:
When I create a query and display it in DATASHEET View I see all the data is
repeated for every marking furthermore when I generate report and do a total
it adds pcs as many times as markings. i.e when one shipment weights 5 kg
and has 5 markings it shows the total as 25 kgs. Please help I have tried to
implement your earlier proposed solution to ZIMMJE but has not resolved my
problem. Any help will be appreciated.

Thanks

Sami


Allen Browne said:
Yes, if you look at the query that feeds the report, you will see that
Access repeats the 2 credit hours for all 3 records (with the different
competencies), and so when you add up the total you get 6.

To avoid that, use a Running Sum in the group header to sum the hours for
the header, but not repeat them in the detail section. You can then show the
total hours in the report footer like this:

1. Add a text box to the ClassID group header, and set these properties:
Name: txtSumOfHours
Control Source: =[CreditHours]
Running Sum: Over All
Visible No
(Add this, even if you already hae a ClassHours text box.)

2. In the Report Footer section, add a text box with Control Source of:
=[txtSumOfHours]

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

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

ZIMMJE said:
Here is the setup:

In ClassID header I have: ClassDate, ClassName, CompetencieID, Credithours
In the detail I have:Competency name(each class could have multiple
competencies)

For my report total: If a class had 2 credit hours but 3 competencies in
the detail the Total Credit hours is reporting 6 instead of 2. The way I
have the calculation in the report total is =sum([credithours])

Any advice would help

JZ
Click to expand...
Click to expand...
 
Back
Top