SUM does not return a value (1 Viewer)

nancy54

Registered User.
Local time
Today, 06:18
Joined
Jun 19, 2018
Messages
49
Hello,
I have a table in my data base with several number data type fields. For some reason I can not get the SUM to return a value on any of the number fields. The COUNT and Average works, but not the SUM. Other tables in the database work fine. Is there a setting or something I am missing??

Nancy
 

Minty

AWF VIP
Local time
Today, 10:18
Joined
Jul 26, 2013
Messages
10,353
Make sure you have no null values in the data.

Null + anything you like = Null

As far as Access is concerned.

If you need to include data with Nulls then use Sum(NZ(YourField,0))
 

nancy54

Registered User.
Local time
Today, 06:18
Joined
Jun 19, 2018
Messages
49
There are no empty cells- I have the default value set to 0.
 

jdraw

Super Moderator
Staff member
Local time
Today, 06:18
Joined
Jan 23, 2006
Messages
15,361
Please post relevant code, or a copy of the database with instructions to execute the query/code involved.
 

nancy54

Registered User.
Local time
Today, 06:18
Joined
Jun 19, 2018
Messages
49
There is no code. I am just using the Totals button in the Menu bar.
 

jdraw

Super Moderator
Staff member
Local time
Today, 06:18
Joined
Jan 23, 2006
Messages
15,361
Please post the SQL for the query that is causing the issue.
 

nancy54

Registered User.
Local time
Today, 06:18
Joined
Jun 19, 2018
Messages
49
This is not a query. It is a table with several number fields. I am clicking on the Total button on the Menu bar which displays a Totals row at the end of the records. When I click in the cell for which I want the SUM, the other functions work (such as COUNT and AVG), but not the SUM function.
 

Minty

AWF VIP
Local time
Today, 10:18
Joined
Jul 26, 2013
Messages
10,353
You shouldn't be using a table as a data entry or as a method of looking at your data, apart from development work.
In fact I've no idea why Microsoft thought that would be a good idea. Unsurprisingly it doesn't work on SQL linked tables

Create a query and I suspect this will work.
 

nancy54

Registered User.
Local time
Today, 06:18
Joined
Jun 19, 2018
Messages
49
Actually, I am using a datasheet form to work with the data, but neither the form or the table is giving me a SUM. I'll create a query from the table and try that.....

Thanks, Nancy
 

nancy54

Registered User.
Local time
Today, 06:18
Joined
Jun 19, 2018
Messages
49
It looks like I had a query after all: and thanks for your help and patience's!

SELECT GelProductWaste.ID, GelProductWaste.[Part No], GelProductWaste.[Lot Number], GelProductWaste.Description, GelProductWaste.[Batch Size], GelProductWaste.Silica, GelProductWaste.[Theoretical Lbs], GelProductWaste.[Wt / Gal], GelProductWaste.[Ended Gal], GelProductWaste.[Ended AX Lbs], GelProductWaste.[Lbs Lost], GelProductWaste.[Gal Lost], GelProductWaste.[Percent lost], GelProductWaste.[Partial Lbs], GelProductWaste.[Gal lost w/partial], GelProductWaste.Saved, GelProductWaste.Reclaimed, GelProductWaste.Filtered, GelProductWaste.[% Error w/Partial], GelProductWaste.Comment, GelProductWaste.[Time Stamp], GelProductWaste.PartConfd, GelProductWaste.[Part Recl on BC]
FROM GelProductWaste
ORDER BY GelProductWaste.ID;
 

jdraw

Super Moderator
Staff member
Local time
Today, 06:18
Joined
Jan 23, 2006
Messages
15,361
Simplified SQL for readability

Code:
SELECT ID
	,[Part No]
	,[Lot Number]
	,Description
	,[Batch Size]
	,Silica
	,[Theoretical Lbs]
	,[Wt / Gal]
	,[Ended Gal]
	,[Ended AX Lbs]
	,[Lbs Lost]
	,[Gal Lost]
	,[Percent lost]
	,[Partial Lbs]
	,[Gal lost w/partial]
	,Saved
	,Reclaimed
	,Filtered
	,[% Error w/Partial]
	,Comment
	,[Time Stamp]
	,PartConfd
	,[Part Recl on BC]
