Running a query to yield blank/non existent data

[jrsarrat]

Hello!
I have been struggling with producing a query that will pull all data from one field in a table that doesn’t exist in another table.
I have two tables. The first table holds all employee information. The second table holds log in/log out information.
I want the query to pull the employees whom do not exist in table 2, but do exist in table 1.
Your help is much appreciated.
Thank you!

 

[xenou]

I'm not sure if there is an alternative. What kind of date are you in need of?

 

[jrsarrat]

I'm not sure if there is an alternative. What kind of date are you in need of?

The report will be ran daily, so I'm in need of each day's date. The tblEmpLogin table will meet this criterion, but the query does not work when it is Table 1.

 

[xenou]

THis would find only the employees that don't have a login today (assuming there is a Dte1 field in the login table and it holds dates without times, not dates with times ie (1/1/2018, not 1/1/2018 12:00 PM).
SELECT
T.Emp_FName,
T.Emp_LName,
T.Team_Lead,
T.Role
FROM
Emp_tbl T
WHERE
(
(Not Exists (SELECT * FROM tblEmpLogin where tblEmpLogin.pf_id = T.pf_id and tbleEmplogin.Dte1 = date()))
);


Otherwise I guess the query was returning employees who had never logged in ever.

 

[jrsarrat]

THis would find only the employees that don't have a login today (assuming there is a Dte1 field in the login table and it holds dates without times, not dates with times ie (1/1/2018, not 1/1/2018 12:00 PM).
SELECT
T.Emp_FName,
T.Emp_LName,
T.Team_Lead,
T.Role
FROM
Emp_tbl T
WHERE
(
(Not Exists (SELECT * FROM tblEmpLogin where tblEmpLogin.pf_id = T.pf_id and tbleEmplogin.Dte1 = date()))
);


Otherwise I guess the query was returning employees who had never logged in ever.

I tested your query with just a few records, and it worked! It even excluded a record which had the Now() expression included (Ironically, I changed the expression before you informed me to do so.) I can’t thank you enough, Xenou!

 

[xenou]

No problem. Glad I could help.

 

[jrsarrat]

No problem. Glad I could help.

Hello, again!

One last question. I created a form that end-users will use to log in, and would like for The Emp_tbl (which holds all employee info) to fact-check the end-user's entry when they log in. In case a user accidentally inputs the wrong ID, is there a way for me to apply a command to return an error message prompting the user to try again?
Also, I've noticed currently, if an incorrect ID is entered, the form locks. Will my requested command supersede this glitch?

Thank you!

 

[xenou]

I don't really deal with login security sorry. Sounds like you have some custom implementation since forms don't lock on their own.

 

[jrsarrat]

I don't really deal with login security sorry. Sounds like you have some custom implementation since forms don't lock on their own.

Okay. Thank you, Xenou!