Query Question

[mattbrem]

I have a query I need to design and I am having a bit of trouble figuring out how to do it, my access skills are not my best.

I have a table with several fields, but only two are relevant to the query. The first is a case number, and the second is a date. Some records have the same case number. I would to retrieve all unique case numbers, I don't want any records to have the same case number. If two records have the same case number I want to record with the latest date to be pulled.

Any advice on how to do this?

 

[jal]

Let's assume your table is called table1 and your date column is called dateValue
SELECT T.* FROM
(
SELECT caseNo, max(dateValue) as theDate
FROM table1
GROUP by caseNo
) as UniqueCombos
INNER JOIN
table1 as T on T.CaseNo = UniqueCombos.CaseNo
AND T.dateValue = UniqueCombos.theDate

You'll still get both records if they agree on BOTH caseNo AND date. (In other words my query assumes they only agree on caseNo, never on both).

And do some testing - rarely do I get a query right on the first try.