Help with Starting Point Figures for Pipeline Report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi there

Am currently transferring a large Excel spreadsheet over to Access as it is now too large and complicated - contains all our sales data and am finding Access brilliant for pulling info I need HOWEVER ...

I need to create a pipeline of carried forward figures and I need to enter a start figure to calculate the current month carried forward figure as follows :

Pipeline: SaleNo SaleValue SaleComm SaleDep
C/f from Prev Mth 1.00 500000 10000 50000
Plus Signed Up This Mth 2.00 1000000 20000 100000
Less Fallen 0.00 0 0 0
Less Settled 1.00 500000 10000 50000
Equals:
Agreements on Hand 2.00 1000000 20000 100000

Can get all the above to calculate correctly in the report (from a query), but not sure how to enter the first date's figures, ie: figures only start from Dec 2001, so it needs to calculate from that date then do all the above calculations to arrive at the date selected on the report, ie: May 1994 (using April 1994 carried forward figures).

Am now totally confused ... someone please help!!
 
If you think you are confused, what about us? How could data that _starts_
in Dec 2001 possibly be used to derive figures for 1994?

Take a deep breath, think what we might need to know about the data you
actualy have, how its stored, how you're retrieving it, etc. and go through
those details, slowly.

Larry Linson
Microsoft Access MVP

Bruum said:
Hi there

Am currently transferring a large Excel spreadsheet over to Access as it
Click to expand...
is now too large and complicated - contains all our sales data and am
finding Access brilliant for pulling info I need HOWEVER ...
I need to create a pipeline of carried forward figures and I need to enter
Click to expand...
a start figure to calculate the current month carried forward figure as
follows :
Pipeline: SaleNo SaleValue SaleComm SaleDep
C/f from Prev Mth 1.00 500000 10000 50000
Plus Signed Up This Mth 2.00 1000000 20000 100000
Less Fallen 0.00 0 0 0
Less Settled 1.00 500000 10000 50000
Equals:
Agreements on Hand 2.00 1000000 20000 100000

Can get all the above to calculate correctly in the report (from a query),
Click to expand...
but not sure how to enter the first date's figures, ie: figures only start
from Dec 2001, so it needs to calculate from that date then do all the above
calculations to arrive at the date selected on the report, ie: May 1994
(using April 1994 carried forward figures).
 
Hi Larry

Basically each month I need to derive an "Agreements on Hand" figure. This comes from the prior month's "Agreements on Hand" figure, as follows :

C/F from Previous Month (Agreements on Hand) 5.0
Plus Signed Up for This Month 4.0
Less Fallen this Month 2.0
Less Settled this Month 3.0
Total Agreements on Hand 4.0

The Signed Up, Fallen and Settled figures are entered into a table each month and totalled in the report. However the Carried Forward figure is not always the same as changes to the signed up, fallen and settled figures can be made during any month which will change the carried forward figures.

The problem I had was that I have to have a starting point for each calculation for each month, and our data only starts from December 2001, e.g.

C/F Agreements on Hand Dec 2001 5.0
+ Signed Up/- Fallen/- Settled = C/F for Jan 2002 3.0
+ Signed Up/-Fallen/-Settled = C/F for Feb 2002 4.0
etc..

When I run the sales pipeline report for say Mar 2002, the pipeline brings in the figure C/F for Feb 2002 as the starting point for the calculation (+ Signed Up, -Fallen, - Settled = C/F figure for Mar 2002).

I think I have sorted out what I can do, which is create a report which has unbound control fields with the December 2001 figures manually typed in. Then I can use a query on the report to bring each month's actual data in from the table, and do a total to calculate the carried forward figure for the month.

I should then be able to use that report as a subreport for the current month I am in.

Please let me know if this is enough information - it is quite hard to explain - and if you think I am going in the right direction or if I am not, what I should be looking at doing.

Many thanks, Jo
 
Back
Top