?
Solved

Count Records Query

Posted on 2014-02-06
12
Medium Priority
?
482 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 34

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 34

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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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 34

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 34

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 34

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 34

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

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Audit trails are very important in any system to hold people responsible for certain transactions and hold them to take ownership of their actions. This article is dedicated to all novice "Microsoft Access" developers.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses
Course of the Month7 days, 10 hours left to enroll

608 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