Query per year colums

[renante]

Hello,
I know this would be easy for the experts. I have a query with three 4 fields, contractInfo,AmountperAnnum, begContractDate and Ending Contract Date. Most of the contracts rans from 3 to 5 years. What i need is query that will shows annual cost for 5 years- meaning each column will show the years and row will show the contract info.
to make this interesting, my fiscal year starts from April 1. Hope somebody could spare some code for me..

Thanks,

Renx

 

[dhookom]

Please provide some sample records and desired output/display. I expect you need a table of numbers with values from at least 1 to the maximum number of years.

Duane
Hook'D on Access
MS Access MVP

 

[renante]

Sample data

ContractNo. Start End Annualcost
SSI-001 8/1/09 7/31/15 5,000
SSI-002 12/1/10 11/30/12 15,000

5 year annual projected cost starting from 4/1/08 (expected output)

ContractNo. Annualcost FY 2008 FY2009 FY2010 FY2011
SSI-001 5,000 0 (6/12)*5000 5,000 5,000
SSI-002 15,000 0 0 (4/12)*15000 15,000


Hope this clears everything. Thanks in advance

 

[dhookom]

I don't understand your output since no contract has any date within FY 2008. Are you actually looking at splitting the annual cost into monthly sections?

Duane
Hook'D on Access
MS Access MVP

 

[renante]

The user can put whatever beginning period he wants then the query should show the yearly allocation. Since fiscal year starts in april 1, any contracts that starts on the same fiscal year should have a amount equal to the contract start month till the end of the fiscal year. Therefore a contract will only have few months of its annual amount. The same if the contracts end within the same fiscal year. The sample was cut to reflect to show first 3 years.

Thanks again