How do I choose the most current record from a database? For exam.

[Guest]

I need to select the most current sales price from a table that has multiple
sales prices for the same item depending on the effective date of the sales
price. For example XYZ Item was $1.50 as of 3/2/2001, $2.10 sa of 6/3/04 and
$2.30 as of 8/1/05. I want to bring back only $2.30 as of 8/1/05 as that is
the current sales price in the system.

 

[Guest]

Try this, to get the last record entered for specific Item

Select Top 1 Item, PriceDate, Price From TableName Where Item = "XYZ" Order
By
PriceDate Desc

Or, by using Dlookup
=Dlookup("Price","TableName","Item = 'XYZ' And PriceDate = #" &
DMax("PriceDate","TableName","Item = 'XYZ'") & "#")

 

[Guest]

Use two queries --

SELECT Pricing.Item, Pricing.PriceDate, Pricing.Price
FROM Pricing INNER JOIN [Last Pricing] ON (Pricing.PriceDate = [Last
Pricing].[Last Price]) AND (Pricing.Item = [Last Pricing].Item);

SELECT Pricing.Item, Pricing.PriceDate, Pricing.Price
FROM Pricing INNER JOIN [Last Pricing] ON (Pricing.PriceDate = [Last
Pricing].[Last Price]) AND (Pricing.Item = [Last Pricing].Item);