Access Query Not Found criteria

[jeff.white]

Hello - - I have a query in Access that searches in part a table that
list the 'events' of employees. These events can be many things, name
change, hire, term etc....

What I'm trying to do is search for data entry errors. In this case,
for employees that were term'd (with a term date) but no term event
was entered. The field name is CODE. I've done things like '<>
TERM', Not In ("TERM"), NOT LIKE TERM...etc. But I getting a ton of
records that will list all term'd employees it just excludes this
CODE. Is there a criteria that would show only those employees with a
term date but no code listed as TERM?

Thanks...

 

[Amy Blankenship]

What about [Term Date] IS NOT NULL AND CODE <> 'TERM'

 

[jeff.white]

- Show quoted text -

Thanks Amy, but when you use <> TERM what I get in return are all
employees that were term'd and all their other codes, just as JOB,
NAME CHANGE. Most employees have more than one code entered, some
have a lot simply because there were plenty of changes to their
record. What I'm looking for is where there is a term date entered
but no event with a code of TERM. I'm trying now the NOT EXISTS option
in the query but I must have something wrong there cuz I do not get
any returns, when I know there should be at least one. I'm using
this now:

select .... where not exists (select table.code where code = "TERM")
but again, this returns no data...!?

 

[John Spencer]

Would you care to share your table structure?
I am guessing that you have an Employees table and an EmployeeEvents table.
If so, you might try something like the following

SELECT *
FROM Employees
WHERE EmployeeID Not IN
(SELECT EmployeeEvents.EmployeeID
FROM EmployeeEvents
WHERE Event.Code = "Term")
AND Employees.TermDate Is Not Null

My personal feeling is that I would be storing the Event Date in
EmployeeEvents table instead of in the Employees table. Perhaps you are,
but (again) you haven't shared your structure with us.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

What about [Term Date] IS NOT NULL AND CODE <>

Hello - - I have a query in Access that searches in part a table that
list the 'events' of employees. These events can be many things, name
change, hire, term etc....

What I'm trying to do is search for data entry errors. In this case,
for employees that were term'd (with a term date) but no term event
was entered. The field name is CODE. I've done things like '<>
TERM', Not In ("TERM"), NOT LIKE TERM...etc. But I getting a ton of
records that will list all term'd employees it just excludes this
CODE. Is there a criteria that would show only those employees with a
term date but no code listed as TERM?

Thanks...- Hide quoted text -

- Show quoted text -

Thanks Amy, but when you use <> TERM what I get in return are all
employees that were term'd and all their other codes, just as JOB,
NAME CHANGE. Most employees have more than one code entered, some
have a lot simply because there were plenty of changes to their
record. What I'm looking for is where there is a term date entered
but no event with a code of TERM. I'm trying now the NOT EXISTS option
in the query but I must have something wrong there cuz I do not get
any returns, when I know there should be at least one. I'm using
this now:

select .... where not exists (select table.code where code = "TERM")
but again, this returns no data...!?

 

[Amy Blankenship]

Would you care to share your table structure?
I am guessing that you have an Employees table and an EmployeeEvents
table. If so, you might try something like the following

SELECT *
FROM Employees
WHERE EmployeeID Not IN
(SELECT EmployeeEvents.EmployeeID
FROM EmployeeEvents
WHERE Event.Code = "Term")
AND Employees.TermDate Is Not Null

My personal feeling is that I would be storing the Event Date in
EmployeeEvents table instead of in the Employees table. Perhaps you are,
but (again) you haven't shared your structure with us.

That's the impression I was working under. I'd expect that the date would
be associated with the event.

 

[Amy Blankenship]

What about [Term Date] IS NOT NULL AND CODE <>

Hello - - I have a query in Access that searches in part a table that
list the 'events' of employees. These events can be many things, name
change, hire, term etc....

What I'm trying to do is search for data entry errors. In this case,
for employees that were term'd (with a term date) but no term event
was entered. The field name is CODE. I've done things like '<>
TERM', Not In ("TERM"), NOT LIKE TERM...etc. But I getting a ton of
records that will list all term'd employees it just excludes this
CODE. Is there a criteria that would show only those employees with a
term date but no code listed as TERM?

I'm confused. Why would there be a term date associated with events other
than termination?

 

[jeff.white]

Would you care to share your table structure?
I am guessing that you have an Employees table and an EmployeeEvents table.
If so, you might try something like the following

SELECT *
FROM Employees
WHERE EmployeeID Not IN
(SELECT EmployeeEvents.EmployeeID
FROM EmployeeEvents
WHERE Event.Code = "Term")
AND Employees.TermDate Is Not Null

My personal feeling is that I would be storing the Event Date in
EmployeeEvents table instead of in the Employees table. Perhaps you are,
but (again) you haven't shared your structure with us.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

What about [Term Date] IS NOT NULL AND CODE <>

