Help! getting duplicate record

  • Thread starter Thread starter tony V via AccessMonster.com
  • Start date Start date
T

tony V via AccessMonster.com

HI! Im doing a query and getting duplicate. I just want the max of Result
and the date for that result.
I tried using max in total for Result and it still show all the output, Can
anyone help me?

Im getting:
ID Result Date
1 1 1/22/2000
1 6 12/6/2003
1 12 6/3/2004
2 2 6/7/2000
2 10 7/25/2005

I want max of Result and the date for the Result.
ID Result Date
1 12 6/3/2004
2 10 7/25/2005
 
Tony,

Try this.

Select *
FROM yourTable T1
INNER JOIN
(SELECT ID, MAX([DateField]) as MaxDate
FROM YourTable
GROUP BY ID) as T2
ON T1.ID = T2.ID
AND T1.[DateField] = T2.MaxDate

BTW,

It is not a good idea to name your [DateField] Date. Date is a reserved
word in Access, and it can cause interpretation problems when you give fields
names that are reserved words. Just give the field a more descriptive name
TransDate, TestDate, or something like that.

HTH
Dale
 
I might of rephrase this wrong, but I want the max of Result.

Dale said:
Tony,

Try this.

Select *
FROM yourTable T1
INNER JOIN
(SELECT ID, MAX([DateField]) as MaxDate
FROM YourTable
GROUP BY ID) as T2
ON T1.ID = T2.ID
AND T1.[DateField] = T2.MaxDate

BTW,

It is not a good idea to name your [DateField] Date. Date is a reserved
word in Access, and it can cause interpretation problems when you give fields
names that are reserved words. Just give the field a more descriptive name
TransDate, TestDate, or something like that.

HTH
Dale
HI! Im doing a query and getting duplicate. I just want the max of Result
and the date for that result.
Click to expand...
[quoted text clipped - 13 lines]
1 12 6/3/2004
2 10 7/25/2005
Click to expand...
Click to expand...
 
Select Main.*
FROM [YourTable] As Main
INNER JOIN
( SELECT [ID], MAX([Result]) as MaxResult
FROM [YourTable]
GROUP BY [ID]
) As Sub
ON Main.[ID] = Sub.[ID]
AND Main.[Result] = Sub.[MaxResult]
 
Back
Top