Query in Access With Group By or Equivalent with Output in Classic ASP

I am a PHP / MySQL developer that has been given the task of adding a view / email report within an classic ASP / Access application.  Normally, I would write a simple query with an ORDER BY and a GROUP BY statement, but this does not seem to work with Access.  So, I need expert help.  Let me define the problem:

Assume an Access Database Table named CRM with the following fields:  ID, type, employee, message.
Assume that there are five distinct type values: email, phone, meeting, voicemail, other.
(There is an image attached of the database structure for reference.)

I am trying to write a simple query that generates a report sorted by Employee and showing the count of each type for that employee today.  

Here is an example of what I mean:

WS
email = 3
phone = 5
voicemail = 6

DR
email = 5
phone =35
voicemail = 9

How can this query be written?  You can assume that the current date can be brought into the query as a variable.  

The output of the query needs to be viewed on a Web page using Classic ASP.  If you can provide that code, that would be great as well.  Assume I am using the following connection:
Set objRS = Server.CreateObject("ADODB.Recordset")



Database-Picture.jpg
archangelmanAsked:
Who is Participating?
 
autosblindoCommented:
I can't understand: you receive a blank email, or you receive nothing?

Try this:

Dim ObjConn, ObjR, StrSql
Dim name, email, message, NewMailObj
Dim StrMailMessage
StrMailMessage = ""

Set ObjConn = CreateObject("ADODB.Connection")
ObjConn.ConnectionString = "driver={Microsoft Access Driver (*.mdb)};dbq=" & server.mappath("./DB_Access/DbName.mdb")
ObjConn.Open

StrSql = "SELECT Employee, Type, Count(Type) AS CountType .......

Set ObjRs = server.CreateObject("adodb.recordset")
ObjRs.Open StrSql, ObjConnDB, adOpenStatic, adLockReadOnly

if not(ObjRs.EOF) then
  Set NewMailObj=CreateObject("CDO.Message")
  NewMailObj.From = "test@test.com"      
  NewMailObj.To = "recipient at test.com"
  NewMailObj.Subject = "Activity Report as of " & Date()
  'Use "NewMailObj.Subject = "Activity Report as of " & Now()" if you want date and time.

   Do while not ObjRs.EOF
          If StrEmp <> ObjRs("Employee") Then
              StrMailMessage = StrMailMessage &  "<br />Employee: " & ObjRs("Employee") & VbCrLf
      End IF
       StrMailMessage = StrMailMessage & ObjRs("Type") & " " & ObjRs("TheCount") & VbCrLf
        Response.Write "<br />"
      StrEmp = ObjRs("Employee")
      ObjRs.MoveNext
   Loop
 
  NewMailObj.TextBody = StrMailMessage

  NewMailObj.Configuration.Fields.Item _
  ("http://schemas.microsoft.com/cdo/configuration/sendusing")=2
  NewMailObj.Configuration.Fields.Item _
  ("http://schemas.microsoft.com/cdo/configuration/smtpserver")="mail.test.com" 'Put here a real smtp server
  NewMailObj.Configuration.Fields.Item _
  ("http://schemas.microsoft.com/cdo/configuration/smtpserverport")=25
  NewMailObj.Configuration.Fields.Item _
  ("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout")=10
  NewMailObj.Configuration.Fields.Item _
  ("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate")=1
  NewMailObj.Configuration.Fields.Item _
  ("http://schemas.microsoft.com/cdo/configuration/sendusername")="user@test.com" 'User and Password only if smtp need them
  NewMailObj.Configuration.Fields.Item _
  ("http://schemas.microsoft.com/cdo/configuration/sendpassword")="password" 'User and Password only if smtp need them

  NewMailObj.Configuration.Fields.Update
  NewMailObj.Send

  'set myMail=nothing 'What is this code?

  NewMailObj.Send
  Set NewMailObj = nothing
  Response.write "The email was sent."
Else
  Response.write "The email was not sent because there are no data."
End if

ObjRs.Close
Set ObjRs = nothing

