Need to return latest record when there are duplicates

[M.Stanley]

Hi,
I have a problem..I'm doing a specific query where I'm joining fields
from a table with appednded data (there are duplicate records, except
for the date/time), and another query. I want the results of the new
query to return ALL of the matches, and only the latest record when it
finds duplicates. Performing a return top values doesn't work because
it doesn't return all of the matchs, using LAST in the date field
criteria only returns 1 record.

I'd appreciate any help or advise.

Thanks,
Mark

[Don Leverton]

Hi Mark,

Try using "Max" instead of "Last"

In your query design view, open the 'View' menu on the menu bar, and check
the 'Totals' option
This adds another row in you QBE grid. Choose "Group By" for all of the
fields that contain identical data, and "Max" for your date/time field.

This sample SQL that I used for testing returns 251 of the 254 records in
the table.
Where the CheckDate and PmtAmout are the same, it returns only the latest
entry.
----------------------------------------------------------------------------
----
SELECT tblCheckWriter.CheckDate, tblCheckWriter.PmtAmount,
Max(tblCheckWriter.ClearedDate) AS MaxOfClearedDate
FROM tblCheckWriter
GROUP BY tblCheckWriter.CheckDate, tblCheckWriter.PmtAmount;

I verified the results using the "Find Duplicates" query wizard to create
this SQL which returns 6 records (IOW ... 3 duplicates)
----------------------------------------------------------------------------
----
SELECT DISTINCTROW tblCheckWriter.CheckDate, tblCheckWriter.PmtAmount,
tblCheckWriter.ClearedDate
FROM tblCheckWriter
WHERE (((tblCheckWriter.CheckDate) In (SELECT [CheckDate] FROM
[tblCheckWriter] As Tmp GROUP BY [CheckDate],[PmtAmount] HAVING Count(*)>1
And [PmtAmount] = [tblCheckWriter].[PmtAmount])))
ORDER BY tblCheckWriter.CheckDate, tblCheckWriter.PmtAmount;
--
HTH,
Don
=============================
E-Mail (if you must)
My*****@Telus.net

Disclaimer:
Professional PartsPerson
Amateur Database Programmer {:o)

I'm an Access97 user, so all posted code
samples are also Access97- based
unless otherwise noted.

Do Until SinksIn = True
File/Save, <slam fingers in desk drawer>
Loop

================================
"M.Stanley" <ms********@hotmail.com> wrote in message
news:79**************************@posting.google.c om...

Hi,
I have a problem..I'm doing a specific query where I'm joining fields
from a table with appednded data (there are duplicate records, except
for the date/time), and another query. I want the results of the new
query to return ALL of the matches, and only the latest record when it
finds duplicates. Performing a return top values doesn't work because
it doesn't return all of the matchs, using LAST in the date field
criteria only returns 1 record.

I'd appreciate any help or advise.

Thanks,
Mark

[Trevor Best]

M.Stanley wrote:

Hi,
I have a problem..I'm doing a specific query where I'm joining fields
from a table with appednded data (there are duplicate records, except
for the date/time), and another query. I want the results of the new
query to return ALL of the matches, and only the latest record when it
finds duplicates. Performing a return top values doesn't work because
it doesn't return all of the matchs, using LAST in the date field
criteria only returns 1 record.

I'd appreciate any help or advise.

Thanks,
Mark

USe Last but group by what is duplicated.

--
Error reading sig - A)bort R)etry I)nfluence with large hammer