Counting Rows

  • Thread starter Thread starter Chip
  • Start date Start date
C

Chip

All, I am very new to ACCESS and I am needing to build a report that shows
all rows but also counts all rows and also counts all rows that has a
"yes/No" box with a "yes". This way I can build a percentage to show the
percentage of rows that are "yes" compared to the Grand Total. So far I have
been able to get the Grand Total just not the total of Yes rows. Can anyone
help??

As Always, Thanks in Advance!!!!

Chip
 
You can count the number of records with a yes value with an expression
like:
=Abs(Sum([YesNoField] = True))
 
Duane's method will work, based on the not-exactly intuitively obvious fact
that a true value is represented by -1 and false by 0. A more readable form
would be one of the following:
Select COUNT(*) FROM [MyTable] WHERE [YesNoField] = TRUE
Select COUNT(*) FROM [MyTable] WHERE [YesNoField]
 
Actually, along the lines of counting rows, but a little different:

Is is possible to create an autonumber type count of values in a field?

Basically, I have a modified find duplicates query that shows data rows of
phone call outcomes, ordered by the counselor name that worked them. I only
want to keep 10 records for each counselor. My query shows me all instances
where there were more than 10 for a given counselor and I have to manually
delete #11 on to whatever.

My criteria statement looks like this: In (SELECT [Counselor] FROM [Call Data
Table] As Tmp GROUP BY [Counselor] HAVING Count(*)>10 )

Is is possible to make a row that will give me the count by counselor? I
can't really use a "group by" for this, because I need to see each individual
record.
 
Hi Brian,

The general idea is to use a subquery that counts, for each record, the
number of records for the same counsellor before the current record (in
the current sort order, of course).

Here's an example that works in the Northwind sample database. I'll
leave it to you to apply it to your own structure. It returns the order
details, sorted by OrderID and ProductID, with a sequential number that
starts at 1 for each OrderID:

SELECT
(SELECT COUNT(C.OrderID)
FROM [Order Details] AS C
WHERE C.OrderID = A.OrderID
AND C.ProductID <= A.ProductID) AS Seq,
A.OrderID,
A.ProductID,
A.UnitPrice,
A.Quantity
FROM [Order Details] AS A
ORDER BY A.OrderID, A.ProductID
;

If you only want to see the "surplus" records, repeat the subquery in a
WHERE clause, e.g. this, which shows the orders that include more than 5
items:

SELECT
(SELECT COUNT(C.OrderID)
FROM [Order Details] AS C
WHERE C.OrderID = A.OrderID
AND C.ProductID <= A.ProductID) AS Seq,
A.OrderID,
A.ProductID,
A.UnitPrice,
A.Quantity
FROM [Order Details] AS A
WHERE (SELECT COUNT(C.OrderID)
FROM [Order Details] AS C
WHERE C.OrderID = A.OrderID
AND C.ProductID <= A.ProductID) > 6
ORDER BY A.OrderID, A.ProductID;

Or if you want to see the "surplus" items but otherwise don't care about
the sequence, you can omit the first subquery, leaving

SELECT
A.OrderID,
A.ProductID,
A.UnitPrice,
A.Quantity
FROM [Order Details] AS A
WHERE (SELECT COUNT(C.OrderID)
FROM [Order Details] AS C
WHERE C.OrderID = A.OrderID
AND C.ProductID <= A.ProductID) > 5
ORDER BY A.OrderID, A.ProductID;



Actually, along the lines of counting rows, but a little different:

Is is possible to create an autonumber type count of values in a field?

Basically, I have a modified find duplicates query that shows data rows of
phone call outcomes, ordered by the counselor name that worked them. I only
want to keep 10 records for each counselor. My query shows me all instances
where there were more than 10 for a given counselor and I have to manually
delete #11 on to whatever.

My criteria statement looks like this: In (SELECT [Counselor] FROM [Call Data
Table] As Tmp GROUP BY [Counselor] HAVING Count(*)>10 )

Is is possible to make a row that will give me the count by counselor? I
can't really use a "group by" for this, because I need to see each individual
record.
Click to expand...
 
Ok,

Thank you!! That works. I was stuck on that one for a while.

So, this can only be done in SQL view, right? That's no problem, I'm just
curious.

Thanks again,
Brian


John said:
Hi Brian,

