Results 1 to 8 of 8
  1. #1
    mk01111000 is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2020
    Posts
    5

    How do I get values from 1 table where there is a common value in both tables

    I have the following 2 tables:








    I'd like to create a query, equal to table 2 with all the "text"-values from table1 where the "number"-value is the same.
    I am still new to access, and unfortunately unable to solve this.

    The desired outcome is as follows:



  2. #2
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    You need to join the two tables in the query window.

    Your SQL for the query would look something like

    Code:
    SELECT Table2.ID, Table2.[Number], [text] 
    From Table1 Inner Join Table2 
    On Table2.[Number] = Table1.[Number]
    The type of join you select will determine if only complete matches are returned.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    mk01111000 is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2020
    Posts
    5
    Hello Minty,

    I am getting a "Type mismatch in expression"

  4. #4
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Is the Number field in one table text and the other a number?
    If so that will cause you an issue.

    And whilst I realise this is probably only demo data, ID, Number and Text as are poor choices of field names.
    Number and Text are both reserved words in access, and ID is very non descript, ID of what?

    Field names like EntryID, TextDesc, and DataValue will mean much more to you later in your development, and avoid the need for square brackets everywhere, note the lack of spaces and special characters as well.

    Back to your question / error - You will either need to either change the data type (as a rule you shouldn't store numbers in a text field) or perform some further fiddling in the query.
    If you change the data type it should work, if that is not possible come back and we'll explore the other possibilities.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  5. #5
    mk01111000 is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2020
    Posts
    5
    Thank you Minty for your explaining this in detail. Helps me a lot.
    I think you got me almost there.



    Number 1315 is duplicated in the result.
    I'd prefer a list, equal to Table2, with an extra field for the text

  6. #6
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    If you look at the query in the design view, double click on the join between the two tables.
    Select the option that gives you "All records from Table2 and only the ones that match in Table1"

    This determines the "direction" of the join, e.g. which table is considered the "master" as far as this query is concerned.

    Then pick the fields you want to see.

    If you don't want duplicates there is an option for "Unique Records Only"
    This will add the Distinct keyword to the SQL statement.

    Code:
    SELECT DISTINCT Table2.ID, Table2.[Number], [text]
    FROM Table2 LEFT JOIN Table1
    ON Table2.[Number] = Table1.[Number]
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  7. #7
    mk01111000 is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2020
    Posts
    5
    Thank you Minty.
    I got it working now!

  8. #8
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Quote Originally Posted by mk01111000 View Post
    Thank you Minty.
    I got it working now!
    Excellent, good luck with the rest of your project!
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

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

Similar Threads

  1. Replies: 7
    Last Post: 03-26-2020, 06:09 AM
  2. Replies: 5
    Last Post: 08-08-2019, 11:10 AM
  3. Replies: 2
    Last Post: 09-28-2017, 06:56 PM
  4. Replies: 3
    Last Post: 04-04-2015, 01:17 PM
  5. Common entries within multiple tables
    By JesterMania in forum Queries
    Replies: 4
    Last Post: 08-02-2011, 02:57 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