CODEWITHSUNDEEP
ms-access
user1200540
user1200540

Reputation:

Access Query Selecting only items with a max of a field below a certain number

Ok so... I have this query :

SELECT * from Orders o
INNER JOIN OrderStatus os
ON o.Serial = os.Serial 
WHERE ( 
Select Max(StatusCode) FROM OrderStatus Where Serial = o.Serial  
) < 14;

I think this says select everything from both tables where the highest status code is less than 14

But it just selects everything (Every order has at least 1 status code associated with it below 14)

I also tried this :

SELECT * from Orders o
INNER JOIN OrderStatus os
ON o.Serial = os.Serial 
WHERE  Max(os.StatusCode) < 14;

But you can't use aggregate functions in access so it obv just errored.

Is there a way of filtering an access query so that it will only return rows where the highest status code of an order is below a specified number ?

Upvotes: 1

Views: 357

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123549

I think you're looking for something like this:

SELECT * 
FROM 
    Orders o
    INNER JOIN 
    OrderStatus os
        ON o.Serial = os.Serial 
WHERE o.Serial IN
    (
        SELECT Serial 
        FROM OrderStatus 
        GROUP BY Serial 
        HAVING MAX(StatusCode)<14
    )

Upvotes: 1

Related Questions