Access? How do I find the most current price date of an item?

[Guest]

I have items that have several price updates and I need the current price of
the item.

 

[Ken Snell \(MVP\)]

Very difficult to make suggestions until you tell us about your data / table
structure. Where / how are price data stored?

Generic answer:

SELECT TableName.PartID, TableName.PartPrice
FROM TableName
WHERE PartPriceDate =
(SELECT Max(T.PartPriceDate) AS MP
FROM TableName AS T
WHERE T.PartID = TableName.PartID);

 

[Guest]

If you take a look at the sample Northwind database which comes with Access
you'll see that the Products table has a UnitPrice column in which the
current price of each product is stored. The Order Details table also has a
UnitPrice column in which the price of each product at the time of each order
is stored.

The current price is inserted into a row in the Order Details table when a
new row is added to that table via the Orders Subform by means of the
following code in the AfterUpdate event procedure of the OrderID combo box:

On Error GoTo Err_ProductID_AfterUpdate

Dim strFilter As String

' Evaluate filter before it's passed to DLookup function.
strFilter = "ProductID = " & Me!ProductID

' Look up product's unit price and assign it to UnitPrice control.
Me!UnitPrice = DLookup("UnitPrice", "Products", strFilter)

Exit_ProductID_AfterUpdate:
Exit Sub

Err_ProductID_AfterUpdate:
MsgBox Err.Description
Resume Exit_ProductID_AfterUpdate

Consequently when the unit price of a product is changed its simply a matter
of changing, via the Products form, the value of the UnitPrice column in the
relevant row of the Products table. Any new orders will then use this price,
but all previous orders will retain the price in force at the time of the
order.

I'd imagine you might want to do something analogous to this.

Ken Sheridan
Stafford, England

 

[John W. Vinson]

On Thu, 26 Apr 2007 08:56:01 -0700, Patsy H <Patsy

I have items that have several price updates and I need the current price of
the item.

Do a Query searching for the maximum date of the date-updated field.

The fact that you didn't post any description of your table makes it hard to
give a more specific answer.

John W. Vinson [MVP]