Query - Highest Date

  • Thread starter Thread starter Jasmine
  • Start date Start date
J

Jasmine

I have a parent table and a child table.

The child table has multiple dates, I would like to create a query
that has selects the highest date in the child table so I can act upon
the highest date.

I built a query but could't quite figure out how to accomplish this
I know you can use Max() function.

The Child table has the following fields
ID
CASEID
CHNGDTE
=====================
The parent Table has ( Among other fields )
ID
CASEID

Hope someone can help me
 
The solution depends on whether or not you need to update the data or
just view the data. And also on the relationship between the parent
table and the child table. It is unclear from your posting which
field(s) in the child table link to which field(s) in the parent table.

Is parent.CaseID unique in the Parent table? If so, you can use that to
link to the records in the child table.

This query will give you the caseID and the latest change date for the
CaseID.

SELECT CaseId, Max(ChngDte) as LatestChange
FROM [Child Table]
GROUP BY CaseID

If you don't know how to build a query using the SQL view, post back for
directions. Also, tell use more about what you are attempting.
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
That did the trick -

thanks for all your help



Steve said:
After you create your query, click on the Sigma button (looks like capital
E) in the menu at the top of the screen to change the query to a totals
query. Then change Group By under your date field to Max. When you run the
query you will get the most current date.

Steve
(e-mail address removed)
Click to expand...
 
Back
Top