Solved

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

Posted on 2013-11-20
19
357 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
[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
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
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…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

627 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