Record Count in Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a query that I'm having trouble in figuring out. I have created a
database that is used for tracking sewer overflows at certain points in the
city. The points in the city are labeled with a number, (i.e. 102, 103,
104,...up to 130). These points are checked roughly 2-3 times per week and
when they are checked I have a simple form that is filled out by the user.
It has a few fields, one is date, one is point number, and another is "Did
overflow occur?" (Yes/No field). I am trying to make a query that will list
each point number once, and then count the number of times "Yes" was selected
for the overflow field during a certain date range. I have done more
complicated things than this but for some reason this one has me stumped,
probably cuz its friday afternoon! Any help would be greatly appreciated.
 
Try a totals query with SQL like:
SELECT PointNumber, Abs(Sum([OverFlow])) as NumOf
FROM tblNoNameGiven
GROUP BY PointNumber;

You can add a date criteria after this works. The date range should come
from values entered into controls on a form.
 
SELECT pointNumber, COUNT(*)
FROM tableName
WHERE overflowOccured
GROUP BY pointNumber


should do. Well, if a point number have NEVER got any overflow, it won't be
listed. Assuming you have a table with all pointNumber, no duplicated entry,
then:


SELECT a.pointNumber, COUNT(b.pointNumber)
FORM pointsNumbers AS a LEFT JOIN
(SELECT * FROM tableNameOfOverflow WHERE overflowOccured) AS b
ON a.pointNumber=b.pointNumber
GROUP BY a.pointNumber


would do, also listing those points, with a count of zero.



Hoping it may help,
Vanderghast, Access MVP
 
Thanks for all the feedback so quickly. I haven't used SQL language a lot, I
have mostly stuck to the access query design mode so bare with me. I used the
first SQL statement you wrote ( shown below) but changed it to:
SELECT CSO, COUNT(*)
FROM [CSO Overflow Visual Inspection]
WHERE Overflowatinspection
GROUP BY CSO
Click to expand...

This seems to be on the right track as i get no errors, but it is counting
all the entries for the CSO (or point number), I want it to only count the
Yes entries for Overflowatinspection for each CSO. I have tried to change a
few things to get it to work that way but cant get it to work. Thanks again
for your help, its appreciated. Let me know if you have any questions.

P.S.
I may not get a chance to work on this until monday now so if i don't
respond to any questions that is why.
 
If the field Overflowatinspection is a text field then try.

SELECT CSO, COUNT(IIF(Overflowatinspection="yes",1,Null))
FROM [CSO Overflow Visual Inspection]
WHERE Overflowatinspection
GROUP BY CSO

If the field Overflowatinspection is a yes/no field then try
SELECT CSO, COUNT(IIF(Overflowatinspection=True,1,Null))
FROM [CSO Overflow Visual Inspection]
WHERE Overflowatinspection
GROUP BY CSO

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Thanks for all the feedback so quickly. I haven't used SQL language a lot, I
have mostly stuck to the access query design mode so bare with me. I used the
first SQL statement you wrote ( shown below) but changed it to:
SELECT CSO, COUNT(*)
FROM [CSO Overflow Visual Inspection]
WHERE Overflowatinspection
GROUP BY CSO
Click to expand...

This seems to be on the right track as i get no errors, but it is counting
all the entries for the CSO (or point number), I want it to only count the
Yes entries for Overflowatinspection for each CSO. I have tried to change a
few things to get it to work that way but cant get it to work. Thanks again
for your help, its appreciated. Let me know if you have any questions.

P.S.
I may not get a chance to work on this until monday now so if i don't
respond to any questions that is why.

Michel Walsh said:
SELECT pointNumber, COUNT(*)
FROM tableName
WHERE overflowOccured
GROUP BY pointNumber
Click to expand...
Click to expand...
 
Thanks for everyones help! It's working now after i added that IIF statement.
I have another similar problem that I may need help on, so you may hear from
me again.

Thanks again

John Spencer said:
If the field Overflowatinspection is a text field then try.

SELECT CSO, COUNT(IIF(Overflowatinspection="yes",1,Null))
FROM [CSO Overflow Visual Inspection]
WHERE Overflowatinspection
GROUP BY CSO

If the field Overflowatinspection is a yes/no field then try
SELECT CSO, COUNT(IIF(Overflowatinspection=True,1,Null))
FROM [CSO Overflow Visual Inspection]
WHERE Overflowatinspection
GROUP BY CSO

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Thanks for all the feedback so quickly. I haven't used SQL language a lot, I
have mostly stuck to the access query design mode so bare with me. I used the
first SQL statement you wrote ( shown below) but changed it to:
SELECT CSO, COUNT(*)
FROM [CSO Overflow Visual Inspection]
WHERE Overflowatinspection
GROUP BY CSO
Click to expand...

This seems to be on the right track as i get no errors, but it is counting
all the entries for the CSO (or point number), I want it to only count the
Yes entries for Overflowatinspection for each CSO. I have tried to change a
few things to get it to work that way but cant get it to work. Thanks again
for your help, its appreciated. Let me know if you have any questions.

P.S.
I may not get a chance to work on this until monday now so if i don't
respond to any questions that is why.

Michel Walsh said:
SELECT pointNumber, COUNT(*)
FROM tableName
WHERE overflowOccured
GROUP BY pointNumber
Click to expand...
Click to expand...
Click to expand...
 
Back
Top