Totals or sum like excel?

[deejay_totoro]

Hello,

I have a very simple query:

one column contains names

the second column contains a count of the name

so, the query looks somethin like this:

Name CountOfName
A name 1
B name 1
C name 1

etc....

What I would like to do is somehow total all the "1's", kind of like the sum used in Excel?

What I need in then is a single figure that tells me how many names are held...

Thank you!

dj_T

PS: I tried doing this before but couldnt work it out. Thanks!

 

[dcx693]

I think you want a totals query. I don't know if the column with "A,B,C" is a column In your query, but you don't need it. In query design view, go to the View menu and choose Totals. You'll get a new line in your grid called "Total:". Under the Name field, choose "Group By". I would probably replace your existing CountOfName field with another field unique to each individual and set the "Total:" line to be "Count". Look in the Access help system for more info on totals queries. In Access XP, the topic is called "Total records in a query".

 

[deejay_totoro]

only 1

Hello,

Well, what happens when I do that is this:

The query counts the column to the left. So, it correctly displays a "1" in the counting column. There is only one name - so it counts it as "1".

What I need to do is calculate the number of entries returned in a particular column of a query.

Basically the same as totaling veritically (not counting horizontally?)

thank you!

dj_T

 

[dcx693]

Some type of totals query will be the way to solve this. You don't even need to group on anything, just have one field and do a "Count" on that field if you want a total recordcount or use field with "Where" as the Total if you need to specify criteria.

If I'm misunderstanding you, please post some sample data with desired output.

 

[deejay_totoro]

Example

Hello,

Thank you for your reply.

Here is a simple example made in excel.

It shows the names and a total.

So how would I get a query to calculate the 6?

Thank you.

dj_T

 

[dcx693]

First, I want to make clear that in the same query, you cannot simultaneously show detail information and summary information. You'll need to do that on a report (or even a form). You can reproduce the screen you have in an Access report.

If you just want to calculate the 6 (and not display the names), you can use a totals query, include just the name field and do a count (since they occur just once each). You could also do a sum on your count field.