MS Access Query - Same query 2 diff results

[RayA]

Hi,
I used Query Wizard and wanted to select Column A when the following was True - Column A=(Column B * .20)
The query should retrieve 3 rows but only 2 rows are retrieved. The data has been confirmed in all 3 rows.

[RayA]

Hello, Please let me know if I should search another site for an answer.
Thanks

[Allan Murphy]

Send a zipped copy of your database with your query and the table(s) associated with the query.

[RayA]

Send it where ?

[Allan Murphy]

Please attach a zipped copy as a post to this forum.

[RayA]

Please attach a zipped copy as a post to this forum.

I can't for the life of me figure out how to attached the zip file. I'll keep trying. Thanks

[Allan Murphy]

Just under the Quick Reply of this post is a button called Go Advanced. The Go Advanced opens a new page that allows you to enter a longer reply and format your reply e.g. make words Bold, Italics, Underline etc..

If you scroll down on the page you will see a section named Attachments click on the Attachments button a popup is displayed then click on Add files in the top right hand corner of the popup. Add your zip file then click on Done at the bottom right hand corner of the popup

[RayA]

Here's the SQL Query - I attached the zipped database

SELECT [TestData 2].Amount_Paid, [TestData 2].Charges, [TestData 2].Coinsurance, [TestData 2].Deductible, [TestData 2].Copayment, [TestData 2].Allowed_Amount
FROM [TestData 2]
WHERE ((([TestData 2].Coinsurance)=[Allowed_Amount]*0.2));

[Allan Murphy]

Your SQL is correct but the problems lies with your data.

Is the data in the table from an external source or was it entered into the table via a form etc.? The coinsurance field for the first record is displayed as $19.30 but if you place your cursor in the field the value is actually $19.9299. Because of your format property the second decimal place is rounded up.

This also applies to the values in the Allowed_Amount fields As an example for record 2 this amount is shown as $289.90 but it is actually $289.8999.

[RayA]

Your SQL is correct but the problems lies with your data.

Is the data in the table from an external source or was it entered into the table via a form etc.? The coinsurance field for the first record is displayed as $19.30 but if you place your cursor in the field the value is actually $19.9299. Because of your format property the second decimal place is rounded up.

This also applies to the values in the Allowed_Amount fields As an example for record 2 this amount is shown as $289.90 but it is actually $289.8999.

Hi,
Thank you for help and explanation, greatly appreciated.
The data is from a mainframe application, a central database is created by a "stored process" which extracts the required data based on the selection criteria that we enter into the "stored process" parameters.
I will ask the owner of the "stored process" if a change can be made to create the currency columns with a "Rounded" option, I'm guessing that would resolve the issue.
If the cost for the modification is not feasible then I will create a process to reformat the currency columns.
Thanks again, Have a great day.
Ray A.




I will have to develop an interim step that will re-format the currency columns.