Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Find matched query results (WIN XP/Access 2003)

    I love Access - I hate Access.

    Yesterday this query worked fine. Today, it doesn't.
    Recovery Spreadsheet table has 413 records (includes some duplicates)
    Managed Care Spreadsheet has 391 records (includes some duplicates)

    Yesterday I ran the below "Find Matches" query, and it returned 75 records. Today it returns 3800+ records.
    If Mem123 has 9 records in one table and 7 in the other, it is now coming up with 63 records.
    Did a setting get corrupt? How can I fix this?

    SELECT [Recovery Spreadsheet].[ID #]
    FROM [Recovery Spreadsheet] LEFT JOIN [Managed Care Spreadsheet] ON [Recovery Spreadsheet].[ID #] = [Managed Care Spreadsheet].[Member #]
    WHERE ((([Managed Care Spreadsheet].[Member #]) Is Not Null));

    Thank you for looking !

    Michael

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

    Re: Find matched query results (WIN XP/Access 2003)

    From your description it looks like the (left) join between the tables has been deleted from the query. Are you sure that the SQL you posted is still the SQL of the query?

  3. #3
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find matched query results (WIN XP/Access 2003)

    I just went into SQL view and this is what is there:
    SELECT [Recovery Spreadsheet].[ID #]
    FROM [Recovery Spreadsheet] LEFT JOIN [Managed Care Spreadsheet] ON [Recovery Spreadsheet].[ID #] = [Managed Care Spreadsheet].[Member #]
    WHERE ((([Managed Care Spreadsheet].[Member #]) Is Not Null));

    I used the Find Unmatched Wizard and changed Is Null to Is Not Null.

    Does it have to do with the duplicates in each table? Or do I need to tweak the query?

    Thank you.

    Michael

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

    Re: Find matched query results (WIN XP/Access 2003)

    Try changing SELECT to SELECT DISTINCT. Does that improve things?

  5. #5
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find matched query results (WIN XP/Access 2003)

    That helped - I will probably have to tweak it further to get the duplicates to show from the Recovery Spreadsheet. side

    Thank you Hans !

    Michael

Posting Permissions

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