Current Status

I have a table of orders, called "tblOrder".

That's not important right now, because this week's tip is about another table I have, called "tblOrderStatus". The structure of this second table follows:

Values for the "sStatusCode" field are:

There will be 3 records in tblOrderStatus for each completed order:

If the goods were lost in transit, then the first two records would be as above, and the third record would have sStatusCode = "L".

I can find the date and time when an order's status was last updated, using the following query as a template:

SELECT MAX(wDate) FROM tblOrderStatus
WHERE lOrderID = 23

That's not very useful, because I would still need to examine the corresponding record in tblOrderStatus to find out the order's current status.

That can be accomplished in a single step, using a subquery:

SELECT lOrderStatusID, wDate, sStatusCode
FROM tblOrderStatus
WHERE lOrderID = 23
AND wDate = (SELECT MAX(wDate) FROM tblOrderStatus
WHERE lOrderID = 23)

It is possible to go a step further, and retrieve the current status of all orders in the system:

SELECT lOrderStatusID, lOrderID, wDate, sStatusCode
FROM tblOrderStatus AS t1
WHERE wDate = (SELECT MAX(wDate) FROM tblOrderStatus AS t2
WHERE t2.lOrderID = t1.lOrderID)

To retrieve all the orders which have been received, but which have not yet been dispatched, requires only a minor modification:

SELECT lOrderStatusID, lOrderID, wDate, sStatusCode
FROM tblOrderStatus AS t1
WHERE wDate = (SELECT MAX(wDate) FROM tblOrderStatus AS t2
WHERE t2.lOrderID = t1.lOrderID)
AND sStatusCode='R'

This approach assumes that no two records could be entered with exactly the same date and time, (which is why date and time must both be used, and not just the date).

Adelle.