Multiple calculations for single field in one query

[ckk922]

Hello,

I am designing a database in Access 2010 to handle customer discounts. The problem I am facing is that the discount can vary by customer. One could be a straight percentage based on certain sales totals, even the percentages can vary by customer, and some customer discounts are tiered. I was hoping there was a way to handle all through one query, because there are so many variations, it would require several queries if I separate by each calculation available.

Is there a way to set up a table listing the customer and their corresponding calculation expression then running a query using that table and the sales data table to do all the calculations. Or is there another way to handle this level of complexity in Access? The end result I would like is one data set with each customer and their calculated discount.

Thank you for any help you can provide.

[trevor40]

you should use a table to keep account of current discount rates, fields can include various discount reasons with descriptions. this can then be updated as required. you then link your data to the reference table and do all the calculations there on the data with respect to the references.