Not Equal SQL Queries in Access
Question posted by: sajitk
(Member)
on
July 4th, 2008 06:09 AM
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.Cost_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.Cost_SL)-([Payment details].Payment)>0
GROUP BY Beneficiary.[Solar Lantern No], Beneficiary.[Name of the motivator], Beneficiary.[Name of the Beneficiary], Beneficiary.Cost_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
|
|
July 4th, 2008 06:35 AM
# 2
|
Re: Not Equal SQL Queries in Access
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
|
|
|
July 4th, 2008 06:55 AM
# 3
|
Re: Not Equal SQL Queries in Access
Hi Stewart,
doesnt works....I have have records where Cost = Payment received being displayed....
sajit
Quote:
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
|
|
|
|
July 4th, 2008 08:14 AM
# 4
|
Re: Not Equal SQL Queries in Access
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
|
|
|
July 4th, 2008 09:36 AM
# 5
|
Re: Not Equal SQL Queries in Access
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
Quote:
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
|
|
|
|
July 4th, 2008 10:53 AM
# 6
|
Re: Not Equal SQL Queries in Access
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.
|
|
|
July 4th, 2008 11:18 AM
# 7
|
Re: Not Equal SQL Queries in Access
Can you write this SQL for me...bcos when I write the way u want, it says..wrng number of arguments....
sajit
Quote:
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.
|
|
|
|
July 4th, 2008 11:28 AM
# 8
|
Re: Not Equal SQL Queries in Access
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]
-
WHERE (Beneficiary.Cost_SL)-([Payment details].Payment)>0
-
GROUP BY Beneficiary.[Solar Lantern No], Beneficiary.[Name of the motivator], Beneficiary.[Name of the Beneficiary], Beneficiary.Cost_SL;
|
|
|
July 4th, 2008 01:02 PM
# 9
|
Re: Not Equal SQL Queries in Access
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
Not the answer you were looking for? Post your question . . .
189,282 Experts ready to help you find a solution.
Sign up for a free account, or Login (if you're already a member).
|
