How to add a sum text box to a form

[Spidey3721]

My current setup is this:

I have a form with one text box: ProjectID (name of the project), data
property is set to the "General Project info" table, which has the
ProjectID, address, project description, etc...

This form has a subform, which shows all costs associated with this project.
This subform's data property is set to the cost amounts table, which has a
foreign key for ProjectID and includes different cost line items.

Question: How do I include a total for the line items that are showing. I
have tried playing with the sum row in a query, but can't figure that out. I
want a total of only the cost line items which are showing in the subform,
which are dictated by which ProjectID is showing on the main form.

 

[Gary Miller]

Spidey,

Put a textbox in the Footer of your subform and set the
controlsource to...

=Sum([YourCostField])

If you need it on the main form, you could use DSum() to
pull the values from the subforms table.

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________

 

[Spidey3721]

I will look into the DSum function....

As for the textbox in the footer - it does not appear that I can get a
textbox to show up in my subform footer because I have the default view of
the subform set to "datasheet"

I want to be able to see all line items at once (for the ProjectID that is
displayed in the main form) and datasheet seems to be the only way to do
this ??? If I choose single form then I have to navigate through all the
cost line item records (subform) after navigating to the ProjectID on the
main.....

Spidey,

Put a textbox in the Footer of your subform and set the
controlsource to...

=Sum([YourCostField])

If you need it on the main form, you could use DSum() to
pull the values from the subforms table.

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________

My current setup is this:

I have a form with one text box: ProjectID (name of the project), data
property is set to the "General Project info" table, which has the
ProjectID, address, project description, etc...

This form has a subform, which shows all costs associated with this project.
This subform's data property is set to the cost amounts table, which has a
foreign key for ProjectID and includes different cost line items.

Question: How do I include a total for the line items that are showing. I
have tried playing with the sum row in a query, but can't figure that out. I
want a total of only the cost line items which are showing in the subform,
which are dictated by which ProjectID is showing on the main form.

 

[Gary Miller]

You can design a subform to look almost identical to a
datasheet by making it one thin row and setting to be
continuous. You would then get the footer capability.

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________

I will look into the DSum function....

As for the textbox in the footer - it does not appear that I can get a
textbox to show up in my subform footer because I have the default view of
the subform set to "datasheet"

I want to be able to see all line items at once (for the ProjectID that is
displayed in the main form) and datasheet seems to be the only way to do
this ??? If I choose single form then I have to navigate through all the
cost line item records (subform) after navigating to the ProjectID on the
main.....

Spidey,

Put a textbox in the Footer of your subform and set the
controlsource to...

=Sum([YourCostField])

If you need it on the main form, you could use DSum() to
pull the values from the subforms table.

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________

My current setup is this:

I have a form with one text box: ProjectID (name of

the
project), data

property is set to the "General Project info" table,

which
has the

ProjectID, address, project description, etc...

This form has a subform, which shows all costs

associated
with this project.

This subform's data property is set to the cost

amounts
table, which has a

foreign key for ProjectID and includes different cost

line
items.

Question: How do I include a total for the line items

that
are showing. I

have tried playing with the sum row in a query, but

can't
figure that out. I

want a total of only the cost line items which are

showing
in the subform,

which are dictated by which ProjectID is showing on

the
main form.

 

[Spidey3721]

Great - I will try that as well.

I have been trying to use dsum - I apologize for my inexperience - can you
tell me what I might be doing wrong here ?

In expression builder for the textbox control source :
=DSum ( [name of query that sums up costs by ProjectID]![name of sum field
in that query] ,[name of query that sums up costs by ProjectID]!, [name of
query that sums up costs by ProjectID]![Project ID] = Forms![name of main
form]![Project ID] )

Maybe the domain portion is wrong? or maybe I'm just completely lost on the
use of dsum...

You can design a subform to look almost identical to a
datasheet by making it one thin row and setting to be
continuous. You would then get the footer capability.

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________

I will look into the DSum function....

As for the textbox in the footer - it does not appear that I can get a
textbox to show up in my subform footer because I have the default view of
the subform set to "datasheet"

I want to be able to see all line items at once (for the ProjectID that is
displayed in the main form) and datasheet seems to be the only way to do
this ??? If I choose single form then I have to navigate through all the
cost line item records (subform) after navigating to the ProjectID on the
main.....

Spidey,

Put a textbox in the Footer of your subform and set the
controlsource to...

=Sum([YourCostField])

If you need it on the main form, you could use DSum() to
pull the values from the subforms table.

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
My current setup is this:

I have a form with one text box: ProjectID (name of the
project), data
property is set to the "General Project info" table, which
has the
ProjectID, address, project description, etc...

This form has a subform, which shows all costs associated
with this project.
This subform's data property is set to the cost amounts
table, which has a
foreign key for ProjectID and includes different cost line
items.

Question: How do I include a total for the line items that
are showing. I
have tried playing with the sum row in a query, but can't
figure that out. I
want a total of only the cost line items which are showing
in the subform,
which are dictated by which ProjectID is showing on the
main form.

 

[John Vinson]

I have been trying to use dsum - I apologize for my inexperience - can you
tell me what I might be doing wrong here ?

In expression builder for the textbox control source :
=DSum ( [name of query that sums up costs by ProjectID]![name of sum field
in that query] ,[name of query that sums up costs by ProjectID]!, [name of
query that sums up costs by ProjectID]![Project ID] = Forms![name of main
form]![Project ID] )

Maybe the domain portion is wrong? or maybe I'm just completely lost on the
use of dsum...

The DSum (and all the domain functions) take three arguments, which
must all be TEXT STRINGS.

The first argument is the name of the field to sum; the second
argument is the name of the table or query containing that field; and
the third (optional) argument is a SQL WHERE clause without the word
WHERE, selecting the desired records. In your case it should be
something like

=DSum("[name of sum field]", "[name of the query]", "[ProjectID] = " &
[Forms]![name of main form]![Project ID])

Or if the query is already doing the Sum, use DLookUp instead of DSum
- though this will be slower since it will use both a totals query
*and* a (often slow) domain function. Perhaps you can use

=DSum("[name of field to sum]", "[name of table]", "[ProjectID] = " &
[Forms]![name of main form]![Project ID])

so that DSum does the summing for you straight from the source table.