ObjConn.close
Set ObjConn = nothing
0
 
eantarCommented:
Go into the query designer for a regular query. Drop down View and choose Totals.

Select the Employee field as a Total Type of Group and any other field you want as a Total Type of Count.

That should do it.

E
0
 
eantarCommented:
Don't forget to add your table. before doing the work suggested above.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
autosblindoCommented:
I can't understant why your query with GROUP BY is not working, I think it's easy:

SELECT Employee, Type, Count(Type)
FROM CRM
GROUP BY Employee, Type
ORDER BY Employee, Type

Then you can use your recordset in your asp page without problems.

Bye.
0
 
autosblindoCommented:
For the asp page you should use something like this:
Dim ObjConn, ObjR, StrSql

Set ObjConn = CreateObject("ADODB.Connection")
ObjConn.ConnectionString = "driver={Microsoft Access Driver (*.mdb)};dbq=" & server.mappath("./DB_Access/DbName.mdb")
ObjConn.Open

StrSql = "SELECT Employee, Type, Count(Type) AS CountType .......

Set ObjRs = server.CreateObject("adodb.recordset")
ObjRs.Open StrSql, ObjConnDB, adOpenStatic, adLockReadOnly

if not(ObjRs.EOF) then
   Do while not ObjRs.EOF
      Response.Write(ObjRs("Employee"))
      Response.Write(ObjRs("Type"))
      Response.Write(ObjRs("CountType"))
      ObjRs.MoveNext
   Loop
End if

ObjRs.Close
Set ObjRs = nothing
ObjConn.Close
Set ObjConn = nothin
0
 
archangelmanAuthor Commented:
OK, this Query actually worked:

SELECT Employee, Type, Count(Type) FROM CRM WHERE MessageDate = '1/4/2010' GROUP BY Employee, Type ORDER BY Employee, Type

My issue is with the date itself.  How can I get today's date to be inserted in the query dynamically using today's value?  Create a variable and make it equal to Cdate(Date) ?




0
 
autosblindoCommented:
there are at least 2 way:

