How can I use data from a table as criteria in a query?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

What I am trying to do is use values from a field in one table as criteria in
a query from another table. I can get the query to run fine as long as there
is a perfect match in the field I am querying off of. But the problem I am
having is when I try to use wildcards. I can put what I am looking for along
with wildcards directly in the query and get the requested results, but when
I reference the field in my second table I get either the exact match
(assuming no wildcards are used in the query) or I get nothing if wildcards
are included. Hope that made sense. The main reason for wanting to use a
table for search criteria is to allow the end user the ability to change the
values without having to mess with the queries. I have read the many posts
about using a form to feed the query, however in this situation each query
can have multiple criteria, so I don't think a form will work, unless I am
missing something.

Anyway, I am using Access 2003 and any help would be appreciated and if
further clarification is needed please let me know. Thanks in advance.
 
George said:
What I am trying to do is use values from a field in one table as criteria in
a query from another table. I can get the query to run fine as long as there
is a perfect match in the field I am querying off of. But the problem I am
having is when I try to use wildcards. I can put what I am looking for along
with wildcards directly in the query and get the requested results, but when
I reference the field in my second table I get either the exact match
(assuming no wildcards are used in the query) or I get nothing if wildcards
are included. Hope that made sense. The main reason for wanting to use a
table for search criteria is to allow the end user the ability to change the
values without having to mess with the queries. I have read the many posts
about using a form to feed the query, however in this situation each query
can have multiple criteria, so I don't think a form will work, unless I am
missing something.

Anyway, I am using Access 2003 and any help would be appreciated and if
further clarification is needed please let me know. Thanks in advance.
Click to expand...


You should have posted the SQL of the query that works for
an exact match so we could see what you're trying to do.

Lacking that information, I'll guess the where ever you have
tbl1.fielda = tbl2.fieldx, you should chanke it to something
like:
tbl1.fielda Like "*" & tbl2.fieldx & "*"
 
George, I posted something similar a while ago and the response was that I
should join the 'criteria' table to the 'source' table and set the join so
thyat values are only listed where they are the same.

Something like that anyway!!

Cheers.
Bill
 
Marshall,

Here is my sql:

SELECT tblLocation.Location, tblLocation.Process, Comments.COMMENT
FROM Comments INNER JOIN tblLocation ON Comments.COMMENT = tblLocation.COMMENT
WHERE (((tblLocation.Location) Is Not Null) AND ((Comments.COMMENT) Like "*"
& [tbllocation].[comment] & "*"))
ORDER BY tblLocation.Location, tblLocation.Process;

tblLocation has my criteria in it, Comments is my main table. I have the
first few characters of the Comments.COMMENT in my tblLocation.comment field
and am trying to pull anything in the Comments.COMMENT that has any part of
what is in the tblLocation.comment field. I only get exact matches even with
the wildcards and it doesn't matter how I join the two tables. Hope this
explains it a little better.
 
Your JOIN condition explicitly requests only matching
comments, making the WHERE clause ineffective. I guess my
advice about replacing the = with Like was confusing because
you used the = condition twice.

Scrap the check in the WHERE clause and do it all in the
JOIN clause. You will not be able to specify this kind of
join in the query design grid so you will have to work on
this query exclusively in SQL view.

SELECT L.Location, L.Process,C.COMMENT
FROM Comments As C INNER JOIN tblLocation As L
ON C.COMMENT Like "*" &L.comment & "*"
WHERE L.Location Is Not Null
ORDER BY L.Location, L.Process
--
Marsh
MVP [MS Access]

SELECT tblLocation.Location, tblLocation.Process, Comments.COMMENT
FROM Comments INNER JOIN tblLocation ON Comments.COMMENT = tblLocation.COMMENT
WHERE (((tblLocation.Location) Is Not Null) AND ((Comments.COMMENT) Like "*"
& [tbllocation].[comment] & "*"))
ORDER BY tblLocation.Location, tblLocation.Process;

tblLocation has my criteria in it, Comments is my main table. I have the
first few characters of the Comments.COMMENT in my tblLocation.comment field
and am trying to pull anything in the Comments.COMMENT that has any part of
what is in the tblLocation.comment field. I only get exact matches even with
the wildcards and it doesn't matter how I join the two tables. Hope this
explains it a little better.


Marshall Barton said:
You should have posted the SQL of the query that works for
an exact match so we could see what you're trying to do.

Lacking that information, I'll guess the where ever you have
tbl1.fielda = tbl2.fieldx, you should chanke it to something
like:
tbl1.fielda Like "*" & tbl2.fieldx & "*"
Click to expand...
Click to expand...
 
Marshall,

That works great, thank you very much, never would have thought of that
solution.

I tried the query two different ways, one with the wildcard upfront and one
without. By putting the wildcard in front I got 3,000 more hits, due to the
criteria being somewhere in comment field versus the beginning where it
should be. It now comes down to the old "garbage in garbage out" issue, I
will have to make sure the data is either "fixed" or deal with it. It is
sometimes better to have too much data than not know what is there at all.

Again thanks for your assistance.

Marshall Barton said:
Your JOIN condition explicitly requests only matching
comments, making the WHERE clause ineffective. I guess my
advice about replacing the = with Like was confusing because
you used the = condition twice.

Scrap the check in the WHERE clause and do it all in the
JOIN clause. You will not be able to specify this kind of
join in the query design grid so you will have to work on
this query exclusively in SQL view.

SELECT L.Location, L.Process,C.COMMENT
FROM Comments As C INNER JOIN tblLocation As L
ON C.COMMENT Like "*" &L.comment & "*"
WHERE L.Location Is Not Null
ORDER BY L.Location, L.Process
--
Marsh
MVP [MS Access]

SELECT tblLocation.Location, tblLocation.Process, Comments.COMMENT
FROM Comments INNER JOIN tblLocation ON Comments.COMMENT = tblLocation.COMMENT
WHERE (((tblLocation.Location) Is Not Null) AND ((Comments.COMMENT) Like "*"
& [tbllocation].[comment] & "*"))
ORDER BY tblLocation.Location, tblLocation.Process;

tblLocation has my criteria in it, Comments is my main table. I have the
first few characters of the Comments.COMMENT in my tblLocation.comment field
and am trying to pull anything in the Comments.COMMENT that has any part of
what is in the tblLocation.comment field. I only get exact matches even with
the wildcards and it doesn't matter how I join the two tables. Hope this
explains it a little better.

George wrote:
What I am trying to do is use values from a field in one table as criteria in
a query from another table. I can get the query to run fine as long as there
is a perfect match in the field I am querying off of. But the problem I am
having is when I try to use wildcards. I can put what I am looking for along
with wildcards directly in the query and get the requested results, but when
I reference the field in my second table I get either the exact match
(assuming no wildcards are used in the query) or I get nothing if wildcards
are included. Hope that made sense. The main reason for wanting to use a
table for search criteria is to allow the end user the ability to change the
values without having to mess with the queries. I have read the many posts
about using a form to feed the query, however in this situation each query
can have multiple criteria, so I don't think a form will work, unless I am
missing something.

Anyway, I am using Access 2003 and any help would be appreciated and if
further clarification is needed please let me know. Thanks in advance.
Click to expand...

Marshall Barton said:
You should have posted the SQL of the query that works for
an exact match so we could see what you're trying to do.

Lacking that information, I'll guess the where ever you have
tbl1.fielda = tbl2.fieldx, you should chanke it to something
like:
tbl1.fielda Like "*" & tbl2.fieldx & "*"
Click to expand...
Click to expand...
Click to expand...
 
Back
Top