Solved

Count Records Query

Posted on 2014-02-06
12
466 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
  • 6
  • 5
12 Comments
 
LVL 32

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 32

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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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 32

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 32

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 32

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 32

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

816 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now