you can write WHERE MessageData = #" & DataVar & "# (you must use # to delimt data
or
WHERE MessageData = Date() (or Date()-1 for yesterday, Date()-n.....

Be carefull with data format of your access field.
0
 
archangelmanAuthor Commented:
Clear on the Date issue now.  One question regarding the ASP Code loop above.  

When I execure this loop:

if not(ObjRs.EOF) then
   Do while not ObjRs.EOF
        Response.Write(ObjRs("Employee"))
        Response.Write " "
      Response.Write(ObjRs("Type"))
        Response.Write " "
      Response.Write(ObjRs("TheCount"))
        Response.Write "<br>"
      ObjRs.MoveNext
   Loop
End if

The result looks like this:

DR email 10
DR phone 3
MM email 2
MM phone 1
WS email 18
WS phone 8

How can I structure the loop to make the result look like this:

Employee: DR
email 10
phone 3

Employee: MM
email 2
phone 1

Employee WS
email 18
phone 8
0
 
autosblindoCommented:
Dim StrEmp

StrEmp = ""

if not(ObjRs.EOF) then
   Do while not ObjRs.EOF
      If StrEmp = "" or StrEmp <> ObjRs("Employee") Then
              Response.Write("Employee: " & ObjRs("Employee") & "<br />)
      End IF
      Response.Write(ObjRs("Type"))
        Response.Write " "
      Response.Write(ObjRs("TheCount"))
        Response.Write "<br />"
      StrEmp = ObjRs("Employee")
      ObjRs.MoveNext
   Loop
End if
0
 
autosblindoCommented:
Sorry, the or is not necessary:

change this :If StrEmp = "" or StrEmp <> ObjRs("Employee") Then

with: If StrEmp <> ObjRs("Employee") Then

0
 
archangelmanAuthor Commented:
Got it.  I made the following change:

if not(ObjRs.EOF) then
   Do while not ObjRs.EOF
'      If StrEmp = "" or StrEmp <> ObjRs("Employee") Then
          If StrEmp <> ObjRs("Employee") Then
              Response.Write("<br />Employee: " & ObjRs("Employee") & "<br />")
      End IF
      Response.Write(ObjRs("Type"))
        Response.Write " "
      Response.Write(ObjRs("TheCount"))
        Response.Write "<br />"
      StrEmp = ObjRs("Employee")
      ObjRs.MoveNext
   Loop
End if

And the output looks like this:

Employee: DR
email 10
phone 3

Employee: MM
email 2
phone 1

Employee: WS
email 18
phone 8

Is the code below how I would email the above?  I am not sure how to store the output of the loop above in a variable or pointer to pull in the email functions below.  

Dim name, email, message, NewMailObj
Set NewMailObj=Server.CreateObject("CDONTS.NewMail")
NewMailObj.From = "test@test.com"
NewMailObj.To = "recipient@test.com"
NewMailObj.Subject = "Activity Report"

' How do I get the loop above to run here?
NewMailObj.Body =

NewMailObj.BodyFormat = 0
NewMailObj.MailFormat = 0
NewMailObj.Send
Set NewMailObj = nothing
Response.write "The email was sent."
0
 
autosblindoCommented:
>>How do I get the loop above to run here?
Dim StrMailMessage
StrMailMessage = ""

if not(ObjRs.EOF) then
   Do while not ObjRs.EOF
          If StrEmp <> ObjRs("Employee") Then
              StrMailMessage = StrMailMessage &  "Employee: " & ObjRs("Employee") & VbCrLf
      End IF
       StrMailMessage = StrMailMessage & ObjRs("Type") & " " & ObjRs("TheCount") & VbCrLf
        Response.Write "<br />"
      StrEmp = ObjRs("Employee")
      ObjRs.MoveNext
   Loop
End if

If i can suggest you, use CDOSYS object instead of CDONTS. Usage is very similar.
http://www.w3schools.com/asp/asp_send_email.asp
0
 
autosblindoCommented:
I forgot, after last end if write:

NewMailObj.Body = StrMailMessage
0
 
archangelmanAuthor Commented:
autosblindo:

Thank you for your amazing help.  I still have one issue with the email.  It is sending but the contents of the body are blank / empty.  Also, to add the date to the subject, do I append a & date()?  Here is the code I am using:

---------------- Code Starts Below --------------

Dim name, email, message, NewMailObj
Set NewMailObj=CreateObject("CDO.Message")
NewMailObj.From = "test@test.com"
NewMailObj.To = "recipient at test.com"
NewMailObj.Subject = "Activity Report"

Dim StrMailMessage
StrMailMessage = ""

if not(ObjRs.EOF) then
   Do while not ObjRs.EOF
          If StrEmp <> ObjRs("Employee") Then
              StrMailMessage = StrMailMessage &  "<br />Employee: " & ObjRs("Employee") & VbCrLf
      End IF
       StrMailMessage = StrMailMessage & ObjRs("Type") & " " & ObjRs("TheCount") & VbCrLf
        Response.Write "<br />"
      StrEmp = ObjRs("Employee")
      ObjRs.MoveNext
   Loop
End if

NewMailObj.TextBody = StrMailMessage

NewMailObj.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusing")=2
NewMailObj.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserver")="mail.test.com"
NewMailObj.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserverport")=25
NewMailObj.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout")=10
NewMailObj.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate")=1
NewMailObj.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusername")="user@test.com"
NewMailObj.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendpassword")="password"

NewMailObj.Configuration.Fields.Update
NewMailObj.Send

set myMail=nothing

NewMailObj.Send
Set NewMailObj = nothing
Response.write "The email was sent."
0
 
archangelmanAuthor Commented:
OK, I got the date working:

NewMailObj.Subject = "Activity Report as of " & Date()

I still don't konw why the contents are empty.  
0
 
archangelmanAuthor Commented:
It worked!  Here is the final code in case it helps someone else:

---------------  Code Below ---------------

<%
Dim ObjRs, StrSql
StrSql = "SELECT Employee, Type, Count(Type) AS TheCount FROM CRM WHERE MessageDate = Date() GROUP BY Employee, Type ORDER BY Employee, Type"
Set objRs = Server.CreateObject("ADODB.Recordset")
ObjRs.Open StrSql, ObjConn

Dim StrEmp

StrEmp = ""

if not(ObjRs.EOF) then
   Do while not ObjRs.EOF
'      If StrEmp = "" or StrEmp <> ObjRs("Employee") Then
          If StrEmp <> ObjRs("Employee") Then
              Response.Write("<br />Employee: " & ObjRs("Employee") & "<br />")
      End IF
      Response.Write(ObjRs("Type"))
        Response.Write " "
      Response.Write(ObjRs("TheCount"))
        Response.Write "<br />"
      StrEmp = ObjRs("Employee")
      ObjRs.MoveNext
   Loop
End if


Dim name, email, message, NewMailObj
Dim StrMailMessage
StrMailMessage = ""

StrSql = "SELECT Employee, Type, Count(Type) AS TheCount FROM CRM WHERE MessageDate = Date() GROUP BY Employee, Type ORDER BY Employee, Type"

Set ObjRs = server.CreateObject("adodb.recordset")
ObjRs.Open StrSql, ObjConn

if not(ObjRs.EOF) then
  Set NewMailObj=CreateObject("CDO.Message")
  NewMailObj.From = "sender@email.com"
  NewMailObj.To = "recipient@email.com"
  NewMailObj.Subject = "Activity Report as of " & Date()
 'NewMailObj.Subject = "Activity Report as of " & Now()" if recpient needs date and time.

   Do while not ObjRs.EOF
          If StrEmp <> ObjRs("Employee") Then
              StrMailMessage = StrMailMessage &  VbCrLf & "Employee: " & ObjRs("Employee") & VbCrLf
      End IF
       StrMailMessage = StrMailMessage & ObjRs("Type") & " " & ObjRs("TheCount") & VbCrLf
        Response.Write VbCrLf
      StrEmp = ObjRs("Employee")
      ObjRs.MoveNext
   Loop
 
  NewMailObj.TextBody = StrMailMessage


NewMailObj.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusing")=2
NewMailObj.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserver")="mail.email.com"
NewMailObj.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserverport")=25
NewMailObj.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout")=10
NewMailObj.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate")=1
NewMailObj.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusername")="username"
NewMailObj.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendpassword")="password"

NewMailObj.Configuration.Fields.Update

NewMailObj.Send
Set NewMailObj = nothing
Response.write "The email was sent."

End If

ObjRs.Close
Set ObjRs = nothing
ObjConn.Close
Set ObjConn = nothing

%>

Awarding points!
0
 
archangelmanAuthor Commented:
Amazing help!  Thank you so very much!!
0
 
autosblindoCommented:
You don't need to open and loop the recordset 2 times.
Change the fist loop to:
if not(ObjRs.EOF) then
   Do while not ObjRs.EOF
'      If StrEmp = "" or StrEmp <> ObjRs("Employee") Then
          If StrEmp <> ObjRs("Employee") Then
              Response.Write("<br />Employee: " & ObjRs("Employee") & "<br />")
              StrMailMessage = StrMailMessage &  VbCrLf & "Employee: " & ObjRs("Employee") & VbCrLf
      End IF
      Response.Write(ObjRs("Type"))
        Response.Write " "
      Response.Write(ObjRs("TheCount"))
        Response.Write "<br />"
       StrMailMessage = StrMailMessage & ObjRs("Type") & " " & ObjRs("TheCount") & VbCrLf
      StrEmp = ObjRs("Employee")
      ObjRs.MoveNext
   Loop
End if

After this you have 2 possibilities:
- put the code to send email between first loop and end if
- after End IF of first loop test the va rStrMailMessage. If is not = "" then put the code to send email inside if end if.

Bye.
0
 
archangelmanAuthor Commented:
Ah yes.  Thank you for the clarification and correction.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.