can't find the right query

[Paolo]

Hi everybody. First of all I'm not very good in SQL... and I've been working
on this query for a while without finding a solution.

I have 3 tables.

Table 1: SALES - fields: product_id, sale_date, QT, etc...

Table 2: ORDER_ROWS - fields: product_id, order_id, product_cost, etc...

Tabel 3: ORDERS_HEAD - fields: order_id, loading_date, etc...

I need to obtain the cost of a product (product_cost), with the earlier
purchase date (loading_date), possibly right before the selling date
(sale_date).

If I run the following query:

SELECT SALES.product_id, SALES.sale_date, SALES.QT
FROM SALES, ORDER_ROWS, ORDERS_HEAD,
(SELECT r.product_id, max(t.loading_date) AS maxdate FROM ORDER_ROWS AS R,
ORDERS_HEAD AS T WHERE R.order_id=T.order_id And r.qt>0 GROUP BY
r.product_id) AS tabmaxdate
WHERE SALES.product_id=ORDER_ROWS.product_id And
ORDER_ROWS.product_id=tabmaxdate.product_id And
ORDERS_HEAD.loading_date=tabmaxdate.maxdate
GROUP BY SALES.product_id, SALES.sale_date, SALES.QT;

I get a list of all products with all the relative earlier purchase date.
But as soon as I include in the main SELECT the field product_cost, since
there are many purchases of the same product and with different prices, my
records become much and much more.

I know there are "nicer" ways of writing an INNER JOIN in access, with the
ON statement instead of WHERE, but I tried both kinds without appreciatable
results.

Could somebody help me understanding where I'm wrong and possibly lead me to
a solution?

Thanks in advance.

Paolo