Solved

MS Access Query using In Fuction to filter data... or not

Posted on 2013-11-20
19
353 Views
Last Modified: 2013-11-21
Hello All,
I have Table1 and table2. Table1 has all my records and Table2 may or may not have any records in it. Both Tables have a Field named EmployeeID. I would like to write a query that will show all the record from table1 IF table2 has no records in it (Null) or if table2 has any record in it, then show record from table1 where the employee_ID match in both tables (The relationships is a one (table2)-to-many(Table1) . Can it be done in a query? How?   I was thinking of using the IN FUNCTION but that does not give me any data when table2 is null.  

SELECT qrySearch.Employee_ID
FROM qrySearch
WHERE (((qrySearch.Employee_ID) In (SELECT Table2. Employee_ID FROM Table2)));
0
Comment
Question by:Cobra967
  • 10
  • 9
19 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 39663384
Try this:


SELECT q.Employee_ID
FROM qrySearch q LEFT JOIN Table2 t ON q.Employee_ID = t.Employee_ID
WHERE t.Employee_ID IS NOT NULL

Open in new window

0
 

Author Comment

by:Cobra967
ID: 39663446
I am not sure I have dome something wrong but it does not show any data if table2 has no data in it.

SELECT q.Employee_ID
FROM Table1 q LEFT JOIN Table2 t ON q.Employee_ID = t.Employee_ID
WHERE t.Employee_ID IS NOT NULL

Open in new window

0
 
LVL 61

Expert Comment

by:mbizup
ID: 39663453
Is this more like it?

SELECT q.Employee_ID
FROM Table1 q LEFT JOIN Table2 t ON q.Employee_ID = t.Employee_ID

Open in new window


That should show ALL records in your query, regardless of whether matches are found in the table.
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

Author Comment

by:Cobra967
ID: 39663462
Nope! That gives me all the records even when Table2 has only specific employees only.
0
 

Author Comment

by:Cobra967
ID: 39663476
This is what it needs to happen:

No Data in table2 = Show all record from Table1
Data in Table2 = Show only matching records from Table1
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39663702
You could either use two queries, a union query, or nested queries like this:

SELECT q1.*, q2.CountOfRecs
FROM
(SELECT Table1.*, Table2.*, Table1.Employee_ID AS ID1,  Table2.Employee_ID AS ID2
FROM Table1 LEFT JOIN Table2 ON Main.Employee_ID = Table2.Employee_ID) q1,
(SELECT COUNT(*)  AS CountOfRecs FROM Table2) q2
 WHERE q1.ID1 LIKE  (iif(q2.CountOfRecs= 0, "*", q1.ID2)) 

Open in new window

0
 

Author Comment

by:Cobra967
ID: 39663802
I am getting a "Syntax error in JOIN operation." using your example above. :-(
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39663864
Hmm - seems to work for me.

This is working SQL directly out of my database... try changing Main and Main2 with your exact table names:

SELECT q1.*, q2.CountOfRecs
FROM
(SELECT Main.*, Main2.*, Main2.Employee_ID AS ID2,  Main.Employee_ID AS ID1
FROM Main LEFT JOIN Main2 ON Main.Employee_ID = Main2.Employee_ID) q1,
(SELECT COUNT(*)  AS CountOfRecs FROM Main2) q2
 WHERE q1.ID1 LIKE  (iif(q2.CountOfRecs= 0, "*", q1.ID2)) 

Open in new window

0
 
LVL 61

Expert Comment

by:mbizup
ID: 39663871
If that doesn't help, upload a scaled down sample with just the needed tables and junk data (no sensitive employee info)
0
 

Author Comment

by:Cobra967
ID: 39663890
It is Main = Table1
Main2 = Table2
for me?
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39664116
Yes -  If those are your exact table names, try this:

SELECT q1.*, q2.CountOfRecs
FROM
(SELECT Table1.*, Table2.*, Table2.Employee_ID AS ID2,  Table1.Employee_ID AS ID1
FROM Table1 LEFT JOIN Table2 ON Main.Employee_ID = Table2.Employee_ID) q1,
(SELECT COUNT(*)  AS CountOfRecs FROM Table2) q2
 WHERE q1.ID1 LIKE  (iif(q2.CountOfRecs= 0, "*", q1.ID2)) 

Open in new window

0
 
LVL 61

Expert Comment

by:mbizup
ID: 39664124
Hang on... I've been assuming MS Access.  What database platform are you using?
0
 

Author Comment

by:Cobra967
ID: 39664232
It is MS Access 2007
0
 

Author Comment

by:Cobra967
ID: 39664234
Feel free to upload a simple access database example :-)
0
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 39664371
0
 

Author Comment

by:Cobra967
ID: 39665667
Mmhhh when attempting to open the file, I am getting a "Unrecognized database format ...". I am using Access 2007. What version did you use to produce it?
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39666023
2010.  But there were no 2010 specific features...

I was afraid of that, which is why I asked YOU to post a scaled down sample earlier.  Are you able to post a sample... just two sample tables, representative of your scenario, with no sensitive data?
0
 

Author Comment

by:Cobra967
ID: 39666066
I was able to open it with a co-worker Access 2010. It works as a charm. Thank you much mbizup
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39666139
Glad to help out.  No idea why that one wouldn't open for you in 2007.

thank you. Microsoft. :-)
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access Update Query 1 20
Calculation in Access 5 25
Why get error when delete all records on a sub-form 2 15
Connection to multiple databases 13 16
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

808 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