Sum in Report Errors

  • Thread starter Thread starter Peter Hibbs
  • Start date Start date
P

Peter Hibbs

Access 2003

I have a report based on a query, in the report there is a Totals
field which is supposed to show the sum of a field but it doesn't show
the correct value.

The table schema looks like this :-

tblClients tblProperties tblClaims
----------- --------------- ----------
ClientID --------> ClientID ID
ClientName InsID --------------> InsID
...more fields.. BDV ClaimRef
..more fields.. .more fields


One record per client in tblClients. 1-M relationship to tblProperties
as a client can have more than one property and a 1-M relationship
from tblProperties to tblClaims as any property could have multiple
insurance claims made against it. The BDV (Building Declared Value)
field holds the value of the property.

The query for the report looks like this :-

SELECT tblClients.ClientID, tblProperties.PolicyClass,
Replace([RiskAddress],Chr(13) & Chr(10),", ") & ", " & [RiskTown] &
", " & [RiskPostcode] AS Addr, tblProperties.Insured,
tblProperties.BDV, Year([EndDate]) & Format(Month([EndDate]),"00") AS
YearMonth, tblProperties.Status, tblProperties.InsID,
tblProperties.LOR, tblProperties.RentPeriod,
tblProperties.TerrorismCover, tblProperties.BuildingDescription,
tblProperties.TenantType, tblProperties.TenantDetails, tblClaims.ID,
tblClaims.DateClaim, tblClaims.ClaimDetails, tblClaims.AmountRequested
FROM (tblClients INNER JOIN tblProperties ON tblClients.ClientID =
tblProperties.ClientID) LEFT JOIN tblClaims ON tblProperties.InsID =
tblClaims.InsID
WHERE (((tblClients.ClientID) Like
[Forms]![frmReports]![cboClientSOF]) AND
((tblProperties.PolicyClass)="PRO") AND ((Year([EndDate]) &
Format(Month([EndDate]),"00"))=[Forms]![frmReports]![cboYearMonth])
AND ((tblProperties.Status)="LIVE"));

The query returns the correct number of records, the criteria is set
up to return records for all clients or a particular client that have
'LIVE' insurance policies due in a selected month.

The report layout is something like this (simplified) :-

PAGE HEADER Section
----------------------------------------------------------------------------------
Company Logo only
----------------------------------------------------------------------------------
CLIENTID HEADER Section
----------------------------------------------------------------------------------
ClientID field
----------------------------------------------------------------------------------
INSID HEADER Section
----------------------------------------------------------------------------------
Addr, BDV field, + some others
----------------------------------------------------------------------------------
DETAIL Section
----------------------------------------------------------------------------------
ClaimDetails, DateClaim, AmountRequested fields
----------------------------------------------------------------------------------
INSID FOOTER Section
----------------------------------------------------------------------------------
(used for spacing only)
----------------------------------------------------------------------------------
CLIENTID FOOTER Section
----------------------------------------------------------------------------------
txtSumBDV =Sum(BDV)
----------------------------------------------------------------------------------
PAGE FOOTER Section
----------------------------------------------------------------------------------
Page No field.
----------------------------------------------------------------------------------

The ClientID section shows info on the Client and the INSID section
shows a list of properties that are owned by the client (including the
BDV for each property). The Detail section shows the info on any
insurance claims made against each property (if any).

The txtSumBDV fieldin the ClientID footer section is supposed to show
the total value of all the properties for the client, which is does in
most cases. However, if a property has more than one insurance claim
against it, the sum value of the properties is increased by the amount
of that property's BDV.

For example, a report looks something like :-

Mr J Smith
1 The High Street £200,000 ..other fields..
200 London Road £300,000
Broken window, etc
Damage to roof.
66 The Mall £100,00

Total - £900,000

The total BDV should be £600,000 but it adds in the £300,000 value
again because there are two claims for the second property, (although
with only one claim, the total shows correctly).

I cannot see why the BDV sum is not working since the BDV field only
appears once for each property record. Does anyone know why and how to
fix it. I have tried moving the sum calculation into various different
sections but it makes no difference.

I should also say that (as the query probably shows) the report is
actually showing a number of client records (depending on the criteria
settings on the Reports form).

TIA

Peter Hibbs.
 
Peter said:
Access 2003

I have a report based on a query, in the report there is a Totals
field which is supposed to show the sum of a field but it doesn't show
the correct value.

The table schema looks like this :-

tblClients tblProperties tblClaims
----------- --------------- ----------
ClientID --------> ClientID ID
ClientName InsID --------------> InsID
..more fields.. BDV ClaimRef
..more fields.. .more fields


