G
Guest
Hi,
I am developing a database that should be used for royalty reporting. For
every customer I have the following information; a number of different
products that a customer buys, quarterly volumes of these products which I
have chosen to store in fields depending on which quarter they are sold. The
revenues from these sales are then dependant of what volumes the customer
have reached over time i.e. a type of volume discounting. Hence when a
customer reaches a certain volume they obtain a smaller unit price. The
volume levels and corresponding pricing have been placed in a separate table
where the upper volume limit for each intervall are placed in separate fields
with the unit price for that intervall next to it in a separate field. What I
want to do now is calculate my revenue based on the accumulated volume over
time i.e. I want to sum the volumes for a specific customer and product for
all reported periods and then I want access to multiply the volumes by the
unit price. For example if customer A have produced 5.000 units in Q1, 10.000
units in Q2 and 10.000 units in Q3 access should muliply the first 10.000
units (upper limit for volume level 1) with the price 1,0, then I want it to
muliply the next 10.000 units (upper limit for level 2 = 20.000) with 0,8 and
at least the last 5.000 units with 0,6. Does anyone have any suggestions of
how I should go about creating such a query? Note that the volume levels as
well as its corresponding prices are different for different products and
customers but generally there are no more then 6 different levels.
Since I have placed the volumes and volume intervalls in different tables I
have had some problems with a chartesian join and I guess it would be more
resonable to put everything in one table since the customer and product
information are the same both for volumes, volume intervalls and
corresponding prices. However even if I do so I have no good idea of how I
should solve the rest of my problems.
Very greatful to answers / Bell
I am developing a database that should be used for royalty reporting. For
every customer I have the following information; a number of different
products that a customer buys, quarterly volumes of these products which I
have chosen to store in fields depending on which quarter they are sold. The
revenues from these sales are then dependant of what volumes the customer
have reached over time i.e. a type of volume discounting. Hence when a
customer reaches a certain volume they obtain a smaller unit price. The
volume levels and corresponding pricing have been placed in a separate table
where the upper volume limit for each intervall are placed in separate fields
with the unit price for that intervall next to it in a separate field. What I
want to do now is calculate my revenue based on the accumulated volume over
time i.e. I want to sum the volumes for a specific customer and product for
all reported periods and then I want access to multiply the volumes by the
unit price. For example if customer A have produced 5.000 units in Q1, 10.000
units in Q2 and 10.000 units in Q3 access should muliply the first 10.000
units (upper limit for volume level 1) with the price 1,0, then I want it to
muliply the next 10.000 units (upper limit for level 2 = 20.000) with 0,8 and
at least the last 5.000 units with 0,6. Does anyone have any suggestions of
how I should go about creating such a query? Note that the volume levels as
well as its corresponding prices are different for different products and
customers but generally there are no more then 6 different levels.
Since I have placed the volumes and volume intervalls in different tables I
have had some problems with a chartesian join and I guess it would be more
resonable to put everything in one table since the customer and product
information are the same both for volumes, volume intervalls and
corresponding prices. However even if I do so I have no good idea of how I
should solve the rest of my problems.
Very greatful to answers / Bell