Ordering data in Access

Q I am building an order database in Access. I have written a query to pull up the most recent order (the one with the highest order number) and then I base a report on the query.

But the query is very specific and has to be rewritten each time. Is there any way to make it more general?
SELECT ORDER.OrderID, ORDER.OrderDate, ORDER.OrderValue
FROM [ORDER]
WHERE (ORDER.OrderID)=6;
Brian Williams

A There are a couple of ways of doing this. For example, you can use a query of the general format:
SELECT ORDER.OrderID, ORDER.OrderDate, ORDER.OrderValue
FROM [ORDER]
WHERE ORDER.OrderID=(SELECT Max(ORDER.OrderID) FROM [ORDER]);

This should work in many different databases engines, although you need to check that they have a function called MAX and, if not, look for a substitute.

However, Access queries also have properties that you can set, one of which is ‘top Value’.

If you set this to the value 1, place the OderID field as the left-most one in the query and sort it in descending order, then the query should return just the top value for OrderID.

Like this story? Spread the news by clicking below:

 del.icio.us     Digg this     reddit!