The general idea is to use a subquery that counts, for each record, the
number of records for the same counsellor before the current record (in
the current sort order, of course).

Here's an example that works in the Northwind sample database. I'll
leave it to you to apply it to your own structure. It returns the order
details, sorted by OrderID and ProductID, with a sequential number that
starts at 1 for each OrderID:

SELECT
(SELECT COUNT(C.OrderID)
FROM [Order Details] AS C
WHERE C.OrderID = A.OrderID
AND C.ProductID <= A.ProductID) AS Seq,
A.OrderID,
A.ProductID,
A.UnitPrice,
A.Quantity
FROM [Order Details] AS A
ORDER BY A.OrderID, A.ProductID
;

If you only want to see the "surplus" records, repeat the subquery in a
WHERE clause, e.g. this, which shows the orders that include more than 5
items:

SELECT
(SELECT COUNT(C.OrderID)
FROM [Order Details] AS C
WHERE C.OrderID = A.OrderID
AND C.ProductID <= A.ProductID) AS Seq,
A.OrderID,
A.ProductID,
A.UnitPrice,
A.Quantity
FROM [Order Details] AS A
WHERE (SELECT COUNT(C.OrderID)
FROM [Order Details] AS C
WHERE C.OrderID = A.OrderID
AND C.ProductID <= A.ProductID) > 6
ORDER BY A.OrderID, A.ProductID;

Or if you want to see the "surplus" items but otherwise don't care about
the sequence, you can omit the first subquery, leaving

SELECT
A.OrderID,
A.ProductID,
A.UnitPrice,
A.Quantity
FROM [Order Details] AS A
WHERE (SELECT COUNT(C.OrderID)
FROM [Order Details] AS C
WHERE C.OrderID = A.OrderID
Actually, along the lines of counting rows, but a little different:
Click to expand...
[quoted text clipped - 12 lines]
can't really use a "group by" for this, because I need to see each individual
record.
Click to expand...
Click to expand...
 
Dear Brian:

This query, I believe, can be viewed (and therefore entered) in the design
view. After you enter it in the SQL view and save it, switch views and see.

The subquery will be represented as the SQL code when viewed this way. Not
particularly helpful, I expect, but you can view and enter a query this way
if you wish.

Tom Ellison


Brian M via AccessMonster.com said:
Ok,

Thank you!! That works. I was stuck on that one for a while.

So, this can only be done in SQL view, right? That's no problem, I'm just
curious.

Thanks again,
Brian


John said:
Hi Brian,

The general idea is to use a subquery that counts, for each record, the
number of records for the same counsellor before the current record (in
the current sort order, of course).

Here's an example that works in the Northwind sample database. I'll
leave it to you to apply it to your own structure. It returns the order
details, sorted by OrderID and ProductID, with a sequential number that
starts at 1 for each OrderID:

SELECT
(SELECT COUNT(C.OrderID)
FROM [Order Details] AS C
WHERE C.OrderID = A.OrderID
AND C.ProductID <= A.ProductID) AS Seq,
A.OrderID,
A.ProductID,
A.UnitPrice,
A.Quantity
FROM [Order Details] AS A
ORDER BY A.OrderID, A.ProductID
;

If you only want to see the "surplus" records, repeat the subquery in a
WHERE clause, e.g. this, which shows the orders that include more than 5
items:

SELECT
(SELECT COUNT(C.OrderID)
FROM [Order Details] AS C
WHERE C.OrderID = A.OrderID
AND C.ProductID <= A.ProductID) AS Seq,
A.OrderID,
A.ProductID,
A.UnitPrice,
A.Quantity
FROM [Order Details] AS A
WHERE (SELECT COUNT(C.OrderID)
FROM [Order Details] AS C
WHERE C.OrderID = A.OrderID
AND C.ProductID <= A.ProductID) > 6
ORDER BY A.OrderID, A.ProductID;

Or if you want to see the "surplus" items but otherwise don't care about
the sequence, you can omit the first subquery, leaving

SELECT
A.OrderID,
A.ProductID,
A.UnitPrice,
A.Quantity
FROM [Order Details] AS A
WHERE (SELECT COUNT(C.OrderID)
FROM [Order Details] AS C
WHERE C.OrderID = A.OrderID
Actually, along the lines of counting rows, but a little different:
Click to expand...
[quoted text clipped - 12 lines]
can't really use a "group by" for this, because I need to see each
individual
record.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top