SUM does not return a value

[nancy54]

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]

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]

There are no empty cells- I have the default value set to 0.

 

[jdraw]

Please post relevant code, or a copy of the database with instructions to execute the query/code involved.

 

[nancy54]

There is no code. I am just using the Totals button in the Menu bar.

 

[jdraw]

Please post the SQL for the query that is causing the issue.

 

[nancy54]

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]

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]

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]

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]

Simplified SQL for readability

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]

,[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

 

[jdraw]

??? Not following...
Here is a link to syntax and examples of SQL SUM

 

[nancy54]

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]

Nancy,

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

 

[nancy54]

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

 

[jdraw]

Nancy,

See this from Allen Browne re Calculated Fields

Also, Pat's recommendations on naming convention are important.

Good luck.

 

[nancy54]

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]

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)

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

SumOfTheoretical Lbs	    SumOfGal Lost	           SumOfPartial Lbs
60341.99	                  121.002785457472	           154.9