Help with Date Query

[Guest]

Originally, I had asked for help with a query that returned the latest date
for a satisfactory score; however, I am needing a comparison query.


What I need to do is a query that states the last date that they
unsatisfactorily completed a test and then again when they were satisfactory
in that test/drill.

This is what Ken returned to me for the original unsat but I need to return
when they are sat as well. So, I can have a list of the people who need a
drill. Of course when they are unsat they need a drill but if at at later
date they become sat then they do not need a drill can someone help me with
this query?
Many thanks!!!!!!!!
This is the original query!
SELECT [Individual Drill Performance Data].[Name],

 

[John Vinson]

Originally, I had asked for help with a query that returned the latest date
for a satisfactory score; however, I am needing a comparison query.


What I need to do is a query that states the last date that they
unsatisfactorily completed a test and then again when they were satisfactory
in that test/drill.

This is what Ken returned to me for the original unsat but I need to return
when they are sat as well. So, I can have a list of the people who need a
drill. Of course when they are unsat they need a drill but if at at later
date they become sat then they do not need a drill can someone help me with
this query?

I do not know the structure of your database, so I can't tell what the
appropriate criteria would be to find the "sat" records - but you'ld
just add those criteria to the subquery:

SELECT
[Individual Drill Performance Data].PERNR,
[Drill information].[Date]
FROM [Drill information] INNER JOIN
[Individual Drill Performance Data]
ON [Drill information].DrillID =
[Individual Drill Performance Data].DrillID
WHERE
[Drill information].[Date] = (SELECT Max(T.[Date]) AS MTD
FROM [Drill information] AS T
WHERE T.DrillID = [Individual Drill Performance Data].DrillID
AND <whatever criteria identify the type of record you want>);
WITH OWNERACCESS OPTION;


John W. Vinson[MVP]

 

[Guest]

Hey John,

Here is my setup

Table [Individual Drill Performance Data].[Name]
[Individual Drill Performance Data].[Employee number]
[Individual Drill Performance Data].[Sat?]

Table [Drill information].[Drill_Date]
[Drill information].[DrillID]

Table [Main Roster].[Name]
[Main Roster].[Employee number]

As stated, at one point an employee can have a unsat score on a drill and
then at a later date they can have a sat score. I need to be able to say
"who needs a drill", they will only need a drill if they have not had one
already for the year or if they were unsat recently by date.

Thank you very for your help in advance.
Judy T

Originally, I had asked for help with a query that returned the latest date
for a satisfactory score; however, I am needing a comparison query.


What I need to do is a query that states the last date that they
unsatisfactorily completed a test and then again when they were satisfactory
in that test/drill.

This is what Ken returned to me for the original unsat but I need to return
when they are sat as well. So, I can have a list of the people who need a
drill. Of course when they are unsat they need a drill but if at at later
date they become sat then they do not need a drill can someone help me with
this query?

I do not know the structure of your database, so I can't tell what the
appropriate criteria would be to find the "sat" records - but you'ld
just add those criteria to the subquery:

SELECT
[Individual Drill Performance Data].PERNR,
[Drill information].[Date]
FROM [Drill information] INNER JOIN
[Individual Drill Performance Data]
ON [Drill information].DrillID =
[Individual Drill Performance Data].DrillID
WHERE
[Drill information].[Date] = (SELECT Max(T.[Date]) AS MTD
FROM [Drill information] AS T
WHERE T.DrillID = [Individual Drill Performance Data].DrillID
AND <whatever criteria identify the type of record you want>);
WITH OWNERACCESS OPTION;


John W. Vinson[MVP]

 

[John Vinson]

Hey John,

Here is my setup

Table [Individual Drill Performance Data].[Name]
[Individual Drill Performance Data].[Employee number]
[Individual Drill Performance Data].[Sat?]

Table [Drill information].[Drill_Date]
[Drill information].[DrillID]

Table [Main Roster].[Name]
[Main Roster].[Employee number]

As stated, at one point an employee can have a unsat score on a drill and
then at a later date they can have a sat score. I need to be able to say
"who needs a drill", they will only need a drill if they have not had one
already for the year or if they were unsat recently by date.

OK, I've read this several times, and I still don't clearly understand
it.

What do you mean "recently by date"? Do you only want to check the
score on the most recently taken test?


John W. Vinson[MVP]

 

[Guest]

What I mean is if they were by date recently sat on a drill performance, I do
not want them to show up on who needs a drill. The only people that I want
to show up on a drill is...those whom have not had a drill in the last year
and those who were unsat for the last drills by date. Does that clear things
up?

Thanks for your help in advance,
JudyT

Hey John,

Here is my setup

Table [Individual Drill Performance Data].[Name]
[Individual Drill Performance Data].[Employee number]
[Individual Drill Performance Data].[Sat?]

Table [Drill information].[Drill_Date]
[Drill information].[DrillID]

Table [Main Roster].[Name]
[Main Roster].[Employee number]

As stated, at one point an employee can have a unsat score on a drill and
then at a later date they can have a sat score. I need to be able to say
"who needs a drill", they will only need a drill if they have not had one
already for the year or if they were unsat recently by date.

OK, I've read this several times, and I still don't clearly understand
it.

What do you mean "recently by date"? Do you only want to check the
score on the most recently taken test?


John W. Vinson[MVP]

 

[Guest]

I apparently did not clear things up for you.

Okay, If on 1/1/06 a person was unsat and on 2/2/06 that person is now sat
that is what I meant by recent date. The sql that i used was latest date and
that did not work. Any ideas? Please help.

Hey John,

Here is my setup

Table [Individual Drill Performance Data].[Name]
[Individual Drill Performance Data].[Employee number]
[Individual Drill Performance Data].[Sat?]

Table [Drill information].[Drill_Date]
[Drill information].[DrillID]

Table [Main Roster].[Name]
[Main Roster].[Employee number]

As stated, at one point an employee can have a unsat score on a drill and
then at a later date they can have a sat score. I need to be able to say
"who needs a drill", they will only need a drill if they have not had one
already for the year or if they were unsat recently by date.

OK, I've read this several times, and I still don't clearly understand
it.

What do you mean "recently by date"? Do you only want to check the
score on the most recently taken test?


John W. Vinson[MVP]