How can I calculate line total in msaccess query?

[Guest]

How can I calculate line total in msaccess query?
Please give me an example or other way.

 

[Rick B]

Just add a new column to your query with...

LineTotal: [SomeField1] + [SomeField2] + [SomeField3]

Etc.

 

[Guest]

Dear Rick,
How can I do this total calculation?
=Sum(field name) in criteria?
------------------------------------------------------

Just add a new column to your query with...

LineTotal: [SomeField1] + [SomeField2] + [SomeField3]

Etc.



--
Rick B

How can I calculate line total in msaccess query?
Please give me an example or other way.

 

[Rick B]

That is a column total, not a line total.

I don't think you can list all the items AND include a total in one query.

--
Rick B

Dear Rick,
How can I do this total calculation?
=Sum(field name) in criteria?
------------------------------------------------------

Just add a new column to your query with...

LineTotal: [SomeField1] + [SomeField2] + [SomeField3]

Etc.



--
Rick B

How can I calculate line total in msaccess query?
Please give me an example or other way.

 

[Guest]

Sorry, that is a line total. I hope I can total that. But please tell me
how to do
-----------------------------------------------------------------------------------

That is a column total, not a line total.

I don't think you can list all the items AND include a total in one query.

--
Rick B

Dear Rick,
How can I do this total calculation?
=Sum(field name) in criteria?
------------------------------------------------------

Just add a new column to your query with...

LineTotal: [SomeField1] + [SomeField2] + [SomeField3]

Etc.



--
Rick B



How can I calculate line total in msaccess query?
Please give me an example or other way.

 

[Rick B]

No, that is a COLUMN total.
Columns go up and down (vertical). They contain data from multiple records
for one field.

Lines (actually called rows) go accross and contain information from one
record for multiple fields.

If you want a column total (all the values from a column added together) you
can create a total query. You can also create a separate detail query. I
don't think you can have them both in one query.

If you want a line total (several fields on one line/record added together)
then you'd create a new column on your LINE and put in a formula as I
indicated.

--
Rick B

Sorry, that is a line total. I hope I can total that. But please tell me
how to do.
-------------------------------------------------------------------------- ---------

That is a column total, not a line total.

I don't think you can list all the items AND include a total in one query.

--
Rick B

Dear Rick,
How can I do this total calculation?
=Sum(field name) in criteria?
------------------------------------------------------
:

Just add a new column to your query with...

LineTotal: [SomeField1] + [SomeField2] + [SomeField3]

Etc.



--
Rick B



How can I calculate line total in msaccess query?
Please give me an example or other way.

 

[Guest]

oh I got what you said Rick. Thanks. You told me lineTotal: [SomeField1] +
[SomeField2] + [SomeField3]
In my program there are some members in a mess membership. Mess will
calculate monthly. For example if there is 5 people in the mess end of month
mess
manager will calculate. I need total members' total mess amount. In case
sometimes one will added or deleted. The way you told is ok for small head
count.
But say if the mess run by a big company more than 5,000 head count we
cannot do the way you told. Also this calculation will be from a sub form.
in sub form any time we can add or delete persons. In this case what will do?

Would appreciate your information.

No, that is a COLUMN total.
Columns go up and down (vertical). They contain data from multiple records
for one field.

Lines (actually called rows) go accross and contain information from one
record for multiple fields.

If you want a column total (all the values from a column added together) you
can create a total query. You can also create a separate detail query. I
don't think you can have them both in one query.

If you want a line total (several fields on one line/record added together)
then you'd create a new column on your LINE and put in a formula as I
indicated.

--
Rick B

Sorry, that is a line total. I hope I can total that. But please tell me
how to do.
-------------------------------------------------------------------------- ---------

That is a column total, not a line total.

I don't think you can list all the items AND include a total in one query.

--
Rick B



Dear Rick,
How can I do this total calculation?
=Sum(field name) in criteria?
------------------------------------------------------
:

Just add a new column to your query with...

LineTotal: [SomeField1] + [SomeField2] + [SomeField3]

Etc.



--
Rick B



How can I calculate line total in msaccess query?
Please give me an example or other way.

 

[John Vinson]

