Help with Max Date

[pokdbz]

I have this query to find duplicates in a table:
SELECT randomization.SCREEN_ID, randomization.DATE_RANDOMIZED,
randomization.[STUDY ASSIGNMENT], Count(randomization.[STUDY ASSIGNMENT]) AS
[CountOfSTUDY ASSIGNMENT]
FROM randomization
GROUP BY randomization.SCREEN_ID, randomization.DATE_RANDOMIZED,
randomization.[STUDY ASSIGNMENT]
HAVING (((randomization.SCREEN_ID) In (SELECT [SCREEN_ID] FROM
[randomization] As Tmp GROUP BY [SCREEN_ID] HAVING Count(*)>1 )))
ORDER BY randomization.DATE_RANDOMIZED DESC;


I would like to only show the max date of the duplicates and another field
called STUDY ASSIGNMENT

So when I made another query using the previous one I put the date field as
Max in totals then when I put in the STUDY ASSIGNMENT field it shows all of
the duplicates again.

What am I doing wrong?

 

[strive4peace]

try something like this:

SELECT
r.SCREEN_ID,
r.DATE_RANDOMIZED,
r.[STUDY ASSIGNMENT],
Count(r.[STUDY ASSIGNMENT]) AS [CountASSIGNMENT]
FROM randomization as r
GROUP BY
r.SCREEN_ID,
r.DATE_RANDOMIZED,
r.[STUDY ASSIGNMENT]
HAVING
(r.SCREEN_ID
In
(SELECT [SCREEN_ID]
FROM [randomization] As Tmp
GROUP BY [SCREEN_ID]
HAVING Count(*)>1;
)
)
AND
(r.DATE_RANDOMIZED
In
(SELECT MAX([DATE_RANDOMIZED])
FROM [randomization] As Tmp
WHERE [SCREEN_ID] = r.SCREEN_ID
)
)
;

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*