price list

[Guest]

I am working on a database to track inventories, sales, orders etc... but i
am facing a problem, my pricelist changes from times to times (different
prices for the same product - same reference). In fact the price change once
or twice a year so i need to enter these different pricelist in my database.
Problem? How to make it simple to choose the right pricelist? I was thinking
of putting a date as start date to use this or that pricelist.
Does anybody worked on something similar, what was the solution found (easy
to create, use)?

Thanks in advance for your help.

 

[Guest]

Take a look at the way the sample Northwind database handles this sort of
thing; the approach is pretty standard. The current UnitPrice for a product
is a column in the Products table, but each row in the OrderDetails table
also has a value in the UnitPrice column. In the AfterUpdate event procedure
of the ProductID control on the Orders Subform the current unit price is
looked up from the Products table and assigned to the control on the subform
bound to the UnitPrice column in the OrderDetails table.

Consequently, while the UnitPrice values in the products table can change
over time those in the OrderDetails table remain static as the value at the
time each order was created.

Ken Sheridan
Stafford, England

 

[Guest]

Hum hum, i think this could work, BUT, i am also facing the fact that i have
different customers with differents prices for the same product then this
solution does not work anymore as i would have to change my price list each
time i change customer. So the solution would be to have as many price lists
as i have customer and then use the here after solution. I beleive there is
nothing easier than that.

Tahnks again,

 

[Guest]

What you have here is a many-to-many relationship between customers and
products, which you'd model with a separate table, CustomerProducts say, with
foreign key columns CustomerID, ProductID. The UnitPrice column would go in
this table so you would have a different current unit price per product per
customer; in the jargon the price is said to be functionally dependent on the
key of CustomerProducts, the key of this table being a composite one made up
of the CustomerID and ProductID columns. When creating an invoice or order
the price would be looked up from this table, using the CustomerID and
ProductID as the ctriteria, and stored in a UnitPrice column with the invoice
or order, as in the case of the Northwind example.

Ken Sheridan
Stafford, England

 

[Guest]

This, also, is not uncommon. In addtion to your Product Code, your price
list needs a CustomerID. When you use the price list, filter by CustomerID.
But, include one "standard list" that you will use for new or unknow
customers that it will default to. The query to pull it by customer or
unknow customer would be:

SELECT * FROM tblPriceLIst WHERE ProductID = Me.txtProduct AND CustomerID =
Nz(Me.Customer, "STANDARD");

 

[John W. Vinson]

Hum hum, i think this could work, BUT, i am also facing the fact that i have
different customers with differents prices for the same product then this
solution does not work anymore as i would have to change my price list each
time i change customer. So the solution would be to have as many price lists
as i have customer and then use the here after solution. I beleive there is
nothing easier than that.

There is, actually!

Consider a table with three (at least) fields: ProductID; CustomerID;
and Price. The Primary Key of this table could consist of the two ID
fields as a joint, two field primary key.

You could then use a Query selecting just that customer's prices to
find the price of an item *for that customer*.

John W. Vinson [MVP]

 

[Guest]

thanks to all of you for your help. The option with a table and ProductID and
CustomerID is exactly what i am working on now.

Thanks.