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

Sabtu, 31 Desember 2011

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

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])));