468,315 Members | 1,497 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,315 developers. It's quick & easy.

Need to return latest record when there are duplicates

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
Nov 13 '05 #1
2 3011
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

Nov 13 '05 #2
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
Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by NPC403 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.