Multiple Prices per product (1 Viewer)

[Toffy]

Hey there i am looking for a way to have different prices saved for each individual client.

I would have one main price list but i want to set certain items to have special prices for different clients. How is this possible in Access in excel its so simple.

Thank

 

[Galaxiom]

Prices would be a table related to Inventory by ProductID. It would also contain fields for ClientID and Price.

The base price could use an out of scope ClientID, eg zero.

 

[Geotch]

Use iif statements in Access. If customer doesn't have a match, set the customer to "generic". In the price table you should have item, price, and customer. Every item should have a customer called "generic."

If you need a sample I could throw one together quickly. Hope that helps.

 

[Toffy]

Thanks Geotch I would appreciate a quick example. I think I understand most except how to formulate a generic price that goes by defult when there is no secondary price for that client/product.

 

[Geotch]

I've attached the sample.

 

[Toffy]

So just to clarify i make a prices table and i list all the product numbers and leave the client number blank and that becomes my generic price list??

then any clients that have special prices i make another entry in the datasheet and insert their client id number followed by their price.

 

[David R]

I like Galaxiom's idea of using a 'generic' client number of 0 or -999 or something that will never show up in an actual order. Saves you having to check for nulls.

 

[Geotch]

Exactly right. Leave it blank and it becomes the generic customer price. Follow the queries in alpha name order one by one and understand how it works.

 

[Geotch]

David r, saves you from checking null but you'll have a customer for each order. Each customer should have an id number. Not sure how that would work.

 

[David R]

Well, instead of checking for null, you check against the dummy ID.

Your way should work too, it's just not the way my brain attacks the problem.

 

[catalin.petrut]

I have another suggestion for your problem. Is based on my need of different prices for my services for some of the clients.
So, i suggest:
1. a table for price list: product_id, product_name, price.
2. a table for special prices: special_price_id, client_id, product_id, special_price
Using this way, you can make your forms to automatic use the special price (if exists) or the standard price. Also you respect the database rule for no blank fields in the row.

 

[spikepl]

@catalin.petrut

Unfortunately your suggestion goes against the tenets of normalization. You should not store data in table names. Similar kind of data belongs in the same container, with tags (i.e. another column) designating any differences.

Update:

"your forms to automatic use the special price (if exists)" means that the data should be looked up somewhere else for some cases (rather than the usual place). This kind of logic is redundant and total PITA

 

[jdraw]

Toffy,

Do you really have a different price for every product for every client?
What happens when you get a new client, or 10 new products?

Would it make sense if some clients had discounts (like 2%, 5% or 10%)? I'm thinking some sort of client loyalty plan (clients who make purchases , say after 10 purchases, get x% discount). Either they get that discount on all products or you identify products as "discountable or not". Seems like it coud be a maintenance nightmare unless it's very clear and very simple.

 

[Galaxiom]

Do you really have a different price for every product for every client?

It isn't that unusual. Some businesses need to negotiate prices with large clients. Until they do that they get a standard price.

Some may have standard discounts as defaults too. Quantity based pricing could be included in both the generic price and the per customer price.

 

[Galaxiom]

Also you respect the database rule for no blank fields in the row.

New rule to me. Not one I would bother following.

 

[jdraw]

@Galaxiom,

Re #14, I agree with you. I got the impression from the OP, different prices for every product for every client ---too wide open.
Volume discounts on some products for certain clients; Sale events (around Holidays etc) with Discounts to all, or special discounts for repeat clients....