A3:
You can pass the value of a text box in the subform back to the parent form.
For example, if the subform control is named Child1, and the text box total
(in the subform's Form Footer section) is named txtTotal, use a Control
Source of:
=[Child1].[Form]![txtTotal]
If you can't get that to work, or if you want the total on a form/report
where there is no subform, you could use a subquery or a DLookup()
expression. To build a subquery, see:
http://allenbrowne.com/subquery-01.html
To use a DLookup() expression:
- Create a query that uses jnct_Project_Scope and Contracts.
- Group by the ProjectID, and sum the Amount.
- Something like this:
SELECT jnct_Project_Scope.ProjectID,
Sum(Contracts.[Contract Amount]) AS ProjectAmount
FROM jnct_Project_Scope INNER JOIN Contracts
ON jnct_Project_Scope.ProjectID = Conracts.ProjectID
GROUP BY jnct_Project_Scope.ProjectID;
Now you can get the total on any form or report that has a ProjectID field,
using a text box with ControlSource of:
=DLookup("ProjectAmount", "Query1", "ProjectID = " & Nz([ProjectID],0))
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
boooney said:
A1. I should've thought of that! By the way, your website is very
helpful.
I found it on Google.
A2. I've tried that already, but if I do that, I cannot for the life of me
get the total to work on the parent form. I've tried the total in the
form
footer and the totals don't work on the parent forms. That leads me to
Q3.
Q3:
Background
I have a one to many link from Project -> jnct_Project_Scope and a one to
many link from jnct_Project_Scope -> Contracts. i.e. each project has
many
scopes and each scope has many contracts.
I followed your website instructions and the only place I'm storing the
amount is in the Contracts.Contract Amount field.
I want to have 2 forms showing:
*total scope amounts for each project (i.e. total project budget) on a
form/subform that lists scopes and scope amounts by project and
*total contracts for each scope on a form/subform that lists contracts by
scope?
Allen Browne said:
A1. Your query aggregates values (GROUP BY clause.)
More info in:
Why is my query read-only?
at:
http://allenbrowne.com/ser-61.html
A2. Use a subform to show the related data.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
Hi-
My form is based on the following SQL statement:
SELECT Project.Project_ID, Project.[Enter Project Name], Project.[CEDC
Project Code], Sum([Contracts.Contract Amount]) AS Amount
FROM Project INNER JOIN (jnct_Project_Scope INNER JOIN Contracts ON
jnct_Project_Scope.Project_Scope_ID = Contracts.Project_Scope_ID) ON
Project.Project_ID = jnct_Project_Scope.Project_ID
GROUP BY Project.Project_ID, Project.[Enter Project Name],
Project.[CEDC
Project Code];
Q1: I can't figure out why it won't let me add or edit Project records.
Q2: How can I include just the Project but all Scopes with linking
Project_IDs (right now, it only shows Scopes that have Contract records
as
children)?
Thanks very much.
