Monthly Values

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

Guest

Here's my query that I would like to query against:

JobNumber (text)
StartDate (date)
EndDate (date)
Value (calculated)

I would like a query that shows the total Value for each month. The Value
occurs at the end of the month. If the job spans many months, the value is
split proportionally between those months (so a job that takes place
throughout Sept, and through half of Oct, will have 75% of the value at end
of Sept, and 25% at end of Oct.

If there is no start date, the start date is assumed to be the earliest
start date in the query. If there is no end date, the end date is assumed to
be the latest end date in the query. The value is then distributed as above.

How can I do this?

Thanks,

Dave
 
That's what I thought, and I've started on. Is it possible to use a recordset
as the basis for a form or report?

It seems strange that something I would consider a pretty basic function
hasn't been discussed before. Surely there are database systems that will
produce a monthly forecast based on values over a date range? Looking around
google, it doesn't seem to have been mentioned before.

Dave
 
Forms and Reports need either a table or a query. I would suggest you create
a table as the output of what you are doing and use that as the record source.

Yes, it is done all the time when the data is to be analyzed within a specif
month, but the requirements you have are unique.

There may be a world class SQL whiz out there that could do this, but IMHO
the recordset idea for what you are doing is easier.
 
Back
Top