Select statement within select statement SQL and access

Question

I am trying to select the maximum price of a product that has been sold within the database, I have three tables (tbl_CarForSale, tbl_Commision and tbl_Salesman).

What I want to do is select the Maximum price of a Car that has been sold, and then, using that record, be able to calculate the commision earned, and link it to the salesman. I have the following and it returns the Maximum car price, but I don't know how to directly use the returned record to get the other information possible.

SELECT Max(tbl_CarForSale.carprice)
FROM (SELECT tbl_CarForSale.*, tbl_Salesman.*,  from tbl_CarForSale, 
WHERE bolSold = true)

I am guessing that I would need to put this into another SELECT statement and nest it, but I am not sure how to do this.

Edit:

As requested the field names are here.

tbl_salesman: name
tbl_CarForSale: registration, soldDatem salesmanNo, carPrice, bolSold
tbl_Commision: minValue, maxValue, comAvliable

Basically, the idea behind the Commission table is that a salesman earns commission based on the price of the car, and therefore I have a query that looks at the car price and will return how much commission the salesman has earned

Answer 1

Something like this should work.

select your fields
from your tables
where bolSold = true
and tbl_CarForSale.carprice = (select max(carprice) from tbl_carForSale)