I see, you're looking for a query that will return a record for each part number. I have some questions about your sample data. In [Query A] can there be more than one record with the same [Part Number] and [Qty] combination? If so do you want to return all matching records or may just the one with the highest or lowest unit price? Can you also provide the data behind [Query A] comes from? Finally, I think you must have a typo in your desired results for the ABC row..?
There may be a better way to do this:
Code:
SELECT subquery.[part number],
[query a].qty,
[query a].[unit price]
FROM (SELECT [query a].[part number],
Max([query a].qty) AS MaxOfQty
FROM [table a]
INNER JOIN [query a]
ON [table a].[part number] = [query a].[part number]
WHERE (( ( [query a].qty ) <= [table a].[total qty need] ))
GROUP BY [query a].[part number]) AS subquery
INNER JOIN [query a]
ON ( subquery.[part number] = [query a].[part number] )
AND ( subquery.maxofqty = [query a].qty );