Spreading costs over time (from another thread)

[Guest]

I have a table with 3 fields:

Amount
Start Date
End Date

I want to apply the Amount to each month based on the Start Date and End
Date. I was thinking there could be a query written that would:

- Set up the columns (months)
- Analyse the start and end dates
- Populate the amount in the columns where start and end meet the criteria

The end result would be a "table" of results

Example:

Amount Start End Jan-05 Feb-05 Mar-05 Apr-05

$100 Feb-05 Mar-05 $100 $100
$200 Jan-05 Apr-05 $200 $200 $200
$200
$150 Mar-05 Apr-05 $150
$150

so on, and so on....


Sorry for the re-post....

 

[[MVP] S.Clark]

If you omitted the start and end date columns, you could just make a cross
tab queries. But to add those columns, I would create a temp table and
populate each row as needed with queries and VBA code.

 

[Duane Hookom]

Add a primary key field to your table and name it ID. Also, assume your
table name is tblMark.
Then create a table for dates of each month.
tlbMonths
Monthdate (one date record for the first of each month)

Then create a crosstab query like:

TRANSFORM Avg(tblMark.Amount) AS AvgOfAmount
SELECT tblMark.ID, tblMark.Amount, tblMark.StartDate, tblMark.EndDate
FROM tblMark, tblMonths
WHERE (((tblMonths.MonthDate) Between [StartDate] And [EndDate]))
GROUP BY tblMark.ID, tblMark.Amount, tblMark.StartDate, tblMark.EndDate
PIVOT Format([MonthDate],"mmm yyyy") In ("Jan 2005","Feb 2005","Mar
2005","Apr 2005","May 2005","Jun 2005","Jul 2005","Aug 2005","Sep 2005","Oct
2005","Nov 2005","Dec 2005");