Select Query to create a Total field

[Jim Dudley]

I have a query that selects records form a table.
I Total 5 fields in each record and want the total result to appear in my
table and split form.

The query works and returns the totals I require in a column Expr1.


How do I get these results to appear as a column in my table?

How do I get this result to appear in a field in my split form and
underlying table?

If I edit or change a value in one or several of the fields, how do I get
the query to update the data in the table and form.

Any help for this novus would be appreciated.

Thank you

 

[Duane Hookom]

Jim,
You generally don't want to store a value that can be calculated. Also,
adding values across fields in a table generally suggests an un-normalized
table structure.

Apparently your query already works. I would change the name of "Expr1" to a
name that makes sense. The calculation will update when the record is updated.

 

[Jim Dudley]

I realize that I do not want to store the value but I want the table and
related form to show the results of Expr1: as a column or field in both.

When an edit or change is made to one of the underlying fields that part of
the query total expr: What to I have to do to get the new result reflected in
the datasheet view? Do I have to close and rerun the query or will the
refresh function update the reslults?

 

[Duane Hookom]

Your calculated column will update as soon as you update a field value in the
record.

What is happening in your form when you edit a field value?

 

[Jim Dudley]

I run the query and it updates the total data but neither my form nor the
actal table show any change.

In order for the query to update, I have to close and re-open it.

The form and the table do not seem to be connected to the select query.

How do I add the query column to display in the table and in the split form.

JD

 

[Duane Hookom]

You need to display the total expression in your form. Don't worry about the
table since users shouldn't see your table.

If you really think you need to store the value in the table, you should
provide some justification.

 

[Jim Dudley]

I understand it not being in the table.

How do I get the results of the query:
Expr1:
NZ([Workshop_Credits]![UL_Credits])+Nz([Workshop_Credits]![OD_Credits])+Nz([Workshop_Credits]![PD_Credits])+Nz([Workshop_Credits]![PL_Credits])+Nz([Workshop_Credits]![GC_Credits])

to appear in my form?

I added a text box to the form and put as its' control source both the above
expresssion and also tried the query reference [QryWorshop_Credits]![Expr1]

Both returned the following in the Control: #Name?

What am I doing wrong?

We need to know the Total number of Credits each student has in each of the
5 categories. Students earn Ceritificates over a 4 year University Term, the
level of the Certificate is determined by the number of credits they have
earned in each category. There are 5 Levels of Certificates.

Thank you.....

JD

 

[Jim Dudley]

Sorry, I made one ommission in what we the total by category for.
The students need so many credits in the categories and the total reflects
the Level of the certificatel

i.e.
3 credits in El
3 credits in PD
3 credits in OD
Total 9 credits earns Level 1
Level 2 is earned after total 17 credits the totals coming the 5 groups.

Hope this makes the need for the total more clear.

Later we will select records by Totals >=9 if the have at least 3 credits in
the aforementioned queries.

This info allows to issue certificates and notify students of their status
toward certificates.

We have approx 10,000 students on this campus and a total of 65,000 in all.

Thank you.

JD

 

[Duane Hookom]

I still think your table structure is wrong. I would not store credit types
in field names.

However, does your form record source contain all of these fields? Why not
just add the calculated column in your form's record source query?

If you want to just use a text box, make sure the name of the text box is
not the name of a field. Try set the control source to:

=NZ([UL_Credits],0) + Nz([OD_Credits],0) + Nz([PD_Credits],0) +
Nz([PL_Credits],0)+ Nz([GC_Credits],0)


--
Duane Hookom
Microsoft Access MVP

I understand it not being in the table.

How do I get the results of the query:
Expr1:
NZ([Workshop_Credits]![UL_Credits])+Nz([Workshop_Credits]![OD_Credits])+Nz([Workshop_Credits]![PD_Credits])+Nz([Workshop_Credits]![PL_Credits])+Nz([Workshop_Credits]![GC_Credits])

to appear in my form?

I added a text box to the form and put as its' control source both the above
expresssion and also tried the query reference [QryWorshop_Credits]![Expr1]

Both returned the following in the Control: #Name?

What am I doing wrong?

We need to know the Total number of Credits each student has in each of the
5 categories. Students earn Ceritificates over a 4 year University Term, the
level of the Certificate is determined by the number of credits they have
earned in each category. There are 5 Levels of Certificates.

Thank you.....

JD



--
Thanks in advance...

Jim

I run the query and it updates the total data but neither my form nor the
actal table show any change.

In order for the query to update, I have to close and re-open it.

The form and the table do not seem to be connected to the select query.

How do I add the query column to display in the table and in the split form.

JD

 

[Jim Dudley]

Thank you for patience, I really appreciate it.

Your last solution using the NZ([field],0)+etc worked like a charm. It
worked both in the original field I had created and in the text box I was
trying.

Have a great day!!!!
--
Thanks in advance...

Jim

I still think your table structure is wrong. I would not store credit types
in field names.

However, does your form record source contain all of these fields? Why not
just add the calculated column in your form's record source query?

If you want to just use a text box, make sure the name of the text box is
not the name of a field. Try set the control source to:

=NZ([UL_Credits],0) + Nz([OD_Credits],0) + Nz([PD_Credits],0) +
Nz([PL_Credits],0)+ Nz([GC_Credits],0)


--
Duane Hookom
Microsoft Access MVP

I understand it not being in the table.

How do I get the results of the query:
Expr1:
NZ([Workshop_Credits]![UL_Credits])+Nz([Workshop_Credits]![OD_Credits])+Nz([Workshop_Credits]![PD_Credits])+Nz([Workshop_Credits]![PL_Credits])+Nz([Workshop_Credits]![GC_Credits])

to appear in my form?

I added a text box to the form and put as its' control source both the above
expresssion and also tried the query reference [QryWorshop_Credits]![Expr1]

Both returned the following in the Control: #Name?

What am I doing wrong?

We need to know the Total number of Credits each student has in each of the
5 categories. Students earn Ceritificates over a 4 year University Term, the
level of the Certificate is determined by the number of credits they have
earned in each category. There are 5 Levels of Certificates.

Thank you.....

JD



--
Thanks in advance...

Jim

I run the query and it updates the total data but neither my form nor the
actal table show any change.

In order for the query to update, I have to close and re-open it.

The form and the table do not seem to be connected to the select query.

How do I add the query column to display in the table and in the split form.

JD
--
Thanks in advance...

Jim


:

I have a query that selects records form a table.
I Total 5 fields in each record and want the total result to appear in my
table and split form.

The query works and returns the totals I require in a column Expr1.


How do I get these results to appear as a column in my table?

How do I get this result to appear in a field in my split form and
underlying table?

If I edit or change a value in one or several of the fields, how do I get
the query to update the data in the table and form.

Any help for this novus would be appreciated.

Thank you