Problem with Sum total

[Sheila.deJesus]

I need assistance on one of my reports. :-(

How do i limit the sum total of one record?

PolicyNo PremiumAmount CollectionID AmountCollected OfficialReceipt DateOfReceipt
ABC 12,000 1252 2,000 152680 21-Sept-05
ABC 12,000 1253 2,000 152681 21-Oct-05
ABC 12,000 1254 2,000 152682 21-Nov-05
ABC 12,000 1255 2,000 152683 21-Dec-05

If i total the PremiumAmount, PolicyNo ABC should only sum up to 12,000 considering that it is only 1 record. But because of the 4 OfficialReceipts, it totals to 48,000.

Thanks!
Sheila

 

[Rich]

Since you already have the total policy amount there's no need to Sum it.

 

[Sheila.deJesus]

Hi Rich!
Thanks for your response. I need to sum up all the records on my report. I just emphasized one policy number. If i have 4 records of PolicyNumbers with Premium amounting to 12,000/each, the sum total should be 48,000.00. But because there are possible multiple payment for each PolicyNumber, the query generates the same number of records as that of the OfficialReceipt encoded, thus, my report doesn't sum up the total premium based on the number of PolicyNumber.

I hope this could give you a clearer picture:

Table1 [CoverageData]
Fields:
CoverageID - PK/Autonumber
PolicyNumber
PremiumAmount
etc..

Table2 [CollectionData]
CollectionID - PK/Autonumber
CoverageID - FK
OfficialReceipt
AmountCollected
DateCollected


Sheila

 

[apr pillai]

problem with sum total

Are you using a Total Query as Source for your Report? In that case use FIRST or LAST instead of SUM to pick only one Amount of 12000 out of 4 Records of the Policy Amount.

 

[stopher]

PolicyNo PremiumAmount CollectionID AmountCollected OfficialReceipt DateOfReceipt
ABC 12,000 1252 2,000 152680 21-Sept-05
ABC 12,000 1253 2,000 152681 21-Oct-05
ABC 12,000 1254 2,000 152682 21-Nov-05
ABC 12,000 1255 2,000 152683 21-Dec-05

I think you would be better off showing the PolicyNo and PremiumAmount as a group level and the collections as detail. Then you report will look like:

<PolicyNumber group header>
PolicyNo________ ABC
PremiumAmount _12,000

<Detail>
CollectionID AmountCollected OfficialReceipt DateOfReceipt
1252 ________2,000_________ 152680_____ 21-Sept-05
1253 ________2,000_________ 152681_____ 21-Oct-05
1254 ________2,000_________ 152682_____ 21-Nov-05
1255 ________2,000_________ 152683_____ 21-Dec-05

Total ________8,000

<PolicyNumber group footer>
Balance remaining___4,000

The point is, listing the PolicyNo and PremiumAmount like you are doing is confusing imho.

If you really want to do it your way, then you can use DLOOKUP() to pull the value of 12,000 from your DataCoverage table

hth
Stopher