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

tbl_salesman needs to have the salesmanNo attribute too that way the tables have a like attribute to join on. That goes for the other tables, as well.

What's bolSold do?

If I'm understanding correctly, tbl_Commision doesn't make sense as a table, but rather a function or handled at application level with a couple special queries.

This should give the maximum price of a car sold by each salesman if the salesmanNo was included in tbl_salesman.

SELECT salesman.name, MAX(tbl_CarForSale.carprice) FROM tbl_salesman JOIN tbl_CarsForSale GROUP BY salesman.name ORDER BY MAX(tbl_CarForSale.carprice);

You could add a WHERE clause at the end of that to specify greater than and less than constraints or you can create a function and replace the MAX() function in example above with newly created one.

It also might make sense to normalize a bit. I don't know your exact requirements, but I would consider something like this:

car: registration, carPrice, bolSold
salesman: salesmanNo, name
sold_record: date, salesmanNo, registration REFERENCES car.registration,salesmanNo REFERENCES salesman.salesmanNo

Answer 2

Something like this should work.

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