Freezing values

  • Thread starter Thread starter Rose
  • Start date Start date
R

Rose

I'm putting together a db for a retailer. The retailer
sometimes has sales, or sometimes a product will change in
price. Standard stuff.
What I need to do is to be able to "freeze" invoices, so
that the values in an invoice do not change should the
product prices change. In Excel, I would copy the results
and paste special somewhere else using the only the values
and not the formulas. Can I do something similar in
Access - set up a table for my line items that is a static
copy of my product prices (and the calculations based on
them)?
 
This is one of the few cases where storing a calculated value is the correct
thing to do. You look up the price of an item from your prices table at the
time the invoice is created, possibly multiply by the number of items, then
save that value to the invoice detail table. Then the value is fixed, and
no longer depends on subsequent changes to the price table.

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control
 
Right. I'm just unclear on the how part.
I have a Sales table that includes the customer's name and
such, primarily drawing off my Customers table, plus the
invoice number. I have a LineItems table that draws
product info off my Inventory table. And then there's
queries to do the calculations. All these things create my
invoice. But they draw off of tables that could change.
The values they calculate are being stored, but they get
their original values from tables that could change -
ergo, if I change the Customers or Inventory, Sales or
LineItems is going to change because it's still calling
those original fields. I need the values to be stored as
values, not as calculations.
I must be missing something elementary, but you know how
it gets when you're knee-deep in design. :)
 
When you say you are storing the values the invoice calculates - presumably
in the invoice details table, isnt this what you want ?

To make it a bit clearer, maybe this helps:

On your invoice details subform, you have a query pulling the item
description, partnumber etc and cost from the item table, so the textboxes
are bound to the query fields. Then you probably have a quantity textbox,
filled in by the user, maybe bound to a field in the invoice details table
( depends whether you need to keep track of quantity as a separate issue ).
And you have another textbox, field calculated by the query, computing price
* quantity. Access wont let you bind this to a table, for very good
reasons. But you can create another textbox ( probably invisible ), and
bind it to a field in the invoice details table. Then in the subform before
insert event, set this textbox to the textbox calculated value, so that
whenever the calculated fields have new entries ( but *only* via this
form ), the new value gets placed in the invoice details table. Its a moot
point whether you save the calculated value of price * quantity, rather than
just the price at the time the record was created. Purists would argue that
you should just save the unit price, and the quantity, since you can always
calculate the extension, but the point is to do it in the correct form
event, so it gets saved only when the record is created.

For 'informal' pricing, where maybe you want to give some ad-hoc discount,
its often better to actually show the extended value, and allow ( and trust
! ) the user to adjust the value manually if necessary.

Hope this helps.

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control
 
Back
Top