?
Solved

Count Records Query

Posted on 2014-02-06
12
Medium Priority
?
475 Views
Last Modified: 2014-02-07
I need to count records that meet certain criteria. I thought this was very straightforward but I'm missing something.
	    sql = "SELECT COUNT(*) AS iTotal FROM studentsAll WHERE (lastName = '" & last_name & "' AND firstName = '" & first_name & "');"
		Set rs=conn.Execute(sql)
		   		  
		textfile.WriteLine Now
		textfile.WriteLine "<BR><B>SQL:   </B>  " & (sql)
		textfile.WriteLine "<BR><B>Total:   </B>  " & iTotal
		textfile.WriteLine "<br>===============================<p>"

Open in new window

     
Nothing prints for iTotal. I've tried CInt(iTotal) and it always produces a zero.
0
Comment
Question by:slegy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
12 Comments
 
LVL 33

Expert Comment

by:Big Monty
ID: 39840400
there's nothing wrong with the actual sql, my guess is it's the where clause that causing the issue. did you try running the query directly in the database, plugging in values for first and last name? are those fields matched up case-wise, meaning if they're all lower case, then that is how it should appear in the query
0
 

Author Comment

by:slegy
ID: 39840422
Log:
2/6/2014 3:57:07 PM
SQL:  SELECT COUNT(memberID) AS iTotal FROM membersAll WHERE (lastName = 'Ellis' AND firstName = 'Scot');
Total:

Partial database fields:
ELLSC000      CN      173      Mr      Ellis      Scot                        M
0
 
LVL 33

Expert Comment

by:Big Monty
ID: 39840427
that log is showing a table of membersAll where your original query is showing a table of studentsAll
0
How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

 
LVL 26

Expert Comment

by:jerryb30
ID: 39840428
where do last_name and first_name come from? A form?
0
 

Author Comment

by:slegy
ID: 39840438
Stumbled across this example and it works:

	    sql = "SELECT COUNT(memberID) AS iTotal FROM membersAll WHERE (lastName = '" & last_name & "' AND firstName = '" & first_name & "');"
		Set rs=conn.Execute(sql)
		   		  
		textfile.WriteLine Now
		textfile.WriteLine "<BR><B>SQL:   </B>  " & (sql)
		textfile.WriteLine "<BR><B>Total:   </B>  " & rs("iTotal")
		textfile.WriteLine "<br>===============================<p>"	

	    If rs("iTotal") > 0 Then

Open in new window

I'm a little confused!
0
 
LVL 33

Expert Comment

by:Big Monty
ID: 39840450
thats because you're selecting from a different table
0
 

Author Comment

by:slegy
ID: 39840487
I changed the name of the table when I posted the question. I'm confused about the rs("iTotal") causing it to work. None of the examples I've been reviewing for the past couple of hours looked like this.
0
 
LVL 33

Expert Comment

by:Big Monty
ID: 39840503
it didnt work originally because you never assigned the database value to the asp variable iTotal, that is why nothing was showing. Once you did that, it displayed as it should have. if you changed your code to the following, it'll also work:

sql = "SELECT COUNT(memberID) AS iTotal FROM membersAll WHERE (lastName = '" & last_name & "' AND firstName = '" & first_name & "');"
		Set rs=conn.Execute(sql)
		iTotal = rs("iTotal")   		  
		textfile.WriteLine Now
		textfile.WriteLine "<BR><B>SQL:   </B>  " & (sql)
		textfile.WriteLine "<BR><B>Total:   </B>  " & iTotal
		textfile.WriteLine "<br>===============================<p>"

Open in new window

0
 

Author Comment

by:slegy
ID: 39840539
I so apologize for confusing this. My initial changes to the query were meant to make it generic and remove actual names - which I did not do so well. last_name and first_name come from info returned from a social media login.

I just ran a test where I had changed the code in several places and it worked:

	    sql = "SELECT COUNT(memberID) AS iTotal FROM membersAll WHERE (lastName = '" & last_name & "' AND firstName = '" & first_name & "');"
		Set rs=conn.Execute(sql)
		   		  
		textfile.WriteLine Now
		textfile.WriteLine "<BR><B>SQL:   </B>  " & (sql)
		textfile.WriteLine "<BR><B>Total:   </B>  " & rs("iTotal")
		textfile.WriteLine "<br>===============================<p>"	

	    If rs("iTotal") > 0 Then...

Open in new window

iTotal is not a field in the database.
0
 
LVL 33

Accepted Solution

by:
Big Monty earned 1000 total points
ID: 39841986
correct, iTotal is an alias in your query that counts the number of records (memberID) that meets you where clause conditions.

the latest code you just gave us, is that now working? if so, do you have any other questions?  I'm guessing there may be a disconnect in what you're asking and what we're understanding...not a big deal, i just want to make sure you get this resolved :)
0
 

Author Closing Comment

by:slegy
ID: 39842389
Yes, everything is working well. I guess I just didn't completely understand how the "AS" works. As always, you've been great. Thank you.
0
 
LVL 33

Expert Comment

by:Big Monty
ID: 39842586
many thanks :)

think of the AS keyword that allows you to name a column whatever you like:

select count( userID ) as numberOfUsers, firstName + ' ' + lastName as fullName, ......
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question