Find max records where duplicates exist

[Matt]

Hello to anyone who can help,

I have a table of property sales which can contain duplicate
properties and sales (a particular sale (PropID, SalePrice, SaleDate)
can be duplicated, and multiple sales of different prices can appear
on one date for the same property)
I need to select for each property that has a sale, the most recent
sale where the SalePrice > 0. If the property has multiple sales on
the max(Date) then the one with the highest price should be selected.

At the moment I'm using 5 queries to select the required records, but
I think there must be a more efficient way.

Currently using:
qry_MV_Sales_1_Useful: (Removes sales with no price)

SELECT SaleId, PropID, SalePrice, SaleDate FROM tblSales WHERE
SalePrice>0


qry_MV_Sales_2_GroupDate: (Select most recent date of sale for each
prop)
SELECT PropID, Max(SaleDate) AS MaxOfDate
FROM qry_MV_Sales_1_Useful
GROUP BY PropID;


qry_MV_Sales_3_GroupDatePriceSelects highest price)
SELECT S1.PropID, S2.MaxOfDate, Max(S1.SalePrice) AS MaxOfSalePrice
FROM qry_MV_Sales_1_Useful AS S1 INNER JOIN qry_MV_Sales_2_GroupDate
as S2
ON (S1.SaleDate = S2.MaxOfDate) AND (S1.PropID = S2.PropID)
GROUP BY S1.PropID, S2.MaxOfDate;


qry_MV_Sales_4_GroupDatePriceID (Selects last records where sale
duplicated)
SELECT S1.PropID, Max(S1.SaleId) AS MaxOfSaleId1
FROM qry_MV_Sales_1_Useful AS S1 INNER JOIN
qry_MV_Sales_3_GroupDatePrice: as S3
ON (S1.SalePrice = S3.MaxOfSalePrice) AND (S1.PropID = S3.PropID) AND
(S1.SaleDate = S3.MaxOfDate)
GROUP BY S1.PropID;

qry_MV_Sales_5_ToUse
SELECT tblSales.*
FROM qry_MV_Sales_4_GroupDatePriceID AS S4 INNER JOIN tblSales ON
S4.MaxOfSaleId1 = tblSales.SaleId;


If anyone can offer a suggestio to streamline this process, I'd be
very appreciative.

Thanks,

Matt

 

[Allen Browne]

See:
Queries: Getting a related field from a GroupBy (total) query
at:
http://www.mvps.org/access/queries/qry0020.htm
for four ways to approach this.

 

[Matt]

Allen,

Thanks for the response. The link was helpful for my aim to combine
the 5 queries into one using sub queries. I gather that there is no
simpler way that I was missing.

On a related topic, when access is executing a query which is based on
other queries, I assume it effectively builds the full query (i.e to
use base tables only) before executing rather than evaluating each
subquery into a temporary table. If so, is there any way you know of
to get the SQL of the resulting query - whcih would simplify writing
nested queries much as the Query Design mode simplifies writing simple
SQL?

Thanks again,

Matt

 

[Allen Browne]

Matt, I don't think Access creates temporary query defs for subqueries.
Rather, it executes the subquery at reach row to return the result for that
row.

Perhaps I did not understand your question.

 

[Matt]

Allen,

Thanks, you have answered my question.

I've been playing around tonight with subqueries within queries
(managed to reduce my previous 5 queries to one), but I'm finding that
Access (XP) will often crash when I save the queries, and almost
always does so if I click Design mode with a nested query in SQL mode.

Have you experienced this before?

Thanks again,

Matt

 

[Allen Browne]

Yes. Access does crash with subqueries, and it is a growing problem.

Name AutoCorrect is one known cause of the crash, as documented in:
http://support.microsoft.com/?id=319491

However, the problem is much deeper than that. The query engine in Access
becomes unstable once you ask it to do anything beyond the basics. That is
true for all the 32 bit versions of Access: 95, 97, 2000, 2002, and 2003.
Access 2000 and later (JET 4) have more "features".

Now it can even get a *really* basic query like this wrong:
SELECT * FROM MyTable ORDER BY MyField DESC;
Details:
http://allenbrowne.com/bug-08.html

The bugs are not being fixed, so Access is gradually becoming more unusable.

 

[Matt]

Allen,

Good to know it's not just me (well actually, I suppose it just being
me would be much better than everyone having to live with known
serious bugs that MS is not fixing) ;-)

I know they're trying to push SQL Server as a better solution to
Access for many users, but surely Access will be around for a while
yet and these things should be fixed ??

Thanks again for your help,

Matt

 

[Allen Browne]

Agreed completely.

The basic query sort problem applies to decimal fields in SQL Server or any
back end, but AFAIK, Microsoft doesn't even think this is with a k.b.
article, let alone fixing it.