On Oct 13, 12:25 pm, "Ken Snell \(MVP\)"
<kthsneisll...@ncoomcastt.renaetl> wrote:

Form: frmBudgetForm.
RecordSource: qryBudget
SELECT [GoalNumber], [GoalBudget]

UNION
SELECT [Level2Number], [Level2Budget]
FROM [tblLevel2Goals];
UNION
SELECT [Level3Number], [Level3Budget]
FROM [tblLevel3Goals]
UNION
SELECT [Level4Number], [Level4Budget]
FROM [tblLevel4Goals]
UNION
SELECT [Level5Number], [Level5Budget]
FROM [tblLevel5Goals]
UNION SELECT [Level6Number], [Level6Budget]
FROM [tblLevel6Goals];

Subform was created within the form, called qryBudget subform
RecordSouce: SELECT tblBudget.BudgetLineID,
tblBudget.BudgetGoalNumber, tblBudget.Amount, tblBudget.BudgetCategory
FROM tblBudget;

On frmBudgetForm the only field is a text box TotalBudget=[qryBudget
subform].Form!TotalAmount
On qryBudget subform the fields are BudgetLineID (text box),
BudgetGoalNumber (Text box), Amount (text box) and Combo8 which is the
Budget categories: SELECT tblBudgetCategories.BudgetCategoryID,
tblBudgetCategories.BudgetCategory FROM tblBudgetCategories;
In the form footer I have a hidden control text box
TotalAmount=Sum([Amount])

Sample data might be:
ID: 1
Goal Number: 1
Amount: $100.00
Category: Travel

ID: 2
Goal Number: 1.1
Amount: $50.00
Category: Office Supplies

ID: 3
Goal Number: 1.1
Amount $25.00
Category: Travel

ID: 4
Goal Number: 1.1.1
Amount: $50.00
Category: Misc

The goals go down 6 levels to 1.2.3.4.5.6, and I need to know a total
amount of all the subgoals for any particular goal. So for instance,
if I open the form to goal 1.1, I need to have it add the specific
line items that apply to 1.1, but also the line items that apply to
1.1.2, 1.1.2.3, etc., essentially to GoalNumber*.

Should I change the record source of frmBudgetForm to tblBudget? I
think one of the problems I have is I don't know how to relate the
subsubform data to the subform or to the actual form. It doesn't seem
like a direct relationship to me, and it seems like one that would
have to be created artificially.

Thanks so much.