How to show only 1 record in subquery

[evanscamman]

I have two tables in a one to many relationship - tblOrder and tblOrderStatus.

How can I create a query that returns each record in tblOrder and only the last matching record per order from tblOrderStatus?

I've tried using a subquery for tblOrderStatus - with a TOP of 1 -
but this only returns the very last record in the table - not the last for each order.

I've also tried the first and last aggregate functions, but they don't seem to do anything.

I wouldn't have thought that this could be so hard - any ideas?
I could do a dlookup in the query, but that is sooooo slow....

Thank you!
Evan

 

[Banana]

If you want a last record for an exact order the subquery SQL would be:

SELECT TOP 1 Status FROM tblOrderStatus WHERE OrderID = [lOrderID] ORDER BY StatusDate DESC;

TOP 1 by itself isn't enough, you need to have a WHERE criteria and a ORDER BY to ensure you get the last row for a given order ID.

HTH.

 

[HiTechCoach]

Evan,

Also Check out this:

Get Information Associated with the Last Entry From a Table

Hope it helps ...


EDIT: You got the Banana on your case ... the porsche used gravity (known to be about 9.81 m/s² )!

 

[evanscamman]

Banana - it's no problem to do as you said for 1 for one record by supplying the OrderID, but I need to see every orderID with the most recent status.

HiTechCoach - your example was just about exactly what I needed, but the only problem is I can only include two fields in the aggragate query -
Group by OrderID and Max(StatusDate). It won't let me add another field. If I can't include the 3rd fields - OrderStatus, I can't see how I can use this data.

Can you have 3 fields in an aggragate function like this?

 

[evanscamman]

I got something working after reading the link you posted again.

qryCurrentStatus is the aggregate query which only has the two fields.

Then in a new query i have tblOrders ----> qryCurrentStatus ----> tblStatus. I can grab the data from tblStatus that I couldn't put into the aggregate query.
Now the only problem is that the recordset is no longer updateable - even though I have the queries, subforms and forms set to: Dynaset (Inconsistent Updates).

Hmmm... - I wonder If i will have to revert to a dlookup() inside the query to do this. I know this is really slow.
Either that or write the store the "current" status in a field in tblOrders - and rewrite it everytime there is a status change.

Any thoughts?

Thanks!
Evan

 

[HiTechCoach]

This may also help:

Subquery basics

I NEVER use a dlookup in a query. I will create a User Defined Function that handle the lookup with a recordset before I would ever use a DLookup in a query.

Also I find that DLookup is even slower with Linked tables. AFAIK, Domain functions are designed for local tables not linked tables. I ALWAYS split every databases into a FE and BE which is another reason I avoid DLookup.