ACCESS FEED: Re: [MS_AccessPros] SQL find Max or most recent dates

Jumat, 07 Agustus 2015

Re: [MS_AccessPros] SQL find Max or most recent dates

Phucon-

You can do it in one go, but it might be slow:

SELECT ProdID, Category, OrderDate

FROM tblProdDates

WHERE OrderDate =

(SELECT Max(OrderDate)

FROM tblProdDates As A

WHERE A.ProdID = tblProdDates.ProdID)

OR OrderDate =

(SELECT Max(OrderDate)

FROM tblProdDates As B

WHERE B.ProdID = tblProdDates.ProdID

AND B.OrderDate <

(SELECT Max(OrderDate)

FROM tblProdDates As C

WHERE C.ProdID = tblProdDates.ProdID))

The output of this query should also be updatable where your UNION query will not be.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Aug 7, 2015, at 5:32 PM, saigonf7q5@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

My table contains the data as shown below.

ProdID Category OrderDate
1 Baked Goods & Mixes 4/5/2006
1 Baked Goods & Mixes 3/24/2006
1 Baked Goods & Mixes 2/6/2006
2 Beverages 6/7/2006
2 Beverages 4/25/2006
2 Beverages 4/8/2006
3 Candy 6/8/2006
3 Candy 6/5/2006
3 Candy 4/3/2006
4 Canned Fruit & Vegetables 6/5/2006
5 Dairy Products 4/30/2006
5 Dairy Products 4/5/2006
6 Grains 4/22/2006
7 Jams, Preserves 6/5/2006
7 Jams, Preserves 4/22/2006
7 Jams, Preserves 3/24/2006
8 Oil 4/5/2006

My task, the final output is to pull out the most recent (order date) record, and the record which immediate below the most recent order date (as shown).

ProdID Category OrderDate
1 Baked Goods & Mixes 4/5/2006
1 Baked Goods & Mixes 3/24/2006
2 Beverages 6/7/2006
2 Beverages 4/25/2006
3 Candy 6/8/2006
3 Candy 6/5/2006
4 Canned Fruit & Vegetables 6/5/2006
5 Dairy Products 4/30/2006
5 Dairy Products 4/5/2006
6 Grains 4/22/2006
7 Jams, Preserves 6/5/2006
7 Jams, Preserves 4/22/2006
8 Oil 4/5/2006

I first, use this SQL to pulled the Max Order date.

SELECT a.ProdID, a.Category, Max(a.OrderDate) AS orderDate
FROM tblProdDates AS a
GROUP BY a.ProdID, a.Category
ORDER BY a.Category;

Next, I pulled the 2nd-MAX orderdate (the record which right below the MAX orderdate).

SELECT a.ProdID, a.Category, Max(a.OrderDate) AS OrderDate
FROM tblProdDates AS a INNER JOIN tblProdDates AS b ON a.ProdID = b.ProdID
WHERE (((a.OrderDate)<[b].[OrderDate]))
GROUP BY a.ProdID, a.Category, b.ProdID, b.Category
ORDER BY a.Category;

I then Union the MAX and the 2nd-MAX to show all filtering records and sorted them.

SELECT a.* FROM QryPulltheMaxTEST AS a
UNION SELECT b.* FROM QryPullthe2ndMaxTEST AS b
ORDER BY Category, Orderdate DESC;

I have been trying to learn a better or efficient coding. I've tried Sub Query, that works too. But wondering if it can be done without the UNION? By joining the tables perhaps?

Phucon

__._,_.___

Posted by: John Viescas <johnv@msn.com>

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (2)