Customer Specific pricing

[dominicgingras]

I would like to asing a pricing level to my customer. For example 1 to 10. Then we i use my order entry from, i will like assing that specific price to that customer. So far I use a dlookup function to assing that pricing. Any lead for where to start?

 

[KenReay]

Hi

My start point would be:

Customer Table

CustomerId (Prime Key)
PriceList
CustomerName
CustomerAddress
..etc

Price Table

ProductId ( Prime
PriceList ( Key
UnitPrice

Then during Ordering, when you know the productId you can use teh PriceList from the Customer Table to reference the correct Pricelist row, I cannot describe the whole process (in this limited space) but it should be possible to do it using a query of even from a combo box.

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[JeremyNYC]

Ken,

Why the separate PriceList field? Why not just have the customerID field serve that function?

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[KenReay]

Hi

Because I am assuming that several customers may be on the same price list.

If each Customer has their own unique Price List then I would agree, use CustomerId Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[dominicgingras]

What I intend to do is more like this:

In the price list I have 5 level of price:

PriceLevel1
PriceLevel2
PriceLevel3
PriceLevel4
PriceLevel5

And in the customer table a pirce level:

For example customer 123 have price level 4

When you plave the order depending on the customer, you will get the right price in the unitprice text box.

 

[Cleis]

Hi dominicgingras!

From your last post, your logic seems to be on the right track. Is you question more of how you lookup the pricing level for a given customer on your order entry screen? Just a tip, have you looked at the Orders DB sample that comes with Access? They have a pretty good example order entry sceen that can do what your looking to do. The db should be in C:\Program Files\Microsoft Office\Office\Samples\Orders.mdb

 

[JeremyNYC]

Dominic,

That seems to be pretty durn close to what Ken's suggesting (which, now that I look at it again, makes much, much sense).

I would not use a DLookup--I'm pretty biased against them, having been told a zillion times how slow they are. If you're doing it in code, go get a recordset that returns the right value. If you're doing it on a form, use a combo box (you'll have to reset the rowsource in the Current event of the form).

I'll agree with the last poster, though, that I'm not quite sure whaqt you question is.

Jeremy
=============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[MichaelRed]

My experience is more along the line of having a single price, and a discount schedual. The discount can be applied to an individual account (customer), or a volume (or sales ammount) for individual customers or individual sales (orders) or cummulative sales.


Maintenance of seperate pricing is (becomes) a maintenance problem (actually NIGHTMARE).


MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[dominicgingras]

I am not so experience in coding, so sometimes I do thing in such a way that it may take more line to code to do what I am trying to do. Michael has a strong point, but in this case a discount is inappropriate. I could give discount a per category basis but that will be even more complicated at this stage.

I was planning to do my coding using a bunch of if ... then ... end if .... and dlookup. By looking at the other post I see that some of you have alternative to this scenario.

Could you give me your exact approach to this?

 

[KenReay]

Hi

If you send me your Email address (to kenneth.reay@talk21.com) I can send you a little worked example. I will post the information here as well for completeness, but for yourself an example will probably help.

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[KenReay]

Hi

OOPS, forgot to ask, which version of Access are you using? Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK