Wildcard on an "INNER JOIN"

M

mark909

I was wondring if it is possible to do a wildcard on an inner join and if so
what would be the correct SQL to get the wildcard to function?

The query im working on has multiple joins but i only need the wildcard to
function on one of them.

SELECT CIVILS_LINE.DUCT_TYPE, CIVILS_LINE.SURFACE_TYPE,
CIVILS_LINE.DETAILED_SURFACE_TYPE, Sum(CIVILS_LINE.LENGTH) AS SumOfLENGTH

FROM CIVILS_LINE INNER JOIN ROUTE_INSTALL_COSTS ON
(CIVILS_LINE.CIVILS_LINE_TYPE = ROUTE_INSTALL_COSTS.CIVILS_LINE_TYPE) AND
(CIVILS_LINE.DUCT_TYPE=ROUTE_INSTALL_COSTS.Duct_Type) AND
(CIVILS_LINE.Surface_Type = ROUTE_INSTALL_COSTS.Surface_Type) AND
(CIVILS_LINE.Detailed_Surface_Type =
ROUTE_INSTALL_COSTS.Detailed_Surface_Type)

GROUP BY CIVILS_LINE.DUCT_TYPE, CIVILS_LINE.SURFACE_TYPE,
CIVILS_LINE.DETAILED_SURFACE_TYPE, CIVILS_LINE.CIVILS_LINE_TYPE,
CIVILS_LINE.PROJECT_ID, ROUTE_INSTALL_COSTS.CONTRACTORS_CIVILS

HAVING
(((CIVILS_LINE.PROJECT_ID)=[Forms]![frmCivJobPackInst]![txtProject_ID]) AND
((ROUTE_INSTALL_COSTS.CONTRACTORS_CIVILS)=[Forms]![frmCivJobPackInst]![txtContractors_Civils]));

I want to be able to wildcard the following join within the SQL:

AND (CIVILS_LINE.DUCT_TYPE=ROUTE_INSTALL_COSTS.Duct_Type)

Thanks for any help :)
 
D

Dale Fye

Not sure what you mean by "Wildcard" the join.

do you mean that you the value in Civils_Line.Duct_Type is not exactly the
same as in Route_Install_Costs.Duct_Type, but that one or the other contains
text that is similar to the the other? If so, you could modify the join (you
won't be able to view this in design view, only in SQL) to use the Like
operator:

AND (Civils_Line.Duct_Type Like "*" & Route_Install_Costs.Duct_Type & "*")
 
M

Marshall Barton

mark909 said:
I was wondring if it is possible to do a wildcard on an inner join and if so
what would be the correct SQL to get the wildcard to function?

The query im working on has multiple joins but i only need the wildcard to
function on one of them.

SELECT CIVILS_LINE.DUCT_TYPE, CIVILS_LINE.SURFACE_TYPE,
CIVILS_LINE.DETAILED_SURFACE_TYPE, Sum(CIVILS_LINE.LENGTH) AS SumOfLENGTH

FROM CIVILS_LINE INNER JOIN ROUTE_INSTALL_COSTS ON
(CIVILS_LINE.CIVILS_LINE_TYPE = ROUTE_INSTALL_COSTS.CIVILS_LINE_TYPE) AND
(CIVILS_LINE.DUCT_TYPE=ROUTE_INSTALL_COSTS.Duct_Type) AND
(CIVILS_LINE.Surface_Type = ROUTE_INSTALL_COSTS.Surface_Type) AND
(CIVILS_LINE.Detailed_Surface_Type =
ROUTE_INSTALL_COSTS.Detailed_Surface_Type)

GROUP BY CIVILS_LINE.DUCT_TYPE, CIVILS_LINE.SURFACE_TYPE,
CIVILS_LINE.DETAILED_SURFACE_TYPE, CIVILS_LINE.CIVILS_LINE_TYPE,
CIVILS_LINE.PROJECT_ID, ROUTE_INSTALL_COSTS.CONTRACTORS_CIVILS

HAVING
(((CIVILS_LINE.PROJECT_ID)=[Forms]![frmCivJobPackInst]![txtProject_ID]) AND
((ROUTE_INSTALL_COSTS.CONTRACTORS_CIVILS)=[Forms]![frmCivJobPackInst]![txtContractors_Civils]));

I want to be able to wildcard the following join within the SQL:

AND (CIVILS_LINE.DUCT_TYPE=ROUTE_INSTALL_COSTS.Duct_Type)
Click to expand...


Instead of using
t1.a = t2.b
you can use
t1.a LIKE t2.b & "*"
or some other wildcard pattern.

BUT, only the = joins can be done using the query designer.
Joins using <, >, LIKE, etc. MUST be done in SQL view and
can not be switched back to the query designer without
making a mess of it.
 
M

mark909

Yes Dale thats exactly what i mean. Sorry if not using the correct teminology
but im still pretty new to access.

Ive got it working now and thankyou very much for your help.

However the query is displaying the text from CIVILS_LINE.DUCT_TYPE. I want
to show the text from ROUTE_INSTALL_COSTS.DUCT_TYPE

Ive tried

AND (ROUTE_INSTALL_COSTS.Duct_Type Like “*†& CIVILS_LINE.DUCT_TYPE & “*â€)

But had no joy. How would i write the SQL properly in order to do this please?

Thanks again for your help!




Dale Fye said:
Not sure what you mean by "Wildcard" the join.

do you mean that you the value in Civils_Line.Duct_Type is not exactly the
same as in Route_Install_Costs.Duct_Type, but that one or the other contains
text that is similar to the the other? If so, you could modify the join (you
won't be able to view this in design view, only in SQL) to use the Like
operator:

AND (Civils_Line.Duct_Type Like "*" & Route_Install_Costs.Duct_Type & "*")

----
HTH
Dale



mark909 said:
I was wondring if it is possible to do a wildcard on an inner join and if so
what would be the correct SQL to get the wildcard to function?

The query im working on has multiple joins but i only need the wildcard to
function on one of them.

SELECT CIVILS_LINE.DUCT_TYPE, CIVILS_LINE.SURFACE_TYPE,
CIVILS_LINE.DETAILED_SURFACE_TYPE, Sum(CIVILS_LINE.LENGTH) AS SumOfLENGTH

FROM CIVILS_LINE INNER JOIN ROUTE_INSTALL_COSTS ON
(CIVILS_LINE.CIVILS_LINE_TYPE = ROUTE_INSTALL_COSTS.CIVILS_LINE_TYPE) AND
(CIVILS_LINE.DUCT_TYPE=ROUTE_INSTALL_COSTS.Duct_Type) AND
(CIVILS_LINE.Surface_Type = ROUTE_INSTALL_COSTS.Surface_Type) AND
(CIVILS_LINE.Detailed_Surface_Type =
ROUTE_INSTALL_COSTS.Detailed_Surface_Type)

GROUP BY CIVILS_LINE.DUCT_TYPE, CIVILS_LINE.SURFACE_TYPE,
CIVILS_LINE.DETAILED_SURFACE_TYPE, CIVILS_LINE.CIVILS_LINE_TYPE,
CIVILS_LINE.PROJECT_ID, ROUTE_INSTALL_COSTS.CONTRACTORS_CIVILS

HAVING
(((CIVILS_LINE.PROJECT_ID)=[Forms]![frmCivJobPackInst]![txtProject_ID]) AND
((ROUTE_INSTALL_COSTS.CONTRACTORS_CIVILS)=[Forms]![frmCivJobPackInst]![txtContractors_Civils]));

I want to be able to wildcard the following join within the SQL:

AND (CIVILS_LINE.DUCT_TYPE=ROUTE_INSTALL_COSTS.Duct_Type)

Thanks for any help :)
Click to expand...
Click to expand...
 
D

Dale Fye

Mark,

Just change the SELECT part of the sql to reflect
Route_install_Costs.Duct_Type instead of (or in addition to)
Civils_Line.Duct_type.

I'd probably start out by displaying both, to make sure that my query was
doing what I wanted, then drop the one you don't want.

----
HTH
Dale



mark909 said:
Yes Dale thats exactly what i mean. Sorry if not using the correct teminology
but im still pretty new to access.

Ive got it working now and thankyou very much for your help.

However the query is displaying the text from CIVILS_LINE.DUCT_TYPE. I want
to show the text from ROUTE_INSTALL_COSTS.DUCT_TYPE

Ive tried

AND (ROUTE_INSTALL_COSTS.Duct_Type Like “*†& CIVILS_LINE.DUCT_TYPE & “*â€)

But had no joy. How would i write the SQL properly in order to do this please?

Thanks again for your help!




Dale Fye said:
Not sure what you mean by "Wildcard" the join.

do you mean that you the value in Civils_Line.Duct_Type is not exactly the
same as in Route_Install_Costs.Duct_Type, but that one or the other contains
text that is similar to the the other? If so, you could modify the join (you
won't be able to view this in design view, only in SQL) to use the Like
operator:

AND (Civils_Line.Duct_Type Like "*" & Route_Install_Costs.Duct_Type & "*")

----
HTH
Dale



mark909 said:
I was wondring if it is possible to do a wildcard on an inner join and if so
what would be the correct SQL to get the wildcard to function?

The query im working on has multiple joins but i only need the wildcard to
function on one of them.

SELECT CIVILS_LINE.DUCT_TYPE, CIVILS_LINE.SURFACE_TYPE,
CIVILS_LINE.DETAILED_SURFACE_TYPE, Sum(CIVILS_LINE.LENGTH) AS SumOfLENGTH

FROM CIVILS_LINE INNER JOIN ROUTE_INSTALL_COSTS ON
(CIVILS_LINE.CIVILS_LINE_TYPE = ROUTE_INSTALL_COSTS.CIVILS_LINE_TYPE) AND
(CIVILS_LINE.DUCT_TYPE=ROUTE_INSTALL_COSTS.Duct_Type) AND
(CIVILS_LINE.Surface_Type = ROUTE_INSTALL_COSTS.Surface_Type) AND
(CIVILS_LINE.Detailed_Surface_Type =
ROUTE_INSTALL_COSTS.Detailed_Surface_Type)

GROUP BY CIVILS_LINE.DUCT_TYPE, CIVILS_LINE.SURFACE_TYPE,
CIVILS_LINE.DETAILED_SURFACE_TYPE, CIVILS_LINE.CIVILS_LINE_TYPE,
CIVILS_LINE.PROJECT_ID, ROUTE_INSTALL_COSTS.CONTRACTORS_CIVILS

HAVING
(((CIVILS_LINE.PROJECT_ID)=[Forms]![frmCivJobPackInst]![txtProject_ID]) AND
((ROUTE_INSTALL_COSTS.CONTRACTORS_CIVILS)=[Forms]![frmCivJobPackInst]![txtContractors_Civils]));

I want to be able to wildcard the following join within the SQL:

AND (CIVILS_LINE.DUCT_TYPE=ROUTE_INSTALL_COSTS.Duct_Type)

Thanks for any help :)
Click to expand...
Click to expand...
Click to expand...
 
M

mark909

Brilliant! Thankyou very much for your help Dale ive got it working now :)

