How do I get the sum from a column and display the sum?

[Guest]

I didn't design the database, which was put all on one table, should've used
excel if they were gonna do it that way!

 

[Joseph Meehan]

I didn't design the database, which was put all on one table,
should've used excel if they were gonna do it that way!

I can't tell what your question really is and what the situation really
is so I will try a few guesses. If I am wrong, maybe you can give us a more
detailed question and more information.

You should be able to use the Office Links menu selection to send the
data from a table back to Excel. (Analyze with Excel). If that is what you
want.

I am not at all sure you understand databases and how they work. Often
a lot of information is put into one table and then filtered or manipulated
as needed. Usually a query is used for this. You can obtain sums with a
query.

Tables are not designed to view or work with data. They store data.
You use Forms and queries to work with the data.

If you can give us some more information, maybe we can help.

 

[Guest]

A column. A column! Data can be entered in rows (records) or columns. Access'
sum function will provide the sum of each like item, but not just a total
going straight down.

There's no need to be condescending. My comment meant that a whole database
worth of information was placed into a single table, rather than having a
different table for different objects, it was all crammed onto one. I have no
trouble writing queries, but pulling the data I need is easy, doing something
simple with it eludes me. The querie will give me a datasheet with prices
listed in a column. I would like to get a sum of the values in the column.

 

[John Vinson]

I didn't design the database, which was put all on one table, should've used
excel if they were gonna do it that way!

I have to agree with Joseph that your question (if it is a question)
is thoroughly obscure; your snarling at his attempt to be helpful was
really out of line.

If you want to see a total at the bottom of a table datasheet... You
can't. Tables are not spreadsheets and are not designed to work as
spreadsheets.

You can *display* the data together with a sum of any or all columns
by creating a Report based on the table, and putting textboxes in the
Report Footer with control source of

=Sum([column name])

John W. Vinson[MVP]

 

[Joseph Meehan]

A column. A column! Data can be entered in rows (records) or columns.
Access' sum function will provide the sum of each like item, but not
just a total going straight down.

There's no need to be condescending. My comment meant that a whole
database worth of information was placed into a single table, rather
than having a different table for different objects, it was all
crammed onto one. I have no trouble writing queries, but pulling the
data I need is easy, doing something simple with it eludes me. The
querie will give me a datasheet with prices listed in a column. I
would like to get a sum of the values in the column.

Sorry, I have no more of an idea what you have or what you are asking
than I did before.

There was no attempt to be condescending, I have no idea, even now, how
much or how little you know of Access. May well be far more than I, but I
would have to suggest that you have not shown great communication skills in
the English language in your two messages.

I am willing to try and help, if you have a question, but like everyone
else here, we are just users like you, not paid not scheduled, we offer
assistance as best we can because we want to help.

 

[Guest]

The question was simple. How to display a sum at the bottom of a column.
Thank you for answering it.

The additional information is required in order to post the question. Have
you not noticed how people often use excel when access would work? My case is
unique. Someone used access as if it were excel, placing customers, orders,
tests, products, locations, and prices all in one table.

I didn't design the database, which was put all on one table, should've used
excel if they were gonna do it that way!

I have to agree with Joseph that your question (if it is a question)
is thoroughly obscure; your snarling at his attempt to be helpful was
really out of line.

If you want to see a total at the bottom of a table datasheet... You
can't. Tables are not spreadsheets and are not designed to work as
spreadsheets.

You can *display* the data together with a sum of any or all columns
by creating a Report based on the table, and putting textboxes in the
Report Footer with control source of

=Sum([column name])

John W. Vinson[MVP]

 

[Guest]

How does one display the sum at the bottom of a column?

That is the question. Everything else was typed because you cannot post just
a question, something has to be typed in the message box.

 

[Joseph Meehan]

How does one display the sum at the bottom of a column?

That is the question. Everything else was typed because you cannot
post just a question, something has to be typed in the message box.

Usually we post the message in the message box, even when the message is
a question.

We usually call it totaling a field not a column.

~~~~~~~~~~~~~~~~~~~~~~

Total records in a query

Calculate totals on all the records in a query

Create a select query in Design view and add the tables whose records you
want to use in the calculation.

Add the fields on which you want to perform calculations and specify
criteria.

Click Totals on the toolbar.

Microsoft Access displays the Total row in the design grid.

For each field in the design grid, click its cell in the Total row, and then
click one of the following aggregate functions: Sum, Avg, Min, Max, Count,
StDev, or Var, or click the other functions, First and Last.

