Solved Is it possible to use (like) in a query for a field in another table

[Capitala]

Good day!
Is it possible to make a query based on a table (employee_details) and under the criteria of the field (city) put the following criteria: like * reference_table *
Reference_table always has one record only. Can the query fetch the data from another table?

Thanks in advance

 

[plog]

No. But that doesn't mean the ultimate aim is unachievable. You just need to tell us the ultimate aim.

It's unclear what you want to do, so I suggest you demonstrate your issue with a concrete example. Show us sample data from employee_details, show us what is in Reference_table and then show us what results you expect to end up with when all is said and done.

My guess is that this involves either a Dlookup or brining Reference_table into the query itself.

 

[June7]

Something like:

SELECT table.* FROM table INNER JOIN reference_table ON table.city = reference_table.city

or

SELECT * FROM table WHERE city in (SELECT city FROM reference_table)

 

[Capitala]

Hi, it doesn't work
Attached a simple DB for illustration.

 

[Mark_]

Please define "Doesn't work". What are you expecting and what are you seeing?
Does the database cause your monitor to shoot flame out?
Is it failing to elect the right lizard to office?

 

[Capitala]

No. But that doesn't mean the ultimate aim is unachievable. You just need to tell us the ultimate aim.

It's unclear what you want to do, so I suggest you demonstrate your issue with a concrete example. Show us sample data from employee_details, show us what is in Reference_table and then show us what results you expect to end up with when all is said and done.

My guess is that this involves either a Dlookup or brining Reference_table into the query itself.

I already attached a simple sample of the tables. thanks for your kind interest

 

[plog]

This should do it:

SELECT emp_details.*
FROM emp_details, reference_table
WHERE (((InStr([emp_details].[city],[reference_table].[city]))>0));

Of course it looks for 'cai' anywhere in the emp_details.city field. So that means a city called 'Ancaid' would be a match

 

[Isaac]

PS. In MS Access can you join on creative conditions, like on some column like *something* ?
I don't know, thus asking as I don't use Access much any more

 

[plog]

Yes, its you can use more than equijoins (those using just = sign):

INNER JOIN ABC ON SomeField >= OtherField
INNER JOIN DEF ON FieldX != FieldY
INNER JOIN GHI ON Field1 Like (Field2)

 

[Capitala]

This should do it:

SELECT emp_details.*
FROM emp_details, reference_table
WHERE (((InStr([emp_details].[city],[reference_table].[city]))>0));

Of course it looks for 'cai' anywhere in the emp_details.city field. So that means a city called 'Ancaid' would be a match

IT works more than great!!
Thaaaaaaaaaaaaaaaaaaaaaaaaaank

 

[Isaac]

Yes, its you can use more than equijoins (those using just = sign):

INNER JOIN ABC ON SomeField >= OtherField
INNER JOIN DEF ON FieldX != FieldY
INNER JOIN GHI ON Field1 Like (Field2)

I bet the QBE fails after that tho
Becomes sql-only. i suppose that makes sense if so

 

[DHookom]

You can create joins with like although the join can't be represented in design view.

I created a small table [tblCompanyNameParts] with a short text field [CompanyPart] and added a couple records with values "co" and "ow". I then created a query:

SELECT Companies.CompanyID, Companies.CompanyName, tblCompanyNameParts.CompanyPart, tblCompanyNameParts.CompanyInfo
FROM tblCompanyNameParts INNER JOIN Companies ON Companies.CompanyName LIKE "*" & tblCompanyNameParts.CompanyPart & "*";

And got this result. If both co and ow are in the company name, it will appear twice.