Hello - - I have a query in Access that searches in part a table that
list the 'events' of employees. These events can be many things, name
change, hire, term etc....
What I'm trying to do is search for data entry errors. In this case,
for employees that were term'd (with a term date) but no term event
was entered. The field name is CODE. I've done things like '<>
TERM', Not In ("TERM"), NOT LIKE TERM...etc. But I getting a ton of
records that will list all term'd employees it just excludes this
CODE. Is there a criteria that would show only those employees with a
term date but no code listed as TERM?
Thanks...- Hide quoted text -
- Show quoted text -

Thanks Amy, but when you use <> TERM what I get in return are all
employees that were term'd and all their other codes, just as JOB,
NAME CHANGE. Most employees have more than one code entered, some
have a lot simply because there were plenty of changes to their
record. What I'm looking for is where there is a term date entered
but no event with a code of TERM. I'm trying now the NOT EXISTS option
in the query but I must have something wrong there cuz I do not get
any returns, when I know there should be at least one. I'm using
this now:

select .... where not exists (select table.code where code = "TERM")
but again, this returns no data...!?- Hide quoted text -

- Show quoted text -

Thanks John....I tried this but I got the same result, no data. Here
is my full query:

SELECT PR_MAST.Active_Stat, PR_MAST.Loc_No, PR_MAST.Emp_No,
PR_MAST.Hire_Date, PR_MAST.Term_Date, PR_MAST.Last_Name,
PR_MAST.First_Name
FROM PR_MAST INNER JOIN PR_EVENT ON (PR_MAST.Loc_No = PR_EVENT.Loc_No)
AND (PR_MAST.Emp_No = PR_EVENT.Emp_No)
WHERE (((Exists (select PR_EVENT.emp_no from PR_EVENT where
PR_EVENT.Code = "TERM"))=False))
GROUP BY PR_MAST.Active_Stat, PR_MAST.Loc_No, PR_MAST.Emp_No,
PR_MAST.Hire_Date, PR_MAST.Term_Date, PR_MAST.Last_Name,
PR_MAST.First_Name
HAVING (((PR_MAST.Active_Stat)="T") AND
((PR_MAST.Term_Date)>#5/1/2007#))
ORDER BY PR_MAST.Last_Name;

Here I've tried your suggestion...I've also tried in the where portion
'...(select event.code from event...) with the same result. Each
employee whether term'd or not will have more than one code entered.
So I'm looking for those employees that have a term date but no event
where the code = TERM....does this make sense?

 

[jeff.white]

What about [Term Date] IS NOT NULL AND CODE <>

Hello - - I have a query in Access that searches in part a table that
list the 'events' of employees. These events can be many things, name
change, hire, term etc....
What I'm trying to do is search for data entry errors. In this case,
for employees that were term'd (with a term date) but no term event
was entered. The field name is CODE. I've done things like '<>
TERM', Not In ("TERM"), NOT LIKE TERM...etc. But I getting a ton of
records that will list all term'd employees it just excludes this
CODE. Is there a criteria that would show only those employees with a
term date but no code listed as TERM?

I'm confused. Why would there be a term date associated with events other
than termination?- Hide quoted text -

- Show quoted text -

Amy, to answer your question, the way I understand it, there are two
places one needs to go to in the software to term someone, first they
enter a term date, which is stored in one table, then they proceed to
another section and 'create' a new event and code this event a TERM,
which is stored in an events table. I found, much by accident, that
there was a term employee that did not appear on a term report. I
knew they were term'd in the timeframe of the report. It was then I
saw they had a term date entered but the event protion was not
complete...thus my dilema, finding all employees with a term date not
null and an event with a code = to TERM. I know once exists today as
our data entry clerk just entered one like this....

 

[John Spencer]

Your exists query checks to see if ANY record in PR_EVENT exists with the
CODE of "TERM". You want to know if any record exists for the specific
employee that the main clause is currently examining.

EXISTS (SELECT * FROM PR_EVENT as T WHERE T.Code = "TERM" and T.Emp_NO =
PR_Mast.Emp_No) = False

This code should be more efficient

SELECT PR_MAST.Active_Stat
, PR_MAST.Loc_No
, PR_MAST.Emp_No
, PR_MAST.Hire_Date
, PR_MAST.Term_Date
, PR_MAST.Last_Name
, PR_MAST.First_Name
FROM PR_MAST
WHERE PR_MAST.Emp_No NOT IN
(SELECT T.Emp_No
FROM PR_EVENT as T
WHERE T.Emp_No is not null and T.Code = "TERM")




SELECT PR_MAST.Active_Stat, PR_MAST.Loc_No, PR_MAST.Emp_No,
PR_MAST.Hire_Date, PR_MAST.Term_Date, PR_MAST.Last_Name,
PR_MAST.First_Name
FROM PR_MAST INNER JOIN PR_EVENT ON (PR_MAST.Loc_No = PR_EVENT.Loc_No)
AND (PR_MAST.Emp_No = PR_EVENT.Emp_No)
WHERE (((Exists (select PR_EVENT.emp_no from PR_EVENT where
PR_EVENT.Code = "TERM"))=False))
GROUP BY PR_MAST.Active_Stat, PR_MAST.Loc_No, PR_MAST.Emp_No,
PR_MAST.Hire_Date, PR_MAST.Term_Date, PR_MAST.Last_Name,
PR_MAST.First_Name
HAVING (((PR_MAST.Active_Stat)="T") AND
((PR_MAST.Term_Date)>#5/1/2007#))
ORDER BY PR_MAST.Last_Name;


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Would you care to share your table structure?
I am guessing that you have an Employees table and an EmployeeEvents
table.
If so, you might try something like the following

SELECT *
FROM Employees
WHERE EmployeeID Not IN
(SELECT EmployeeEvents.EmployeeID
FROM EmployeeEvents
WHERE Event.Code = "Term")
AND Employees.TermDate Is Not Null

My personal feeling is that I would be storing the Event Date in
EmployeeEvents table instead of in the Employees table. Perhaps you are,
but (again) you haven't shared your structure with us.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

On May 4, 11:52 am, "Amy Blankenship"
What about [Term Date] IS NOT NULL AND CODE <>

news:[email protected]...

Hello - - I have a query in Access that searches in part a table
that
list the 'events' of employees. These events can be many things,
name
change, hire, term etc....

What I'm trying to do is search for data entry errors. In this
case,
for employees that were term'd (with a term date) but no term event
was entered. The field name is CODE. I've done things like '<>
TERM', Not In ("TERM"), NOT LIKE TERM...etc. But I getting a ton of
records that will list all term'd employees it just excludes this
CODE. Is there a criteria that would show only those employees with
a
term date but no code listed as TERM?

Thanks...- Hide quoted text -

- Show quoted text -

Thanks Amy, but when you use <> TERM what I get in return are all
employees that were term'd and all their other codes, just as JOB,
NAME CHANGE. Most employees have more than one code entered, some
have a lot simply because there were plenty of changes to their
record. What I'm looking for is where there is a term date entered
but no event with a code of TERM. I'm trying now the NOT EXISTS option
in the query but I must have something wrong there cuz I do not get
any returns, when I know there should be at least one. I'm using
this now:

select .... where not exists (select table.code where code = "TERM")
but again, this returns no data...!?- Hide quoted text -

- Show quoted text -

Thanks John....I tried this but I got the same result, no data. Here
is my full query:

SELECT PR_MAST.Active_Stat, PR_MAST.Loc_No, PR_MAST.Emp_No,
PR_MAST.Hire_Date, PR_MAST.Term_Date, PR_MAST.Last_Name,
PR_MAST.First_Name
FROM PR_MAST INNER JOIN PR_EVENT ON (PR_MAST.Loc_No = PR_EVENT.Loc_No)
AND (PR_MAST.Emp_No = PR_EVENT.Emp_No)
WHERE (((Exists (select PR_EVENT.emp_no from PR_EVENT where
PR_EVENT.Code = "TERM"))=False))
GROUP BY PR_MAST.Active_Stat, PR_MAST.Loc_No, PR_MAST.Emp_No,
PR_MAST.Hire_Date, PR_MAST.Term_Date, PR_MAST.Last_Name,
PR_MAST.First_Name
HAVING (((PR_MAST.Active_Stat)="T") AND
((PR_MAST.Term_Date)>#5/1/2007#))
ORDER BY PR_MAST.Last_Name;

Here I've tried your suggestion...I've also tried in the where portion
'...(select event.code from event...) with the same result. Each
employee whether term'd or not will have more than one code entered.
So I'm looking for those employees that have a term date but no event
where the code = TERM....does this make sense?

 

[Amy Blankenship]

On May 4, 11:52 am, "Amy Blankenship"
What about [Term Date] IS NOT NULL AND CODE <>

news:[email protected]...

Hello - - I have a query in Access that searches in part a table
that
list the 'events' of employees. These events can be many things,
name
change, hire, term etc....

What I'm trying to do is search for data entry errors. In this
case,
for employees that were term'd (with a term date) but no term event
was entered. The field name is CODE. I've done things like '<>
TERM', Not In ("TERM"), NOT LIKE TERM...etc. But I getting a ton of
records that will list all term'd employees it just excludes this
CODE. Is there a criteria that would show only those employees with
a
term date but no code listed as TERM?

I'm confused. Why would there be a term date associated with events
other
than termination?- Hide quoted text -

- Show quoted text -

Amy, to answer your question, the way I understand it, there are two
places one needs to go to in the software to term someone, first they
enter a term date, which is stored in one table, then they proceed to
another section and 'create' a new event and code this event a TERM,
which is stored in an events table. I found, much by accident, that
there was a term employee that did not appear on a term report. I
knew they were term'd in the timeframe of the report. It was then I
saw they had a term date entered but the event protion was not
complete...thus my dilema, finding all employees with a term date not
null and an event with a code = to TERM. I know once exists today as
our data entry clerk just entered one like this....

Why not just date stamp all events, regardless of type, when they are
entered?