Crosstab-'type' query effect needed!

[Cocis91]

I am making a database which records people's contributions to a pension plan but then invests these contributions by splitting them up and investing them in a variety of funds according to a certain ratio.

This ratio by which a contribution is split among the funds is to vary with age so for example I might have 4 possible funds an when a contributor is between age 40 and 50, the split to the 4 funds may be 20%:20%:30%:30%.

I want to set up a query that looks like a crosstab query. I would like to have fund name as collumn heading at the top and row headings will be the start age of the age range. The actual cells in the table will be the 'allocation ratio' as described above.

The point of setting it up in this way would be so that I could add an age band and then fill in the ratios for each fund. Also, I would like to be able to add a fund to the collumn headings too. Such a thing would be very user-friendly and flexible only there is one problem - I am damned if I know how I might go about doing this.

Does anyone have any ideas?

 

[Cocis91]

Here is an example of the sort of layout I am looking for. It is basically as you would set up a spreadsheet - with a spreadsheet you would be able to add another fund if you wanted or insert a row to add a new age band.

Fund1 Fund2 Fund3
Start age
20 20% 40% 40%
40 30% 35% 35%
50 50% 20% 30%
60 80% 0% 20%

 

[Cocis91]

Hopefully the alighnemt will display better this time...

------------Fund1 Fund2 Fund3
Start age
--20 20% 40% 40%
--40 30% 35% 35%
--50 50% 20% 30%
--60 80% 0% 20%

 

[Cocis91]

Perhaps this time!

------------Fund1----Fund2----Fund3
Start age--------------------------
--20---------20%------40%------40%
--40---------30%------35%------35%
--50---------50%------20%------30%
--60---------80%-------0%------20%

 

[Ally]

Have you actually tried it as a crosstab query? Are the funds separate fields or a lookup from another table - better still, can you list your fields.