• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 438
  • Last Modified:

When creating a table in MS Access 2007. How do I sum a series of detailed records and assign it a field holder (store the sum) in an Access form?

I have a simple detail table that takes a series of suppliers that send us a commodity and the columns need to be summed.
                                                    Suppliers       Weights

                                                     ACME            1000 lbs
                                                     ABC Co         1200 lbs
                                                     XYZ Co          1150 lbs
                     I need the count of suppliers and total weights summary below
                                                         3                3350 lbs

I think I need to create a field for each summary ten some how build the formula or expression that will add those up. Is that right and do you have an example in steps of how to do that?
0
Fletcher Burdine
Asked:
Fletcher Burdine
4 Solutions
 
rockiroadsCommented:
no field, you should never created fields you build on calculations

sql (queries) can handle this

count returns count of rows
sum returns total

eg create a query

select count(*) as TotalSuppliers, SUM(Weights) AS TotalWeights
from mytable

if unfamilair with queries you can do this in design view also
0
 
Dale FyeCommented:
a single query will do this.

SELECT COUNT(Suppliers) as CountOfSuppliers, SUM(Weights) as SumOfWeights
FROM yourTable

To do this from the query grid, drag the suppliers and weights fields into the grid.  Then click the "Totals" button in the Query Design ribbon, and change the Total Row in the query grid to Count and Sum (as appropriate).
0
 
rockiroadsCommented:
non programmatic way

open up new query, go to design, select your table and fields you want (suppliers and weights)

below on the field, right click and select totals

in the totals row select COUNT on the suppliers and SUM on the weights

then run your query. The sql generated should look something like what I posted
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
AccessYourBiz_ComCommented:
The easiest way to do this is to create a totals row within a datasheet veiw form. To learn how to do that you can google
"Access 2007 add total row" or just follow this link.
http://www.ehow.com/how_2125021_use-access-2007-aggregate-functions.html
 
0
 
Fletcher BurdineTableau Trainer & Consultant Sales Exec.Author Commented:
Sorry I forgot to get back to this one. Great stuff my appologies for the late rewards.
0
 
rockiroadsCommented:
No worries and thanks :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now