Results 1 to 3 of 3
  1. #1
    Join Date
    May 2009
    Posts
    29

    Unanswered: Query Build Help

    Hello. Im new to the forums, so in advance, thank you to all the provide help and support!!!!

    Here is what I have. I have a Databse that currently has 2 tables and about 4 querys. What the databse currently does is return back a set of results by Contact Name, Contact Email, Org ID, and Org Domain(s)

    The databse then collects all this information, compiles it and sends out emails via lotus notes.

    Here is what I am looking to do now. After the emails when out, I had about 200 emails rejected (bad email addresses or no longer exist). I have manually gone through the failed emails to obtain the Email address it was sent to and the Org ID. I need to run these results against my Query of "Unique Results" to see which Org ID's that were rejected were not contacted by a different email contact (because we are sending this to multiple contacts per Bank ID.)

    Example: Org ID 1001 has 4 Org Domain(s) and 2 contacts Bob and Susan. Also we have Org ID 1004 has 2 Org Domains and 1 contacts Larry. 3 seperate emails are sent out (1 to each) but 1 email is rejected and returned for Bob. I have manuall read the rejected email and have Bobs email addresss and his Org ID of 1001.

    The Query of rejected email addresses and Org IDs needs to run against my "unique Contacts" query to let me know that Org ID 1001 was still contacted via Susan. SO really...the result of the Query in this Case would be Empty Because Susan at Org ID 1001 was still contacted.

    Ultimatly this new query will be used to fileter down the rejection list of who STILL needs to be contacted because NOBODY at a specific Org ID was contacted.

    I hope this makes sense. I think Im looking too much into this, so I have gone to the net and the forums.

    Please let me know if you need more deatil.

    Thanks!

  2. #2
    Join Date
    May 2009
    Posts
    258
    Something like the following may work:
    Code:
    SELECT * FROM [Unique Results]
    WHERE OrgID NOT IN 
    (SELECT DISTINCT A.OrgID FROM [Unique Results] A
    LEFT JOIN Failed B
    ON A.OrgID = B.OrgID
    AND A.Email = B.Email
    WHERE B.OrgID Is Null)
    This will get everything in the "Unique Results" where the OrgID did not have an email sent successfully. "[Unique Results]" represents your query for sending the emails, "Failed" represents the table with the OrgID and Email address of the failed emails.

    Edit: This would also work:
    Code:
    SELECT * FROM [Unique Results]
    WHERE OrgID NOT IN
    (SELECT DISTINCT A.OrgID FROM [Unique Results] A
    WHERE NOT EXISTS
    (SELECT * FROM Failed B
    WHERE A.OrgID = B.OrgID
    AND A.Email = B.Email))
    Ax
    Last edited by Ax238; 05-28-09 at 17:40.

  3. #3
    Join Date
    May 2009
    Posts
    29
    I think this works!!!!

    Have to validate the data....but i think thats it!!!

    Thanks!!!

Posting Permissions

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