Subquery Help?

  • Thread starter Thread starter Jay
  • Start date Start date
J

Jay

Hi,

I have the following 3 fields ID, Month & Year. And I need a query to
return the highest 'Year' value for each ID, but then the highest 'Month'
field for that Year & ID.

Can anyone help,

Regards.....Jasonm
 
Jason,

From my perspective, the easiest way to do this would be to use the
DateSerial function to create a date based on the YearField, MonthField, and
the first day of the month Notice that I have replaced your Month and Year
with MonthField and YearField; Month and Year are reserved words in Access
and should not be used as field names.

The subquery below identifies the maximum date for each ID and then uses
that to filter the other query.

SELECT ID, MonthField, YearField
FROM yourTable
WHERE DateSerial(YearField, MonthField, 1) = (SELECT
Max(DateSerial(YearField, MonthField, 1)) FROM yourTable T1 WHERE T1.ID =
yourTable.ID)

Another method would be:
SELECT ID, MonthField, YearField
FROM yourTable
INNER JOIN (SELECT ID, MAX(DateSerial(YearField,MonthField,1)) as MaxDate
FROM yourTable
GROUP BY ID) as T1
ON yourTable.ID = T1.ID
AND Dateserial(YearField, MonthField, 1) = T1.MaxDate

The second query would have to be written in the SQL view, and would not be
editable or viewable in the query design view because of the join on the
DateSerial functions value.

HTH
Dale
 
Hi Dale, Thanks for the help. I should have mentioned that my Month field
is simply an integer i.e 1,8,9.And I'm going to have to right this in an
..adp so is DateSerial available in T-sql?

Regards,

Jason
 
Hve not used T-SQL in a while. I don't think it was available in 2000, but
may be in SQL Server 2005.

--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.
 
Back
Top