Counting rows in a Query

J

Janet

I want to count all the rows in a query, but when I right click on the
background of the diagram pane (as the help menu tells me to do), I don't get
the Group by option on the menu. I'm assuming that the diagram pane is the
area where my table appears after I have closed the select table box. I'm
using Access 2003, but I had saved the file in 2000 file format, but I have
now changed the default to 2002-2003 format. Any suggestions? Thanks
 
B

Brendan Reynolds

The option you're looking for is 'Totals'. I don't get it when I click the
upper part of the query design window either, but I do get it if I click in
the lower grid part, or you can get to it view the View menu on the query
design menu bar, or the Sigma symbol on the toolbar.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
J

Janet

I thought you had the answer BUT when I click on Totals, a Total row is added
to the field. I can select Group By and then count, but I am only counting
in that row or record. According to the Help menu, when I select Group By, I
should be inserting a column, which will then count all the rows in the
query. So now how do I the column that will count the rows? Thanks again.
 
B

Brendan Reynolds

What's the title of the help topic you're looking at?

To get a count of all rows returned by a table or query, create a new query
based on the original table or query. Do *not* select the Totals option. Add
a single column with the following expression in the Field row: TheCount:
Count(*). 'TheCount' is just an alias (a title) for the column, you can call
it whatever you like. If you didn't give it any alias, Access would
automatically give it the alias 'Expr1'. In SQL view, it looks like this ...

SELECT Count(*) AS TheCount FROM YourTableOrQueryNameHere

If you wanted to add a column such as you describe to the existing query,
you could do that using DCount(). Again you would not use a totals query,
but enter an expression such as the following in the Field row in query
design view ...

TheCount: DCount("YourTableOrQueryName","YourPrimaryKeyField")

In SQL view, it looks something like this (again substitute your table/query
and field names) ...

SELECT TestID, TestText, TestDate, DCount("TestID","tblTest") AS TheCount
FROM tblTest;

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
J

Janet

I go to the Help Menu - Total records in a query to Count rows in a query to
Count all rows. I would like to have Access count the rows similarly to the
way that rows can be counted in Excel, so that when I print out my query, I
can easily see that there are, for example 32 companies listed in Toronto,
rather than having to manually count the companies and then write the number
 
J

Janet

Ok, so now I've tried TheCount:Count(Toronto) - if I put a D in front of
Count, there is an error message (I can't put in "Toronto" either). I enter
the above in the Field of a blank column, but then the query won't run - I
get the error message - You tried to execute a query that does not include a
specified expression Cust Acc as part of an aggregate function. What on
earth does that mean? Cust Acc is the first column in the query and it's
just the account numbers. Once I did manage to get past the error message -
I'm not sure how, but then I got a box asking me for the parameter. What
parameter? I just want to count the number of rows!! Thanks
 
B

Brendan Reynolds

Sorry, Janet, but that's not what I advised you to do. Count(Toronto) is not
at all the same thing as Count(*), and DCount(Toronto) is not at all the
same thing as DCount("YourTableOrQueryName", "YourPrimaryKeyFieldName").

It seems that you have little experience yet of how queries work in Access,
and it is very difficult to communicate that kind of background information
within the confines of a newsgroup post. Newsgroups are a wonderful resource
for specific answers to specific questions, but for tutorials, they are no
substitute for a good book.

It also appears to me that your requirements would probably be better met by
a report, rather than a query. In a report, you can use the Sorting and
Grouping feature to group your customers by City, if that's what you want,
and add an expression to the group footer to count the number of customers
in each city.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
J

Janet

Thanks for your time - yes, I am new to Access. I have one easy step book in
Access which doesn't address the topic and another Office 2003 all in one,
which suggests that calculations such as sum or count (the number of entries
in a field, which is what I want), etc. can be done using the Total Row in a
Query. It would also be useful for me to be able to calculate the total
sales in a field - but I can't get that to work either. It really should be
very simple - the Total row should operate as easily as the Sort row or
criteria row and that's never a problem. So I don't understand this. It was
suggested to me that I import the query into Excel, but having read one post,
that sounds simpler said than done! Anyway, thanks again.
 
B

Brendan Reynolds

The thing you have to remember about the Total row is that you have to have
something in there for each column. Suppose, for example, you create a
Totals query based on the Orders table from the Northwind sample database.
If you Group By OrderID and Count CustomerID, you'll get one row for each
order, and the Count column will return 1 for each record. It is returning
the number of customers who places this specific Order, which of course can
only be one.

If you were to remove the OrderID column from the query and replace it with,
say, OrderDate, so that you Group By OrderDate and Count CustomerID, you'll
get one row for each date that orders were taken. The Count column will then
return varying numbers, because it is counting the number of customers who
placed orders on that date.

If you removed the OrderDate column, and replaced it with EmployeeID column,
you'd get one row for each employee, with a count of how many customers have
ever placed an order with that employee.

The key point is that every column you add or take away is likely to change
the result. That is why simply adding a totals row to your existing query is
not the simple solution that it might at first appear to be. If you wanted
to count the number of customers in each city, you could certainly do that,
by adding *only* the city and customer ID fields to the query, grouping on
City and counting customer ID. But as soon as you start adding any
additional columns to that query, you're likely to change the result -
unless only one value can occur in that additional column for each value
that occurs in the city column.

I really think you'll find a report the best solution to your problem - and,
unlike a query, you won't have to re-set the margins every time you want to
print it! :)

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
Top