Date if then statement help!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need help with a date based issue.

I have an employee who takes a test on 01/01/2006 and then later that year
that same person takes a similar test 02/02/2006. I am looking to do query
that returns latest date (most recent). This is for many people within an
organization who may have multiple different tests dates.

JudyT
 
Try this:

SELECT * FROM Tablename
WHERE TestDateField =
(SELECT Max(T.TestDateField) AS MTD
FROM Tablename AS T
WHERE T.EmployeeNum = Tablename.EmployeeNum);
 
Ken,

I am not sure how to use this select statement based on what I have. Here
is what I have in my SQL view maybe it may work better if you plug inthe info.

SELECT [Individual Drill Performance Data].Name, [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
WITH OWNERACCESS OPTION;
 
Not easily. I don't know what the different fields in the tables are. Can
you explain what these fields are:

[Individual Drill Performance Data].Name
[Individual Drill Performance Data].PERNR
[Drill information].[Date]
[Drill information].DrillID
[Individual Drill Performance Data].DrillID

Also, do not use Date as the names of a field. It is a reserved word in
ACCESS, and your use of it as a field name can lead to all kinds of problems
and confusion. See these articles for more information:

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763


--

Ken Snell
<MS ACCESS MVP>



JudyT said:
Ken,

I am not sure how to use this select statement based on what I have. Here
is what I have in my SQL view maybe it may work better if you plug inthe
info.

SELECT [Individual Drill Performance Data].Name, [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
WITH OWNERACCESS OPTION;

Ken Snell (MVP) said:
Try this:

SELECT * FROM Tablename
WHERE TestDateField =
(SELECT Max(T.TestDateField) AS MTD
FROM Tablename AS T
WHERE T.EmployeeNum = Tablename.EmployeeNum);
Click to expand...
Click to expand...
 
Hey Ken,

Alright, from the beginning...this is a database that I have taken over. I
will change the date issue but can we work around it for now? Here is the
other info about the fields.

[Individual Drill Performance Data].Name---Employee name--text type
[Individual Drill Performance Data].PERNR---Employee Number-text type
[Drill information].[Date]---The date the drill was held---short date type
[Drill information].DrillID---Primary Key---the Identification of the
drill---number type(which I know does not need to be this because there is no
calculations done on this field.)INNER JOIN [Individual Drill Performance
Data].DrillID---Foriegn key


Ken Snell (MVP) said:
Not easily. I don't know what the different fields in the tables are. Can
you explain what these fields are:

[Individual Drill Performance Data].Name
[Individual Drill Performance Data].PERNR
[Drill information].[Date]
[Drill information].DrillID
[Individual Drill Performance Data].DrillID

Also, do not use Date as the names of a field. It is a reserved word in
ACCESS, and your use of it as a field name can lead to all kinds of problems
and confusion. See these articles for more information:

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763


--

Ken Snell
<MS ACCESS MVP>



JudyT said:
Ken,

I am not sure how to use this select statement based on what I have. Here
is what I have in my SQL view maybe it may work better if you plug inthe
info.

SELECT [Individual Drill Performance Data].Name, [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
WITH OWNERACCESS OPTION;

Ken Snell (MVP) said:
Try this:

SELECT * FROM Tablename
WHERE TestDateField =
(SELECT Max(T.TestDateField) AS MTD
FROM Tablename AS T
WHERE T.EmployeeNum = Tablename.EmployeeNum);


--

Ken Snell
<MS ACCESS MVP>



I need help with a date based issue.

I have an employee who takes a test on 01/01/2006 and then later that
year
that same person takes a similar test 02/02/2006. I am looking to do
query
that returns latest date (most recent). This is for many people within
an
organization who may have multiple different tests dates.

JudyT
Click to expand...
Click to expand...
Click to expand...
 
You can continue to use Date as the field name, but you must always surround
it with [ ] characters so that it is clearly defined as a field. However,
you should change the field name to something else at your earliest
opportunity. By the way, the same "concern" applies to the use of Name as a
field name; it too is a reserved word.

Using DrillID field as the unique identifier for the type of drill, this
query may be what you seek:

SELECT [Individual Drill Performance Data].[Name],
[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);
WITH OWNERACCESS OPTION;


--

Ken Snell
<MS ACCESS MVP>




JudyT said:
Hey Ken,

Alright, from the beginning...this is a database that I have taken over.
I
will change the date issue but can we work around it for now? Here is the
other info about the fields.

[Individual Drill Performance Data].Name---Employee name--text type
[Individual Drill Performance Data].PERNR---Employee Number-text type
[Drill information].[Date]---The date the drill was held---short date type
[Drill information].DrillID---Primary Key---the Identification of the
drill---number type(which I know does not need to be this because there is
no
calculations done on this field.)INNER JOIN [Individual Drill Performance
Data].DrillID---Foriegn key


Ken Snell (MVP) said:
Not easily. I don't know what the different fields in the tables are. Can
you explain what these fields are:

[Individual Drill Performance Data].Name
[Individual Drill Performance Data].PERNR
[Drill information].[Date]
[Drill information].DrillID
[Individual Drill Performance Data].DrillID

Also, do not use Date as the names of a field. It is a reserved word in
ACCESS, and your use of it as a field name can lead to all kinds of
problems
and confusion. See these articles for more information:

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763


--

Ken Snell
<MS ACCESS MVP>



JudyT said:
Ken,

I am not sure how to use this select statement based on what I have.
Here
is what I have in my SQL view maybe it may work better if you plug
inthe
info.

SELECT [Individual Drill Performance Data].Name, [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
WITH OWNERACCESS OPTION;

:

Try this:

SELECT * FROM Tablename
WHERE TestDateField =
(SELECT Max(T.TestDateField) AS MTD
FROM Tablename AS T
WHERE T.EmployeeNum = Tablename.EmployeeNum);


--

Ken Snell
<MS ACCESS MVP>



I need help with a date based issue.

I have an employee who takes a test on 01/01/2006 and then later
that
year
that same person takes a similar test 02/02/2006. I am looking to
do
query
that returns latest date (most recent). This is for many people
within
an
organization who may have multiple different tests dates.

JudyT
Click to expand...
Click to expand...
Click to expand...
 
Ken,

Thank you for your help, it did work and I have made that change to the date
field to reflect [Date]. I am very grateful.
Judy

Ken Snell (MVP) said:
You can continue to use Date as the field name, but you must always surround
it with [ ] characters so that it is clearly defined as a field. However,
you should change the field name to something else at your earliest
opportunity. By the way, the same "concern" applies to the use of Name as a
field name; it too is a reserved word.

Using DrillID field as the unique identifier for the type of drill, this
query may be what you seek:

SELECT [Individual Drill Performance Data].[Name],
[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);
WITH OWNERACCESS OPTION;


--

Ken Snell
<MS ACCESS MVP>




JudyT said:
Hey Ken,

Alright, from the beginning...this is a database that I have taken over.
I
will change the date issue but can we work around it for now? Here is the
other info about the fields.

[Individual Drill Performance Data].Name---Employee name--text type
[Individual Drill Performance Data].PERNR---Employee Number-text type
[Drill information].[Date]---The date the drill was held---short date type
[Drill information].DrillID---Primary Key---the Identification of the
drill---number type(which I know does not need to be this because there is
no
calculations done on this field.)INNER JOIN [Individual Drill Performance
Data].DrillID---Foriegn key


Ken Snell (MVP) said:
Not easily. I don't know what the different fields in the tables are. Can
you explain what these fields are:

[Individual Drill Performance Data].Name
[Individual Drill Performance Data].PERNR
[Drill information].[Date]
[Drill information].DrillID
[Individual Drill Performance Data].DrillID

Also, do not use Date as the names of a field. It is a reserved word in
ACCESS, and your use of it as a field name can lead to all kinds of
problems
and confusion. See these articles for more information:

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763


--

Ken Snell
<MS ACCESS MVP>



Ken,

I am not sure how to use this select statement based on what I have.
Here
is what I have in my SQL view maybe it may work better if you plug
inthe
info.

SELECT [Individual Drill Performance Data].Name, [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
WITH OWNERACCESS OPTION;

:

Try this:

SELECT * FROM Tablename
WHERE TestDateField =
(SELECT Max(T.TestDateField) AS MTD
FROM Tablename AS T
WHERE T.EmployeeNum = Tablename.EmployeeNum);


--

Ken Snell
<MS ACCESS MVP>



I need help with a date based issue.

I have an employee who takes a test on 01/01/2006 and then later
that
year
that same person takes a similar test 02/02/2006. I am looking to
do
query
that returns latest date (most recent). This is for many people
within
an
organization who may have multiple different tests dates.

JudyT
Click to expand...
Click to expand...
Click to expand...
 
JudyT said:
Ken,

Thank you for your help, it did work and I have made that change to the
date
field to reflect [Date]. I am very grateful.
Judy
Click to expand...

Glad that the query worked.

Do not use [Date] (the literal text with the [ and ] characters) as the name
of the field. What I meant was that you need to surround Date with those [
and ] characters when you use a field named Date in queries. For example, if
you named the field Date, a query using that field would be this:

SELECT [Date] FROM TableName;

If you name the field [Date], then your query will need to be this:

SELECT [[Date]] FROM TableName;

So instead, name your field DrillDate or something like that.
 
Ken

I know it has been a while this was posted but I recently found out that the
query we built does not properly work...It does show the latest date they
were unsat in a drill but I need to do a comparison of when they become sat
in the next drill.

In other words the query returns the latest date of when they were unsat but
they can at a later date become sat thereby nullifying the first query. I
have a query that shows those that were sat and the above showing unsat. How
do I query on the one date that is the latest that is unsat but when they
become sat they come off the list.

Thank you for your help
Judy

Ken Snell (MVP) said:
You can continue to use Date as the field name, but you must always surround
it with [ ] characters so that it is clearly defined as a field. However,
you should change the field name to something else at your earliest
opportunity. By the way, the same "concern" applies to the use of Name as a
field name; it too is a reserved word.

Using DrillID field as the unique identifier for the type of drill, this
query may be what you seek:

SELECT [Individual Drill Performance Data].[Name],
[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);
WITH OWNERACCESS OPTION;


--

Ken Snell
<MS ACCESS MVP>




JudyT said:
Hey Ken,

Alright, from the beginning...this is a database that I have taken over.
I
will change the date issue but can we work around it for now? Here is the
other info about the fields.

[Individual Drill Performance Data].Name---Employee name--text type
[Individual Drill Performance Data].PERNR---Employee Number-text type
[Drill information].[Date]---The date the drill was held---short date type
[Drill information].DrillID---Primary Key---the Identification of the
drill---number type(which I know does not need to be this because there is
no
calculations done on this field.)INNER JOIN [Individual Drill Performance
Data].DrillID---Foriegn key


Ken Snell (MVP) said:
Not easily. I don't know what the different fields in the tables are. Can
you explain what these fields are:

[Individual Drill Performance Data].Name
[Individual Drill Performance Data].PERNR
[Drill information].[Date]
[Drill information].DrillID
[Individual Drill Performance Data].DrillID

Also, do not use Date as the names of a field. It is a reserved word in
ACCESS, and your use of it as a field name can lead to all kinds of
problems
and confusion. See these articles for more information:

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763


--

Ken Snell
<MS ACCESS MVP>



Ken,

I am not sure how to use this select statement based on what I have.
Here
is what I have in my SQL view maybe it may work better if you plug
inthe
info.

SELECT [Individual Drill Performance Data].Name, [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
WITH OWNERACCESS OPTION;

:

Try this:

SELECT * FROM Tablename
WHERE TestDateField =
(SELECT Max(T.TestDateField) AS MTD
FROM Tablename AS T
WHERE T.EmployeeNum = Tablename.EmployeeNum);


--

Ken Snell
<MS ACCESS MVP>



I need help with a date based issue.

I have an employee who takes a test on 01/01/2006 and then later
that
year
that same person takes a similar test 02/02/2006. I am looking to
do
query
that returns latest date (most recent). This is for many people
within
an
organization who may have multiple different tests dates.

JudyT
Click to expand...
Click to expand...
Click to expand...
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Using DateAdd and IIF conditions in a Query 2
Create weekly incremental dates and add records 4
automate date increments by weeks and add records 26
Please help me ! 4
Help with Date Difference Expression 2
Query using 'Between [Enter Start Date] And [Enter End date]' 4
Date problems 1
Display "week 1" based on date in another field 1

Back
Top