If you want, enter criteria to affect the results of the calculation.
How?

In the query design grid, where you specify criteria determines when the
calculation is performed and can produce different query results.

Note Although the following examples only show queries that calculate
totals, these guidelines apply to calculated fields as well.

Limit groups before performing calculations on groups of records
Specify the criteria in the Group By fields as the following example shows.

This query totals extended prices for ...
... companies in Canada and the UK only.
Return selected results after calculations are performed
Specify criteria in the field that contains the calculation as the following
example shows.

This query totals extended prices for companies in Canada and the UK ...
... but shows only those that are less than $10,000.

Limit records before they are grouped and before the calculation is
performed
Add to the design grid the field whose records you want to limit, and then
specify criteria in the field's Criteria cell.

If you're calculating totals in the same query, set the Total cell for the
field containing the criteria to Where. This rule applies whether you're
calculating the total on all records or groups of records. (Microsoft Access
automatically clears the Show check box.)

The following example uses the ExtendedPrice field twice, once to limit the
records and once to calculate the total. However, you can use a different
field to limit records by dragging that field to the design grid and setting
its Total cell to Where.

From these records, the query retrieves only those with extended prices
greater than $500.00 before it groups or totals ...
... and then it sums and shows only those totals for companies in Canada
or the UK.

This total for Seven Seas Imports does not include the order for $210.00.

If you want, sort the results.

Click View on the toolbar to view the results.

Calculate totals on groups of records in a query

Create a select query in Design view and add the tables whose records you
want to use in the calculation.

Add the fields on which you want to perform calculations, define groupings,
and specify criteria.

Click Totals on the toolbar.

Microsoft Access displays the Total row in the design grid.

For the field or fields you want to group on, leave Group By in the Total
cell.

For each field you want to calculate, click its cell in the Total row, and
then click one of the following aggregate functions: Sum, Avg, Min, Max,
Count, StDev, or Var, or click the other functions, First and Last.

If you want, enter criteria to affect the results of the calculation.

How?

In the query design grid, where you specify criteria determines when the
calculation is performed and can produce different query results.

Note Although the following examples only show queries that calculate
totals, these guidelines apply to calculated fields as well.

Limit groups before performing calculations on groups of records
Specify the criteria in the Group By fields as the following example shows.

This query totals extended prices for ...
... companies in Canada and the UK only.

Return selected results after calculations are performed
Specify criteria in the field that contains the calculation as the following
example shows.

This query totals extended prices for companies in Canada and the UK ...
... but shows only those that are less than $10,000.

Limit records before they are grouped and before the calculation is
performed
Add to the design grid the field whose records you want to limit, and then
specify criteria in the field's Criteria cell.

If you're calculating totals in the same query, set the Total cell for the
field containing the criteria to Where. This rule applies whether you're
calculating the total on all records or groups of records. (Microsoft Access
automatically clears the Show check box.)

The following example uses the ExtendedPrice field twice, once to limit the
records and once to calculate the total. However, you can use a different
field to limit records by dragging that field to the design grid and setting
its Total cell to Where.

From these records, the query retrieves only those with extended prices
greater than $500.00 before it groups or totals ...
... and then it sums and shows only those totals for companies in Canada
or the UK.
This total for Seven Seas Imports does not include the order for $210.00.

If you want, sort the results.

Click View on the toolbar to view the results.

Note If you add a calculated field that includes one or more aggregate
functions to a query in which you're calculating totals on all the records,
you must set the calculated field's Total cell to Expression.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

I would also suggest, that if you have not already done so and if you
have the authority to do so, that you consider breaking up that table to
improve the table design.

BTW the above instructions are right out of the Access help file (XP
version, your version may vary)

 

[Joseph Meehan]

The question was simple. How to display a sum at the bottom of a
column. Thank you for answering it.

I am not sure, but I suspect that you may need to use a report to show a
sum at the end of list of data.

 

[John Vinson]

The additional information is required in order to post the question. Have
you not noticed how people often use excel when access would work? My case is
unique. Someone used access as if it were excel, placing customers, orders,
tests, products, locations, and prices all in one table.

I've seen both directions of the error; "committing spreadsheet upon a
database" is a very common mistake. My phrase for the situation is
"You can use a crescent wrench to drive nails, but that doesn't make
it a hammer"!

John W. Vinson[MVP]