How to auto-populate a field based on a query on another table

[lissy_vincent@yahoo.com]

Hi,


I am using access 2007.


I am using the following tables:


1) PriceMaster with fields
-PriceDate
-ProductCode
-UOM
-UnitPrice.
The PK is a combination of PriceDate, ProductCode & UOM.


2) Sales with fields
-InvoiceNo (PK)
-SaleDate
-ProductCode
-UOM
-UnitPrice
-Quantity -Amount






I have a form based on the Sales table. When the user enters the SaleDate, ProductCode and the UOM, I want to query the PriceMaster table and find the record wherein


the SaleDate <= PriceDate and Sales.ProductCode = PriceMaster.ProductCode and Sales.UOM = PriceMaster.UOM.


I am struggling to get this done.


Would appreciate if someone can throw some light on how to achieve this.


Thanks in advance,
Lissy

[pbaldy]

Along the lines of:

SELECT TOP 1...
FROM...
WHERE...
ORDER BY PriceDate DESC

[lissy_vincent@yahoo.com]

Hi, Could you please explain a little bit in detail?

Along the lines of:

SELECT TOP 1...
FROM...
WHERE...
ORDER BY PriceDate DESC