Help with function

  • Thread starter Thread starter Ather Mian
  • Start date Start date
A

Ather Mian

Hi,

I am using Access 200. I need some help and guidance. My dilemma is as
follows.

I am working with Service Contracts and I need to forecast revenue. These
are the fields I have available to me:

Contract number
Contract value
Start date
End date

My thoughts were that do a User defined function which would calculate the
complete months between the start and end date and then repeat the
contracts that many times by returning the month(actual month not like
month 1 or month 2) and forecasted revenue. for example. If I have a one
year contract then it should be repeated 12 times as.

Contract 1234
Contract value 1200
Start date 1/1/2003
End date 12/31/2003

The function give the results as:

Jan 03 100 (1200/12)
Feb 03 100
Mar 03 100
::
::
Dec 03 100


I am not sure where or how to get started. Any help or suggestions would
be greatly appreciated.

Thanks
Ather
 
1. The report needs to get the months from somewhere.
Create a table (tblMonth) with just one Date/Time field (WotMonth).
Enter a row for each month, e.g.
1/1/03
2/1/03
3/1/03
...

2. Create a query that contains your Service Contracts table as well as
tblMonth. If you see any line joining the 2 tables in query design, delete
the line: it is the lack of any join that gives a record for each row.

3. Drag tblMonth.WotMonth into the grid. In the Critieria row under this
field, enter:
Between [Start date] And [End date]
Add the other fields you want to the grid. The query gives a row for every
first-of-the-month that is within the range of the contract. Save with the
name "qryContractMonths".

4. It appears that you need to divide the contract value by the number of
months? If so, type something like this into a fresh column of the query
design grid (Field row):
MonthlyAmount: CCur([Contract value] / (DateDiff("m", [Start date], [End
date]) + 1))

5. Create a report based on this query. In report design view, open the
Sorting And Grouping dialog. Enter the field WotMonth, and in the lower pane
of the dialog, set Group Footer to Yes.

6. In the WotMonth group footer section, place a text box bound to WotMonth.
Set its Format property to mmm-yy.

7. Add another text box to the same section. Set its ControlSource to:
=Sum([MonthlyAmount])

The report should show all contracts in each month, with a total for each.
Set the Visible property of the Detail section to No if you just want a
summary.
 
Ather
Are you saying that
this is the only table you have?
There is no column that shows the length of the contract, so everything
is assumed to be 12 months

How far into the future do you want to project? - Max(startDate) + 12
months?

HS
 
Back
Top