Hi,
Apologies in advance for the long answer but this required a bit of thought outside of the Access "arena" - especially since you have not yet started to build a database.
I don't think this is a platform / software problem (i.e. Excel versus Access) but instead I think it might be more of an administration and organisation problem. By "organisation" I mean simply that - being organised with the data sources and calculations. To use a phrase from your 2nd post, it is the "administrative maintenance" of the rebates which I think is the issue.
To build a rebate calculation database will require considerable organisation of the data into logical groupings, expressing the rebates mathematically, having the imported data in the right format in the right place at the right time and so forth. If all of these tasks have to be done before you set up your Access database, then have a think about how easy your existing Excel system would be if the same efforts went into ensuring it was set up to process rebate calculations quickly and efficiently. Then calculating the rebates in Excel might not be half the task that it currently appears to be.
Transferring the methodology across to Access will not necessarily make it any more streamlined or easier. In fact, if you migrated a disorganised Excel system to Access then I think you would actually be worse off as you not only grapple with the "administration maintenance", but also grapple with Access. The best way to make using an Access database simple and easy would be to automate the import of sales data from the ERP system into the database combined with well defined rules for the rebate calculations.
If you were to use a database then some of the tables that you would require include : customers (including regional information), sales values and volumes by (regional) customer by product group by period, product groups, periods, rebate logic per customer and so forth. After data import routines and organistion, the next hardest part would be the rebate logic, i.e. trying to build a set of criteria to accommodate all of the possible combinations of possible rebate structures. Keep in mid that this is a lot of effort to achieve what can already be achieved in Excel.
I started to design a single formula in Excel to calculate the rebate given a variety of criteria, that I could then transform into an Access formula. The Excel formula was based on a choice of value or volume with up to 2 thresholds where the rebate for the last threshold could either be an incremental rebate only or for all sales (i.e. your 4% on the entire $2m example). This might not sound like much but the formula ended up quite long and very complex. These were basic criteria that I used and the formula would be infinitely more complex if we introduced more thresholds, a rebate limit, scaled rebates and so forth. This could be programmed into Access but it wouldn't be as simple as using Excel.
It was while I was creating this formula I came to the conclusion that Excel would be the better tool for calculating the rebates. So where would Access fit in with this? Maybe it could be used to help with the administration - i.e. you might use Access to set up a rebate register which would act as a periodic prompt or centralised control. This would help to ensure you had all of the right bits of data in the right place at the right time - in other words, the "administration maintenance". But then you don't need Access to do this - Excel works just as well, if not better, at this sort of task.
Lastly, if you can accurately describe the problem (or in this case the problems of the administration of the multitude of rebates) to yourself then the answer as to how to deal with this problem should present itself. So you might want to ask yourself what is the real issue that you are grappling with?
HTH, Andrew.
