P
Patsy H
I have items that have several price updates and I need the current price of
the item.
the item.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
K
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);
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);
K
Ken Sheridan
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
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
J
John W. Vinson
On Thu, 26 Apr 2007 08:56:01 -0700, Patsy H <Patsy
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]
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]