How do I create a list with a table in each item on a form?

[Guest]

A Program is broken down into projects and projects are further broken down
into Types of Funding. Types of Funding has a table associated with it that
shows the fiscal years as columns (06, 07, 08...) and two rows showing
Requested amounts and Actual amounts. Each group above this one has the same
table associated with that shows the rollup/totals from the group below it.
So if Types of funding had:
A = $100 requested, $50 actual for 2006
B = $200 requested, $100 actual for 2006
C = $300 requested, $200 actual for 2006
Then....
Project 1 funding table would have $600 requested and $350 actual for 2006
And lust just say that Project 2 and Project 3 each had $100 requested and
$100 actual for 2006
Then....
Program funding table rollup would be $800 requested and $550 actual for
2006.

So in the form the user picks a Program (drop down list) which then displays
the funding rollup/total table and beneath that shows a list of each Project
with it's corresponding funding rollup table. Eventually I will have to go
one layer deeper to the Type of Funding as well.

I thought of using a continous form, but you can not have subforms (i.e the
funding table) in a continious form. My next thought is to create subforms on
the fly based on what Program the user selected, but that would also mean
creating subsubforms with the corresponding funding table on the fly.

Does anyone have any thoughts, hints code, direction on either another way
to accomplish this or any easy way to create sub and subsub forms on the
fly/dynamically. I did not know if pivot tbales would help her because I have
never used them. Any help is greatly appreciated!

 

[Guest]

Ok, just move everything up a layer and backwards.
Tested - - -

Put a Program (drop down list) for the user to pick in the header. Expand
header to accomodate the funding datasheet subform.

Then you can have continous form in the detail.

 

[Guest]

How do I display the subform-Project? Since it contains a subform a can not
use a continoius form, but that is what the user wants to see on the
form-Program a list of the projects in the program each with its
corresponding funding table.

 

[Guest]

You have your tables laid out like a spreadsheet. You need to change to
something like this --
Progam --
ProgID - autonumber - primary key
Title - text
Remarks - memo

Project --
ProjID - autonumber - primary key
ProgID - number - integer - foreign key
Title - text
Remarks - memo

ProjFunding --
ProjID - number - integer - foreign key
Funding Year - text - 4 character
Requested - number - general
Actual - number - general
Remarks - memo

Use main form-Program, subform-Project, subform datsheet view in Project
subform for funding.

The main form can have a Program (drop down list) for the user to pick. Have
criteria in query that is record source for main form so that all is
displayed until the user picks.