Solved Calculation Error in Query (1 Viewer)

[Tophan]

Good Morning,

I have a query that calculates the insurance on the sum of an invoice.

I have two invoices - the first one consisted of 3 items totalling $75.00 (30+30+15); the second one was 1 item totalling $75.00. The insurance is 17.1%.

For the first invoice the calculation returned a total of $10.26 which is 17.1% of $60. For the second one the calculation returned a total of $12.83 which is correct. Any idea why the first invoice would return an incorrect total?

 

[Isaac]

Wouldn't we need to see your code in order to answer this?

 

[Tophan]

Here is the code:

Sum(IIf([TotalSum]<21,[TotalSum]*0,IIf([TotalSum]>=4820,4820*0.171,[TotalSum]*0.171)))

If the invoice is less than $21 then insurance is not charged; if it is greater than and equal to $4820, insurance is only paid on $4820.

Even though the TotalSum of the invoice is correct in the datasheet view of the query, the formula seems to be ignoring the $15.00 in the first invoice an only calculating insurance on the two $30.00 items.

 

[Isaac]

Thanks, what's the code for TotalSum?

 

[plog]

You are not doing this in the correct order:

...the first one consisted of 3 items totalling $75.00 (30+30+15)...the calculation returned a total of $10.26 which is 17.1% of $60...

Sum(IIf([TotalSum]<21,[TotalSum]*0,IIf([TotalSum]>=4820,4820*0.171,[TotalSum]*0.171)))

Name it "TotalSum" all you want, but it is not the TotalSum, its the price of the individual items. Your equation treats the first $30 item correctly and assigns it 17.1% insurance to that it adds the 17.1% from the second $30 item. Then it adds $0 because the third item is only $15 which is less than 21.

If the insurance is on the Total TotalSum, then you need to find that and apply it as such. right now you are doing the insurance calculation item by item. Might require a subquery or moving the SUM inside the IIf.

 

[Tophan]

The TotalSum in the invoice actually comes from a table named tblTaskSheetDtls. Once a task is completed the task number is added to the invoice from a combo box.

The query qryTaskSheetDtls calculates the TotalSum, the code for which is:

Nz(IIf(([External]=Yes) And ([TTLTime]<1),1*50,[TTLTime]*50),0)+Nz([TaskRate]*[Qty],0)+Nz([OtherRate]*[Qty],0)

The query for the insurance is grouped by invoice number and the TotalSum is summed. The SQL for the insurance query (qryNIS) is as follows:

SELECT qryInvoices.InvID, qryInvoices.InvDate, Year([InvDate]) AS [Year], DatePart("q",[InvDate]) AS Quarter, qryInvoices.Company, Sum(qryInvoices.TotalSum) AS SumOfTotalSum, Sum(IIf([TotalSum]<21,[TotalSum]*0,IIf([TotalSum]>=4820,4820*0.171,[TotalSum]*0.171))) AS NIS
FROM qryInvoices
GROUP BY qryInvoices.InvID, qryInvoices.InvDate, Year([InvDate]), DatePart("q",[InvDate]), qryInvoices.Company
HAVING (((Year([InvDate]))=[Forms]![frmDateSelect]![cboYears]) AND ((DatePart("q",[InvDate]))=[Forms]![frmDateSelect]![cboQuarter]));

I greatly appreciate your assistance and let me know if you need any further information.

 

[Tophan]

You are not doing this in the correct order:



Name it "TotalSum" all you want, but it is not the TotalSum, its the price of the individual items. Your equation treats the first $30 item correctly and assigns it 17.1% insurance to that it adds the 17.1% from the second $30 item. Then it adds $0 because the third item is only $15 which is less than 21.

If the insurance is on the Total TotalSum, then you need to find that and apply it as such. right now you are doing the insurance calculation item by item. Might require a subquery or moving the SUM inside the IIf.

Thank You!

Created a sub-query just totalling the invoices and added the insurance formula and that worked!

As always...this forum is a great help!