ACCESS FEED: [MS_AccessPros] Re: duplicates not seen but found by "find duplicates" query

Minggu, 01 Januari 2012

[MS_AccessPros] Re: duplicates not seen but found by "find duplicates" query

Hi Connie,

The reason for not finding the duplicates is probably
because you are restricting the records to
'SELECT TOP 1 MarketValueID'.

Regards, Clive.

--- In MS_Access_Professionals@yahoogroups.com, mrsgoudge <no_reply@...> wrote:
>
> I'm trying to get this version wrapped before Tuesday so am working from home.
>
> One of my queries is coming up with a duplicate entry that I think I could get rid of by using "distinct" but I want to make sure it's not a symptom of an issue that will haunt me in the future.
>
> I finally figured out that if I got rid of qMarketValueSales from the FROM portion, the duplicate went away. When I check qMarketValueSales, sure enough two duplicate records show up for that record.
>
> What is weird, is that the find duplicates query finds the extra record for qMarketValueSales, but when I go open qMarketValueSales and sort on HomeInfoID I do not find a duplicate entry.????
>
> The sql for qMarketValueSales is below.
>
> Thanks much!
> Connie
>
> SELECT MarketValue.MarketValueID, MarketValue.HomeInfoID, MarketValue.MarketValue, MarketValue.MVYear
> FROM (Listings LEFT JOIN MarketValue ON Listings.HomeInfoID = MarketValue.HomeInfoID) RIGHT JOIN Sales ON Listings.ListID = Sales.ListID
> WHERE (((MarketValue.MarketValueID) In (SELECT TOP 1 MarketValueID
> FROM MarketValue AS Dupe
> WHERE Dupe.HomeInfoID = MarketValue.HomeInfoID
> ORDER BY Dupe.MVYear DESC, Dupe.MarketValueID DESC)) AND ((MarketValue.MVYear)=Year([Sales].[SoldDate])));
>