Not Equal SQL Queries in Access

[sajitk]

Hi Friends,

I have a problem with a particular SQL query which I wrote.

Just to brief on the Tables involved.

Beneficiary

Solar Lantern No - Text, PK
Name of the Beneficiary - Text
Cost of Lantern - Number

Payment details table
Solar Lantern No - Text, FK
Payment - Number


The beneficiary has an option of making payments in installments.

this access application has a report option where we can see that overall outstanding payment report.

SELECT DISTINCTROW Beneficiary.[Solar Lantern No], Beneficiary.[Name of the Beneficiary], Beneficiary.[Name of the motivator], Beneficiary.Cos t_SL, Sum([Payment details].Payment) AS [Payment Received]
FROM Beneficiary LEFT JOIN [Payment details] ON Beneficiary.[Solar Lantern No]=[Payment details].[Solar Lantern No]
WHERE (Beneficiary.Co st_SL)-([Payment details].Payment)>0
GROUP BY Beneficiary.[Solar Lantern No], Beneficiary.[Name of the motivator], Beneficiary.[Name of the Beneficiary], Beneficiary.Cos t_SL;

This is query which i wrote. But when I execute the query, I find that there are some records which are displayed for which there is no outstanding. I am not able to figure out why. And these entries where made on a particular day.

Can anyone figure out where is the problem.

Looking forward to hear from you

sajit

[Stewart Ross]

Hi Sajit. This appears to be a rounding problem, relating to the internal precision at which numeric values are stored. When you do a comparison such as

balance > 0

you will catch all values where the balance is non-zero as a result of the accumulation of small rounding errors (e.g., a value of 0.000000000011) .

To avoid this, in your comparison you compare not to 0 but to a specific value lower than the lowest balance you want to treat as 0, e.g.

balance > 0.005

Try replacing the > 0 in your WHERE clause with > 0.005 and let us know how you get on.

-Stewart

[sajitk]

Hi Stewart,

doesnt works....I have have records where Cost = Payment received being displayed....

sajit

Hi Sajit. This appears to be a rounding problem, relating to the internal precision at which numeric values are stored. When you do a comparison such as

balance > 0

you will catch all values where the balance is non-zero as a result of the accumulation of small rounding errors (e.g., a value of 0.000000000011) .

To avoid this, in your comparison you compare not to 0 but to a specific value lower than the lowest balance you want to treat as 0, e.g.

balance > 0.005

Try replacing the > 0 in your WHERE clause with > 0.005 and let us know how you get on.

-Stewart

[Stewart Ross]

Hi Sajit. Could you add the following calculated field to your SQL and let me know the values output by the balance field for such cases as the one you mention:

SELECT ... (other fields as before), beneficiary.cost_SL - [payment details].payment AS Balance ...

Please also report the values of the payment and cost fields as exactly as possible, so that I can understand better what still appears to me to be a rounding issue but where there may be other explanations.

-Stewart

[sajitk]

Hi

Tried adding the calulated fied....it adds up....

ie. if the cost is 2500 and the payment received is also 2500 the balance figure is 5000.


Sajit

Hi Sajit. Could you add the following calculated field to your SQL and let me know the values output by the balance field for such cases as the one you mention:

SELECT ... (other fields as before), beneficiary.cost_SL - [payment details].payment AS Balance ...

Please also report the values of the payment and cost fields as exactly as possible, so that I can understand better what still appears to me to be a rounding issue but where there may be other explanations.

-Stewart

[Stewart Ross]

No, Sajit, I need you to report exactly what the result of the subtraction performed by the new calculated balance field is. You may wish to copy the query results and append them to your next post.

I am checking to see if there is a very small non-zero balance reported, and any calculation you perform yourself cannot help resolve this.

Please include the calculated field in your SQL as per my previous post, and let me know the exact values shown in your query for the payment, the cost, and the balance.

I would not have asked you to do so if I did not think it was important in resolving your query.

-Stewart

ps so that I can be sure about the value of the balance, please use the following format statement for the subtraction:

format(beneficiary.cost_sl - [payment details].payment, "e-") as Balance

this will show the value in scientific notation.

[sajitk]

Can you write this SQL for me...bcos when I write the way u want, it says..wrng number of arguments....

sajit

No, Sajit, I need you to report exactly what the result of the subtraction performed by the new calculated balance field is. You may wish to copy the query results and append them to your next post.

I am checking to see if there is a very small non-zero balance reported, and any calculation you perform yourself cannot help resolve this.

Please include the calculated field in your SQL as per my previous post, and let me know the exact values shown in your query for the payment, the cost, and the balance.

I would not have asked you to do so if I did not think it was important in resolving your query.

-Stewart

ps so that I can be sure about the value of the balance, please use the following format statement for the subtraction:

format(beneficiary.cost_sl - [payment details].payment, "e-") as Balance

this will show the value in scientific notation.

[Stewart Ross]

OK:

SELECT DISTINCTROW Beneficiary.[Solar Lantern No], Beneficiary.[Name of the Beneficiary], Beneficiary.[Name of the motivator], Beneficiary.Cost_SL, Sum([Payment details].Payment) AS [Payment Received], Format(Beneficiary.Cost_SL - [Payment Details].Payment, "e-") as Balance 
FROM Beneficiary LEFT JOIN [Payment details] ON Beneficiary.[Solar Lantern No]=[Payment details].[Solar Lantern No]
[b]WHERE (Beneficiary.Cost_SL)-([Payment details].Payment)>0[/b]
GROUP BY Beneficiary.[Solar Lantern No], Beneficiary.[Name of the motivator], Beneficiary.[Name of the Beneficiary], Beneficiary.Cost_SL;

[Stewart Ross]

Apologies, Sajit - I have the format string in error. It should be

Format(Beneficiary.Cost_SL - [Payment Details].Payment, "0.00e-00") as Balance

-S