How do I change a field value($) in Access without changing previo

[Guest]

I want to change the value($) of a field relating to a product without
changing it across the entire database. I need to look back over order
details and have the correct price for the time when the order was made. I'm
sure this is a design fault on my part, but I can't figure out how to do
it!!!! If anyone can help you'll make me VERY happy!! If you need more
details please let me know. Thanks!!

 

[Rick Brandt]

I want to change the value($) of a field relating to a product without
changing it across the entire database. I need to look back over order
details and have the correct price for the time when the order was
made. I'm sure this is a design fault on my part, but I can't figure
out how to do it!!!! If anyone can help you'll make me VERY happy!!
If you need more details please let me know. Thanks!!

For time sensitive data like price you should be storing that as part of the
order rather than just looking it up from the products table. The
alternative is to keep a history of price changes so you can use a lookup
that is also keyed to when the order was placed, but in my opinion that is
an overly complicated method to use.

 

[Guest]

Thanks Rick, I think a lightbulb just lit above my head!! If I have
"unitprice" in the "orderdetails" table the link to products wont be there so
it wont change, but will be unique for that order...Yes?? Just checking as
I'm a bit of a learner with Access!! Thanks again, will try and let you know
how I go :>

 

[Arvi Laanemets]

Hi

There are 2 ways for this.

a) Design your database so, that the price is saved for every record. I.e.
for every transaction, along with product code there is the price stored in
Transactions table. You can have current prices in Products table, but you
can insert the price from there automatically only when you insert new
transaction.

b) Prices for all products are stored in separate Prices table - along with
validity period (from which date to which date). In Transactions table you
have transaction date and product code - but no price. On Transactions
form/report the price is calculated from Prices table using DLookUp(), with
date and product code as filtering parameters. As accidental change of some
price from past affects all entries for this time interval and product, you
probably have to think about locking closed prices (the ones with 'to which
date' field not empty).

 

[Rick Brandt]

Thanks Rick, I think a lightbulb just lit above my head!! If I have
"unitprice" in the "orderdetails" table the link to products wont be
there so it wont change, but will be unique for that order...Yes??
Just checking as I'm a bit of a learner with Access!! Thanks again,
will try and let you know how I go :>

Yes. There are two (off the top of my head) reasons to *copy* data from a
lookup into the related record being created as opposed to just looking it
up which is the usual and better practice.

1) Time sensitive data such as your unitprice. Since this can change in
the lookup table and you need a historical record of the price at the time
the order was processed.

2) When the lookup is used as a default value that can optionally be
overridden. A good example of this would be a ship-to address. A Customers
table might include a ship-to address as part of the lookup data, but you
might have some orders where the "normal" ship-to address is going to be
overridden with a different one. So in that case one would also copy the
data to the order instead of merley linking to the data in the lookup table.

 

[Guest]

Arvi,

So does that mean I can lock the price within each order ID (or
transaction)? If so how? That would save me having to recreate a new
database!! I'm not too good at this stuff!! It's a prety basic database:
Customers, Order ID, Order Details, Products Put it all together and it
should be easy??!!

 

[Arvi Laanemets]

Hi

Arvi,

So does that mean I can lock the price within each order ID (or
transaction)? If so how? That would save me having to recreate a new

You can't do this in table, but it is possible when you restrict the user to
enter/edit data through forms only. P.e. in form's Current event you can
enable/disable form controls at your choice - the event occurs every time
when another record is selected.

When you have prices with validity periods in separate table, then on
Transactions form (and on reports) you display it in calculated control -
you disable it for user when designing the form, and the user never gets
direct access to it.

You have to control the access to closed prices in Prices form, where the
user enters new prices or edits existing ones. Into form's current event
write something like:
....
Me!Price.Enabled=LogicalExpression
....

, where LogicalExpression returns True, when the control Price is empty and
the control ValidTo is empty and the control ValidFrom is not empty.