Doing a sum function in a query

  • Thread starter Thread starter T C via AccessMonster.com
  • Start date Start date
T

T C via AccessMonster.com

I have a table with about a 1000 lines of data pertaining to some client
data (much of the data is numerical). For about 50 of my clients I have 2
different lines of data - I need my query to spit out the exact same data
as my table but with only 1 line (with the numbers summed) for each of my
50 or so clients that have 2 lines of data in the table. Can anyone offer
me advice? This looks too easy to be this hard!
 
You need to write a query against the table. Include the columns for the
unique client names and the information you want to sum. Hit the Sigma
button on the toolbar (looks like an "E"). A new row appears that says
"Total." Say you want to group by the client name and sum the numerical
fields. You could also average, take the max, or other common functions.

Hope this helps,
Jane
 
Some of the fields in the query are acutaly calculated. How do I get the
query to SUM these fields? I keep getting an error. As it is, it errors
if I have the "Total" set as SUM or as EXPRESSION.
 
You'll need to query the query. Save your original query with no sums and
the repeating clients. Create a new query, using the original query as the
basis instead of the table. You should then be able to use SUM.
 
I have a table with about a 1000 lines of data pertaining to some client
data (much of the data is numerical). For about 50 of my clients I have 2
different lines of data - I need my query to spit out the exact same data
as my table but with only 1 line (with the numbers summed) for each of my
50 or so clients that have 2 lines of data in the table. Can anyone offer
me advice? This looks too easy to be this hard!
Click to expand...

What have you tried?

Create a Query based on your table. Click the Greek Sigma icon - looks
like a sideways W. Leave the default Group By for the fields which
will be the same in each record, and change it to Sum for the number
fields which you want to sum.

Base a Form or Report on this query (for viewing or printing
respectively).

This might pose problems if you have not-quite-duplicate data - for
instance if you have two records for ClientID 651 with ClientName
"Fred Jones" and "Fred E. Jones" you'll see two lines if you group by
ClientName.

John W. Vinson[MVP]
 
Back
Top