Results 1 to 5 of 5
  1. #1
    aperring is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2018
    Posts
    2

    Creating a Query to Copmare two code feilds to a Key in another table and display only the matches

    I am newer to access and I need to create a query.

    In Table A I have an ID number and two fields that have 2 character codes.
    ID Code A CodeB
    1234A AX B1
    2345B B1
    3456A C5 AX
    4567A ZZ QT

    In Table B I have a List of Codes as a Key.
    Code Key
    AX
    B3
    ZZ



    I need to reference the two fields of codes against the key and display only the ID # that have those codes in either field.

    Results
    ID
    1234A
    3456A
    4567A

    Any help given would be appreciated.

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Welcome to the forum

    Create a query with table A joined to table B using the fields Code A & Code Key. Add the ID field
    Now drag another copy of table B to the query designer window and join Code B to Code Key.

    Done!
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    SELECT TableA.ID
    FROM TableB INNER JOIN TableA ON (TableB.CodeKey = TableA.CodeB) or (TableB.CodeKey = TableA.CodeA);

  4. #4
    Peter M is offline Advanced Beginner
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2017
    Posts
    67
    Combing the responses from Ridders52 and Davegri, you drag TableB (reference codes) into the query pane twice and join it to TableA with the Join Properties set to include ALL Records from TableA and only those records from TableB (do this twice, once for CodeA and once for CodeB)
    You then need to filter for BOTH instances where TableB is joined to either be Not Null or some other criteria. I add a "Selected" field and set it to True. You also want to set the query results to Group By otherwise you could get duplication.
    See the attached screenshot for details.
    How to join one table to two columns.

  5. #5
    aperring is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2018
    Posts
    2
    Thank you all for the quick response. I have figured it out.

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

Similar Threads

  1. Replies: 3
    Last Post: 03-02-2018, 01:51 PM
  2. Replies: 7
    Last Post: 02-29-2016, 06:13 PM
  3. Replies: 4
    Last Post: 08-01-2014, 09:20 AM
  4. Replies: 15
    Last Post: 08-30-2012, 04:16 PM
  5. Replies: 3
    Last Post: 06-22-2012, 04:19 PM

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