Varied Price Lists

[BobJ]

Hey,

My company have a set of default prices for each of their services, however these prices can vary per customer. Clearly i need to incorporate this feature into my database and ive thought of a few ways and im pretty sure the following way is the most efficient.

I plan on Saving the Order entries in a separate table so that the default prices are not changed in the main tables, but i have to admit im a little confused as if you look at my relationships jpg that's pretty much what i am doing. The order information is saved in the OrderDetails tables, but if you change a price on the order form the default price changes too which sucks!.. I realise that my table structures are pretty poor but i didnt really know what i was doing at the time i made them and they work so.. hey :s

How can i stop the price changes from changing the default values?

 

[GaryPanic]

right what you need is a default price table - you got that
this needs to be copied into your main form (I have not looked at your structure )
and then the ability to adjust this depending on client discount structure
if you look at the (bad) example on the flash attached
this copies over 1 product into the main table - you then can edit the price - but the default prices does not change

eg

1 tim of beans cost 50 pence
however client mr Heinz gets a 10% discount so I can either discount the price or put a discount field in and have it work this out form me .

now this is a simplistic view based on 1 product per client -
you will of course have many products to 1 client -
so you need to list these a contious form layout would work and a combo box for your items draging the items into your subtable so you can edit the prices -
you will also need a history table - so you can see what customer has order etc..

 

[GaryPanic]

just looked at your layout- most of it's there

and you look as if you have started from the right point

in this project your customer is the centre of your "world" (this isn't always the case)
your customer has orders and discounts
your orders have products

now are the discounts set
ie mr Heinz always get a flat 10% off or does it move according to the product

this is slightly more complicated but again you would then need to code product to discount
if Product class = A then Cleint discount A would apply
if B the B disocunt

this would involve seting products codes (not a problem) and discount codes - easy but time consuming as you list could get quite long

mr Heinz
productdiscount A= 10%
Productdiscotn B =5%
C,D,E,F etc

 

[BobJ]

hmmm im not sure i get what you mean.. do i need to work out the % discount from the difference in price and store that figure? :s

 

[BobJ]

db

here's a copy of a part of my db might help :>

 

[neileg]

What Gary is asking is how do you know what to charge a customer? Do they get a fixed discount across the entire product range, do they get a different discount for different products, is the price agree sale by sale, or what?

 

[GaryPanic]

I could not open this (I am sing A2000)
anyway
Discount is it a set discount per client/customer
ie does mr Heinz get 10% off everything if so then have your customer/client table store discount

however if the disocunt varies depending on the product

back to my tin of beans

Mr Heinz get 10% discount
so 1 tin of bean £0.50
1 tin or sardines £0.75
discount 10% of £125.00

however if
1 tin of Beans discount to this product is 10%
1 tin of sardines discount on this product is 7.5% then you need to code both your product and also your client depending on product/client rating



hopefully its the first option - as this is nice and easy
second option involves an extra table and some fancy coding as each item has to have 1 full price , 2 discount rate tied to discount table and net price

as you can get your invocie/statements to show and hihlight this discount


lets assume 1st option

TofB (tin of bean) TofS (tin of Sardines)

5 * TofB @ £0.50 each = £2.50
4 * TofS @ £0.75 each = £3.00

total due £5.50
loyalty customer discount (or someother such rubbish) 10%
Total after discount £4.95

however if its the other way you probable don't want to show the disocunts per product
TofB @ 0.5 less 10% = 0.45
TofS @ 0.75 less 7.5% = 0.70

just the net result TofB @ 0.45, TofS @ 0.7

it is swings and roundabouts and depends on your situation
having a discount per product is a nightmare


as to storing historic data

different views on this -
I would actual store everything
so full price discount applied and net price - the pureists are probably froathing at the mouth - don't store calculated values etc... so this is a personal view

by storing all you do have a full historic table of excatly what you have done .

I am coming at this from an insurance point of view and the old busy bodies don't like prices that are calculated they prefer a set value even if that value is from a calculated field(madness I know-I do understand where they are coming from- but well ...)


hope this helps some what - I do have a sample of something simliar - but not on me at the mo - I'll try and remember when I get home and throw a copy up - its based on somethn completely different but might help.




g

 

[BobJ]

Generally those companies who do not go by the default price list are generally given a quote which would apply to specific items or services (eg storage). As when we deal with our clients we draw up contracts which will last a specific length of time.

 

[BobJ]

ok i had a meeting with my boss and hes told me that i dont need to worry about price lists for separate companies. i just need to be able to modify prices if theyre different, surely if all my orders are being saved in the OrderDetails tables there must be a simple way of telling access to save any entry in the price box without modifying the original price?!!

 

[GaryPanic]

This is easy -
order table will have
TofB and a default value copied (copied being the thing) into it and then amend the price - this will change the price for this 1 time
reselect an item and it will revert back to its orginal price

 

[neileg]

Yes, you need to do this anyway since you don't want old records changing when you update your price list.

The way I would do it is to use a combo box to select the item from the list an to retrieve the price as one of the columns (hidden or visible whatever you prefer). In the After Update event of the combo set the value of your price text box to the price in the combo. You can still overtype this value in the text box.

 

[BobJ]

awesome ill give it a go tomorrow morning my shift finishes in 10 mins really appreciate your helps lads thanks for being patient with me

 

[GaryPanic]

on my flash example
if you select a product (there are only 2 options there)
on says 5% commission but you can over type this with what you want and save - as this is not looking up data that is in your default table - but is actual stored on your main table

 

[BobJ]

bah sorry guys i worked on it over the weekend... couldnt get it to work... im not sure where to start.

ive managed to sus out that with my current structure which goes like this:

InputMedia (main table that holds all the products and prices) ---> iOrderDetails (which should store the details of the order ie price modifications)

however all im getting is a £0.00 in the cost column of the iOrderDetails for all orders so clearly atm my db is saving details to my InputMedia table... I have the InputMedia related information in a subform which has OrderDetailsExtended (a query that has fields coming from both InputMedia and iOrderDetails) as its record source.

as i said ive been playing around with it over the weekend but i cant figure out how to change it so that it saves the price modifications to my iOrderDetails table and doesnt change the InputMedia table!

Any ideas? :/