Hi all,

Im Currently in the process of disigning a ordering database. Which is fine for a single price model. The problem is that we have multi customers each with there own agreed pricing model. (same products across the board).

What I would like to do is have a single product list and a pricing group so I could create a price group called company A, Company B, Company C or Default, 20% Markup Etc..

I have tried the following Table set up

TblProducts
ID
Desc

TblProdPriceList
ID
GroupID
ProdID
UnitPrice


TblProdGroup
ID
groupName


what i would like be able to do is when setting up a new customer and assigning a new pricing structure. create a new group companyX return all products with either blank price or the defualt price be able to go through and update prices and not miss any products.

Hope this makes sense.

many thanks

Darren

If prices are calculated as a percentage of a standard and that percentage is standard for a customer, then you need:
tblCustomer:
CustomerID
PricingGroupID
...
tblProduct:
ProductID
UnitPrice
...
tblPricingGroup
PricingGroupID
PctChange (can be > 1 if customer pays more than standard)

You haven't really given us the business rules for how price is calculated so I can't make any other guesses at a reasonable structure.

Or you could do the following, not sure which way would be best for you though.

Customer Table
Cust_ID {pk}
etc ...

Product Table
Prod_ID {pk}
Prod_Price
etc ...

Price Table
Prod_ID {pk}
Cust_ID {pk}
Sale_Markup

That way you would only ever have a single price for a product for a particular customer, but at the same time you can amend a price for each product for a customer, rather than having to change all the prices for a particular customer ... If that makes sence

Thank you for your reply,

Im sorry if I was not clear, I have worked it out now. What i needed was a many to many relationship and use a make table queries to create a new list from the default pricing list from this I can then do an append to add the new pricing to the tables. Initial test seems to work. Just need to do this with VBA instead of queries

Once again many thanks for your help.

Darren

Queries are nearly always much more efficient than VBA so if you can do it with queries then that's the way to go