Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Nov 2004
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Matches & Differences (97)

    I have a data set where I need to identify those cases where the date and claim number are the same but have different treatments and providers.

    I have tried doing this running a combination of find duplicates and find unmatched queries but this has failed because some cases are mixed, for example where there are two rows where date = claim number = treatment = provider and one row which doesn't. This means that the end result of all my queries is that one row will be returned and I need to pick up all three - or however many there are.

    Is there some clever way of getting what I need to do out of one query please?

    Many thanks

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

    Re: Matches & Differences (97)

    Yes, it is possible, but it would be easier to show how if we could see the table. Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  3. #3
    Star Lounger
    Join Date
    Apr 2004
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Matches & Differences (97)

    Hi

    Apologies for delay. Problems in uploading file. I have stripped the table of most columns and provided a short set of dummy data. Hopefully this is enough for you to work with.

    Many thanks

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

    Re: Matches & Differences (97)

    Create a query in design view.
    Add the table twice to the table grid.
    Join the two instances of the table on Clain and Date. This means that the query will only return records having the same claim number and date.
    Add all fields from the first instance, or *.
    Add the SUPPLIER and TREATMENT fields from the second instance.
    If you want to return the records where at least one of these is different from the corresponding field in the first instance, enter criteria as in the screenshot below.
    If you want to return the records where both fields are different from the corresponding fields in the first instance, place both conditions in the first criteria line.

  5. #5
    Star Lounger
    Join Date
    Apr 2004
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Matches & Differences (97)

    Brilliant, elegant solution. Absolute genius. What more can I say? Except thank you very much.

Posting Permissions

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