SQL lIke and Access DB

[RosieGp]

i have to retrieve data from access based on Emp_ID extracted from Sql DB. the queries looks like the following:

Can i do 3 queries and do something like the following:

[COLOR=blue]
<cfquery datasource="MasterSQL" username="***" password="***" name="SQLQRY">
     Select Employee.Emp_Name, Salary.Emp_ID
     From Employee inner join Salary on (Employee.Emp_ID = Salary.Emp_ID) AND (Employee.SectionName = Salary.SectionName)
     Where Emp_Name like <cfqueryparam value="%#arguments.search#%" cfsqltype="Sql_VarChar">
</cfquery>
 
<cfquery datasource="MasterDB" name="AccessQRY">
     Select Emp_Pro.Emp_ID
     From Emp_Pro 
     WHERE Emp_Pro.Emp_ID like ("%#ValueList(SQLQRY.Emp_ID)#%")
</cfquery>
 
<cfquery datasource="MasterDB" name="AccessQRY2">
     Select Emp_Info.Desc,
     From Emp_Info inner join Emp_Pro on (Emp_Info.Emp_ID = Emp_Pro.Emp_ID) AND (Emp_Info.Con = Emp_Pro.Con)
     WHERE Emp_Info.Emp_ID IN
 [/color]
[COLOR=red]
[b]this block will change too...[/b]
( <cfqueryparam
value="#ValueList(SQLQRY.Emp_ID)#"
cfsqltype="cf_sql_integer"
list="true">
)
</cfquery>
[/color red]

What do i have to change to make this query work...

 

[RosieGp]

<cfquery datasource="MasterSQL" username="***" password="***" name="SQLQRY">
     Select Employee.Emp_Name, Salary.Emp_ID
     From Employee inner join Salary on (Employee.Emp_ID = Salary.Emp_ID) AND (Employee.SectionName = Salary.SectionName)
     Where Emp_Name like <cfqueryparam value="%#arguments.search#%" cfsqltype="Sql_VarChar">
</cfquery>
 
<cfquery datasource="MasterDB" name="AccessQRY">
     Select Emp_Pro.Emp_ID
     From Emp_Pro 
     WHERE Emp_Pro.Emp_ID like "%#(SQLQRY.Emp_ID)#%"
</cfquery>
 
<cfquery datasource="MasterDB" name="AccessQRY2">
     Select Emp_Info.Desc,
     From Emp_Info inner join Emp_Pro on (Emp_Info.Emp_ID = Emp_Pro.Emp_ID) AND (Emp_Info.Con = Emp_Pro.Con)
     WHERE Emp_Info.Emp_ID IN
 

this block will change too...
( <cfqueryparam
value="#ValueList(SQLQRY.Emp_ID)#"
cfsqltype="cf_sql_integer"
list="true">
)
</cfquery>

The first query is returning me Emp_ID from SQL Server.

I need to match these Emp_ID with Emp_ID in access database and extract the Emp_Desc from Access.

The problem is the Emp_ID looks like the following:


The Emp_ID from the first query (Sql Server)looks like the following:
A 1000
A 1123
A 23456
B 450674


The Emp_ID in Access DB looks like
1000**
1123***
23456**
450674***
If i just use WHERE IN it doesn't return anything so I have to use LIKE some how to match these Emp_ID...

is it possible to use an escape charater in this case for example:

WHERE Emp_Pro.Emp_ID LIKE "' %' escape' '#SQLQRY.Emp_ID#%"

the idea here is to look after the space A 1000 so that it could be matched to Emp.ID in Access that looks like 1000**