Results 1 to 7 of 7
  1. #1
    14erclimber is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2010
    Posts
    3

    Help: Comparison query to find non-matches

    I am having a hard time figuring this one out. Can anyone help?



    I have two tables linked by primary/foreign key. Both tables have a very similar other field (email address). I need to run a query that will find all situations that the email address fields don't match.

    Example)

    Table 1: which has the foreign key field under "Siebel ID"
    Table 2: which has the primary key field under "Opportunity ID"
    Both tables have an email address field.

    In table 1, I select a Siebel ID from the drop down linked to the Opportunity ID so the tables are related. Then I want to compare the email address fields in both tables and have the query show me all times that the two email address fields don't match.

    I've tried the unmatched query in Access but not getting it to work. It only looks for nulls.

    Can anyone help?

  2. #2
    Join Date
    May 2010
    Posts
    339
    Quote Originally Posted by 14erclimber View Post
    I have two tables linked by primary/foreign key. Both tables have a very similar other field (email address).

    Why are you storing similar info in different tables?

    Please give more info.


    Richard

  3. #3
    14erclimber is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2010
    Posts
    3
    We are tracking lead information (table 1) and bringing in our client's CRM information (table 2) of which has mostly different information. However, the email address is a similar field and we want to be able to verify our information (email address for lead assignment) is the same as in the client's CRM system which has a mind of its own and will/could assign a lead to a different email address.

  4. #4
    kimmer is offline Competent Performer
    Windows 7 Access 2010 (version 14.0)
    Join Date
    May 2010
    Posts
    100
    Try this:
    Create a query with both tables. Add email address from each table to the grid.
    Use the following as criteria for Table 1 email address

    Not [table 2].[email address]

  5. #5
    14erclimber is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2010
    Posts
    3
    Thanks Kimmer but no luck.

    I created a select query with both email fields from the two tables and put the following in the Table 1 email field criteria:

    Not [Siebel_Report_5_18].[Opportunity Primary Sales Team Member Email Address Text]

    I even tried <>.

    Any other ideas?

  6. #6
    kimmer is offline Competent Performer
    Windows 7 Access 2010 (version 14.0)
    Join Date
    May 2010
    Posts
    100
    Quote Originally Posted by 14erclimber View Post
    Thanks Kimmer but no luck.
    Then I am apparently misunderstanding the structure.

  7. #7
    kimmer is offline Competent Performer
    Windows 7 Access 2010 (version 14.0)
    Join Date
    May 2010
    Posts
    100
    So, your tables aren't like these? (see attached file)

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 05-18-2010, 12:05 PM
  2. Find Duplicates Query
    By mulefeathers in forum Queries
    Replies: 13
    Last Post: 04-22-2010, 05:39 PM
  3. Comparison
    By VICTOR HUGO in forum Access
    Replies: 8
    Last Post: 02-10-2010, 04:32 PM
  4. Replies: 1
    Last Post: 07-08-2009, 03:31 PM
  5. Time Comparison
    By Larry819 in forum Queries
    Replies: 1
    Last Post: 06-22-2009, 09:26 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums