combine records in query count

[Guest]

I'm in need of additional help on a query that I thought I had figured out.
The problem that I got help on before is located at the following URL:

http://msdn.microsoft.com/newsgroup...7d0ed9-474e-481a-b196-a0867467a3eb&sloc=en-us

The SQL statements I used are as follows:

SELECT [CSO Overflow Visual Inspection].CSO,
Count(IIf([Overflow]="yes",1,Null)) AS NumofOverflows
FROM [CSO Overflow Visual Inspection]
WHERE ((([CSO Overflow Visual Inspection].Overflow)<>"False"))
GROUP BY [CSO Overflow Visual Inspection].CSO;

This statement works fine for finding the Total number of overflows for ALL
dates, however, I want to be able to find the total number of overflows
between a date range. When I add a Between..And statement in the query
design mode, it works to get the dates but it lists each date in a seperate
line along with the point number. I want there to be one column for the
point number with each point only having one row, and then one column for the
number of overflows between the dates. Let me know if this doesn't make
sense or if you have any questions as it is difficult to explain. Thanks for
any help you can give.

 

[Tom Ellison]

Dear Omaha:

This may be an improvement:

SELECT CSO, Count(*) AS NumofOverflows
FROM [CSO Overflow Visual Inspection]
WHERE Overflow = "yes" AND
Overflow IS NOT NULL AND
MyDate BETWEEN [Enter Beginning Date]
AND [Enter Ending Date]
GROUP BY CSO;

I have assumed there is a column containing the dates (without any time
component on any of them) and I called that MyDate.

Is that any closer?

Tom Ellison
Microsoft Access MVP

 

[Guest]

Thanks alot Tom that works great. Right now if there were no overflows
between the dates that I specify, it doesn't show up on the list, is there a
way to add every cso even if the numofoverflows is 0. Not a huge deal if it
is difficult. Again, i appreciate the help, thanks

Dear Omaha:

This may be an improvement:

SELECT CSO, Count(*) AS NumofOverflows
FROM [CSO Overflow Visual Inspection]
WHERE Overflow = "yes" AND
Overflow IS NOT NULL AND
MyDate BETWEEN [Enter Beginning Date]
AND [Enter Ending Date]
GROUP BY CSO;

I have assumed there is a column containing the dates (without any time
component on any of them) and I called that MyDate.

Is that any closer?

Tom Ellison
Microsoft Access MVP

I'm in need of additional help on a query that I thought I had figured
out.
The problem that I got help on before is located at the following URL:

http://msdn.microsoft.com/newsgroup...7d0ed9-474e-481a-b196-a0867467a3eb&sloc=en-us

The SQL statements I used are as follows:

SELECT [CSO Overflow Visual Inspection].CSO,
Count(IIf([Overflow]="yes",1,Null)) AS NumofOverflows
FROM [CSO Overflow Visual Inspection]
WHERE ((([CSO Overflow Visual Inspection].Overflow)<>"False"))
GROUP BY [CSO Overflow Visual Inspection].CSO;

This statement works fine for finding the Total number of overflows for
ALL
dates, however, I want to be able to find the total number of overflows
between a date range. When I add a Between..And statement in the query
design mode, it works to get the dates but it lists each date in a
seperate
line along with the point number. I want there to be one column for the
point number with each point only having one row, and then one column for
the
number of overflows between the dates. Let me know if this doesn't make
sense or if you have any questions as it is difficult to explain. Thanks
for
any help you can give.

 

[Tom Ellison]

Dear fellow Nebraskan:

I'm not sure where the problem is. Could you please give some sample data,
what it is doing with that sample data now, and what you want it to do
differently. Perhaps I'd catch on if you did.

Tom Ellison
Microsoft Access MVP

Thanks alot Tom that works great. Right now if there were no overflows
between the dates that I specify, it doesn't show up on the list, is there
a
way to add every cso even if the numofoverflows is 0. Not a huge deal if
it
is difficult. Again, i appreciate the help, thanks

Dear Omaha:

This may be an improvement:

SELECT CSO, Count(*) AS NumofOverflows
FROM [CSO Overflow Visual Inspection]
WHERE Overflow = "yes" AND
Overflow IS NOT NULL AND
MyDate BETWEEN [Enter Beginning Date]
AND [Enter Ending Date]
GROUP BY CSO;

I have assumed there is a column containing the dates (without any time
component on any of them) and I called that MyDate.

Is that any closer?

Tom Ellison
Microsoft Access MVP

I'm in need of additional help on a query that I thought I had figured
out.
The problem that I got help on before is located at the following URL:

http://msdn.microsoft.com/newsgroup...7d0ed9-474e-481a-b196-a0867467a3eb&sloc=en-us

The SQL statements I used are as follows:

SELECT [CSO Overflow Visual Inspection].CSO,
Count(IIf([Overflow]="yes",1,Null)) AS NumofOverflows
FROM [CSO Overflow Visual Inspection]
WHERE ((([CSO Overflow Visual Inspection].Overflow)<>"False"))
GROUP BY [CSO Overflow Visual Inspection].CSO;

This statement works fine for finding the Total number of overflows for
ALL
dates, however, I want to be able to find the total number of overflows
between a date range. When I add a Between..And statement in the query
design mode, it works to get the dates but it lists each date in a
seperate
line along with the point number. I want there to be one column for
the
point number with each point only having one row, and then one column
for
the
number of overflows between the dates. Let me know if this doesn't
make
sense or if you have any questions as it is difficult to explain.
Thanks
for
any help you can give.

 

[John Spencer]

Dear Tom,

Glad to see you back in the fold. I tried to drop you an email at your old
address, but it bounced.

I think OmahaAccess wants to show the CSO even if there were no overflows
during the time period specified for that CSO.

I would think that there has to be a table of CSO that can be outer-joined
to the [CSO Overflow Visual Inspection] table or that a separate query
would have to be used to get a Distinct list of CSO that can be outer joined
to the the [CSO Overflow Visual Inspection] table.

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

Dear fellow Nebraskan:

I'm not sure where the problem is. Could you please give some sample
data, what it is doing with that sample data now, and what you want it to
do differently. Perhaps I'd catch on if you did.

Tom Ellison
Microsoft Access MVP

Thanks alot Tom that works great. Right now if there were no overflows
between the dates that I specify, it doesn't show up on the list, is
there a
way to add every cso even if the numofoverflows is 0. Not a huge deal if
it
is difficult. Again, i appreciate the help, thanks

Dear Omaha:

This may be an improvement:

SELECT CSO, Count(*) AS NumofOverflows
FROM [CSO Overflow Visual Inspection]
WHERE Overflow = "yes" AND
Overflow IS NOT NULL AND
MyDate BETWEEN [Enter Beginning Date]
AND [Enter Ending Date]
GROUP BY CSO;

I have assumed there is a column containing the dates (without any time
component on any of them) and I called that MyDate.

Is that any closer?

Tom Ellison
Microsoft Access MVP


I'm in need of additional help on a query that I thought I had figured
out.
The problem that I got help on before is located at the following URL:

http://msdn.microsoft.com/newsgroup...7d0ed9-474e-481a-b196-a0867467a3eb&sloc=en-us

The SQL statements I used are as follows:

SELECT [CSO Overflow Visual Inspection].CSO,
Count(IIf([Overflow]="yes",1,Null)) AS NumofOverflows
FROM [CSO Overflow Visual Inspection]
WHERE ((([CSO Overflow Visual Inspection].Overflow)<>"False"))
GROUP BY [CSO Overflow Visual Inspection].CSO;

This statement works fine for finding the Total number of overflows
for
ALL
dates, however, I want to be able to find the total number of
overflows
between a date range. When I add a Between..And statement in the
query
design mode, it works to get the dates but it lists each date in a
seperate
line along with the point number. I want there to be one column for
the
point number with each point only having one row, and then one column
for
the
number of overflows between the dates. Let me know if this doesn't
make
sense or if you have any questions as it is difficult to explain.
Thanks
for
any help you can give.

 

[Tom Ellison]

Dear John:

That may be. I hope to hear from him soon.

Tom Ellison
Microsoft Access MVP

Dear Tom,

Glad to see you back in the fold. I tried to drop you an email at your
old address, but it bounced.

I think OmahaAccess wants to show the CSO even if there were no overflows
during the time period specified for that CSO.

I would think that there has to be a table of CSO that can be outer-joined
to the [CSO Overflow Visual Inspection] table or that a separate query
would have to be used to get a Distinct list of CSO that can be outer
joined to the the [CSO Overflow Visual Inspection] table.

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

Dear fellow Nebraskan:

I'm not sure where the problem is. Could you please give some sample
data, what it is doing with that sample data now, and what you want it to
do differently. Perhaps I'd catch on if you did.

Tom Ellison
Microsoft Access MVP

Thanks alot Tom that works great. Right now if there were no overflows
between the dates that I specify, it doesn't show up on the list, is
there a
way to add every cso even if the numofoverflows is 0. Not a huge deal
if it
is difficult. Again, i appreciate the help, thanks

:

Dear Omaha:

This may be an improvement:

SELECT CSO, Count(*) AS NumofOverflows
FROM [CSO Overflow Visual Inspection]
WHERE Overflow = "yes" AND
Overflow IS NOT NULL AND
MyDate BETWEEN [Enter Beginning Date]
AND [Enter Ending Date]
GROUP BY CSO;

I have assumed there is a column containing the dates (without any time
component on any of them) and I called that MyDate.

Is that any closer?

Tom Ellison
Microsoft Access MVP


I'm in need of additional help on a query that I thought I had
figured
out.
The problem that I got help on before is located at the following
URL:

http://msdn.microsoft.com/newsgroup...7d0ed9-474e-481a-b196-a0867467a3eb&sloc=en-us

The SQL statements I used are as follows:

SELECT [CSO Overflow Visual Inspection].CSO,
Count(IIf([Overflow]="yes",1,Null)) AS NumofOverflows
FROM [CSO Overflow Visual Inspection]
WHERE ((([CSO Overflow Visual Inspection].Overflow)<>"False"))
GROUP BY [CSO Overflow Visual Inspection].CSO;

This statement works fine for finding the Total number of overflows
for
ALL
dates, however, I want to be able to find the total number of
overflows
between a date range. When I add a Between..And statement in the
query
design mode, it works to get the dates but it lists each date in a
seperate
line along with the point number. I want there to be one column for
the
point number with each point only having one row, and then one column
for
the
number of overflows between the dates. Let me know if this doesn't
make
sense or if you have any questions as it is difficult to explain.
Thanks
for
any help you can give.