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

    Unmatched Query Modification (WIN XP 5.1 SP1 Acc97)

    I tried modifying the "Find Unmatched Query" but I seem to have hit a brick wall.

    Table1
    FieldName

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Unmatched Query Modification (WIN XP 5.1 SP1 Acc97)

    Try this:

    SELECT Table1.MEMNUM
    FROM (Table1 LEFT JOIN Table2 ON Table1.MEMNUM = Table2.MEMNUM) LEFT JOIN Table2 AS Table2_1 ON Table1.MEMNUM = Table2_1.AMI
    WHERE Table2.MEMNUM Is Null OR Table2_1.AMI Is Null

    What you need is all the records in Table1 (or at least all the MEMNUMs) that don't match the MEMNUM in Table 2 OR that don't match the AMI in Table2. You handle that by joining the same table2 twice, aliasing it as Table2_1 the second time.
    Charlotte

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Unmatched Query Modification (WIN XP 5.1 SP1 Acc97)

    Now you're confusing me. I used your SQL as the basis for the query, but your explanation in the original post says "MBRNUM", and I have no idea what you hope to achieve by doing it in reverse, which only requires using Table2 as the first table and adding Table1 twice with the appropriate joins. The spreadsheet doesn't clear anything up. In some rows, you have the same values in both fields in Table2, and I see no purpose to that. Why are you trying to compare these tables two ways, when only one direction is normally meaningful? Instead of saying what the result should look like, tell us what you're trying to accomplish. <img src=/S/confused.gif border=0 alt=confused width=15 height=20>
    Charlotte

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

    Re: Unmatched Query Modification (WIN XP 5.1 SP1 Acc97)

    Table1 is our main database.
    Table2 is a monthly list received from our client.

    Every month we run the monthly list into our system, which adds new members and deletes members based on an activity code on the monthly list.

    MEMNUM represents the member

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

    Re: Unmatched Query Modification (WIN XP 5.1 SP1 Acc97)

    Thanks Charlotte - I actually changed
    WHERE Table2.MEMNUM Is Null OR Table2_1.AMI Is Null
    to
    WHERE Table2.MEMNUM Is Null AND Table2_1.AMI Is Null
    This gives the desired result for Query1.

    Now to do it in reverse? I need the results as attached. (I can attach the db if necessary)

    Thank you so much !!

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Unmatched Query Modification (WIN XP 5.1 SP1 Acc97)

    You previously failed to mention the AMI field in Table1 and the fact that, according to your explanation, your download only returns a single value. Does that mean, you only get a single column and have to tell by the content which field it represents?
    Charlotte

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

    Re: Unmatched Query Modification (WIN XP 5.1 SP1 Acc97)

    Does this do what you want? It's the same as Query1, but with the tables switched.

    SELECT Table2.*
    FROM (Table2 LEFT JOIN Table1 ON Table2.MemNum = Table1.AMI) LEFT JOIN Table1 AS Table1_1 ON Table2.AMI = Table1_1.AMI
    WHERE (((Table1.AMI) Is Null) AND ((Table1_1.AMI) Is Null));

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

    Re: Unmatched Query Modification (WIN XP 5.1 SP1 Acc97)

    Although Table1 contains both fields, when we download the table from the system it only includes the AMI # (Which will be the either the true AMI# or the SSN if an AMI was not provided) This is a company program that I cannot change.
    So I am basically bumping a single field (AMI) from Table1 against 2 fields in Table2.

    When I bump Table1 against Table2, I am checking to see if any members (based on AMI) are in Table1 that are NOT in Table2(based against EITHER the AMI or MEMNUM in Table2)

    When I bump Table2 against Table1, I am checking to see if any members (based on AMI or MEMNUM) are in Table2 that are NOT in Table1(based against Table1's AMI)

    In the IDX system, we enter a MEMNUM (SSN) - the AMI defaults to the SSN unless the client supplies a unique AMI. Then the AMI populates the AMI field in the system.

    So either way, the AMI field in the system is the number we would use to audit against the monthly files.
    Because the monthly files have both a MEMNUM field AND a AMI field, in order to audit our system, we
    bump the two tables to see who is in one table that isn't in the other and vice versa. .

    Probably too wordy an explanation, but that should tell the story <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

    edited to add:

    Here is what I wound up with:
    Query1: As listed in previous post - works great.

    Query2:
    SELECT Table2.MEMNUM
    FROM (Table2 LEFT JOIN table1 AS Table1_1 ON Table2.AMI = Table1_1.MEMNUM) LEFT JOIN Table1 ON Table2.MEMNUM = Table1.MEMNUM
    WHERE (((Table1.MEMNUM) Is Null) AND ((Table1_1.MEMNUM) Is Null));

    THANK YOU AGAIN !!

Posting Permissions

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