Let's start with a clear picture of your form and subform design --
include
the SQL statements for the form's and subform's RecordSources. Tell us
about
the actual data -- field names, table names, examples of data values.
If you're not including the "subsubgoal" data in the subform, then how
are
those data associated to the subgoal data?
--
Ken Snell
<MS ACCESS MVP>
On Oct 13, 11:22 am, "Ken Snell \(MVP\)"
Put a textbox in the subform's Form Footer section. Set its Control
Source
to
=Sum([NameOfFieldThatYouWantToSum])
Ken Snell
<MS ACCESS MVP>
I have a project for a client that is essentially goals and subgoals,
so for instance, you have goal 1 and subgoal 1.1. I've created a
table
tblBudget with fields BudgetLineID, BudgetGoalNumber, Amount and
BudgetCategory (which comes from a separate categories table). I
created a form with a subform to enter the different budget amounts,
it opens from the form which you enter the goals and takes the goal
number as a filter for the budget form. The client would like the
budget to display the sum of all the subgoals, as well as have the
opportunity to enter ine items for that goal.
So for instance, if I open the budget form for goal 1, it has the
subform which I've already created which shows you the line items
for
goal 1, and then has another box which has a sum of goals 1.1, 1.2,
1.1.1, etc.
Any hope? Thanks!- Hide quoted text -
I do have a box on the subform that sums up the totals on the subform,
but the part i'm having trouble with is getting the other subgoals.
For instance, the subform has line items for goal 1.1, and I need to
have the total of goals 1.1.1, 1.1.2, 1.1.1.1, etc., and those goals
are not on the subform. It's essentially the sum of goal 1.1*, I just
don't knwo how to code that. Thanks.
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.
