ACCESS FEED: Re: [MS_AccessPros] SELECT TOP 1 Returning 2 records if there are duplicate records

Senin, 02 Juli 2012

Re: [MS_AccessPros] SELECT TOP 1 Returning 2 records if there are duplicate records

thanks John,

I'll use DISTINCT unless I can control the users input. Hmmmm. Thanks! Connie

--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> 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]
>