One record per client in tblClients. 1-M relationship to tblProperties
as a client can have more than one property and a 1-M relationship
from tblProperties to tblClaims as any property could have multiple
insurance claims made against it. The BDV (Building Declared Value)
field holds the value of the property.

The query for the report looks like this :-

SELECT tblClients.ClientID, tblProperties.PolicyClass,
Replace([RiskAddress],Chr(13) & Chr(10),", ") & ", " & [RiskTown] &
", " & [RiskPostcode] AS Addr, tblProperties.Insured,
tblProperties.BDV, Year([EndDate]) & Format(Month([EndDate]),"00") AS
YearMonth, tblProperties.Status, tblProperties.InsID,
tblProperties.LOR, tblProperties.RentPeriod,
tblProperties.TerrorismCover, tblProperties.BuildingDescription,
tblProperties.TenantType, tblProperties.TenantDetails, tblClaims.ID,
tblClaims.DateClaim, tblClaims.ClaimDetails, tblClaims.AmountRequested
FROM (tblClients INNER JOIN tblProperties ON tblClients.ClientID =
tblProperties.ClientID) LEFT JOIN tblClaims ON tblProperties.InsID =
tblClaims.InsID
WHERE (((tblClients.ClientID) Like
[Forms]![frmReports]![cboClientSOF]) AND
((tblProperties.PolicyClass)="PRO") AND ((Year([EndDate]) &
Format(Month([EndDate]),"00"))=[Forms]![frmReports]![cboYearMonth])
AND ((tblProperties.Status)="LIVE"));

The query returns the correct number of records, the criteria is set
up to return records for all clients or a particular client that have
'LIVE' insurance policies due in a selected month.

The report layout is something like this (simplified) :-

PAGE HEADER Section
----------------------------------------------------------------------------------
Company Logo only
----------------------------------------------------------------------------------
CLIENTID HEADER Section
----------------------------------------------------------------------------------
ClientID field
----------------------------------------------------------------------------------
INSID HEADER Section
----------------------------------------------------------------------------------
Addr, BDV field, + some others
----------------------------------------------------------------------------------
DETAIL Section
----------------------------------------------------------------------------------
ClaimDetails, DateClaim, AmountRequested fields
----------------------------------------------------------------------------------
INSID FOOTER Section
----------------------------------------------------------------------------------
(used for spacing only)
----------------------------------------------------------------------------------
CLIENTID FOOTER Section
----------------------------------------------------------------------------------
txtSumBDV =Sum(BDV)
----------------------------------------------------------------------------------
PAGE FOOTER Section
----------------------------------------------------------------------------------
Page No field.
----------------------------------------------------------------------------------

The ClientID section shows info on the Client and the INSID section
shows a list of properties that are owned by the client (including the
BDV for each property). The Detail section shows the info on any
insurance claims made against each property (if any).

The txtSumBDV fieldin the ClientID footer section is supposed to show
the total value of all the properties for the client, which is does in
most cases. However, if a property has more than one insurance claim
against it, the sum value of the properties is increased by the amount
of that property's BDV.

For example, a report looks something like :-

Mr J Smith
1 The High Street £200,000 ..other fields..
200 London Road £300,000
Broken window, etc
Damage to roof.
66 The Mall £100,00

Total - £900,000

The total BDV should be £600,000 but it adds in the £300,000 value
again because there are two claims for the second property, (although
with only one claim, the total shows correctly).

I cannot see why the BDV sum is not working since the BDV field only
appears once for each property record. Does anyone know why and how to
fix it. I have tried moving the sum calculation into various different
sections but it makes no difference.

I should also say that (as the query probably shows) the report is
actually showing a number of client records (depending on the criteria
settings on the Reports form).
Click to expand...


Because of the Joins, the query actually does have records
with the same BDV value once for each claim. Since Sum adds
up all the records regardless of what section is used to
display the field, the result you are seeing is expected.

The way to deal with this situation is to use a running sum
text box. Add a text box (named txtRunTotal) to the INSID
group header or footer section. Set its control source to
the BDV field and its RunningSum property to Over Group.
Then the txtSumBDV expression would be =txtRunTotal
 
You stated "the BDV field only appears once for each property record" when
actually the BDV field is repeated in every record in your report's record
source. If you have two or more claims, the same BDV value is displayed in
each.

If you want to sum the BDV by client, you would typically add a text box to
the [Properties] group header:
Name: txtBDVRunSum
Control Source: BDV
Running Sum: Over Group
Visible: No

