How to create a Join when fields are different (1 Viewer)

Number11

Member
Local time
Today, 12:49
Joined
Jan 29, 2020
Messages
607
so i have 2 tables that i need to join within a query issues is that one table is set field as "Number" and the other table is set as a "Text", is their a way around as i cant change the table formats :)
 

plog

Banishment Pending
Local time
Today, 07:49
Joined
May 11, 2011
Messages
11,611
Using the table where it's a number create a query and create a calculated field to convert it to a string:

LinkField: Cstr(Your field)

Be sure to bring in all the other fields as well. Save that query then use it instead of the table it's based on, and JOIN it by the LinkField
 

Isaac

Lifelong Learner
Local time
Today, 05:49
Joined
Mar 14, 2017
Messages
8,738
I would create a query that converts the one to number first (clng()) for example.
Then join to that query.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:49
Joined
Feb 28, 2001
Messages
26,996
As you can tell from the two responses, the trick includes the fact that you can JOIN queries as well as tables. Access just wants two recordsets to do the JOIN.
 

isladogs

MVP / VIP
Local time
Today, 12:49
Joined
Jan 14, 2017
Messages
18,186
There is another similar approach which cuts out the intermediate query.
Add both tables to the query designer but don't join them.
Now add a text field CStr(YourNumberField) and in the filter criteria enter the name of the text field from the other table
 

CJ_London

Super Moderator
Staff member
Local time
Today, 12:49
Joined
Feb 19, 2013
Messages
16,553
or you can use a non standard join

SELECT *
FROM tbl1 INNER JOIN tbl2 ON tbl1.numfield=val(tbl2.textfield)

If your table with the text field has a lot of records, It would be better to convert the text field to a number so that indexing can be applied or add it as a separate field.
 

Users who are viewing this thread

Top Bottom