trying to sum a group of records but "sum" missing from Group, Sort, and Total dropdown/wizard

  • Thread starter Thread starter Repent
  • Start date Start date
R

Repent

I have a report that is based on several tables and lookups.

when I'm in layout view and I select Group & Sort from the Grouping &
Totals tab I can Group my records by the "productiondate" field then
when I select the Totals dropdown in the Group & Sort feature, select
the field of "LF Run" where it says Total On, the "type" changes from
"Sum" to only show the type of "Count values" or "Count Records". The
Sum, along with average, min, max, etc are ghosted and not available.

There must be something wrong with my tables or something to make
these options not available.

Can anyone help or lend some insight?
 
As a guess Access believes that LF Run is not a number field. Is it by chance
a text field that contains numbers or a calculated field that you have
formatted with the format function. Format changes number type to string type.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
The property sheet for "LF Run" shows that it is a text box. it
contains numbers. Is this t he problem? How can I fix that?
 
The control is a text box. The control has a control source and usually that
is a FIELD in a TABLE or QUERY.

What is the field type in the table? If it is text then you cannot sum it.
If it is a number then you should be able to sum it.

If you are using the table directly, you have to change the field type. If
you are using a query as the source you can use VAL function to change the
text to a number type. You would need a calculated column to do this. OR
open the report in design view, change the control name to txtLFRun and then
change its source to
=Val([Lf Run] & "")

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
John;

thanks for the info and the leads! I changed the type to "numbers"
instead of "text" in the field type in the table (as I am pulling the
data from a table field). That did the trick.
I could then see that "sum" was unghosted and available to me.
I could then also create groups using the group & sort wizards and get
the totals I am looking for.

Once again, thanks for the info. I wracked my brain way too long
yesterday on this.

chris



The control is a text box. The control has a control source and usually that
is a FIELD in a TABLE or QUERY.

What is the field type in the table? If it is text then you cannot sum it.
If it is a number then you should be able to sum it.

If you are using the table directly, you have to change the field type. If
you are using a query as the source you can use VAL function to change the
text to a number type. You would need a calculated column to do this. OR
open the report in design view, change the control name to txtLFRun and then
change its source to
=Val([Lf Run] & "")

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
The property sheet for "LF Run" shows that it is a text box. it
contains numbers. Is this t he problem? How can I fix that?
Click to expand...
Click to expand...
 
You need to convert but it depends on the accuracy you want.
If the values are all whole numbers, no decimal places then use CInt([LF
Run]) to translate.
Otherwise number - single, number - double, or curency.
Csng CDbl CCur
 
Apparently your report thinks [LF Run] is a text value. Is this calculated in
your report's record source query or is it actually a field?
 
John;

now that I have that working....

I want to find the waste% using the sums available. For example, I
have summed up colums for LF Run, LF Produced, and waste.

I want the waste% based on dividing the summed totals of waste by the
summed totals of LF Run.

I tried the code of =Sum([waste])/Sum([LFrun]) but when i run that it
asks for a value, like a query parameter. I just want it to take the
summed value for waste and divide it by the summed value for LFrun.

I thought the code i show above would do that. What do I have wrong?

chris





John;

thanks for the info and the leads! I changed the type to "numbers"
instead of "text" in the field type in the table (as I am pulling the
data from a table field). That did the trick.
I could then see that "sum" was unghosted and available to me.
I could then also create groups using the group & sort wizards and get
the totals I am looking for.

Once again, thanks for the info. I wracked my brain way too long
yesterday on this.

chris



The control is a text box. The control has a control source and usually that
is a FIELD in a TABLE or QUERY.

What is the field type in the table? If it is text then you cannot sum it.
If it is a number then you should be able to sum it.

If you are using the table directly, you have to change the field type. If
you are using a query as the source you can use VAL function to change the
text to a number type. You would need a calculated column to do this. OR
open the report in design view, change the control name to txtLFRun and then
change its source to
=Val([Lf Run] & "")

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
The property sheet for "LF Run" shows that it is a text box. it
contains numbers. Is this t he problem? How can I fix that?




As a guess Access believes that LF Run is not a number field. Is it by chance
a text field that contains numbers or a calculated field that you have
formatted with the format function. Format changes number type to string type.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Repent wrote:
I have a report that is based on several tables and lookups.

when I'm in layout view and I select Group & Sort from the Grouping &
Totals tab I can Group my records by the "productiondate" field then
when I select the Totals dropdown in the Group & Sort feature, select
the field of "LF Run" where it says Total On, the "type" changes from
"Sum" to only show the type of "Count values" or "Count Records". The
Sum, along with average, min, max, etc are ghosted and not available.

There must be something wrong with my tables or something to make
these options not available.

Can anyone help or lend some insight?
Click to expand...
Click to expand...
Click to expand...
 
Well, first thing is the field LF Run or LFRun?

Next, exactly what is the query parameter that you get requested? Usually
when you get an unexpected parameter request it involves a misspelled or
missing field in the query.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
John;

now that I have that working....

I want to find the waste% using the sums available. For example, I
have summed up colums for LF Run, LF Produced, and waste.

I want the waste% based on dividing the summed totals of waste by the
summed totals of LF Run.

I tried the code of =Sum([waste])/Sum([LFrun]) but when i run that it
asks for a value, like a query parameter. I just want it to take the
summed value for waste and divide it by the summed value for LFrun.

I thought the code i show above would do that. What do I have wrong?

chris





John;

thanks for the info and the leads! I changed the type to "numbers"
instead of "text" in the field type in the table (as I am pulling the
data from a table field). That did the trick.
I could then see that "sum" was unghosted and available to me.
I could then also create groups using the group & sort wizards and get
the totals I am looking for.

Once again, thanks for the info. I wracked my brain way too long
yesterday on this.

chris



The control is a text box. The control has a control source and usually that
is a FIELD in a TABLE or QUERY.

What is the field type in the table? If it is text then you cannot sum it.
If it is a number then you should be able to sum it.

If you are using the table directly, you have to change the field type. If
you are using a query as the source you can use VAL function to change the
text to a number type. You would need a calculated column to do this. OR
open the report in design view, change the control name to txtLFRun and then
change its source to
=Val([Lf Run] & "")

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Repent wrote:
The property sheet for "LF Run" shows that it is a text box. it
contains numbers. Is this t he problem? How can I fix that?




As a guess Access believes that LF Run is not a number field. Is it by chance
a text field that contains numbers or a calculated field that you have
formatted with the format function. Format changes number type to string type.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Repent wrote:
I have a report that is based on several tables and lookups.

when I'm in layout view and I select Group & Sort from the Grouping &
Totals tab I can Group my records by the "productiondate" field then
when I select the Totals dropdown in the Group & Sort feature, select
the field of "LF Run" where it says Total On, the "type" changes from
"Sum" to only show the type of "Count values" or "Count Records". The
Sum, along with average, min, max, etc are ghosted and not available.

There must be something wrong with my tables or something to make
these options not available.

Can anyone help or lend some insight?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top