"locking" forms?

[Guest_imported]

We use Access 97 as our order processing systyem. When we recieve an order we call up the part numbers, prices, etc. and print out a work order, packing slip, and invoice. The problem comes in when I modify a price or part specification (for example) in the database. This of course changes the pricing on all previous orders of that part number, rendering the previous orders innaccurate. Is there any way to "lock" a record once completed, so that it wouldn't be changed, yet still allow the revised price to be used for new orders? My computer guy says no, but I am wondering if that just means he doesn't know how. I hope I have explained this well. Thanks very much in advance.

Brian

 

[JaneInMA]

I would imagine this could be done two ways. One would be create a historical table of past orders, where the price at time of order is stored in a field allowing it to be static, while the current price of an item can be changed daily if necessary allowing new orders to be created with the price at date of order being calculated and stored as necessary. Not a true relational solution but works.

Or you create subtable of part number, price and start and finish of dates when it is that price. You then program the invoice to find the time period when the order was generated and use the correct price for that time.

Think about these possible solutions.

 

[RickSpr]

Most of the time, the trick to getting a good relational database design is eliminating redundant information, using a process called "normalization". When you eliminate the redundancy, you maximize efficiency and minimize errors.

Unfortunately, in this case you have eliminated too much apparent redundancy. Your system ties each product on each order with the current price. That's fine when you're creating an order, but once the prices are locked in they shouldn't change; the order document now becomes historical fact, not current data.

In this case - when data changes from current to historical usage - you should have the price redundantly stored. In the historical order it represents the price at some point in the past. In the pricing table it represents the current price for new orders.

Now, you can't "lock" a record the way you're talking about. I think you don't understand that your "record" isn't a single record at all, but is built up from data in several tables, at the time you want to look at it. One of those tables is the current price, and you certainly don't want to lock that from being changed.

But certainly it is possible to design the tables and the application to make it possible to store the historical price. The only trouble is, it may not be practical to do so. It might require redesigning your order processing system to a large degree, or it might require having source code that whoever wrote your system has locked up out of your reach. So I wouldn't accuse your computer guy of lying about it, or being ignorant. He may just be telling you that you can't get there from here. Rick Sprague

 

[JaneInMA]

Rick, perhaps I am being stupid but for the database to not allow a order to be accurately printed unless it is printed on the date processed seems like a bad design. I know we are talking Access here but it seems to me this db has a huge problem in design that the computer guy should be tackling immediately.

Of course the information may be stored in another db without this problem but if I was looking after a db and saw this problem I would be redesigning it asap.
Does this mean I am an over fickle db person? Please feel free to explain this to me in email but the concept of not tackling this problem seems incomprehensible to me.
I am just confused at your statement "may not be practical to do so" as it seems essential to the function of the db to fix this. I have only been designing dbs a year so obviously I still have a lot to learn and would really like to understand how this would be deemed a minor problem.
:-I
Jane
Jane.McConnell@genzyme.com