Query Help - Group By and Where 1

[AnonGod]

TIA for the help

Having trouble getting my head around how this query should be built. I can do it in vba querying all the contracts, then querying all the latest dates seeing if OpenQty > 0, but I'd really like to have a single query that can return all of it on one shot.

Table data:

[b][u]Date      Contract  OpenQty   OpenPrice [/u][/b]
1/2/2006  8/1/2008  5         1.00
1/3/2006  8/1/2008  6         2.00
1/4/2006  9/1/2008  7         3.00
1/5/2006  9/1/2008  0         0.00
1/6/2006  10/1/2008 9         5.00
1/7/2006  10/1/2008 10        6.00

What I'm hoping to end up with is a query that will return all the contracts that have OpenQty > 0 for the last available date for that contract.

Wanted query output:

[b][u]Contract  OpenQty   OpenPrice [/u][/b]
8/1/2008  6         2.00
10/1/2008 10        6.00

(note contract 9/1/2008 is not in the output as the last day that contract was used, OpenQty was 0)


Using Access 2003.

If I left out any info, let me know. Thanks again!
-Andrew

[Signature modified by admin request]
-TAG
anongod@hotmail.com
'Drawing on my fine command of language, I said nothing.'

 

[lespaul]

Something like:

SELECT Contract, OpenQty, OpenPrice
FROM
TableName
INNER JOIN (Contract, Max(Date) FROM TableName WHERE OpenQty > 0) B ON TableName.Contract = B.Contract



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[AnonGod]

Excellent - thanks for the code - here's my final code - Works so far...

You rock.

-Andrew

SELECT Main.Contract, Main.OpenQty, Main.OpenPrice
FROM Main
INNER JOIN
(SELECT Main.Contract, Max(Main.Date) AS MaxDate FROM Main GROUP BY Main.Contract) B
ON
B.[MaxDate] = Main.Date
WHERE OpenQty <> 0;

[Signature modified by admin request]
-TAG
anongod@hotmail.com
'Drawing on my fine command of language, I said nothing.'

 

[AnonGod]

Had to modify it just a bit - was pulling duplicate contracts.

Updated code:

SELECT Main.Contract, Main.Date AS LastDateUsed, Main.OpenQty, Main.OpenPrice
FROM Main
INNER JOIN
(SELECT Main.Contract, Max(Main.Date) AS MaxDate
FROM Main
GROUP BY Main.Contract) B
ON B.MaxDate = Main.Date AND B.Contract = Main.Contract
WHERE Main.OpenQty <> 0;

[Signature modified by admin request]
-TAG
anongod@hotmail.com
'Drawing on my fine command of language, I said nothing.'