Access 2007: Can I use a wildcard to represent all values in a Inner Join

Hi, in the simplified query below, is there a wildcard I can use to pull ALL "ID"?  In the attached example, this query will pull all IDs if the value in Table2.Group is A.  How do I make the query return all IDs for both group A and group B without having to list both A, B in the Table2.Group column?  Thanks.

SELECT Table1.ID
FROM Table1 INNER JOIN Table2 ON Table1.Group = Table2.Group;

test.accdb
JCJGAsked:
Who is Participating?
 
ThomasianConnect With a Mentor Commented:
The query I posted in http:#a34994817 should work that way.

See query2 in the attached db
test.accdb
0
 
als315Commented:
May you show expected result?
You can use left join and have all ID's:

SELECT Table1.ID, Table2.Group
FROM Table1 LEFT JOIN Table2 ON Table1.Group = Table2.Group;
0
 
JCJGAuthor Commented:
I want the flexibilty to produce result according to the value specified in the group column. Table2 will be a linked table from Excel and the group value will be user input.  Sometimes we may want to see result for one group and sometimes we may want to see everything.  There too many values for the user to input if we want the query to return everything.
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
als315Commented:
You can use for table2 next values: A,B,*

SELECT Table1.ID, Table1.Group
FROM Table1, Table2
WHERE (((IIf([Table2]![Group]="*",True,IIf([Table2]![Group]=[Table1]![Group],True,False)))=True));
0
 
ThomasianCommented:
This query will pull all ids from table1 as long as a wildcard ('*') exists in table2.
SELECT Table1.ID
FROM Table1
WHERE EXISTS(SELECT * FROM Table2 WHERE Table1.Group = Table2.Group OR Table2.Group='*')

Open in new window

0
 
JCJGAuthor Commented:
Thanks, Thomasian.  It works except when I changed the name of Table2 to a name with the character "-" then it gave me an error message of "Syntax error in query expression....."

Do you know why?
0
 
ThomasianCommented:
If your table name has special characters, you need to enclose it with square brackets.

i.e.
SELECT [Table1].ID
FROM [Table1]
WHERE EXISTS(SELECT * FROM [Table-2] WHERE [Table1].Group = [Table-2].Group OR [Table-2].Group='*')

Open in new window

0
 
JCJGAuthor Commented:
Thanks! It works.

Can I also make the query to return results on user-defined value with wildcard?  For example, if the user defined value in Table2 Group "*A*" aiming to return result that contains the character "A" in addition to the two alternatives (All or an exact value) we have discussed above.
0
 
ThomasianCommented:
Yes you can. You only need to change "=" to "LIKE" in the subquery.

Note that you can also use the other wildcards for the LIKE function:
http://office.microsoft.com/en-us/access-help/like-operator-HP001032253.aspx
SELECT [Table1].ID
FROM [Table1]
WHERE EXISTS(SELECT * FROM [Table-2] WHERE [Table1].Group LIKE [Table-2].Group)

Open in new window

0
 
JCJGAuthor Commented:
It doesn't work when I just add the like clause to the original query.  It returned everything even I specific a value with wildcard in Table-2.  I would like to have the flexibility to return result based on what user enters in Table-2.  When user enters "*" the query returns everything; "*A*" to return lines with the character "A"; A and B to return lines that are exactly equal to A or B; or a combination of the above.  Is it possible?  Thanks.
SELECT [Table1].ID
FROM [Table1]
WHERE EXISTS(SELECT * FROM [Table-2] WHERE [Table1].Group = [Table-2].Group OR [Table-2].Group='*' OR [Table1].Group LIKE [Table-2].Group)

Open in new window

0
 
JCJGAuthor Commented:
Yes, it works!  I appreciate your help!
0
 
JCJGAuthor Commented:
Hi Thomasian, I have one more related question.  I have difficulty using the Like clause on something like >100 or <100.  Can you please help?
0
 
ThomasianCommented:
Can you be more specific on what you want to do? In general, you cannot use other operators with LIKE.
0
 
JCJGAuthor Commented:
I'd like to add two columns (say Value1 and Value2) to both Table1 and Table-2.  Table-2 will look as follow.

Group  Value1  Value2
*A*          >=100    <=200

The query will return lines with Group contains the character "A" and Value1 is greater than or equal to 100 and Value2 is less than or equal to 200.

Is it doable?

Thanks.
0
 
ThomasianCommented:
Yes you can do that but you can't include the operators in the table.
i.e.

TableB:
Group  Value1  Value2
*A*        100       200
SELECT [Table1].ID
FROM [Table1]
WHERE EXISTS(SELECT * FROM [Table-2] WHERE [Table1].Group LIKE [Table-2].Group
                                           AND [Table1].Value1 >= [Table-2].Value1
                                           AND [Table1].Value2 <= [Table-2].Value2
            )

Open in new window

0
 
JCJGAuthor Commented:
Is there any workaround?  I don't want to hard code the operators as they will change.
0
 
ThomasianCommented:
Try if this works.

1. You must always include the operator on value1 and value2 on table2
2. You must be comparing numeric values on both tables (otherwise you will need to add quotes)
SELECT [Table1].ID
FROM [Table1]
WHERE EXISTS(SELECT * FROM [Table-2] WHERE [Table1].Group LIKE [Table-2].Group
                                           AND EVAL([Table1].Value1 & [Table-2].Value1)
                                           AND EVAL([Table1].Value2 & [Table-2].Value2)
            )

Open in new window

0
 
JCJGAuthor Commented:
It doesn't work.  It returned all lines.
0
 
ThomasianCommented:
Can you upload a test database with sample data?
0
 
JCJGAuthor Commented:
Here it is.  Query3 should return the same result as Query2.

Thanks.
Test.accdb
0
 
ThomasianCommented:
The problem is caused by the null values in Value1 and Value2 on Group="*". For query2, the group is invalidated thus not all records are returned. While for query3, the group is included thus every record is returned.

So, how do you want to handle null values?
0
 
JCJGAuthor Commented:
I see.  Does the current query treat null values as zeros?  I think the null values should mean no restriction on values.
0
 
ThomasianCommented:
SELECT [Table1].ID
FROM [Table1]
WHERE EXISTS(SELECT * FROM [Table3] WHERE [Table1].Group LIKE [Table3].Group
                                           AND ([Table3].Value1 IS NULL OR EVAL([Table1].Value1 & [Table3].Value1))
                                           AND ([Table3].Value2 IS NULL OR EVAL([Table1].Value2 & [Table3].Value2))
            )

Open in new window

0
 
JCJGAuthor Commented:
Hi Thomasian,

I have a another posting related to this thread.  Can you please help me?

The title is "Access 2007: query to look up criteria with operators in a table."

Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.