Selecting a unique row based on a qty value

[EckyThump]

I have a table with lots of parts with different quantities and their costs. I have created a query that extracts rows that have a qty nearest a target qty of 10:

    SELECT A.PartNo, A.Qty,A.Cost
    FROM TableName AS A INNER JOIN 
    (SELECT PartNo, Min(Abs(Qty - 10)) AS MinDiff FROM TableName GROUP BY PartNo)
    AS M ON A.PartNo = M.PartNo
    WHERE Abs(A.Qty-10) = M.MinDiff;

The problem is that if their is a part with a qty of 5 and the same part with a qty of 15 then both are displayed, and I am trying to achieve a unique entry for each part.
If I have an assumption that if two of the same parts with an upper and lower qty exist the upper qty should be used, how do I implement this?
Can I ammend the query, or do I need another query on the results of the above query?

Every bit of help is another step on my access education process!

 

[PHV]

A starting point:
SELECT A.PartNo, Max(A.Qty) AS QtyNear10
FROM TableName AS A INNER JOIN (
SELECT PartNo, Min(Abs(Qty-10)) AS MinDiff FROM TableName GROUP BY PartNo
) AS M ON A.PartNo = M.PartNo
WHERE Abs(A.Qty-10) = M.MinDiff
GROUP BY A.PartNo

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[EckyThump]

How did I miss the Group By?
I dont think this manages the fact that where there are two equidistant qtys I would like the ability to choose either the upper or lower value.
I thought perhaps a SORT BY or Where a.qty < m.qty
but not sure how to do this.

I will give your suggestion a try tomorrow though.

Thanks

 

[PHV]

I would like the ability to choose either the upper or lower value
So, the rules are changing ?
You said this: the upper qty should be used

 

[EckyThump]

Yes I would be happy taking the upper value (would the order by do that?), but I was wondering "what if" the lower qty was more appropriate, then what would be needed.
Sorry I tend to think that way!

 

[EckyThump]

OK
I have tried to get this working now for a few hours.
The only way I seem to be able to do this, is to create the query, then create a new query based on the first.

I was hoping to see how it could be done by one statement. SQL however is not my strongest subject and keep getting errors when trying to use a subquery.

If there is any solution, I would love to here how it is done. Even more interesting is the concept of subqueries in Access.

Any good resources for this?????