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

Minggu, 01 Januari 2012

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

Connie-

I bet if you run this query:

SELECT MarketValue.HomeInfoID, MarketValue.MarketValueID, MarketValue.MVYear
FROM MarketValue
GROUP BY MarketValue.HomeInfoID, MarketValue.MarketValueID, MarketValue.MVYear
HAVING Count(*) > 1
ORDER BY MarketValue.MVYear DESC, MarketValue.MarketValueID DESC

... you'll find duplicate "top" records.

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/
(Waco, Texas <yee haw>)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of mrsgoudge
Sent: Saturday, December 31, 2011 6:05 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [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])));

------------------------------------

Yahoo! Groups Links