Solved

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

Posted on 2011-02-26
24
373 Views
Last Modified: 2012-06-21
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
0
Comment
Question by:JCJG
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 10
  • 2
24 Comments
 
LVL 40

Expert Comment

by:als315
ID: 34990393
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
 

Author Comment

by:JCJG
ID: 34990408
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
 
LVL 40

Expert Comment

by:als315
ID: 34990432
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 22

Expert Comment

by:Thomasian
ID: 34991353
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
 

Author Comment

by:JCJG
ID: 34993181
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
 
LVL 22

Expert Comment

by:Thomasian
ID: 34993754
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
 

Author Comment

by:JCJG
ID: 34994793
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
 
LVL 22

Expert Comment

by:Thomasian
ID: 34994817
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
 

Author Comment

by:JCJG
ID: 34994873
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
 
LVL 22

Accepted Solution

by:
Thomasian earned 500 total points
ID: 34994904
The query I posted in http:#a34994817 should work that way.

See query2 in the attached db
test.accdb
0
 

Author Closing Comment

by:JCJG
ID: 34995059
Yes, it works!  I appreciate your help!
0
 

Author Comment

by:JCJG
ID: 35000902
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
 
LVL 22

Expert Comment

by:Thomasian
ID: 35003196
Can you be more specific on what you want to do? In general, you cannot use other operators with LIKE.
0
 

Author Comment

by:JCJG
ID: 35003277
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
 
LVL 22

Expert Comment

by:Thomasian
ID: 35003298
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
 

Author Comment

by:JCJG
ID: 35004358
Is there any workaround?  I don't want to hard code the operators as they will change.
0
 
LVL 22

Expert Comment

by:Thomasian
ID: 35004431
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
 

Author Comment

by:JCJG
ID: 35004516
It doesn't work.  It returned all lines.
0
 
LVL 22

Expert Comment

by:Thomasian
ID: 35004534
Can you upload a test database with sample data?
0
 

Author Comment

by:JCJG
ID: 35004922
Here it is.  Query3 should return the same result as Query2.

Thanks.
Test.accdb
0
 
LVL 22

Expert Comment

by:Thomasian
ID: 35004974
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
 

Author Comment

by:JCJG
ID: 35013698
I see.  Does the current query treat null values as zeros?  I think the null values should mean no restriction on values.
0
 
LVL 22

Expert Comment

by:Thomasian
ID: 35013724
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
 

Author Comment

by:JCJG
ID: 35305795
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question