Query Problem (Access 97)

Topic

New Reply

I’m sure the answer to this is easy, but like all problems when you don’t know the answer…..

OK. Two tables: tblOrders, primary key = .Order_ID, and tblOrderNotes, primary key = .Note_ID.

Tables linked by tblOrders.Order_ID —— tblOrderNotes.Order_ID

I’m sure you’ve gathered by now that one table holds all the orders, and the other allows users to add multiple notes regarding the progress of any/each order.

I have a form driven by a query that displays the order. I want to display the latest note for that order. The notes table has a date/time stamp field, but i cant work out how to write the query. I am sure it involves an aggregate function along the way, but half of me is thinking i’m going to have to have two queries, one to work out which is the most recent note for any given order number, and then use that as a recordsouce for the second query behind the form….

All comments appreciated…

Reply | Quote

Replies

Peter: Not knowing all the intricacies of your situation, a starting point might be to use th MAX function on the date/time field. This will give you the latest date. Or you could sort all your dates in descending order to give you the latest first. HTH.

Reply | Quote

Thanks for your reply BC. I was hoping i had made myself clear, but perhaps i have not. Basically, what i want is to run a query linking two tables together, showing each row from one table (tblOrders), and the row with the highest [date] in the other (tblOrder_Notes)

Reply | Quote

Peter, I did this in a form. On the date field, in Properties on the Data Tab, in the Order By box, I placed the name of the field with DESC at the end of the field name.

Example: dteLastUpdate DESC

This worked for me.

Reply | Quote

You could try using a table joined to a query on the second table. The second query would be a totals query along these lines:

SELECT Orders.CustomerID, Max(Orders.OrderDate) AS MaxOfOrderDate
FROM Orders
GROUP BY Orders.CustomerID
ORDER BY Orders.CustomerID;

That would give you the maximum order date for each customer, for example, and you could link it to another table through the customerID.

Reply | Quote