You need to join the two tables in the query window.
Your SQL for the query would look something like
The type of join you select will determine if only complete matches are returned.Code:SELECT Table2.ID, Table2.[Number], [text] From Table1 Inner Join Table2 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 ↓↓
Hello Minty,
I am getting a "Type mismatch in expression"
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 ↓↓
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
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 ↓↓
Thank you Minty.
I got it working now!
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 ↓↓