?
Solved

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

Posted on 2010-01-05
19
Medium Priority
?
409 Views
Last Modified: 2012-05-08
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
0
Comment
Question by:archangelman
  • 9
  • 8
  • 2
19 Comments
 
LVL 5

Expert Comment

by:eantar
ID: 26183069
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
 
LVL 5

Expert Comment

by:eantar
ID: 26183085
Don't forget to add your table. before doing the work suggested above.
0
 
LVL 2

Expert Comment

by:autosblindo
ID: 26183178
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 2

Expert Comment

by:autosblindo
ID: 26183247
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
 

Author Comment

by:archangelman
ID: 26183360
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
 
LVL 2

Expert Comment

by:autosblindo
ID: 26183482
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
 

Author Comment

by:archangelman
ID: 26183510
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
 
LVL 2

Expert Comment

by:autosblindo
ID: 26183547
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
 
LVL 2

Expert Comment

by:autosblindo
ID: 26183578
Sorry, the or is not necessary:

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

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

0
 

Author Comment

by:archangelman
ID: 26183657
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
 
LVL 2

Expert Comment

by:autosblindo
ID: 26183807
>>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
 
LVL 2

Expert Comment

by:autosblindo
ID: 26183833
I forgot, after last end if write:

NewMailObj.Body = StrMailMessage
0
 

Author Comment

by:archangelman
ID: 26184443
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
 

Author Comment

by:archangelman
ID: 26184518
OK, I got the date working:

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

I still don't konw why the contents are empty.  
0
 
LVL 2

Accepted Solution

by:
autosblindo earned 2000 total points
ID: 26184791
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
 

Author Comment

by:archangelman
ID: 26185126
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
 

Author Closing Comment

by:archangelman
ID: 31673069
Amazing help!  Thank you so very much!!
0
 
LVL 2

Expert Comment

by:autosblindo
ID: 26185273
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
 

Author Comment

by:archangelman
ID: 26185425
Ah yes.  Thank you for the clarification and correction.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone 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

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

831 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