Get Latest Entry with Query

[Guest]

I have a table that has project management information. It has several
fields but the important ones are:

Project Number
Date Info Entered
Time Info Entered
Information

Basically it has records for information added to project progress. Data
looks something like this:

ProNum Date Time Info
1 2/5/2005 1215 Some older information here
1 2/5/2005 1523 Some older information here
1 2/6/2005 0912 Some information here - newest
2 2/5/2005 1015 Some older information here
2 2/8/2005 1849 Some information here - newest
3 2/5/2005 1435 Some information here - newest


What I want to be able to get from a query is just one record for each
project number that is the most recent information entered. Something like:

ProNum Date Time Info
1 2/6/2005 0912 Some information here - newest
2 2/8/2005 1849 Some information here - newest
3 2/5/2005 1435 Some information here - newest


I am not sure how to set up a Query to get this information, or if it is
even possible. Any advice would be greatly appreciated.

Thanks,
Jim

 

[Ken Snell [MVP]]

Query would use a subquery to identify the correct date that is in the
record that you want. This example assumes that date and time values are
stored in date/time fields. If you're storing those values in different data
type fields, then the query will need modification.

SELECT Q.ProNum, Q.[Date], Q.[Time], Q.Info
FROM YourProjectTable AS Q
WHERE (Q.[Date] + Q.[Time])=
(SELECT Max(T.[Date] + T.[Time])
FROM YourProjectTable AS T
WHERE T.ProNum=Q.ProNum);

Also, in case you are, do not use Date or Time as the name of a field. They
and many other words are reserved words in ACCESS, and can create serious
confusion for ACCESS and Jet. See these Knowledge Base 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

 

[Chris2]

I have a table that has project management information. It has several
fields but the important ones are:

Project Number
Date Info Entered
Time Info Entered
Information

Basically it has records for information added to project progress. Data
looks something like this:

ProNum Date Time Info
1 2/5/2005 1215 Some older information here
1 2/5/2005 1523 Some older information here
1 2/6/2005 0912 Some information here - newest
2 2/5/2005 1015 Some older information here
2 2/8/2005 1849 Some information here - newest
3 2/5/2005 1435 Some information here - newest


What I want to be able to get from a query is just one record for each
project number that is the most recent information entered. Something like:

ProNum Date Time Info
1 2/6/2005 0912 Some information here - newest
2 2/8/2005 1849 Some information here - newest
3 2/5/2005 1435 Some information here - newest


I am not sure how to set up a Query to get this information, or if it is
even possible. Any advice would be greatly appreciated.

Thanks,
Jim

DDL

CREATE TABLE ProjectInfo
(ProjectInfoID AUTOINCREMENT
,ProNum INTEGER
,[Date] DATETIME
,[Time] DATETIME
,Info TEXT(255)
,CONSTRAINT pk_ProjectInfo PRIMARY KEY (ProjectInfoID)
)

SampleData

1 2/5/2005 12:15 Some older information here

1 2/5/2005 15:23 Some older information here

1 2/6/2005 09:12 Some information here - newest

2 2/5/2005 10:15 Some older information here

2 2/8/2005 18:49 Some information here - newest

3 2/5/2005 14:35 Some information here - newest


I believe it would go like:

SELECT P1.ProNum
,P1.[Date]
,Format(P1.[Time], "hh:mm") as [Time]
,P1.Info
FROM ProjectInfo AS P1
WHERE P1.[Date] & P1.[Time] =
(SELECT MAX(P2.[Date] & P2.[Time])
FROM ProjectInfo AS P2
WHERE P2.ProNum = P1.ProNum)

Results:

ProNum Date Time Info
1 2/6/2005 09:12 Some information here - newest
2 2/8/2005 18:49 Some information here - newest
3 2/5/2005 14:35 Some information here - newest

 

[Guest]

That worked great, and I learned something new. Thanks

Query would use a subquery to identify the correct date that is in the
record that you want. This example assumes that date and time values are
stored in date/time fields. If you're storing those values in different data
type fields, then the query will need modification.

SELECT Q.ProNum, Q.[Date], Q.[Time], Q.Info
FROM YourProjectTable AS Q
WHERE (Q.[Date] + Q.[Time])=
(SELECT Max(T.[Date] + T.[Time])
FROM YourProjectTable AS T
WHERE T.ProNum=Q.ProNum);

Also, in case you are, do not use Date or Time as the name of a field. They
and many other words are reserved words in ACCESS, and can create serious
confusion for ACCESS and Jet. See these Knowledge Base 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>

I have a table that has project management information. It has several
fields but the important ones are:

Project Number
Date Info Entered
Time Info Entered
Information

Basically it has records for information added to project progress. Data
looks something like this:

ProNum Date Time Info
1 2/5/2005 1215 Some older information here
1 2/5/2005 1523 Some older information here
1 2/6/2005 0912 Some information here - newest
2 2/5/2005 1015 Some older information here
2 2/8/2005 1849 Some information here - newest
3 2/5/2005 1435 Some information here - newest


What I want to be able to get from a query is just one record for each
project number that is the most recent information entered. Something
like:

ProNum Date Time Info
1 2/6/2005 0912 Some information here - newest
2 2/8/2005 1849 Some information here - newest
3 2/5/2005 1435 Some information here - newest


I am not sure how to set up a Query to get this information, or if it is
even possible. Any advice would be greatly appreciated.

Thanks,
Jim