Counting Row Values (1 Viewer)

Lochwood

Registered User.
Local time
Today, 02:38
Joined
Jun 7, 2017
Messages
130
Ok, i am trying to count the values of field Activity which is a numeric field in 10 rows/records. i can use the totals command in the query that lets me choose the field and it counts up perfect on datasheet view. The issue i have is i cannot get this total onto a report as it doesn't show available to add when i add existing field.

What a i doing wrong?
 

Minty

AWF VIP
Local time
Today, 09:38
Joined
Jul 26, 2013
Messages
10,355
Can you show us your SQL query ?
 

Lochwood

Registered User.
Local time
Today, 02:38
Joined
Jun 7, 2017
Messages
130
SELECT Sources.Source_ID, Sources.Serial_Number, Sources.Date_Of_Receipt, Sources.Form, Sources.Nuclide, Sources.Activity, Assets.Asset_Description, Sources.Reference_Date, Sources.Estimated_Activity_On_Receipt, Sources.Active_Dimensions, Sources.Overall_Dimensions, Sources.Description, Sources.Last_Test_Date, Sources.Test_Validity_Months, Sources.Risk_Assessment, Sources.Current_Location, Sources.Disposal_Date, Sources.How_Disposed
FROM Sources INNER JOIN Assets ON Sources.Serial_Number = Assets.Asset_Serial_Number
GROUP BY Sources.Source_ID, Sources.Serial_Number, Sources.Date_Of_Receipt, Sources.Form, Sources.Nuclide, Sources.Activity, Assets.Asset_Description, Sources.Reference_Date, Sources.Estimated_Activity_On_Receipt, Sources.Active_Dimensions, Sources.Overall_Dimensions, Sources.Description, Sources.Last_Test_Date, Sources.Test_Validity_Months, Sources.Risk_Assessment, Sources.Current_Location, Sources.Disposal_Date, Sources.How_Disposed;
 

Minty

AWF VIP
Local time
Today, 09:38
Joined
Jul 26, 2013
Messages
10,355
Okay - try adding the sum to your select and then it will become a field you can display

Code:
SELECT Sources.Source_ID, Sources.Serial_Number, Sources.Date_Of_Receipt, Sources.Form, Sources.Nuclide, Sources.Activity, Assets.Asset_Description, Sources.Reference_Date, Sources.Estimated_Activity_On_Receipt, Sources.Active_Dimensions, Sources.Overall_Dimensions, Sources.Description, Sources.Last_Test_Date, Sources.Test_Validity_Months, Sources.Risk_Assessment, Sources.Current_Location, Sources.Disposal_Date, Sources.How_Disposed, [COLOR="Red"]Sum(Sources.Activity) as TotalActivity[/COLOR]
FROM Sources INNER JOIN Assets ON Sources.Serial_Number = Assets.Asset_Serial_Number
GROUP BY Sources.Source_ID, Sources.Serial_Number, Sources.Date_Of_Receipt, Sources.Form, Sources.Nuclide, Sources.Activity, Assets.Asset_Description, Sources.Reference_Date, Sources.Estimated_Activity_On_Receipt, Sources.Active_Dimensions, Sources.Overall_Dimensions, Sources.Description, Sources.Last_Test_Date, Sources.Test_Validity_Months, Sources.Risk_Assessment, Sources.Current_Location, Sources.Disposal_Date, Sources.How_Disposed;
 

Lochwood

Registered User.
Local time
Today, 02:38
Joined
Jun 7, 2017
Messages
130
Yeh tried this bit it wont accept the code. see image,of what query looks like in data view.

Capture.PNG
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:38
Joined
May 7, 2009
Messages
19,175
I think he want to Count
 

Minty

AWF VIP
Local time
Today, 09:38
Joined
Jul 26, 2013
Messages
10,355
That is a Access total row, not a field.
It's a nice display function they provide that provides no help for you in real form design unfortunately.

I can't remember what setting turns that on and off as I never use it...
 

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 05:38
Joined
Oct 17, 2012
Messages
3,276
You said this is for a report in the first post. Do you mean an actual report object?

If yes, then what you really want is the Group and Sort feature in the report's design screen. It should be in your Design tab in the ribbon when you're in design mode, and it will open up a 'Group, Sort, and Total' window at the bottom of your screen.

That will let you, well, group and sort and automatically add totals to the bottom of any group. (You can also create the totals by hand - they're just a textbox whose control source is something along the line of '=Sum([FieldToBeSummed]).)
 

Users who are viewing this thread

Top Bottom