Solved

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

Posted on 2013-11-20
19
350 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
 

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

758 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

20 Experts available now in Live!

Get 1:1 Help Now