Solved

Displaying number of records depdning on table contents - classic asp

Posted on 2016-09-04
13
53 Views
Last Modified: 2016-09-05
Hi all,

I want to start messing around with KPI's in my system. I know how to do a simple record count (code displayed below).

I have a task management system where my users have a lot of control concerning system behaviour. For instance, when a task is created it gets the status recieved and when it's done it gets the status Completed. However - in between these types of status, the user can freely define as many status types as they want. So what I want to accomplish is this:

I want my code to run through my Task table counting the number of records per task status - i.e. Tasks completed = 58, Tasks Received = 32, Tasks under review = 23, Tasks waiting on repair = 12 etc. I wont know beforehand what type of status's are present in the system (besides Received and completed) and they may change. How do I accomplish this? Current code below which doesn't solve the issue as it oly looks for one type of task.

Dim objconn,objRSM,strSQL

Set objconn = Server.CreateObject("ADODB.Connection")
objconn.Open strConnect

	  strSQL = "SELECT Count(*) as Numrecords FROM Fejlmelding order by Status ASC"
	     set objRSM = objconn.Execute(strSQL)
             set objRSM = objconn.Execute(strSQL)
         
Modtaget = (objRSM("NumRecords"))

Open in new window

0
Comment
Question by:micamb
[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
  • 7
  • 6
13 Comments
 
LVL 52

Expert Comment

by:Ryan Chong
ID: 41784291
how's your table design looks like? in general, you need to group by with the status text, like:
SELECT Status , Count(Status ) as Numrecords
FROM Fejlmelding
group by Status
order by Status ASC

Open in new window

0
 

Author Comment

by:micamb
ID: 41784295
That returns the number 5 which puzzles me as there in that particular database only exists 4 types currently (Received, Completed, See comments & rejected). How do I display the status text next to the number(s)?
0
 
LVL 52

Expert Comment

by:Ryan Chong
ID: 41784301
>>That returns the number 5 which puzzles me as there in that particular database only exists 4 types currently (Received, Completed, See comments & rejected)

if we are querying same source (same table in same database) with same query, it should return the exact number of records, either as you claimed it 4 or 5 records in total.

>>How do I display the status text next to the number(s)?
do you want to do it in query or in asp ?
0
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

Author Comment

by:micamb
ID: 41784303
Just to clarify:

I'm looking for code, that can display thinsg like this (in this particular database):

Recieved = 12
See comments = 3
Completed = 7
Rejected = 3

(And any other status type found in that table)
0
 

Author Comment

by:micamb
ID: 41784304
Query, please - and thank you so much for the assistance so far!
0
 
LVL 52

Expert Comment

by:Ryan Chong
ID: 41784310
may i also know what database are you using?
0
 

Author Comment

by:micamb
ID: 41784315
mysql
0
 
LVL 52

Expert Comment

by:Ryan Chong
ID: 41784318
see if this is what you need?
SELECT concat(Status, ' = ' , Count(Status )) as Numrecords
FROM Fejlmelding
group by Status
order by Status ASC

Open in new window

0
 

Author Comment

by:micamb
ID: 41784329
We are getting close. This returned:
Completed = 713

However the other types of status are missing. I probably need a loop of some sort? I'll just post the code as it looks now.

<!--#include file="db/database_read.inc"--><%
Dim objconn,objRSM,strSQL

Set objconn = Server.CreateObject("ADODB.Connection")
objconn.Open strConnect

	  strSQL = "SELECT concat(Status, ' = ' , Count(Status )) as Numrecords FROM Fejlmelding group by Status order by Status ASC"
	     set objRSM = objconn.Execute(strSQL)
             
         


Records = (objRSM("Numrecords"))



%>
<br><%= Records %><br>

Open in new window

0
 
LVL 52

Accepted Solution

by:
Ryan Chong earned 500 total points
ID: 41784335
>>However the other types of status are missing

you need to do a loop in your asp codes... like:
<!--#include file="db/database_read.inc"--><%
Dim objconn,objRSM,strSQL

Set objconn = Server.CreateObject("ADODB.Connection")
objconn.Open strConnect

	  strSQL = "SELECT concat(Status, ' = ' , Count(Status )) as Numrecords FROM Fejlmelding group by Status order by Status ASC"
	     set objRSM = objconn.Execute(strSQL)
             
do while objRSM.eof = false
    Records = (objRSM("Numrecords"))
   response.write "<br>" & Records & "<br>"
   objRSM.movenext
loop
%>

Open in new window

0
 

Author Comment

by:micamb
ID: 41784359
Absolutely amazing! Thanks a million!
0
 

Author Closing Comment

by:micamb
ID: 41784360
Great solution that solved my problem quickly and efficiently!
0
 
LVL 52

Expert Comment

by:Ryan Chong
ID: 41784375
you're welcome glad could make help, cheers
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

626 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