subQuery problem

[orangeseatbelt]

not even sure if this is a subquery but I can't even come close to finding another way of solving it.

Table of orders
=====================================
order cust order order
num code date cost
=====================================
456 cust1 1/3/2011 99
234 cust1 4/4/2010 55
780 cust1 2/2/2010 12
239 cust2 1/3/2011 77
894 cust3 1/3/2011 88
558 cust3 6/6/2010 50

What I'm trying to do is to pull orders from a particular data range (assume 1/3/2011) for now and then retrieve the order_cost for the MOST RECENT order.

So for the above table it should show
456 cust1 1/3/2011 99 55
239 cust2 1/3/2011 77 0
894 cust3 1/3/2011 88 50


Can I even do this through a sub-query? I was hoping to use the subquery as an expression - but I can't seem to get Access to accept the date from the first query in the subquery. Obviously I don't want to put in 1/3/2011 in the subquery because the date will change.

thanks

 

[dhookom]

Assuming you have a text box on a form for you users to enter the date, you can try something like:

SELECT OrderNum, CustCode, OrderDate, OrderCost,
(SELECT TOP 1 OrderCost FROM Orders O WHERE o.CustCode = Orders.CustCode AND OrderDate <=Date() ORDER BY OrderDate DESC) as RecentCost
FROM Orders
WHERE OrderDate = Forms!frmDateEntry!txtDate

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

Looks to me that to get the 1/3/2011 from first query, you need to do:

[blue]SELECT MAX([order date]) FROM [Table of orders][/blue]

(I wish people would not use spaces in table names or field names....)
and your SQL would look like:

SELECT * FROM [Table of orders]
WHERE [order date] = [[blue]SELECT MAX([order date]) FROM [Table of orders][/blue])

Have fun.

---- Andy

 

[orangeseatbelt]

Thanks for all your help - it's working.

if I wanted to pull another piece of info for the most recent order - say orderNum as well as orderCost - do I really need another, separate subquery for that?


Finding this SQL such a headache. Can't help but feel it would be so much quicker to knock up some code, open up a recordset and use .movenext to check each record in turn... only kidding, have to get to grips with SQL.

thanks