|
 |
access_asp thread: Larry and Ken :RE: SQL Query Difficulty
Message #1 by "Young, Ashley" <Ashley.Young@c...> on Thu, 24 Oct 2002 10:26:00 -0400
|
|
Ken and Larry-
Thank you very much for your responses. The query worked great except for
one little problem. I'm trying to display the results of the query on a
webpage. This is my sql:
set rscsr = server.createobject("adodb.recordset")
rscsr.open "select calledby,count(calledby) as CSRCount from tblCompanies
group by calledby",conn,3
and this is what I'm using to write the resultset:
do while not rscsr.eof
response.write("CSR: " &rscsr("calledby"))
response.write(" Times Called: " &rscsr("CSRCount"))
response.write("<br>")
rscsr.movenext
loop
The output is a little different than I expect. It looks like this:
CSR: Times Called: 0
CSR: elizabethr Times Called: 7
CSR: maggiem Times Called: 4
CSR: shannonj Times Called: 2
CSR: tristanm Times Called: 1
Is there a way to get rid of that first erroneous result? Am I writing the
resultset wrong? Any of your expertise is appreciated.
Thanks in advance,
--Ashley
-----Original Message-----
From: Larry Woods [mailto:larry@l...]
Sent: Wednesday, October 23, 2002 8:38 PM
To: Access ASP
Subject: [access_asp] RE: SQL Query Difficulty
Ashley,
Try this...
SELECT calledby, Count(calledby) AS Count
FROM tablename GROUP BY calledby
Larry Woods
> -----Original Message-----
> From: Young, Ashley [mailto:Ashley.Young@c...]
> Sent: Wednesday, October 23, 2002 1:27 PM
> To: Access ASP
> Subject: [access_asp] SQL Query Difficulty
>
>
> Hi, everyone.
>
> I'm having a bit of trouble trying to construct an
> SQL query. What I want
> the query to do is count the number of times a call
> was made by a certain
> person. The query pulls from a table called
> tblCompanies and from a field
> named "calledby".
>
> I want the query to display a listing of the names
> in the calledby field
> and how many times that name appears. I tried
> constructing a count query
> like this:
>
> select count(calledby) as Count where calledby='name'
>
> But that didn't work because I'd have to have 20
> different recordsets to
> get the information I want. I'm sure somebody has a
> good way to do this. Any
> help is appreciated and details are always good.
>
> Thanks in advance!
>
> --Ashley
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.377 / Virus Database: 211 - Release Date:
> 7/15/2002
>
>
---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.377 / Virus Database: 211 - Release Date: 7/15/2002
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.377 / Virus Database: 211 - Release Date: 7/15/2002
Message #2 by "Larry Woods" <larry@l...> on Thu, 24 Oct 2002 09:37:16 -0700
|
|
Ashley,
All indications are that you have a BLANK 'calledby' value in
your table. Best to clean it out. Otherwise--but NOT
cool--would be:
rscsr.open "select calledby,count(calledby) as
CSRCount from tblCompanies WHERE calledby IS NOT NULL group by
calledby",conn,3
Best to clean up your data.
Larry Woods
> -----Original Message-----
> From: Young, Ashley [mailto:Ashley.Young@c...]
> Sent: Thursday, October 24, 2002 7:26 AM
> To: Access ASP
> Subject: [access_asp] Larry and Ken :RE: SQL Query Difficulty
>
>
> Ken and Larry-
>
> Thank you very much for your responses. The query
> worked great except for
> one little problem. I'm trying to display the results
> of the query on a
> webpage. This is my sql:
>
> set rscsr = server.createobject("adodb.recordset")
> rscsr.open "select calledby,count(calledby) as
> CSRCount from tblCompanies
> group by calledby",conn,3
>
> and this is what I'm using to write the resultset:
>
> do while not rscsr.eof
> response.write("CSR: " &rscsr("calledby"))
> response.write(" Times Called: " &rscsr("CSRCount"))
> response.write("<br>")
> rscsr.movenext
> loop
>
> The output is a little different than I expect. It
> looks like this:
>
> CSR: Times Called: 0
> CSR: elizabethr Times Called: 7
> CSR: maggiem Times Called: 4
> CSR: shannonj Times Called: 2
> CSR: tristanm Times Called: 1
>
> Is there a way to get rid of that first erroneous
> result? Am I writing the
> resultset wrong? Any of your expertise is appreciated.
>
> Thanks in advance,
>
> --Ashley
>
> -----Original Message-----
> From: Larry Woods [mailto:larry@l...]
> Sent: Wednesday, October 23, 2002 8:38 PM
> To: Access ASP
> Subject: [access_asp] RE: SQL Query Difficulty
>
>
> Ashley,
>
> Try this...
>
> SELECT calledby, Count(calledby) AS Count
> FROM tablename GROUP BY calledby
>
> Larry Woods
>
>
> > -----Original Message-----
> > From: Young, Ashley [mailto:Ashley.Young@c...]
> > Sent: Wednesday, October 23, 2002 1:27 PM
> > To: Access ASP
> > Subject: [access_asp] SQL Query Difficulty
> >
> >
> > Hi, everyone.
> >
> > I'm having a bit of trouble trying to construct an
> > SQL query. What I want
> > the query to do is count the number of times a call
> > was made by a certain
> > person. The query pulls from a table called
> > tblCompanies and from a field
> > named "calledby".
> >
> > I want the query to display a listing of the names
> > in the calledby field
> > and how many times that name appears. I tried
> > constructing a count query
> > like this:
> >
> > select count(calledby) as Count where calledby='name'
> >
> > But that didn't work because I'd have to have 20
> > different recordsets to
> > get the information I want. I'm sure somebody has a
> > good way to do this. Any
> > help is appreciated and details are always good.
> >
> > Thanks in advance!
> >
> > --Ashley
> >
> > ---
> > Outgoing mail is certified Virus Free.
> > Checked by AVG anti-virus system (http://www.grisoft.com).
> > Version: 6.0.377 / Virus Database: 211 - Release Date:
> > 7/15/2002
> >
> >
---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.377 / Virus Database: 211 - Release Date: 7/15/2002
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.377 / Virus Database: 211 - Release Date: 7/15/2002
Message #3 by "Young, Ashley" <Ashley.Young@c...> on Thu, 24 Oct 2002 14:44:17 -0400
|
|
Larry,
Thanks for your response. I tried the IS NOT NULL first and got the
results I wanted, but I did go back into the database and find the bad
record. Thanks again for your help!
--Ashley
-----Original Message-----
From: Larry Woods [mailto:larry@l...]
Sent: Thursday, October 24, 2002 11:37 AM
To: Access ASP
Subject: [access_asp] RE: Larry and Ken :RE: SQL Query Difficulty
Ashley,
All indications are that you have a BLANK 'calledby' value in
your table. Best to clean it out. Otherwise--but NOT
cool--would be:
rscsr.open "select calledby,count(calledby) as
CSRCount from tblCompanies WHERE calledby IS NOT NULL group by
calledby",conn,3
Best to clean up your data.
Larry Woods
> -----Original Message-----
> From: Young, Ashley [mailto:Ashley.Young@c...]
> Sent: Thursday, October 24, 2002 7:26 AM
> To: Access ASP
> Subject: [access_asp] Larry and Ken :RE: SQL Query Difficulty
>
>
> Ken and Larry-
>
> Thank you very much for your responses. The query
> worked great except for
> one little problem. I'm trying to display the results
> of the query on a
> webpage. This is my sql:
>
> set rscsr = server.createobject("adodb.recordset")
> rscsr.open "select calledby,count(calledby) as
> CSRCount from tblCompanies
> group by calledby",conn,3
>
> and this is what I'm using to write the resultset:
>
> do while not rscsr.eof
> response.write("CSR: " &rscsr("calledby"))
> response.write(" Times Called: " &rscsr("CSRCount"))
> response.write("<br>")
> rscsr.movenext
> loop
>
> The output is a little different than I expect. It
> looks like this:
>
> CSR: Times Called: 0
> CSR: elizabethr Times Called: 7
> CSR: maggiem Times Called: 4
> CSR: shannonj Times Called: 2
> CSR: tristanm Times Called: 1
>
> Is there a way to get rid of that first erroneous
> result? Am I writing the
> resultset wrong? Any of your expertise is appreciated.
>
> Thanks in advance,
>
> --Ashley
>
> -----Original Message-----
> From: Larry Woods [mailto:larry@l...]
> Sent: Wednesday, October 23, 2002 8:38 PM
> To: Access ASP
> Subject: [access_asp] RE: SQL Query Difficulty
>
>
> Ashley,
>
> Try this...
>
> SELECT calledby, Count(calledby) AS Count
> FROM tablename GROUP BY calledby
>
> Larry Woods
>
>
> > -----Original Message-----
> > From: Young, Ashley [mailto:Ashley.Young@c...]
> > Sent: Wednesday, October 23, 2002 1:27 PM
> > To: Access ASP
> > Subject: [access_asp] SQL Query Difficulty
> >
> >
> > Hi, everyone.
> >
> > I'm having a bit of trouble trying to construct an
> > SQL query. What I want
> > the query to do is count the number of times a call
> > was made by a certain
> > person. The query pulls from a table called
> > tblCompanies and from a field
> > named "calledby".
> >
> > I want the query to display a listing of the names
> > in the calledby field
> > and how many times that name appears. I tried
> > constructing a count query
> > like this:
> >
> > select count(calledby) as Count where calledby='name'
> >
> > But that didn't work because I'd have to have 20
> > different recordsets to
> > get the information I want. I'm sure somebody has a
> > good way to do this. Any
> > help is appreciated and details are always good.
> >
> > Thanks in advance!
> >
> > --Ashley
> >
> > ---
> > Outgoing mail is certified Virus Free.
> > Checked by AVG anti-virus system (http://www.grisoft.com).
> > Version: 6.0.377 / Virus Database: 211 - Release Date:
> > 7/15/2002
> >
> >
---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.377 / Virus Database: 211 - Release Date: 7/15/2002
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.377 / Virus Database: 211 - Release Date: 7/15/2002
---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.377 / Virus Database: 211 - Release Date: 7/15/2002
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.377 / Virus Database: 211 - Release Date: 7/15/2002
|
|
|
View Cart
