MAX Query

[knowshowrosegrows]

Hi I need help with what I am sure is a simple query.

I have a table = tblCapacity
Fields = Code, EffectiveDate, Capacity.

I need the maximum EffectiveDate and Capacity for each Code.

So the final table would give me:

Code EffDate Capacity
1111 01/01/2001 25
1112 04/01/2003 44

 

[bcap]

In the query design window, add the table and add all three fields to the
grid. On the View menu, choose "Totals". Backm in the grid, under the
EffectiveData and Capacity fields change "Group By" to "Max".

In SQL, it's something like this:

SELECT Code, Max(tblCapacity.EffectiveDate) As EffDate,
Max(tblCapacity.Capacity) As Capacity FROM tblCapacity GROUP BY Code

 

[John Spencer]

Try a query that looks like the following SQL.


SELECT

, EffectiveDate, Capacity
FROM tblCapacity
WHERE EffectiveDate =
(SELECT Max(EffectiveDate)
FROM tblCapacity as Temp
WHERE Temp.[Code] = tblCapacity.[Code])


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================