Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Compare two fields (2000)

    I am trying to compare Specialist Copay field to Physician Copay field to get non matches. In the query I put <> [Specialist Copay] in the criteria for "Physician Copay". This works fine as long as there is only one copay for Specialist Copay and one copay for Physician copay. When there are 2 copays for each then I run into trouble. Say the two copays are $15 and $30 for Specialist copay and $15 and $30 for Physician copay. The way my query is, it compares Specialist 15 to Physician 30 and Specialist 30 to Physician 15. These fields do match so they shouldn't make the report but the way it is evaluating the fields they don't match so they make the report.

    Is there some criteria I can enter to get it to evaluate right if there are 2 copays?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Compare two fields (2000)

    What exactly do you want to return if there is more than one copay? Only those specialists and physicians for whom there is no matching copay at all, or do you want to exclude all matches first, and then see if there are non-matches left?

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compare two fields (2000)

    I only want the records where the copays are different. When there is only one copay say 15 dollars for Specialist and 30 for Physician copay then I want that record on the report. If Specialsit copay has a 15 and a 30 dollar copay and the Physician has a 15 and a 30 copay, it should not make the report but it does because it is comparing the specialist 15 to the physician 30 and then it compares the specialist 30 to the physician copay 15. I know this is confusing and maybe I can't use the criteria <> [Specialist Copay] to find where the copays are different.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Compare two fields (2000)

    But if Specialist A has copays 10 and 25, and Physician B has copays 10 and 30,do you want to return the A - 25 / B - 30 pair, or should A and B not be returned at all because there is a matching record for them?

    Are the records in different tables, or in the same table?

    Do you only compare copay, or are there other fields that should match?

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compare two fields (2000)

    Specialist A has copays 10 and 25 and Physician B has copays 10 and 25 too so they should not be returned but what it is doing is evaluating Specialist copay 10 to Physician copay 25 and specialist copay 25 to physician copay 10 so in that way they are different but as a whole they are not.

    The records are in different tables - I have a table for specialist copays and one for physician copays.

    I am matching Group number to Group number and then trying to find records where the copays are different for Physician and specialist.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Compare two fields (2000)

    I understand that, but you haven't answered my question about what should be done if Specialist A has copays 10 and 25, and Physician B has copays 10 and 30.

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compare two fields (2000)

    If the copays are different, then they should appear on the report. I guess my problem is that this particular group has two records because they have 2 copays. All other groups have 1 copay making comparing them easy. This may be one of those times when the only solution is to just delete the records instead of finding a programing solution.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Compare two fields (2000)

    If you have some kind of unique ID for the records, for example an AutoNumber field, you can do it in two steps:

    1. Create a query that returns all matching records (for the same Group)

    2. Create a query that excludes all matching records from both tables.

    See attached example.
    Attached Files Attached Files

Posting Permissions

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