Help to structure a problem

[PaulJK]

I have part of my database which looks calculates from multiplying a price and store numbers a monthly revenue.

This applies for each order.

Each order also has a start date.

What I would like to do is calcuate in a report a projected revenue month by month and this is where I would appreciate some guidance on how to build this.

The monthly revenue per order will be constant per month. However each order has a different start date.

I hope I have explained this as clearly as possible.

Thanks in advance.

Paul

 

[gemma-the-husky]

bascially you have an order with a startdate and an enddate

and you want to get a report of your potential sales between two other dates.

so (assuming the sales are straightline) then you want to find all orders where

a) the orderend is AFTER your startdate, and
b) the orderstart is BEFORE your enddate -

which is simple for a query. (hope these conditions make sense)

you can then calculate one months sales for each of these orders. you may want to get more fancy and calculate partial months for orders which start/finish part way through - and you may want to get fancy with respect to the timing of the sales pattern - but this idea should give you a starting point.

 

[PaulJK]

Hello,
Thank you very much for your reply.

I have been looking at this problem over the weekend, but I am still a little confused about how to approach the problem.

Apologies if I have not explained things as correctly as I could have earlier.

Regarding the query you mentioned, this is fine and I am able to calculate this. It is then I am uncertain as to how best to proceed.

If I use an example:

• I would like to report from May 1st to December 1st
• Order # 1 starts in May & have a monthly revenue already calculated as 10,000
• Order # 2 starts in August & have a monthly revenue already calculated as 20,000

I would like to create a report that shows by month (col heading) and by order (row heading) the revenue:

For May, June & July 10,000 would be shown for each month for Order # 1
For August to December Order # 1 would show 10,000 per month and order # 20,000 per month.

I guess I am describing a report here, but I need to get the query and table structure correct first.

Your help would be appreciated.

Thank you.

 

[gemma-the-husky]

you need to get a query that selects the contracts you want

while it relatively easy to get a query for a single selected month, i thank that to get it the way you want, to include multiple months, then for each contract falling in the date range you need to generate a line for each particular month - this is doable - but quite tricky, and offhand i think it will need code, rather than just a query - possibly by creating a base table, then a series of appends for each month.

however you produce your final query (either directly, or via a maketable), then once you have created the query, you can sort and report it in any convenient way.

your problem is definitely extracting the data, not reporting it once it is extracted