 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / August 2006Monthly Values
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 I think it would be easier to do using VBA and recordset processing. > Here's my query that I would like to query against: > [quoted text clipped - 18 lines] > > 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 > I think it would be easier to do using VBA and recordset processing. > [quoted text clipped - 20 lines] > > > > 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. > 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? [quoted text clipped - 30 lines] > > > > > > Dave |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.