4 pricelists instead of one price 1

[firsttime]

Hi,

What i now have in my database is one orderprice so when a customer select an product the orderprice that is related with product is given.

Instead of one orderprice i want four pricelists. Each pricelist is related with an customer.
How can i define that??
I don't how or where to put the four pricelists too. The orderprice is in the table products

My database view(like the example in Northwind from the folder Order):

Mainform Order
-------------------------------------
CustomerIdcombobox) Orderid:
-------------------------------------

Subform Order
-----------------------------------
Productname(combobox) Orderprice
----------------------------------

THX IN ADVANCE

DAN

 

[firsttime]

Please can anyone help with this. Or give some comment if it is possible to do?? I need this to work,for my invoice report. Because every customer has a diferent pricelist. If i press on a button it just show the pricelist that is related with the customer.What i now see is 4 pricelist on the invoice.

DAN

 

[Paddyo]

Hi

Im not sure if I understand you properly.

Is this it - Each product has four prices - the price to use depends on the customer?

If this is the case do the following:

Setup the four prices against each product
Setup a price code for each customer on the customer table
This pricecode can tell your program which one of the four prices to give this customer when invoicing.

You mave have to write some VBA code to implement this.

I presume you have an order/orderline table which records details of the order and that you produce the invoice from this.

Hope this helps
Paul

 

[firsttime]

Hi,

This is what i wwant to do. But i don't know the VBA code for the pricecode.
Can you help me Paul?
Or somebody else who can help me??

DAN

 

[firsttime]

Hi again,

Can anyone help me with a vba code for this problem?

DAN

 

[Sylv4n]

assuming that your tables are set up as follows:

tblCustomer:

Customer_ID - Customer_Name - Etc - Etc
1 - John .......
2 - Willims ....


tblProduct:

Product_ID - Product_Name - Product_Description
1 - Walkers Square - Crisps
2 - Mc Vities Cheddars - Crisps

tblPrice:

Product_ID - Customer_ID - Price
1 - 1 - £10
1 - 2 - £20
2 - 1 - £10
2 - 2 - £16

-------

Then the logic behind what you want to do is:

1- Get the customer id (assuming that it is on the form)
1- Allow the user to select a product
2- look up that referance (customer id & product ID) in tblPrice
3- Display that price

This coding wuld have to be on the 'On Change' of the drop down box

Dim RecSet As Recordset
Dim dbs As Database

Set dbs = CurrentDb
Set RecSet = dbs.OpenRecordset("SELECT * FROM tblPrice WHERE Custtomer_ID = '" & me.CustomerID & "' AND Product_ID = '" & me.Product_ID & "';&quot

me.Price = RecSet("Price&quot

-------------

NOTE: this is untested but let me know if it works or not.

 

[Paddyo]

Sylv4n

I think this is going further than firsttime requires. What you have suggested is that each customer has an individual price. Your method can be used as a very powerful pricing tool where every cutomer could conceivably have a different price for each product. However I think he means that there are a maximum of four prices setup against a product. The customer uses one of these four prices.

Product Table:
PRODUCT_ID,PRODUCT_Desc,Price1,Price2,Price3,Price4

Customer Table:
Customer_Id,CustomerPricecode


Your code might change to somthing like this

Dim RecSet As Recordset
Dim dbs As Database

Set dbs = CurrentDb
Set RecSet = dbs.OpenRecordset("SELECT * FROM Product WHERE Product_ID = '" & me.Product_ID & "';&quot

Select case me.CustomerpriceCode
Case 1
me.price = RecSet("Price1&quot
Case 2
me.price = RecSet("Price2&quot
Case 3
Me.Price = RecSet("Price3&quot
Case 4
Me.price = RecSet("Price4&quot
end Select




Comments:
Price1,2,3,4 are fields on the product table
Me.price is a control on the form.
me.Product_ID is a control on the form
me.CustomerpriceCode is a control on the form which matches the field on the customer table.

This is by no means a working solution - its just to set firsttime on the right path