How can I count the number of individuals?

[KimCap]

Hi. I have "inherited" a database that tracks a number of individuals'
information.

However, some flaws exist and I'm trying to figure out how to fix them.

I'm trying to create a function that will total the number of individuals in
the database, preferably one that I could set to say that the individuals who
meet certain criteria, but for now I'd settle for just getting a total of
names and meeting just one criterion.

Any suggestions? I've tried creating a query to count the names, but that
only gives me a count of 1 next to each last name. I've looked into counting
the number of records in a report, but I can't figure out how some of the
reports are set up and which one to use (the "creator" didn't exactly name
the files anything useful).

 

[Michel Walsh]

In case some names appear twice or more, it is preferable to 'remove dup'
with a distinct, then, simply count the number of records:


SELECT COUNT(*)
FROM ( SELECT DISTINCT name FROM yourTable) AS x


should do. name and yourTable have to be replaced by the real field name and
table name.


Vanderghast, Access MVP

 

[Ken Sheridan]

The fact that you are getting a count of 1per name suggests that you quite
correctly have a table with one row per individual. The count of 1 is
presumably because you are grouping by LastName.

A query to count all the rows in the table is simply:

SELECT COUNT(*)
FROM Customers;

where Customers is the name of the table.

To count those who match certain criteria add a WHERE clause, e.g. to count
all customers in London or New York where the Customers table is joined to a
Cities table on CityID columns would be:

SELECT COUNT(*)
FROM Customers INNER JOIN Cities;
ON Cities.CityID = Customers.CityID
WHERE City IN("New York", "London");

or alternatively:

SELECT COUNT(*)
FROM Customers INNER JOIN Cities;
ON Cities.CityID = Customers.CityID
WHERE City = "New York"
OR City = "London";

Things get a little trickier if the criteria are on a column or columns in
table for which there might be more than one row per customer as, if you join
the tables as above you'll get a count of the rows in the related table, but
as some of these will relate to one customer, you'll get a result greater
than the number of customers. Taking an example from the Northwind sample
database, say you wanted to count all customers who've ordered Chai or Chang.
Four tables are involved here Customers, Orders, Order Details and Products.
You can either join them all and count the distinct CustomerID values in the
way the other respondent has explained:

SELECT COUNT(*)
FROM
(SELECT DISTINCT Customers.CustomerID
FROM Customers
INNER JOIN (Products
INNER JOIN (Orders
INNER JOIN [Order Details]
ON Orders.OrderID = [Order Details].OrderID)
ON Products.ProductID = [Order Details].ProductID)
ON Customers.CustomerID = Orders.CustomerID
WHERE ProductName IN("Chai","Chang"));

or you can count the rows in Customers where at least one row is returned by
a subquery which joins the other three tables, restricted on the ProductName
column and correlated with the outer query on CustomerID:

SELECT COUNT(*)
FROM Customers
WHERE EXISTS
(SELECT *
FROM Products
INNER JOIN (Orders
INNER JOIN [Order Details]
ON Orders.OrderID = [Order Details].OrderID)
ON Products.ProductID = [Order Details].ProductID
WHERE Orders.CustomerID = Customers.CustomerID
AND ProductName IN("Chai", "Chang"));

Ken Sheridan
Stafford, England