Solved

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

Posted on 2011-02-26
24
371 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
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 39

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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access query expression 6 20
Allow user to edit an outgoing email 2 25
is Microsoft Access going to Die? 9 47
Open VBA code while form is open and running. 4 25
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

770 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