Help with year to month converson

[John Erickson]

Hi all

I am currently in the process of setting up database to help us with
billing. The information that I get from the sales reps is the date the
contract starts, that date it expires and the total amount of the contract.
The problem is we bill by them month. I was wondering if there is a way to
generate a monthly report just using the information at hand, or would it be
better to breakdown the amount entered by the month on the form.

Any help would be greatly appreciated.

Thanks
John

 

[Jeff Boyce]

John

It sounds like you know a start date, an end date, and the current date.
Access has date-related functions that let you determine the month of a
given date.

If you were doing this with paper & pencil, how would you describe how you
determine a bill?

 

[John Erickson]

Jeff

If I were doing it with paper and pencil, I would know the amount to charge
by the month. As an example, if the contract started on jan 1 and ended on
dec 31 and the total amount of the contract was $1200, I would know that we
would bill out $100 a month from Jan to Dec. I have set up a query that will
give me the amount that we charge per month ( that part was easy).

Thanks
John

 

[Jeff Boyce]

John

Are you saying that the amount is calculated by dividing the contract total
by the number of months in the contract?

 

[John Erickson]

Jeff

Thats pretty much it. The contract can be from a period of not less than 1
month to not greater than 12 months. The only time it is different is when
the client signs a contract in the middle of the month and the first and
last months are prorated ( the prorated amount is the same for both the
first and last month then). What I would ultimatly like to do is generate a
report that looks at the calendar year.

John

 

[Jeff Boyce]

John

"pretty much" means you have exceptions!

So, the only two conditions you need to deal with are 1st of month
(calculate total/months) or mid-month (calculate total/(months-1); then
assign .5 a month's amount to first and .5 to last, all the rest get a
month's share).

Again, if you were working on paper, would you do these calculations?

Once you know how you'd work with the data "by hand", you can handle it in
Access. I suspect you can use a query to do the calculations. You did say
you have startdate, enddate, amount, right?

 

[John Erickson]

Jeff

Thats spot on. We havent yet had an occasion where there was an exception to
this, but there is an off chance that it could happen. At this point in
time, we are not planning on an exception to this rule. Your right on the
calculations. That is how we are currently doing it. I have set up a query
to perform these calculations for me.
I do have the start date, end date and total amount, and if the contract is
prorated, there is a check box to let us know this.

I appreciate your help in this,

Thanks
John