FROM GelProductWaste
ORDER BY ID;

What were you trying to SUM?
 

nancy54

Registered User.
Local time
Today, 06:18
Joined
Jun 19, 2018
Messages
49
,[Batch Size]
,[Theoretical Lbs]
,[Partial Lbs]
These are a few of the number fields that should return a sum.
I noticed a few other things:
In the _be table, I tried to change the formatting and decimal places on some of the number fields, but the properties will not take affect.
I made a copy of the _be table, structure only, populated some fields, and the all worked perfectly.
I have sorted all of the fields to check for errors but can't find anything.

Going crazy, Nancy
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:18
Joined
Feb 19, 2002
Messages
42,970
1. Make sure that the data types of the fields in question are numeric rather than text. Post a picture of the definitions if you are not sure.
2. Tables do not have "cells", nor do forms. Tables have columns or if you prefer - fields. Forms have controls. Language matters. If you call these things cells, you will think that they act like cells and they do not.
3. This "feature" doesn't always work. I don't know what causes the problem but sometimes it takes forever for the values to "sum" or the "sum" just don't appear at all.

And last but not least. Object and variable names should contain ONLY - letters (upper/lower case), numbers, and the underscore (_). NO OTHER characters should ever be used. Access may allow you to get away with using non-standard characters but you will always pay a price and occasionally run into actual problems. Best to get rid of them as soon as you can if you are still in development.
 

nancy54

Registered User.
Local time
Today, 06:18
Joined
Jun 19, 2018
Messages
49
Pat,
Thank you for replying! I understand every thing you said and I'm fairly sure I'm in compliance. What is strange is I can get the other functions (such as AVG) to return a value but not SUM. I have to export any sorts I do to Excel to get totals of my fields.
Nancy
 

jdraw

Super Moderator
Staff member
Local time
Today, 06:18
Joined
Jan 23, 2006
Messages
15,361
Nancy,

Can you provide some sample data, or a database with a few records so readers can review the issue?
 

nancy54

Registered User.
Local time
Today, 06:18
Joined
Jun 19, 2018
Messages
49
Pat,
I am attaching the a copy of the back end tables I named 'NonCon_Copy'. I have removed all text fields so I could attach the database.
Other tables in the db work ok (which I also removed).
The fields I'm have a problem (getting totals) with are in the table GelProductionWaste; fields: Theoretical Lbs, Gal Lost, Partial Lbs.

Appreciate your help, Nancy
 

Attachments

  • NonCon_copy.accdb
    480 KB · Views: 102

nancy54

Registered User.
Local time
Today, 06:18
Joined
Jun 19, 2018
Messages
49
jd,
thanks for this attachment. Ill study it more thoroughly and consider it for future databases, but some of the fields I'm have a problem with are not calculated fields.
Nancy
 

jdraw

Super Moderator
Staff member
Local time
Today, 06:18
Joined
Jan 23, 2006
Messages
15,361
I'm not sure what you are tying to do nor what you have tried. I changed the format of fields to Double rather than Decimal (after a warning that the field was too small)

Code:
SELECT Sum([Copy Of GelProductWaste].[Theoretical Lbs]) AS [SumOfTheoretical Lbs]
, Sum([Copy Of GelProductWaste].[Gal Lost]) AS [SumOfGal Lost]
, Sum([Copy Of GelProductWaste].[Partial Lbs]) AS [SumOfPartial Lbs]
FROM [Copy Of GelProductWaste];

and had this result

Code:
SumOfTheoretical Lbs	    SumOfGal Lost	           SumOfPartial Lbs
60341.99	                  121.002785457472	           154.9
 

Users who are viewing this thread

Top Bottom