How to return overall status based on the line status

[illusionek]

Hi Guys,

I wonder if someone could help me with my problem.

I have a table with order number, products and their stock status. So I may have some orders, where there is enough stock for some lines but not enough for others.

So based on the line status I would like to create an additional column that would give me an overall status of the order based on the status of each line.

So for example, if I can fulfil all lines, I would like to give it a status ‘enough stock’ if I cannot any line, the status would be ‘not enough stock’. So far everything is easy but my problem is that if I have an order with some lines I can fulfil but also some that I cannot, I would like to give it a status ‘not enough stock’.

I have this impression that solution is really easy but I just can’t find it. I tried group by but then still I get two lines for each order, I was thinking about using IIf but then I get information again at the line level.

I have attached an example of table and in the last column there is a status I would like to achieve.

I would be grateful for any help.


Many thanks!!

 

[plog]

I'm a little confused as to what type of object that data you posted is from. Is it an actual table, or is it a query?

This query will provide you the status for every order:

SELECT OrderNumber, MAX([SKU Status]) AS OrderStatus
From YourTableNameHere
GROUP BY OrderNumber

I used YourTableNameHere as the name of the table--if it's a query, it will work for that as well.

 

[illusionek]

So simple, so neat!

Many thanks, this is exactly what I needed.

Could you just please explain for my benefit why 'Not enough stock' is greater than 'Enough stock'? Is it because of the alphabetic order?

 

[plog]

Yes it is because of alphabetic order. If you get a third status, then you have to get into a more complex query and possibly a table to order your stati.