CODEWITHSUNDEEP
ms-access
reggie
reggie

Reputation: 858

ms access: retrieving latest value

I have a table Project. It has the following column:
Project ID, projectName, UpdateTime

The data in the table is as follows: 

  Project ID   projectName   UpdateTime
       1          abc        12-2-2009 01:10:00
       1          abc        12-2-2009 04:18:00
       2          xyz        17-7-2009 08:45:00
       2          xyz        17-7-2009 12:21:00

i want the result set to display the latest update project information based on the update time.
for the above example , it should display

  Project ID   projectName   UpdateTime
       2          xyz        17-7-2009 12:21:00
       1          abc        12-2-2009 04:18:00

Upvotes: 0

Views: 131

Answers (4)

baradosa
baradosa

Reputation: 44

I am not sure why everyone seems to want to group the projects. The question simply seems to ask to display the results in descending order of UpdateTime SELECT * FROM Project ORDER BY UpdateTime DESC

I also notice however, that in his example, the results that he anticipates has the records grouped by Project ID and Project Name, in which case the answer provided by @David-W-Fenton must be sufficient.

In Conclusion, he should have asked his question more clearly.

Upvotes: 0

P M Jugwunth
P M Jugwunth

Reputation: 1

I have an answer which uses the graphical interface of a query which may be easy for you to implement. It is presumed that all 3 fields are contained in a single table. Create a new query by clicking "CREATE" and thereafter "Query Design". Right click in the upper section of the graphical interface that opens and click on "Show Table" Select the table that contains these three fields and double click on each field, one at a time so that it appears in the lower section. If you have an auto-number field please ensure that you do not include it in the lower section since this will cause all the table records to display. Any other additional field included in the query could have the same undesired result. Next, on your ribbon click "DESIGN" and after that click on the "Totals" icon. Focus attention on your "UpdateTime" field in the lower section. Change "Group By" to "Max" by clicking into that field and selecting from the drop-down list. To not make ay changes to the other two fields. Save the query and open it in Datasheet view (Do this by clicking "HOME", "View", "Datasheet View"

You should get the results you required. Please let me know whether this has now worked for you.

Ps. Spelling errors in the project name will also give you inaccurate results. To reduce the likelihood of this possible problem have your users to select project names from a combo-box that that has a drop-down list of project names. It is also possible to devise a means whereby the Project name is auto entered when a user selects a Project ID from a drop-down list.

Upvotes: 0

David-W-Fenton
David-W-Fenton

Reputation: 23067

This ought to do the job (as edited to reflect the information @Remou pointed out that I failed to note in your original question):

  SELECT [Project ID], ProjectName, Max(UpdateTime)
  FROM Project
  GROUP BY [Project ID], ProjectName
  ORDER BY Max(UpdateTime) DESC

If that doesn't do it, either I've made a mistake, or there's other information not included in your question.

Upvotes: 1

JeffO
JeffO

Reputation: 8053

SELECT P.[Project ID]
     , P.projectName
     , Max(P.UpdateTime) AS [Latest Update Time]
FROM Project AS P
GROUP BY P.[Project ID]
     , P.projectName
ORDER BY Max(P.UpdateTime) DESC;

Upvotes: 0

Related Questions