Connie-
If there are duplicates at the "max" value, the DISTINCT is the only way to
return one row, but it won't be updateable.
John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
<http://www.viescas.com/> http://www.viescas.com/
(Paris, France)
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of mrsgoudge
Sent: Monday, July 02, 2012 8:01 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] SELECT TOP 1 Returning 2 records if there are duplicate
records
Today I had not planned on working on Access--too much other stuff to do, but
I'm encounter one issue after another. I am having duplicates returned in a
qCurrentPriceAll that has a subquery with SELECT TOP 1.The query
qCurrentPriceAll is based on is a union query joining price and dates from the
tables Listings and DropPrice. Because of user mistakes or unique circumstances
there is sometimes a duplicate entry in the DropPrice table as the Listings
table. qCurrentPriceAll is supposed to find the most current date and give that
line. That is working except in the instances of duplicate entries in which case
there is a line for both.
Is there a way to change this without using DISTINCT? I've discovered and would
rather not use it if not necessary!
SELECT qCurrentPrice1.ListID, qCurrentPrice1.CurrentPrice, qCurrentPrice1.Dat
FROM qCurrentPrice1
WHERE (((qCurrentPrice1.Dat) In (SELECT TOP 1 Dat
FROM qCurrentPrice1 As Dupe
WHERE qCurrentPrice1.ListID = Dupe.ListID And (Dupe.CurrentPrice Is Not Null)
ORDER BY Dupe.ListID, Dupe.Dat DESC) Or (qCurrentPrice1.Dat) Is Null));
[Non-text portions of this message have been removed]
Tidak ada komentar:
Posting Komentar