Change Products Prices (1 Viewer)

mikekal

Member
Local time
Today, 06:19
Joined
Jun 24, 2017
Messages
114

A Way To Change Products Prices Table But Keep Pevious Priced Records.I have a table "Inventory Transactions" and a table "Inventory".I have in Inventory many products and a price for each one.The inventory Transactions when i give a price in product take that price.When im change it ,change all history.I want to change onle the new records in Inventory Transactions.​

 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:19
Joined
May 7, 2009
Messages
19,175
create Another table for the Price:

tblPricing (table)
ItemID (long) 'FK to inventory table
EffectiveDate (date) 'when this price is applicable
Price (Decimal) 'the price
 

jdraw

Super Moderator
Staff member
Local time
Today, 09:19
Joined
Jan 23, 2006
Messages
15,364
See this thread for related info.
 

LarryE

Active member
Local time
Today, 06:19
Joined
Aug 18, 2021
Messages
562
If you want a separate price for each transaction, then add a price field in the transactions table. Then enter a price for each transaction.
 

mikekal

Member
Local time
Today, 06:19
Joined
Jun 24, 2017
Messages
114
create Another table for the Price:

tblPricing (table)
ItemID (long) 'FK to inventory table
EffectiveDate (date) 'when this price is applicable
Price (Decimal) 'the price
I download your example but when im change the price do nothing.Holds the past price for the product
 

Attachments

  • Eventus.accdb
    672 KB · Views: 181

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:19
Joined
May 7, 2009
Messages
19,175
of course it will hold the last price of the item.
that was already a history and is past.
you can only change the current price.
 

mikekal

Member
Local time
Today, 06:19
Joined
Jun 24, 2017
Messages
114
of course it will hold the last price of the item.
that was already a history and is past.
you can only change the current price.
if i choose product c and change the price,in new event when im choosing the product ,the price is still the old
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:19
Joined
Feb 19, 2002
Messages
42,981
I always create my price table with effective and end dates. That way I can put in a price today that will not be effective until next week. When I add a new, future effective price, the program automatically "closes" the previous price (if there is one) by updating the end date to the day before the new effective date.

For the default to end date, I use a ridiculously future date such as 12/31/2100 because it is easier to query with both a start and end dates.

I also copy the price to the current record when I add an item to an order. I do this for two reasons:
1. It allows the price to be over ridden if necessary. Returns for example would have a negative price to balance the sales records.
2. It means I don't have trouble calculating prices for historic reporting. The price I need is always with the order detail.

Copying the price does NOT violate any normalization rules since the price is a function of date and the date it is dependent on is in the Order detail record.
 

Cronk

Registered User.
Local time
Tomorrow, 00:19
Joined
Jul 4, 2013
Messages
2,770
As your price table enables the of the price applicable to any order because you can determine the price applicable at any date, storing the price in the order table is redundant. Having noted that, I would have probably decided to store the price in the order table to facilitate reports.
 

GPGeorge

Grover Park George
Local time
Today, 06:19
Joined
Nov 25, 2004
Messages
1,776
As your price table enables the of the price applicable to any order because you can determine the price applicable at any date, storing the price in the order table is redundant. Having noted that, I would have probably decided to store the price in the order table to facilitate reports.
As Pat noted, "Price as Of" is not the same thing logically as "Price". In addition, there are multiple ways the actual price charged can differ from a list price anyway. Discounts for preferred customers, for example. You either have to account for that as a price different from list at that point, or a separate discount field. It's not exactly a violation of normalization, although it has a certain feel to it that makes you stop and think through it.
 

Cronk

Registered User.
Local time
Tomorrow, 00:19
Joined
Jul 4, 2013
Messages
2,770
Pat, if say the particular business has prices which are fixed each calendar year, but differ from year to year, then I cannot see why the price cannot be be determined by the date of the order from the pricing table (PriceID, Price, PriceYear) in any future report. Discounts and any other price adjustment would be stored in the order table as an offset to the price in the particular year of sale.

In your example where your pricing table comprises a series of prices with the start/end date range of applicability, the price of an order now or any time in the future can be found by searching for the order date that falls during the effectiveness range.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:19
Joined
Feb 19, 2002
Messages
42,981
if say the particular business has prices which are fixed each calendar year, but differ from year to year,
Call me jaded but when a client tells me something like that, my spidey senses crackle. This kind of statement belongs in the "there will never be more then 3 status dates" category. As soon as I commit to this kind of design, the client decides that inflation is high this year and he wants to increase the price in six months for new orders.

I practice defensive programming. When you have been developing applications as varied as I have and for as many years, you get a good sense for what business rules are actually cast in concrete and which are more flexible. I never build something inflexible like you are suggesting unless I get the client to sign a contract that he isn't going to change his mind - ever:).

The method I suggested might be more flexible than is needed today but it doesn't prevent certain "rules" from changing later. And it doesn't violate any normal forms.

In your example where your pricing table comprises a series of prices with the start/end date range of applicability, the price of an order now or any time in the future can be found by searching for the order date that falls during the effectiveness range.
That is true. However, how do you handle returns (negative values)? How do you handle non-standard pricing (something they type in instead of the book price)? How do you handle replacements (zero cost items)? All of those are handled by simply saving the sale price at the time of this particular sale. Your assumption is absolutely inviolate pricing - no flexibility at all.

You can provide the flexibility to do any of the things I suggested by following my design pattern. You don't need to actually implement them, but if they come up, you'll be a hero rather than a goat because you won't need to make schema changes and all that entails.
 
Last edited:

Cronk

Registered User.
Local time
Tomorrow, 00:19
Joined
Jul 4, 2013
Messages
2,770
I now wish I had not used the example of yearly prices as a date range related price. I did come back to your example of a pricing table which defined for any date range, the price applicable.

Negative returns can be handled by finding the price applicable to the order in the pricing table and taking the negative. I did write in my last post #15 that price adjustments such as discounts be included in the order table.

Lastly, you seem to think that I am advocating the approach of not storing the price in the order table. See the last sentence of my first post on this thread #11
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:19
Joined
Feb 19, 2002
Messages
42,981
Great. We are not disagreeing:) Saving the price in the order detail does not violate normal forms and gives advantages for reporting and other types of situations. Whereas always linking, eliminates or makes more difficult some common functionality.
 

Users who are viewing this thread

Top Bottom