dcsimg
Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2016
    Posts
    6

    MS Access Query - Same query 2 diff results

    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.

  2. #2
    Join Date
    Sep 2016
    Posts
    6
    Hello, Please let me know if I should search another site for an answer.
    Thanks

  3. #3
    Join Date
    Oct 2006
    Location
    Maitland NSW Australia
    Posts
    275
    Send a zipped copy of your database with your query and the table(s) associated with the query.
    Allan

  4. #4
    Join Date
    Sep 2016
    Posts
    6
    Send it where ?

  5. #5
    Join Date
    Oct 2006
    Location
    Maitland NSW Australia
    Posts
    275
    Please attach a zipped copy as a post to this forum.
    Allan

  6. #6
    Join Date
    Sep 2016
    Posts
    6
    Quote Originally Posted by Allan Murphy View Post
    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

  7. #7
    Join Date
    Oct 2006
    Location
    Maitland NSW Australia
    Posts
    275
    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
    Allan

  8. #8
    Join Date
    Sep 2016
    Posts
    6
    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));
    Attached Files Attached Files

  9. #9
    Join Date
    Oct 2006
    Location
    Maitland NSW Australia
    Posts
    275
    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.
    Allan

  10. #10
    Join Date
    Sep 2016
    Posts
    6
    Quote Originally Posted by Allan Murphy View Post
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •