MAX Query

  • Thread starter Thread starter knowshowrosegrows
  • Start date Start date
K

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
 
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
 
Try a query that looks like the following SQL.


SELECT
Code: 
, 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
'====================================================
 
Back
Top