In your Pricing table the natural key is presumably (inventoryitemid,
effectivedate). The following query assumes that (inventoryitemid,
effectivedate) is unique, so you should declare a unique constraint for it.

SELECT I.*, P.*
FROM tblInventoryItem AS I
JOIN
(SELECT inventoryitemid, MAX(effectivedate) AS effectivedate
FROM tblPricing
GROUP BY inventoryitemid) AS M
ON I.inventoryitemid = M.inventoryitemid
JOIN tblPricing AS P
ON P.inventoryitemid = M.inventoryitemid
AND P.effectivedate = M.effectivedate

(untested)

--
David Portas
------------
Please reply only to the newsgroup
--