Rounding and calculation

[kmclane]

I have two reports, one is a detail and the other is a summary. My Problem is this. On the Summary report I have count of 4877 that is multiplied by .045 and gives me a result of $219.47. ON my detail report I have 27 line items that are each multiplied by the same figure but give me a grand total of $219.97. This is a difference of $0.50, and while we all know it is due to the rounding in Access, I need my two reports to agree on these things. If I could find a way for my query to total to three decimal places I'm sure it would work fine. I'm using Access 97. I have checked for a decimal setting in my query design grid. Any suggestions?
Thanks, Ken

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[JonFer]

What are the field types (currency, double, single) for the amounts and what are the expressions you are using to do the calculations? Are they being done in a query or a report control?

 

[kmclane]

Well, lets see, in this section the counts are coming from a query on three different tables. Seems most of those fields are Long integer. The cost amount is coming from a table, the field is currency with 5 decimal places. The calculation is being done in the query.
KEn

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[JonFer]

Can you also post the expressions that perform the calculations?

 

[kmclane]

OK, here is the detail section:

[VoiceMerch].[Voiceauth]+[AuthData].[Total_Auths]+[AuthDataNobel].[NobelTotal_Auths]+[AuthDataVital].[VitalTotal_Auths] AS CredTranCt, CredTranCt*buyrate.Transaction-VoiceMerch.Voiceauth AS [Credit Tran Cost

and actually the summary is done on the report with the count coming from:
=[SumOfNobelTotal_Auths]+[SumOfTotal_Auths]+[SumOfVitalTotal_Auths]-[VoiceCT] and the cost coming from =NZ([Transaction],"$0.00 ") which is still pulled directly from the same table. I have verified that the total counts match.
Ken

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[JonFer]

I don't understand the rules for the VoiceAuths. It looks like a count because you are including it with the other counts but then you subtract it directly from "CredTranCt*buyrate.Transaction" where I would expect it to be "(CredTranCt-VoiceMerch.VoiceAuth)*buyrate.Transaction" unless the rate for VoiceAuth is $1.00 or something.

In the summary you add Nobel, Total, and Vital auths but then subtract the VoiceCT. Is voice included in one of the first 3 categories?

Can you explain what the formulas should be with an example using real numbers? You can simplify it by just using Nobel, Total, Vital, and Voice to describe the types of auths.

 

[kmclane]

Sorry, it wasw late and I was cutting and pasting in pieces. VoiceCt gets subtracted in the first part of the formula, I pasted it in the wrong place. It should be:

]+[AuthData].[Total_Auths]+[AuthDataNobel].[NobelTotal_Auths]+[AuthDataVital].[VitalTotal_Auths]-VoiceMerch.Voiceauth AS CredTranCt, CredTranCt*buyrate.Transaction AS [Credit Tran Cost]

Basically, all the auth fields are counts and the buyrate fields are costs per. Voie transactions are charged at a higher rate. I have a few different cases, but I only see the different total problem where I have 3 or more decimal places in my cost and I have many line items in my detail report. I hope this clarifies.
Ken


- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[JonFer]

$0.50 is more than you'd expect to see from rounding 27 items. Since the detail is higher and incorrect, you might be getting bad results due to nulls. To correct this, you should use nz(AuthCount,0) in your formulas for each auth type. In a calculation using several columns from one record, the result is Null if any of those fields are Null. A+B+C+D = Null if either A, B, C, or D is Null. But generating a sum for A across records first gives the correct total for A (it ignores Nulls) which can then be added to the sum of B, etc. I guess I would look for records with Null counts where the VoiceAuths were higher than the others combined. This would cause a higher detail result because a negative amount is removed.

If nz() doesn't get it closer, I would write a query that has the 4 types of auths listed separately and do the calculation in Excel to see where the numbers break down.

 

[kmclane]

Thanks, I'll check into that this morning. I see I once again bungled my pasting!
Ken

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg