Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 435
  • 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
ruavol2
Asked:
ruavol2
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
ruavol2Tableau 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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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