• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 362
  • Last Modified:

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

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
Cobra967
Asked:
Cobra967
  • 10
  • 9
1 Solution
 
mbizupCommented:
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
 
Cobra967Author Commented:
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
 
mbizupCommented:
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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
Cobra967Author Commented:
Nope! That gives me all the records even when Table2 has only specific employees only.
0
 
Cobra967Author Commented:
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
 
mbizupCommented:
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
 
Cobra967Author Commented:
I am getting a "Syntax error in JOIN operation." using your example above. :-(
0
 
mbizupCommented:
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
 
mbizupCommented:
If that doesn't help, upload a scaled down sample with just the needed tables and junk data (no sensitive employee info)
0
 
Cobra967Author Commented:
It is Main = Table1
Main2 = Table2
for me?
0
 
mbizupCommented:
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
 
mbizupCommented:
Hang on... I've been assuming MS Access.  What database platform are you using?
0
 
Cobra967Author Commented:
It is MS Access 2007
0
 
Cobra967Author Commented:
Feel free to upload a simple access database example :-)
0
 
mbizupCommented:
0
 
Cobra967Author Commented:
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
 
mbizupCommented:
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
 
Cobra967Author Commented:
I was able to open it with a co-worker Access 2010. It works as a charm. Thank you much mbizup
0
 
mbizupCommented:
Glad to help out.  No idea why that one wouldn't open for you in 2007.

thank you. Microsoft. :-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 10
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now