By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,571 Members | 994 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,571 IT Pros & Developers. It's quick & easy.

Need to return latest record when there are duplicates

P: n/a
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
Share this Question
Share on Google+
2 Replies


P: n/a
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

P: n/a
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.