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)));
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)));
ASKER
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
Is this more like it?
That should show ALL records in your query, regardless of whether matches are found in the table.
SELECT q.Employee_ID
FROM Table1 q LEFT JOIN Table2 t ON q.Employee_ID = t.Employee_IDThat should show ALL records in your query, regardless of whether matches are found in the table.
ASKER
Nope! That gives me all the records even when Table2 has only specific employees only.
ASKER
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
No Data in table2 = Show all record from Table1
Data in Table2 = Show only matching records from Table1
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)) ASKER
I am getting a "Syntax error in JOIN operation." using your example above. :-(
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:
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))
If that doesn't help, upload a scaled down sample with just the needed tables and junk data (no sensitive employee info)
ASKER
It is Main = Table1
Main2 = Table2
for me?
Main2 = Table2
for me?
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))
Hang on... I've been assuming MS Access. What database platform are you using?
ASKER
It is MS Access 2007
ASKER
Feel free to upload a simple access database example :-)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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?
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?
ASKER
I was able to open it with a co-worker Access 2010. It works as a charm. Thank you much mbizup
Glad to help out. No idea why that one wouldn't open for you in 2007.
thank you. Microsoft. :-)
thank you. Microsoft. :-)
Open in new window