Help on a complicated query!?

[Access rookie]

Hello,

I am in quite a quandary at the moment:
I have an employee main form (PK Employee Number) and a training subform (PK
Training ID) linked by Employee number. The Employee form is the main form,
the training form is the subform.
On the subform, I have a field called duration. This field is a drop down
that looks up (that's funny) values (numbers) from a table. I also have an
expiry field that adds the duration and indicates the expiry date. For
example, here are my fields in the subform:
Class Name; Date; Duration; Expires.

The formula in the expires field is: =IIf([Duration] Is
Null,Null,DateAdd("yyyy",[Duration],[Class Date])).

Now, how would I set up a query to look for a class e.g. Manager's Training
that has expired by let's say 1/1/2005? How can I indicate this in the query
when the Expires field is calculated? My training subform is not based on a
query but on my training table.

Quite confused,

John.

 

[Dale Fye]

I guess it depends on how you want to get to your query.

Your query might read something like:

Select E.EmployeeNumber, E.EmployeeName
FROM Employees E
INNER JOIN Training T
ON E.EmployeeNumber = T.EmployeeNumber
WHERE T.ClassName = "Manager's Training"
AND T.Expires < #1/1/2005#

But this depends on whether you are retaining data for all instances of the
training. If for example, this is an annual training requirement, and you
are keeping each years entry in your training table, then this query will not
work. If that is the case, post back to this newsgroup for another
alternative.

HTH
Dale

 

[Ken Snell [MVP]]

When is the query to be run? And for what purpose?

Simple answer is for you to include the expression that you're using in the
query as a calculated field, and then use a criterion for that calculated
field. For example,

SELECT *
FROM TableName
WHERE DateAdd("yyyy",[Duration],[Class Date])
<= #1/1/2005#;

 

[Access rookie]

Hello Dale,
Yes, I am keeping all instances of training in the database. I will post a
more detailed description under Ken's post.

Thanks!
John.

I guess it depends on how you want to get to your query.

Your query might read something like:

Select E.EmployeeNumber, E.EmployeeName
FROM Employees E
INNER JOIN Training T
ON E.EmployeeNumber = T.EmployeeNumber
WHERE T.ClassName = "Manager's Training"
AND T.Expires < #1/1/2005#

But this depends on whether you are retaining data for all instances of the
training. If for example, this is an annual training requirement, and you
are keeping each years entry in your training table, then this query will not
work. If that is the case, post back to this newsgroup for another
alternative.

HTH
Dale

Hello,

I am in quite a quandary at the moment:
I have an employee main form (PK Employee Number) and a training subform (PK
Training ID) linked by Employee number. The Employee form is the main form,
the training form is the subform.
On the subform, I have a field called duration. This field is a drop down
that looks up (that's funny) values (numbers) from a table. I also have an
expiry field that adds the duration and indicates the expiry date. For
example, here are my fields in the subform:
Class Name; Date; Duration; Expires.

The formula in the expires field is: =IIf([Duration] Is
Null,Null,DateAdd("yyyy",[Duration],[Class Date])).

Now, how would I set up a query to look for a class e.g. Manager's Training
that has expired by let's say 1/1/2005? How can I indicate this in the query
when the Expires field is calculated? My training subform is not based on a
query but on my training table.

Quite confused,

John.

 

[Access rookie]

Hello Ken and Dale,

Thanks for your input.
The query is to be run any time; it's simply to find out whether an
individual's training, eg First Aid is expired.
I would like to just have a command button to do this; it would prompt for
class name, then a date. The query would then return all employees that have
their training for that class expired.
I know how to do the command button (thank you wizards!), but the SQL
statement you included has me puzzled (thus my display name.) Where would I
put this?
I'm used to entering my parameters in the criteria field in the query
window; how would I do that with SQL? I can't enter the parameters in the
expiry field, because 1. The form is based on my training table, not a query
2. The expiry field is calculated. Grooaaannn....

Willing to valiantly slog through the SQL needed to get this done,

John.

When is the query to be run? And for what purpose?

Simple answer is for you to include the expression that you're using in the
query as a calculated field, and then use a criterion for that calculated
field. For example,

SELECT *
FROM TableName
WHERE DateAdd("yyyy",[Duration],[Class Date])
<= #1/1/2005#;

--

Ken Snell
<MS ACCESS MVP>
..

Hello,

I am in quite a quandary at the moment:
I have an employee main form (PK Employee Number) and a training subform
(PK
Training ID) linked by Employee number. The Employee form is the main
form,
the training form is the subform.
On the subform, I have a field called duration. This field is a drop down
that looks up (that's funny) values (numbers) from a table. I also have an
expiry field that adds the duration and indicates the expiry date. For
example, here are my fields in the subform:
Class Name; Date; Duration; Expires.

The formula in the expires field is: =IIf([Duration] Is
Null,Null,DateAdd("yyyy",[Duration],[Class Date])).

Now, how would I set up a query to look for a class e.g. Manager's
Training
that has expired by let's say 1/1/2005? How can I indicate this in the
query
when the Expires field is calculated? My training subform is not based on
a
query but on my training table.

Quite confused,

John.

 

[Ken Snell [MVP]]

This is a fairly simple query -- you can create it in the QBE (design) view
of the query object.

Create a new query (don't use the wizard). Select the table from the list of
tables presented. Click Add button. Click Close button.

Now drag fields from the table onto the grid; include all fields that you
wish to display.

Then click into the first unused column on the grid, in the "Field:" cell.
Type this into that cell:
ExpireDate: DateAdd("yyyy",[Duration],[Class Date])

(Note that the above assumes that Duration and Class Date are fields in this
table.)

In the "Criteria:" cell under this "calculated" field, type
<= #1/1/2005#

Your query is now done.
--

Ken Snell
<MS ACCESS MVP>

Hello Ken and Dale,

Thanks for your input.
The query is to be run any time; it's simply to find out whether an
individual's training, eg First Aid is expired.
I would like to just have a command button to do this; it would prompt for
class name, then a date. The query would then return all employees that
have
their training for that class expired.
I know how to do the command button (thank you wizards!), but the SQL
statement you included has me puzzled (thus my display name.) Where would
I
put this?
I'm used to entering my parameters in the criteria field in the query
window; how would I do that with SQL? I can't enter the parameters in the
expiry field, because 1. The form is based on my training table, not a
query
2. The expiry field is calculated. Grooaaannn....

Willing to valiantly slog through the SQL needed to get this done,

John.

When is the query to be run? And for what purpose?

Simple answer is for you to include the expression that you're using in
the
query as a calculated field, and then use a criterion for that calculated
field. For example,

SELECT *
FROM TableName
WHERE DateAdd("yyyy",[Duration],[Class Date])
<= #1/1/2005#;

--

Ken Snell
<MS ACCESS MVP>
..

Hello,

I am in quite a quandary at the moment:
I have an employee main form (PK Employee Number) and a training
subform
(PK
Training ID) linked by Employee number. The Employee form is the main
form,
the training form is the subform.
On the subform, I have a field called duration. This field is a drop
down
that looks up (that's funny) values (numbers) from a table. I also have
an
expiry field that adds the duration and indicates the expiry date. For
example, here are my fields in the subform:
Class Name; Date; Duration; Expires.

The formula in the expires field is: =IIf([Duration] Is
Null,Null,DateAdd("yyyy",[Duration],[Class Date])).

Now, how would I set up a query to look for a class e.g. Manager's
Training
that has expired by let's say 1/1/2005? How can I indicate this in the
query
when the Expires field is calculated? My training subform is not based
on
a
query but on my training table.

Quite confused,

John.

 

[Access rookie]

Hey Ken, HOORAY! It works!

First of all, thanks for explaining your solution in English!

When I typed the <=#1/1/2005# in the criteria field, I got a data type
mismatch in criteria expression. When I ran it without the criteria, it just
gave me the expiry dates.

What I did was put this in the Expiry calculated field: IIf([Duration] Is
Null,Null,DateAdd("yyyy",[Duration],[Class Date])) - got this from the other
calculated field.
By doing this, I avoided the #Error message I got from adding Null fields.
I then entered <[Enter Date:] under that calculated field. YAHOOOO!

Thanks again, and have a Happy New Year!

John.

This is a fairly simple query -- you can create it in the QBE (design) view
of the query object.

Create a new query (don't use the wizard). Select the table from the list of
tables presented. Click Add button. Click Close button.

Now drag fields from the table onto the grid; include all fields that you
wish to display.

Then click into the first unused column on the grid, in the "Field:" cell.
Type this into that cell:
ExpireDate: DateAdd("yyyy",[Duration],[Class Date])

(Note that the above assumes that Duration and Class Date are fields in this
table.)

In the "Criteria:" cell under this "calculated" field, type
<= #1/1/2005#

Your query is now done.
--

Ken Snell
<MS ACCESS MVP>

Hello Ken and Dale,

Thanks for your input.
The query is to be run any time; it's simply to find out whether an
individual's training, eg First Aid is expired.
I would like to just have a command button to do this; it would prompt for
class name, then a date. The query would then return all employees that
have
their training for that class expired.
I know how to do the command button (thank you wizards!), but the SQL
statement you included has me puzzled (thus my display name.) Where would
I
put this?
I'm used to entering my parameters in the criteria field in the query
window; how would I do that with SQL? I can't enter the parameters in the
expiry field, because 1. The form is based on my training table, not a
query
2. The expiry field is calculated. Grooaaannn....

Willing to valiantly slog through the SQL needed to get this done,

John.

When is the query to be run? And for what purpose?

Simple answer is for you to include the expression that you're using in
the
query as a calculated field, and then use a criterion for that calculated
field. For example,

SELECT *
FROM TableName
WHERE DateAdd("yyyy",[Duration],[Class Date])
<= #1/1/2005#;

--

Ken Snell
<MS ACCESS MVP>
..


Hello,

I am in quite a quandary at the moment:
I have an employee main form (PK Employee Number) and a training
subform
(PK
Training ID) linked by Employee number. The Employee form is the main
form,
the training form is the subform.
On the subform, I have a field called duration. This field is a drop
down
that looks up (that's funny) values (numbers) from a table. I also have
an
expiry field that adds the duration and indicates the expiry date. For
example, here are my fields in the subform:
Class Name; Date; Duration; Expires.

The formula in the expires field is: =IIf([Duration] Is
Null,Null,DateAdd("yyyy",[Duration],[Class Date])).

Now, how would I set up a query to look for a class e.g. Manager's
Training
that has expired by let's say 1/1/2005? How can I indicate this in the
query
when the Expires field is calculated? My training subform is not based
on
a
query but on my training table.

Quite confused,

John.

 

[Ken Snell [MVP]]

Glad you made it work. Good luck.

--

Ken Snell
<MS ACCESS MVP>

Hey Ken, HOORAY! It works!

First of all, thanks for explaining your solution in English!

When I typed the <=#1/1/2005# in the criteria field, I got a data type
mismatch in criteria expression. When I ran it without the criteria, it
just
gave me the expiry dates.

What I did was put this in the Expiry calculated field: IIf([Duration] Is
Null,Null,DateAdd("yyyy",[Duration],[Class Date])) - got this from the
other
calculated field.
By doing this, I avoided the #Error message I got from adding Null fields.
I then entered <[Enter Date:] under that calculated field. YAHOOOO!

Thanks again, and have a Happy New Year!

John.

This is a fairly simple query -- you can create it in the QBE (design)
view
of the query object.

Create a new query (don't use the wizard). Select the table from the list
of
tables presented. Click Add button. Click Close button.

Now drag fields from the table onto the grid; include all fields that you
wish to display.

Then click into the first unused column on the grid, in the "Field:"
cell.
Type this into that cell:
ExpireDate: DateAdd("yyyy",[Duration],[Class Date])

(Note that the above assumes that Duration and Class Date are fields in
this
table.)

In the "Criteria:" cell under this "calculated" field, type
<= #1/1/2005#

Your query is now done.
--

Ken Snell
<MS ACCESS MVP>

Hello Ken and Dale,

Thanks for your input.
The query is to be run any time; it's simply to find out whether an
individual's training, eg First Aid is expired.
I would like to just have a command button to do this; it would prompt
for
class name, then a date. The query would then return all employees that
have
their training for that class expired.
I know how to do the command button (thank you wizards!), but the SQL
statement you included has me puzzled (thus my display name.) Where
would
I
put this?
I'm used to entering my parameters in the criteria field in the query
window; how would I do that with SQL? I can't enter the parameters in
the
expiry field, because 1. The form is based on my training table, not a
query
2. The expiry field is calculated. Grooaaannn....

Willing to valiantly slog through the SQL needed to get this done,

John.

:

When is the query to be run? And for what purpose?

Simple answer is for you to include the expression that you're using
in
the
query as a calculated field, and then use a criterion for that
calculated
field. For example,

SELECT *
FROM TableName
WHERE DateAdd("yyyy",[Duration],[Class Date])
<= #1/1/2005#;

--

Ken Snell
<MS ACCESS MVP>
..


message
Hello,

I am in quite a quandary at the moment:
I have an employee main form (PK Employee Number) and a training
subform
(PK
Training ID) linked by Employee number. The Employee form is the
main
form,
the training form is the subform.
On the subform, I have a field called duration. This field is a drop
down
that looks up (that's funny) values (numbers) from a table. I also
have
an
expiry field that adds the duration and indicates the expiry date.
For
example, here are my fields in the subform:
Class Name; Date; Duration; Expires.

The formula in the expires field is: =IIf([Duration] Is
Null,Null,DateAdd("yyyy",[Duration],[Class Date])).

Now, how would I set up a query to look for a class e.g. Manager's
Training
that has expired by let's say 1/1/2005? How can I indicate this in
the
query
when the Expires field is calculated? My training subform is not
based
on
a
query but on my training table.

Quite confused,

John.

 

[Dale Fye]

Rookie,

Ken's post will get you basically the same results that the query I posted
will get you. It will not, however tell you that I (for example) have two
entries for the Management course, one that expires on 11/1/04, and the
second that expires on 11/1/05. Using the code that both Ken and I wrote,
it will tell you that my Management course has expired, when I actually have
already renewed it.

I have several questions. In your reply to me, you indicated that you are
maintaining a record of all training accomplished, which includes multiple
rows, to account for every time I take a particular course. How do you know
that I am required to take the course? Using the data you have explained to
us so far, all the query will do is identify people who have already taken
the course, and the expiration date is past. It will not identify people
that are supposed to take the course, and have not yet done so.

If the duration is not filled in, does that mean that the course does not
expire? If so, then you can throw in some arbitrarily large number to make
the query a little simpler. If you assume that all you want to do is
identify when people have training that is out of date, then you need to try
something like:

SELECT E.EmployeeID, E.EmployeeName, T.CourseName,
MAX(DateAdd("yyyy",NZ([Duration], 20),[Class Date])) as MaxExpire
FROM Employee E
INNER JOIN Training T
ON E.EmployeeID = T.EmployeeID
GROUP BY E.EmployeeID, E.EmployeeName, T.CourseName
HAVING MAX(DateAdd("yyyy",NZ([Duration],20),[Class Date]))) <= #1/1/2005#

What this query does is find the max expiration date for each
employee/class, and then filter out those whose most recent training class
has not yet expired.

It will get more complicated if you also want to identify those that are
supposed to take the class but have not taken it yet.

HTH
Dale

Hey Ken, HOORAY! It works!

First of all, thanks for explaining your solution in English!

When I typed the <=#1/1/2005# in the criteria field, I got a data type
mismatch in criteria expression. When I ran it without the criteria, it just
gave me the expiry dates.

What I did was put this in the Expiry calculated field: IIf([Duration] Is
Null,Null,DateAdd("yyyy",[Duration],[Class Date])) - got this from the other
calculated field.
By doing this, I avoided the #Error message I got from adding Null fields.
I then entered <[Enter Date:] under that calculated field. YAHOOOO!

Thanks again, and have a Happy New Year!

John.

This is a fairly simple query -- you can create it in the QBE (design) view
of the query object.

Create a new query (don't use the wizard). Select the table from the list of
tables presented. Click Add button. Click Close button.

Now drag fields from the table onto the grid; include all fields that you
wish to display.

Then click into the first unused column on the grid, in the "Field:" cell.
Type this into that cell:
ExpireDate: DateAdd("yyyy",[Duration],[Class Date])

(Note that the above assumes that Duration and Class Date are fields in this
table.)

In the "Criteria:" cell under this "calculated" field, type
<= #1/1/2005#

Your query is now done.
--

Ken Snell
<MS ACCESS MVP>

Hello Ken and Dale,

Thanks for your input.
The query is to be run any time; it's simply to find out whether an
individual's training, eg First Aid is expired.
I would like to just have a command button to do this; it would prompt for
class name, then a date. The query would then return all employees that
have
their training for that class expired.
I know how to do the command button (thank you wizards!), but the SQL
statement you included has me puzzled (thus my display name.) Where would
I
put this?
I'm used to entering my parameters in the criteria field in the query
window; how would I do that with SQL? I can't enter the parameters in the
expiry field, because 1. The form is based on my training table, not a
query
2. The expiry field is calculated. Grooaaannn....

Willing to valiantly slog through the SQL needed to get this done,

John.

:

When is the query to be run? And for what purpose?

Simple answer is for you to include the expression that you're using in
the
query as a calculated field, and then use a criterion for that calculated
field. For example,

SELECT *
FROM TableName
WHERE DateAdd("yyyy",[Duration],[Class Date])
<= #1/1/2005#;

--

Ken Snell
<MS ACCESS MVP>
..


Hello,

I am in quite a quandary at the moment:
I have an employee main form (PK Employee Number) and a training
subform
(PK
Training ID) linked by Employee number. The Employee form is the main
form,
the training form is the subform.
On the subform, I have a field called duration. This field is a drop
down
that looks up (that's funny) values (numbers) from a table. I also have
an
expiry field that adds the duration and indicates the expiry date. For
example, here are my fields in the subform:
Class Name; Date; Duration; Expires.

The formula in the expires field is: =IIf([Duration] Is
Null,Null,DateAdd("yyyy",[Duration],[Class Date])).

Now, how would I set up a query to look for a class e.g. Manager's
Training
that has expired by let's say 1/1/2005? How can I indicate this in the
query
when the Expires field is calculated? My training subform is not based
on
a
query but on my training table.

Quite confused,

John.

 

[Access rookie]

Hey Dale,
I'm glad I looked at this post again; you're right, when I run this query
(the way it was) I get correct results; however when I enter a parameter, it
doesn't return all the correct results.
You're observation is also correct: it only shows how many classes the
individual has taken, not what they are supposed to take ( I haven't figured
out how to do this yet - it would be a miracle if I can do that!).
Also, concerning the duration, if it is left blank, that means the class
does not expire. You mentioned throwing in an arbitrarily large no...in the
duration field?
You were right on the money - for now, all I want to do is find show
employees whose training is out of date.
I will try the code you sent...I never knew there was a way to check to see
if employees have completed certain trainings...Access can do that? Whoa! Is
there a way to do that without getting neck deep in code?

Thanks a million,

John.

Rookie,

Ken's post will get you basically the same results that the query I posted
will get you. It will not, however tell you that I (for example) have two
entries for the Management course, one that expires on 11/1/04, and the
second that expires on 11/1/05. Using the code that both Ken and I wrote,
it will tell you that my Management course has expired, when I actually have
already renewed it.

I have several questions. In your reply to me, you indicated that you are
maintaining a record of all training accomplished, which includes multiple
rows, to account for every time I take a particular course. How do you know
that I am required to take the course? Using the data you have explained to
us so far, all the query will do is identify people who have already taken
the course, and the expiration date is past. It will not identify people
that are supposed to take the course, and have not yet done so.

If the duration is not filled in, does that mean that the course does not
expire? If so, then you can throw in some arbitrarily large number to make
the query a little simpler. If you assume that all you want to do is
identify when people have training that is out of date, then you need to try
something like:

SELECT E.EmployeeID, E.EmployeeName, T.CourseName,
MAX(DateAdd("yyyy",NZ([Duration], 20),[Class Date])) as MaxExpire
FROM Employee E
INNER JOIN Training T
ON E.EmployeeID = T.EmployeeID
GROUP BY E.EmployeeID, E.EmployeeName, T.CourseName
HAVING MAX(DateAdd("yyyy",NZ([Duration],20),[Class Date]))) <= #1/1/2005#

What this query does is find the max expiration date for each
employee/class, and then filter out those whose most recent training class
has not yet expired.

It will get more complicated if you also want to identify those that are
supposed to take the class but have not taken it yet.

HTH
Dale

Hey Ken, HOORAY! It works!

First of all, thanks for explaining your solution in English!

When I typed the <=#1/1/2005# in the criteria field, I got a data type
mismatch in criteria expression. When I ran it without the criteria, it just
gave me the expiry dates.

What I did was put this in the Expiry calculated field: IIf([Duration] Is
Null,Null,DateAdd("yyyy",[Duration],[Class Date])) - got this from the other
calculated field.
By doing this, I avoided the #Error message I got from adding Null fields.
I then entered <[Enter Date:] under that calculated field. YAHOOOO!

Thanks again, and have a Happy New Year!

John.

This is a fairly simple query -- you can create it in the QBE (design) view
of the query object.

Create a new query (don't use the wizard). Select the table from the list of
tables presented. Click Add button. Click Close button.

Now drag fields from the table onto the grid; include all fields that you
wish to display.

Then click into the first unused column on the grid, in the "Field:" cell.
Type this into that cell:
ExpireDate: DateAdd("yyyy",[Duration],[Class Date])

(Note that the above assumes that Duration and Class Date are fields in this
table.)

In the "Criteria:" cell under this "calculated" field, type
<= #1/1/2005#

Your query is now done.
--

Ken Snell
<MS ACCESS MVP>



Hello Ken and Dale,

Thanks for your input.
The query is to be run any time; it's simply to find out whether an
individual's training, eg First Aid is expired.
I would like to just have a command button to do this; it would prompt for
class name, then a date. The query would then return all employees that
have
their training for that class expired.
I know how to do the command button (thank you wizards!), but the SQL
statement you included has me puzzled (thus my display name.) Where would
I
put this?
I'm used to entering my parameters in the criteria field in the query
window; how would I do that with SQL? I can't enter the parameters in the
expiry field, because 1. The form is based on my training table, not a
query
2. The expiry field is calculated. Grooaaannn....

Willing to valiantly slog through the SQL needed to get this done,

John.

:

When is the query to be run? And for what purpose?

Simple answer is for you to include the expression that you're using in
the
query as a calculated field, and then use a criterion for that calculated
field. For example,

SELECT *
FROM TableName
WHERE DateAdd("yyyy",[Duration],[Class Date])
<= #1/1/2005#;

--

Ken Snell
<MS ACCESS MVP>
..


Hello,

I am in quite a quandary at the moment:
I have an employee main form (PK Employee Number) and a training
subform
(PK
Training ID) linked by Employee number. The Employee form is the main
form,
the training form is the subform.
On the subform, I have a field called duration. This field is a drop
down
that looks up (that's funny) values (numbers) from a table. I also have
an
expiry field that adds the duration and indicates the expiry date. For
example, here are my fields in the subform:
Class Name; Date; Duration; Expires.

The formula in the expires field is: =IIf([Duration] Is
Null,Null,DateAdd("yyyy",[Duration],[Class Date])).

Now, how would I set up a query to look for a class e.g. Manager's
Training
that has expired by let's say 1/1/2005? How can I indicate this in the
query
when the Expires field is calculated? My training subform is not based
on
a
query but on my training table.

Quite confused,

John.

 

[Access rookie]

Thanks Dale - it works great!

Happy New Year!

John.

Rookie,

Ken's post will get you basically the same results that the query I posted
will get you. It will not, however tell you that I (for example) have two
entries for the Management course, one that expires on 11/1/04, and the
second that expires on 11/1/05. Using the code that both Ken and I wrote,
it will tell you that my Management course has expired, when I actually have
already renewed it.

I have several questions. In your reply to me, you indicated that you are
maintaining a record of all training accomplished, which includes multiple
rows, to account for every time I take a particular course. How do you know
that I am required to take the course? Using the data you have explained to
us so far, all the query will do is identify people who have already taken
the course, and the expiration date is past. It will not identify people
that are supposed to take the course, and have not yet done so.

If the duration is not filled in, does that mean that the course does not
expire? If so, then you can throw in some arbitrarily large number to make
the query a little simpler. If you assume that all you want to do is
identify when people have training that is out of date, then you need to try
something like:

SELECT E.EmployeeID, E.EmployeeName, T.CourseName,
MAX(DateAdd("yyyy",NZ([Duration], 20),[Class Date])) as MaxExpire
FROM Employee E
INNER JOIN Training T
ON E.EmployeeID = T.EmployeeID
GROUP BY E.EmployeeID, E.EmployeeName, T.CourseName
HAVING MAX(DateAdd("yyyy",NZ([Duration],20),[Class Date]))) <= #1/1/2005#

What this query does is find the max expiration date for each
employee/class, and then filter out those whose most recent training class
has not yet expired.

It will get more complicated if you also want to identify those that are
supposed to take the class but have not taken it yet.

HTH
Dale

Hey Ken, HOORAY! It works!

First of all, thanks for explaining your solution in English!

When I typed the <=#1/1/2005# in the criteria field, I got a data type
mismatch in criteria expression. When I ran it without the criteria, it just
gave me the expiry dates.

What I did was put this in the Expiry calculated field: IIf([Duration] Is
Null,Null,DateAdd("yyyy",[Duration],[Class Date])) - got this from the other
calculated field.
By doing this, I avoided the #Error message I got from adding Null fields.
I then entered <[Enter Date:] under that calculated field. YAHOOOO!

Thanks again, and have a Happy New Year!

John.

This is a fairly simple query -- you can create it in the QBE (design) view
of the query object.

Create a new query (don't use the wizard). Select the table from the list of
tables presented. Click Add button. Click Close button.

Now drag fields from the table onto the grid; include all fields that you
wish to display.

Then click into the first unused column on the grid, in the "Field:" cell.
Type this into that cell:
ExpireDate: DateAdd("yyyy",[Duration],[Class Date])

(Note that the above assumes that Duration and Class Date are fields in this
table.)

In the "Criteria:" cell under this "calculated" field, type
<= #1/1/2005#

Your query is now done.
--

Ken Snell
<MS ACCESS MVP>



Hello Ken and Dale,

Thanks for your input.
The query is to be run any time; it's simply to find out whether an
individual's training, eg First Aid is expired.
I would like to just have a command button to do this; it would prompt for
class name, then a date. The query would then return all employees that
have
their training for that class expired.
I know how to do the command button (thank you wizards!), but the SQL
statement you included has me puzzled (thus my display name.) Where would
I
put this?
I'm used to entering my parameters in the criteria field in the query
window; how would I do that with SQL? I can't enter the parameters in the
expiry field, because 1. The form is based on my training table, not a
query
2. The expiry field is calculated. Grooaaannn....

Willing to valiantly slog through the SQL needed to get this done,

John.

:

When is the query to be run? And for what purpose?

Simple answer is for you to include the expression that you're using in
the
query as a calculated field, and then use a criterion for that calculated
field. For example,

SELECT *
FROM TableName
WHERE DateAdd("yyyy",[Duration],[Class Date])
<= #1/1/2005#;

--

Ken Snell
<MS ACCESS MVP>
..


Hello,

I am in quite a quandary at the moment:
I have an employee main form (PK Employee Number) and a training
subform
(PK
Training ID) linked by Employee number. The Employee form is the main
form,
the training form is the subform.
On the subform, I have a field called duration. This field is a drop
down
that looks up (that's funny) values (numbers) from a table. I also have
an
expiry field that adds the duration and indicates the expiry date. For
example, here are my fields in the subform:
Class Name; Date; Duration; Expires.

The formula in the expires field is: =IIf([Duration] Is
Null,Null,DateAdd("yyyy",[Duration],[Class Date])).

Now, how would I set up a query to look for a class e.g. Manager's
Training
that has expired by let's say 1/1/2005? How can I indicate this in the
query
when the Expires field is calculated? My training subform is not based
on
a
query but on my training table.

Quite confused,

John.