oh I got what you said Rick. Thanks. You told me lineTotal: [SomeField1] +
[SomeField2] + [SomeField3]
In my program there are some members in a mess membership. Mess will
calculate monthly. For example if there is 5 people in the mess end of month
mess
manager will calculate. I need total members' total mess amount. In case
sometimes one will added or deleted. The way you told is ok for small head
count.
But say if the mess run by a big company more than 5,000 head count we
cannot do the way you told. Also this calculation will be from a sub form.
in sub form any time we can add or delete persons. In this case what will do?

Would appreciate your information.

In your original question you asked for a "line total".

Rick explained correctly how to calculate a "line total".

Now you are saying that you do NOT want a line total; you want a
column total. That is a different question, with a different answer.

On your Subform's footer you can put a textbox with its Control Source
set to

=Sum([fieldname])

to sum the field [fieldname] across all five (or 5000) records
displayed on the subform.

John W. Vinson[MVP]

 

[Guest]

I have done this method at home, in my laptop. The programming this way is
working properly. Msaccess version is 2002 in my laptop. But the same
program I copied and brought in my office and pasted to my computer. Colum
total in the field “SPENT†and “DAY†not working. Both fields are empty.
Why? Is this version problem?

Would appreciate your prompt reply.
-----------------------------------------------------------------

oh I got what you said Rick. Thanks. You told me lineTotal: [SomeField1] +
[SomeField2] + [SomeField3]
In my program there are some members in a mess membership. Mess will
calculate monthly. For example if there is 5 people in the mess end of month
mess
manager will calculate. I need total members' total mess amount. In case
sometimes one will added or deleted. The way you told is ok for small head
count.
But say if the mess run by a big company more than 5,000 head count we
cannot do the way you told. Also this calculation will be from a sub form.
in sub form any time we can add or delete persons. In this case what will do?

Would appreciate your information.

In your original question you asked for a "line total".

Rick explained correctly how to calculate a "line total".

Now you are saying that you do NOT want a line total; you want a
column total. That is a different question, with a different answer.

On your Subform's footer you can put a textbox with its Control Source
set to

=Sum([fieldname])

to sum the field [fieldname] across all five (or 5000) records
displayed on the subform.

John W. Vinson[MVP]

 

[John Vinson]

I have done this method at home, in my laptop. The programming this way is
working properly. Msaccess version is 2002 in my laptop. But the same
program I copied and brought in my office and pasted to my computer. Colum
total in the field “SPENT” and “DAY” not working. Both fields are empty.
Why? Is this version problem?

No, it's not a version problem. This technique has worked exactly in
the same way from Access 1.0 through Access 2003.

However, you don't say what exactly you are doing. YOU can see your
database; we cannot. Please answer some questions to clarify the
problem:

- What is the Recordsource of your Form? If it's a Query, please post
the SQL; if it's a table, please post the relevant fieldnames.
- What are the Control Source properties of the Spent and Day controls
on your Form? What are the datatypes of these fields in the table
design?
- Where on the form are you putting the totals boxes (they will only
work on the Form Footer, not on the form itself nor on the Page
Footer)? What are the exact Control Sources?

John W. Vinson[MVP]

 

[Guest]

Recordsource of my form is table. I am not much sure about SQL.
Controlsource properties of "Spent" and "Day" is
=[Table2 subform]!Text14
=[Table2 subform]!Text18 respectively.
Field name of Spent in Sub Form footer is "Text14"
Controlsource of field name "Spent" is =Sum([SPENT])
Field name of Day in Sub For footer is "Text18"
Controlsource of field name "Day" is = Sum([DAY])

 

[Guest]

SPENT and DAY fields are number datatypes.

 

[John Vinson]

Recordsource of my form is table. I am not much sure about SQL.
Controlsource properties of "Spent" and "Day" is
=[Table2 subform]!Text14
=[Table2 subform]!Text18 respectively.
Field name of Spent in Sub Form footer is "Text14"
Controlsource of field name "Spent" is =Sum([SPENT])
Field name of Day in Sub For footer is "Text18"
Controlsource of field name "Day" is = Sum([DAY])

Ok - that explains the problem. You didn't mention that you were
summing on a Subform!

Try changing the control source to

=[Table2 Subform].Form![Text14]

Also, the *field name* of Spent is not a field name: fields are in
tables, Controls are on forms. If you use the name [Spent] for both
the table field and a textbox, Access WILL get confused; you have
apparently avoided that by using the default Text14, but you might
want to name this textbox txtTotalSpent or some other meaningful name.

John W. Vinson[MVP]