Solved

Count Records Query

Posted on 2014-02-06
12
473 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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 250 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

737 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