Dale Fye said:
Mark,

Just change the SELECT part of the sql to reflect
Route_install_Costs.Duct_Type instead of (or in addition to)
Civils_Line.Duct_type.

I'd probably start out by displaying both, to make sure that my query was
doing what I wanted, then drop the one you don't want.

----
HTH
Dale



mark909 said:
Yes Dale thats exactly what i mean. Sorry if not using the correct teminology
but im still pretty new to access.

Ive got it working now and thankyou very much for your help.

However the query is displaying the text from CIVILS_LINE.DUCT_TYPE. I want
to show the text from ROUTE_INSTALL_COSTS.DUCT_TYPE

Ive tried

AND (ROUTE_INSTALL_COSTS.Duct_Type Like “*†& CIVILS_LINE.DUCT_TYPE & “*â€)

But had no joy. How would i write the SQL properly in order to do this please?

Thanks again for your help!




Dale Fye said:
Not sure what you mean by "Wildcard" the join.

do you mean that you the value in Civils_Line.Duct_Type is not exactly the
same as in Route_Install_Costs.Duct_Type, but that one or the other contains
text that is similar to the the other? If so, you could modify the join (you
won't be able to view this in design view, only in SQL) to use the Like
operator:

AND (Civils_Line.Duct_Type Like "*" & Route_Install_Costs.Duct_Type & "*")

----
HTH
Dale



:

I was wondring if it is possible to do a wildcard on an inner join and if so
what would be the correct SQL to get the wildcard to function?

The query im working on has multiple joins but i only need the wildcard to
function on one of them.

SELECT CIVILS_LINE.DUCT_TYPE, CIVILS_LINE.SURFACE_TYPE,
CIVILS_LINE.DETAILED_SURFACE_TYPE, Sum(CIVILS_LINE.LENGTH) AS SumOfLENGTH

FROM CIVILS_LINE INNER JOIN ROUTE_INSTALL_COSTS ON
(CIVILS_LINE.CIVILS_LINE_TYPE = ROUTE_INSTALL_COSTS.CIVILS_LINE_TYPE) AND
(CIVILS_LINE.DUCT_TYPE=ROUTE_INSTALL_COSTS.Duct_Type) AND
(CIVILS_LINE.Surface_Type = ROUTE_INSTALL_COSTS.Surface_Type) AND
(CIVILS_LINE.Detailed_Surface_Type =
ROUTE_INSTALL_COSTS.Detailed_Surface_Type)

GROUP BY CIVILS_LINE.DUCT_TYPE, CIVILS_LINE.SURFACE_TYPE,
CIVILS_LINE.DETAILED_SURFACE_TYPE, CIVILS_LINE.CIVILS_LINE_TYPE,
CIVILS_LINE.PROJECT_ID, ROUTE_INSTALL_COSTS.CONTRACTORS_CIVILS

HAVING
(((CIVILS_LINE.PROJECT_ID)=[Forms]![frmCivJobPackInst]![txtProject_ID]) AND
((ROUTE_INSTALL_COSTS.CONTRACTORS_CIVILS)=[Forms]![frmCivJobPackInst]![txtContractors_Civils]));

I want to be able to wildcard the following join within the SQL:

AND (CIVILS_LINE.DUCT_TYPE=ROUTE_INSTALL_COSTS.Duct_Type)

Thanks for any help :)
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

An Inner Join on an Inner Join 1
How do you ignore entire tables in an Inner Join Query? 16
Can inner join be done on queries? 8
delete query using inner join 2
Join on a UNION query 2
Append query with an Inner Join to Destination Table Not working 2
Inner join on table with NULL's 10
Return top 2 records on an inner join table 1

Top