Spread projected values across multiple months

[jeg-access]

I have a database of potential Projects with Estimated Fee, Start Date, End Date, FTEs. I want to have a report that will spread the Estimated Fee equally across each month and another report that shows the FTEs spread across the same time frame. I tried using a cross tab query, but it only shows the values in the first month. Any tips? Thanks.

[June7]

So you want to calculate how many months encompassed by the StartDate and EndDate, divide the Fee and FTE by that and assign portion to each month within date range for display horizontally on report?

Won't be easy by any method.

Would involve some complex calcs to extract month from each date and calculate the difference, then IIf conditional expressions to construct 12 fields.

Another approach would involve VBA and temp table.

[jeg-access]

Yes, you have it. I have this in an Excel table, built manually with IFs and LOOKUPs. I think I'm going to have to go the VBA route in Access. Thanks for the advice.

[June7]

I think a query could do it. Maybe not as complex as I first envisioned but certainly tricky.

[jeg-access]

I'm working on a query between two tables: T1: Dates only (first day of the month for 48 months) T2: Opportunities table (with start, end, projected fee, FTEs). Trying to build a crosstab query that works with those two data sets....

[June7]

Don't think CROSSTAB is going to do it. Will have to be a regular SELECT query with calculated fields.