Combo Box to filter products (1 Viewer)

[jmeek]

Customer price lists

I have the following setup
tblCustomers
tblProducts
tblCustomerPrices
tblCustomerProducts
tblPackSizes (different prices per products based on packsize)

MainForm with Order details etc and subform with product details etc
Various queries comboxes etc

When selecting a customer from a combobox on mainform only those products
shown in tblCustomerProducts are listed which then bring up that customers’
own price list from tblCustomerPrices

I would like those products not in the customer’s price list to appear as well and to pick up the default price as shown in the tblProducts. Is such an amalgamation possible, otherwise it would require each customers price list to contain all the products. Based on 600 customers and 3000 products that seems a lot of repetition. Each customer will only ever have at most around 80 to 90 special prices. If product is not shown in customer price list then will default to main product list and standard price as shown in tblProducts
Appreciate some guidance on this
Thanks

 

[Uncle Gizmo]

It appears to me you are inventing your own method of pricing.

You may be better off Looking for an existing method and adapting that to your use.

the method that immediately springs to mind is having a discount system, you have a fixed "retail price" and then you give special customers a discount, this removes the problem of remembering different pricing systems for different customers.

It would be relatively easy to setup, each customer would have a separate field which would display the percentage discount they were offered.

 

[jmeek]

Hi Uncle
What you say is an eye opener. I hadn't thought of doing it that way. It seems perfectly logical to offer a pefcentage discount of the retail as you suggest. Could you please tell me how you would insert/use the additional field you mention so that when a product is called it will insert the correct price.

Thanks for your help

 

[Uncle Gizmo]

I'm not 100% sure but I think the "northwind database, the sample database that comes with MS Access has exactly the system I describe. Have a look at that, if I'm not correct and get back to me in this thread.

 

[jmeek]

With the Nwind database you have to key in a discount. No such customer discount scheme exists.