Then, in the Client footer, add a text box:
Control Source: =txtBDVRunSum

You can also create a totals query of the properties like:
SELECT ClientID, Sum(BDV) As ClientBDV
FROM tblProperties
GROUP BY ClientID;

Add this totals to your report's record source and join to the ClientID. You
now have the total ClientBDV.

--
Duane Hookom
Microsoft Access MVP


Peter Hibbs said:
Access 2003

I have a report based on a query, in the report there is a Totals
field which is supposed to show the sum of a field but it doesn't show
the correct value.

The table schema looks like this :-

tblClients tblProperties tblClaims
----------- --------------- ----------
ClientID --------> ClientID ID
ClientName InsID --------------> InsID
...more fields.. BDV ClaimRef
..more fields.. .more fields


One record per client in tblClients. 1-M relationship to tblProperties
as a client can have more than one property and a 1-M relationship
from tblProperties to tblClaims as any property could have multiple
insurance claims made against it. The BDV (Building Declared Value)
field holds the value of the property.

The query for the report looks like this :-

SELECT tblClients.ClientID, tblProperties.PolicyClass,
Replace([RiskAddress],Chr(13) & Chr(10),", ") & ", " & [RiskTown] &
", " & [RiskPostcode] AS Addr, tblProperties.Insured,
tblProperties.BDV, Year([EndDate]) & Format(Month([EndDate]),"00") AS
YearMonth, tblProperties.Status, tblProperties.InsID,
tblProperties.LOR, tblProperties.RentPeriod,
tblProperties.TerrorismCover, tblProperties.BuildingDescription,
tblProperties.TenantType, tblProperties.TenantDetails, tblClaims.ID,
tblClaims.DateClaim, tblClaims.ClaimDetails, tblClaims.AmountRequested
FROM (tblClients INNER JOIN tblProperties ON tblClients.ClientID =
tblProperties.ClientID) LEFT JOIN tblClaims ON tblProperties.InsID =
tblClaims.InsID
WHERE (((tblClients.ClientID) Like
[Forms]![frmReports]![cboClientSOF]) AND
((tblProperties.PolicyClass)="PRO") AND ((Year([EndDate]) &
Format(Month([EndDate]),"00"))=[Forms]![frmReports]![cboYearMonth])
AND ((tblProperties.Status)="LIVE"));

The query returns the correct number of records, the criteria is set
up to return records for all clients or a particular client that have
'LIVE' insurance policies due in a selected month.

The report layout is something like this (simplified) :-

PAGE HEADER Section
----------------------------------------------------------------------------------
Company Logo only
----------------------------------------------------------------------------------
CLIENTID HEADER Section
----------------------------------------------------------------------------------
ClientID field
----------------------------------------------------------------------------------
INSID HEADER Section
----------------------------------------------------------------------------------
Addr, BDV field, + some others
----------------------------------------------------------------------------------
DETAIL Section
----------------------------------------------------------------------------------
ClaimDetails, DateClaim, AmountRequested fields
----------------------------------------------------------------------------------
INSID FOOTER Section
----------------------------------------------------------------------------------
(used for spacing only)
----------------------------------------------------------------------------------
CLIENTID FOOTER Section
----------------------------------------------------------------------------------
txtSumBDV =Sum(BDV)
----------------------------------------------------------------------------------
PAGE FOOTER Section
----------------------------------------------------------------------------------
Page No field.
----------------------------------------------------------------------------------

The ClientID section shows info on the Client and the INSID section
shows a list of properties that are owned by the client (including the
BDV for each property). The Detail section shows the info on any
insurance claims made against each property (if any).

The txtSumBDV fieldin the ClientID footer section is supposed to show
the total value of all the properties for the client, which is does in
most cases. However, if a property has more than one insurance claim
against it, the sum value of the properties is increased by the amount
of that property's BDV.

For example, a report looks something like :-

Mr J Smith
1 The High Street £200,000 ..other fields..
200 London Road £300,000
Broken window, etc
Damage to roof.
66 The Mall £100,00

Total - £900,000

The total BDV should be £600,000 but it adds in the £300,000 value
again because there are two claims for the second property, (although
with only one claim, the total shows correctly).

I cannot see why the BDV sum is not working since the BDV field only
appears once for each property record. Does anyone know why and how to
fix it. I have tried moving the sum calculation into various different
sections but it makes no difference.

I should also say that (as the query probably shows) the report is
actually showing a number of client records (depending on the criteria
settings on the Reports form).

TIA

Peter Hibbs.
.
Click to expand...
 
Back
Top