# Thread: Formula Question

1. Registered User
Join Date
Aug 2008
Posts
2

## Unanswered: Formula Question

Hi, I am entering prices for the parts that are in my inventory. I would like to have the price breaks for the item, the cost per for that break, and then the extended price for that break. How can i write a formula, just like I would in Excel, to have the extended price already calculated when I enter the break and price per, just so it saves me alittle of work.

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
the database way of doing things is different to the spreadsheet way of doing things
I'd sugest you decide if your price rules are specific to a product or a group of products
Id expect these sort of rules to be defined in a table as either product specific (and part of the row for that product (or a sub table defining the rules applicable per product), or as some form of grouping then I'd expect a rules defintion table and a foreign key to that rule in the product table.

its temtping to hard code this (in a fom or query or wahteevr, but I'd expect these things to change. defining in a table also allows sepcial offers (ie a temporary case discount, or promotion.

just a thought...

3. L33t Helpa Munky
Join Date
Nov 2007
Location
Posts
4,049
What is a "price break" ??

4. Registered User
Join Date
Jun 2008
Posts
163
I think it's a breakdown. What I can't understand is the extended price (best I can assume is that an item as whole costs less than the total sum of it's parts. and the total sum of it's parts is the extended price?).

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
Originally Posted by StarTrekker
What is a "price break" ??
price breaks very
there are quite common in wholesalng
if you buy upto X products the price is say 75,
over x but upto y products 70
over y products 65

sometimes y'get case discounts (ie buy a case price is 65 per item, otherwise price is 75)

sometimes you get volume discounts
spend more than 1000 get n% off

sometims y'get retrospective discounts
spend more than 100000 in a period get an additional n%
a variation on that is the override (often occurs in the sightly sleazy world where company A provides goods and services to company B and buys goods from company C, Company A shows invoices from C to B for payment, and then behind the scenes demands an aditional discount from C which B doesn't see. happens a lot in commercial catering.

sometimes y'get specific customer prices for specified goods

..aaah the joys of purchasing

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
Originally Posted by coffeecat
I think it's a breakdown. What I can't understand is the extended price (best I can assume is that an item as whole costs less than the total sum of it's parts. and the total sum of it's parts is the extended price?).
extended price is usually the line total

ie agree a unit price of say 75
by 100 items the extended price is 7500 (75*100)
some systems then also identify the price including sales taxes, someitmes calling the line total the line gross or gross total
it depends on what is required

for UK legislation there is no specific need to identify sales tax on individual items (although you do have to identify what sales tax rate is applicable. this can be a code or the rae or the actaul tax. the revenue (tax collection authorities [aka money grubbing.....]) only really care about the gross (and I do mean gross) figure they will get their sticky hands on

having said that usually I do invoices with all the relevent data on

eg
prodID prodDesc Qty UnitPrice ExtPrice VAT Rate VATAmount Line TOT

..the downside of that approach is that you are running short of space for things like discount

7. Registered User
Join Date
Jun 2008
Posts
163
Thanks healdem, that was quite informative. If there is a per-client discount rate (which I assume varies per transaction), being able to place that info in the database going to be difficult since the calculation rate per process will vary. Seems like it will take a little more manual input.

Its interesting to see flea-market haggling computations in large scale transactions (I have such a simplistic provincial way of looking things). =D

@nlf:
hmm, I'm not sure what you're looking for (and also what you already have). Maybe you want a form that would display the extended price automatically after typing in other data? the most common tip I hear about this is leave the calculated data as calculated instead of being stored in the tables. Do you have something like a form that modifies the value per transaction (something that defines how much percentage of a discount will rise per increase in the amount of items purchased)? anyway, it sounds like there's a lot of considerations to be taken for this one.
Last edited by coffeecat; 08-15-08 at 01:47.

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
Originally Posted by coffeecat
...If there is a per-client discount rate (which I assume varies per transaction), being able to place that info in the database going to be difficult since the calculation rate per process will vary. Seems like it will take a little more manual input..
essentailly if a scheme is dreamed up by mankind it can be encapsulated into a computer system.. it may well be a bugger in Access but it can be done. the more obtuse agreeemnets are generaly limited to the big players, the sort of companies who will be using server db's rather than JET

..actually its not, you can design such rules and implement them using code (pulling the values from the the db), in many cases you can implement simply them using SQL. in some cases you have to use a strored procedure (on server db's that support such a feature). many wholesalers have standard shcems in place, and then define specific schemes (discount rules) for the big spenders. so you'd have a series of basic rules for say small, medium & large customers (say a template for each type, which is then modified to be customer sepcific if required. so each customer has a set of discount rules, several customers may share the same rule. its a very common demand form customer X "I want to be on the same discount rule as Y", also you can have say purchasing clubs where multiple customers form into a purchasing block and get a higher vlumne dicount rule because although each is a separate customer together the negotiate a better discount. granted it is a heck of a lot easier to say knock off a flat rate discount at the till.... however that leaves the supplier prone to problems with fraud & theft and transparency of who got the discount and why.
fer instance the checkout operator could collude with the customer and give a bigger discount than warranted or agreed. you aren't neccesarily steering the customer into the products where you have agreed discounts on your own purchases (its quite common to have promotions or overide agreements with your suppliers if you do 500,000 of business this year we will give you 10%, below that 5% so you want to ster your customers to that supplier.

Originally Posted by coffeecat
hmm, I'm not sure what you're looking for (and also what you already have). Maybe you want a form that would display the extended price automatically after typing in other data? the most common tip I hear about this is leave the calculated data as calculated instead of being stored in the tables. Do you have something like a form that modifies the value per transaction (something that defines how much percentage of a discount will rise per increase in the amount of items purchased)? anyway, it sounds like there's a lot of considerations to be taken for this one.
im guessing wha the OP is after is how do I show the line total on a form
the easy answer is.....
create a control whose data source is set to (say) =[price]*[quantity], assuming that you had a control or column in the db called "price" and one called "quantity". Id agree that you should resist the temptation to store that value in the db as its a derived value.

however if you have to account forsales tax and the like then I'd strongly recommend that you do store all such values even though they are derived so that there is a clear unambiguus accounting trail. I quite often create a detail replica of the invoice within the db as write only so that there is a proven replica of what was sent out.. there is no risk of someone tinkering with VAt rates throwing the documentation sent to the customer out of kilter, the sales tax collectors can see what was sent. heck I've even stored PDF's of individual invoices so when the customer claims not ot have received the invoice you can send em a copy by fax or email as they are whinging on the phone

9. L33t Helpa Munky
Join Date
Nov 2007
Location