stopher - perhaps "Excel thinking" is not a technically correct phrase, but it conveys (among other baggage) the idea of extremely late binding of formulas. Perhaps if I call it that, you would object less to the language.
hardy - it is my thought that you might benefit from a little more forum searching for key word "invoice" or "invoicing" because you would CERTAINLY see how other people have used Access to approach this problem. And now that we know this is an invoice builder, we CAN direct you to ways to do things.
I will also say that to bring this into Access, you will face one very tedious hurdle - the problem of initial definition of your data. I.e. inputting the things that CAN be invoiced along with whatever indicators you finally choose regarding rules, tax rates, discount rates, etc.
From the discussion and now knowing that this is an invoicing system, you need to define ON PAPER the following ideas:
- What do you sell and how do you sell it? This leads to an inventory table where each line item might have several codes related to the questions that follow.
- Rules on minimum purchases, potentially subdivided into total purchase and whether there is also a minimum for a given item. Total purchase minimums are usually implemented with VBA in the forms used to build the invoice, perhaps assisted by fields in the inventory line-item table showing a minimum purchase code which could be "minimum in context of total purchase, minimum in context of number of this item, minimum in context of currency value of purchase, etc."
- Rules on discounts, which could include various tiers that differ from product to product. Again, could be supported by another code field in the inventory line item table to indicate which of several possible discount schedules would apply. Also, if you have discounts for the elderly, disabled, veterans, or students...
- Rules on taxes. You are in London, and therefore I don't know your tax rules. However, in the USA, we would have to consider whether a particular item was fully taxed or subject to certain tax exemptions (like, no national-level taxes on baby food - or something like that). Again, the inventory line item table could hold a coded field for this case.
- Rules on shipping rates, such as cost tiers based on weight of the shipment or on the number of boxes required, as well as on distance to be shipped.
- Rules on order of application of the other rules. Like, if a discount occurs, do you discount the price pre-tax per item or is there a different rule that says "tax full price" but then apply discount to the cost, leaving the total taxes alone.
Once you have definitions for any rules that apply, you can start towards proper implementation. However, in Access, you ALWAYS benefit from a little extra analysis before implementation. The more you know, the easier it will be to encode your rules, whatever they are.
Hardy, I know this sounds daunting. But remember - it has been done before. Each company has their own in-house wrinkles on this problem - but it has been done and therefore must be eminently doable.
