Results 1 to 9 of 9
  1. #1
    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.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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...
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    What is a "price break" ??
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    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?).

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote 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
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote 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
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    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.

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote 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.

    Quote 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
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Thanks for the details

    I've never heard it referred to as a price break. Every business is different I guess
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •