Report Conditional Sum (1 Viewer)

[solnajeff]

Hi

I am currently transferring a system used for discounts from Excel to Access and would like some advice. I believe that what I want to achieve will be possible in Access Reports but would appreciate any pointers.

What is very easy in Excel is a little more complicated in Access, but overall should be a much better system.

The system calls for an aggregation of sales over an extended period with current sales added at 100%, 1 quarter ago 75%, 2 quarters ago 50%, 3 quarters ago 40%, 4 quarters ago 30%, all others 25%.

I am using a field, 'sales period' which increases by 1 every quarter and would like to use this as a basis for calculating applicable sales. For example if current period is 15, total * 100%, current period - 1 * 75% etc.

I would appreciate it if you could advise if I am on the right track or if I need to rethink my approach.

Many thanks

Jeff

 

[Galaxiom]

You need to calculate the sales period for the transaction and then translate it to the factor. How you calculate the period has many posibilities using a selectopn of date functions available in Access.

I would probably apply this query first to generate a column with a number for the period. Then join that field to a rates table with the percentages to be applied to make the rest of the calculation.

This method is easy to follow and allows the factors to be easily changed by editing the rates table.

It could also be achieved using nested iif statements using DLookup to the rate table. The expression gets a bit combersome though.

 

[solnajeff]

Thanks I will give it a go and see how I get on

Regards

Jeff