Solved

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

Posted on 2011-02-26
24
365 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
  • 12
  • 10
  • 2
24 Comments
 
LVL 39

Expert Comment

by:als315
Comment Utility
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
Comment Utility
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 39

Expert Comment

by:als315
Comment Utility
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
 
LVL 22

Expert Comment

by:Thomasian
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Yes, it works!  I appreciate your help!
0
 

Author Comment

by:JCJG
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 22

Expert Comment

by:Thomasian
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Is there any workaround?  I don't want to hard code the operators as they will change.
0
 
LVL 22

Expert Comment

by:Thomasian
Comment Utility
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
Comment Utility
It doesn't work.  It returned all lines.
0
 
LVL 22

Expert Comment

by:Thomasian
Comment Utility
Can you upload a test database with sample data?
0
 

Author Comment

by:JCJG
Comment Utility
Here it is.  Query3 should return the same result as Query2.

Thanks.
Test.accdb
0
 
LVL 22

Expert Comment

by:Thomasian
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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…

743 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now