Sort by date but group similar fields

Question

I am new to Stack Overflow and to ASP, but this site has bailed me out many times! I am very unfamiliar with ASP and VBS but more familiar with PHP, so if there is a PHP solution to my problem, that will be okay too.

A little background - My access DB has two tables (that are relevant to this query) one is called SignUpLog and the other Notes. The SignUpLog.FirstNoteAddr field corresponds to the Notes.NoteKey field in the other table.

I've been successful in showing all the entries in the DB, but what I'd like to do is group all entries for a specific patient together in one row, while still ordering by date (newest at top).

Here's my code:

Set DataConn = Server.CreateObject("ADODB.Connection")
Set RS = Server.CreateObject("ADODB.RecordSet")
DataConn.Open "DBQ=" & Server.Mappath("/path/to/mydb") & ";Driver={Microsoft Access Driver (*.mdb)};Uid=user;Pwd=pass;"

Set rsDsp = DataConn.Execute("SELECT SignUpLog.PatientFileNumber, SignUpLog.ArrivalDateTime, Notes.Note, SignUpLog.Called, SignUpLog.DrName FROM SignUpLog, Notes WHERE (((Notes.NoteKey)=[SignUpLog].[FirstNoteAddr])) ORDER BY SignUpLog.ArrivalDateTime DESC;")

If rsDsp.EOF Then
    Response.Write "Sorry, no entries in the database!"
Else
%>
<div align="center"><center>
    <table BORDER="0" width="700">
        <tr>
            <th width="105">Name</th>
            <th width="105">Arrival Time</th>
            <th width="105">Doctor</th>
            <th width="105">Notes</th>
        </tr>
 <%
   While Not rsDsp.EOF
     x = x + 1
     If x = 1 Then
       Response.Write "<TR><TD>" & rsDsp.Fields.Item("PatientFileNumber").Value & "</TD>"
       Response.Write "<TD>" & rsDsp("ArrivalDateTime").Value & "</TD>"
        Response.Write "<TD>" & rsDsp("DrName").Value & "</TD>"
        Response.Write "<TD>" & rsDsp("Note").Value & "</TD></TR>"
     Else 
       Response.Write "<TR><TD BGCOLOR=E4E4E4>" & rsDsp.Fields.Item("PatientFileNumber").Value & "</TD>"
       Response.Write "<TD BGCOLOR=E4E4E4>" & rsDsp("ArrivalDateTime").Value & "</TD>"
       Response.Write "<TD BGCOLOR=E4E4E4>" & rsDsp("DrName").Value & "</TD>"
       Response.Write "<TD BGCOLOR=E4E4E4>" & rsDsp("Note").Value & "</TD></TR>"
       x = 0
     End If

     rsDsp.MoveNext
   Wend
   Response.Write "</TABLE>"  


   DataConn.Close

   End If
  %>
 </table>
 </center></div>

This gives me an output similar to this:

Patient A | 9/18/2012 12:56:21 PM | Appt | Note1
Patient A | 9/18/2012 12:56:21 PM | Appt | Note2
Patient A | 9/18/2012 12:56:21 PM | Appt | Note3
Patient B | 9/18/2012 1:56:21 PM | WalkIn | Note1
Patient B | 9/18/2012 1:56:21 PM | WalkIn | Note2

What i would like is this :

Patient A | 9/18/2012 12:56:21 PM | Appt | Note1, Note2, Note3
Patient B | 9/18/2012 1:56:21 PM | WalkIn | Note1, Note2

I've tried playing around with Group By and keep getting hung up on Aggregate functions, which is confusing because I'm not trying to do anything mathematical. Like said I am a complete ASP noob, and I'm not a programmer by no means.

score 0 · Answer 1 · edited May 23 '17 at 10:31

0

I believe there already is a solution for your problem here on SO, try looking at THIS question. You will just need to define (a bit complicated) function and use it as in example.

edited May 23 '17 at 10:31

Community

1
1

answered Sep 18 '12 at 18:21

WojtusJ

1,318
1
12
19

This is a bit over my head. and seems a little complicated for what i'm trying to do. Maybe you could elaborate a bit, remember im a noob at this LOL – Steve Linger Sep 18 '12 at 19:51

score 0 · Answer 2 · edited May 23 '17 at 11:54

0

This is really a SQL query issue that has little to do with ASP. Since you're using MDB you may find it easier to model your queries using MS Access, then paste the generated SQL statement back into your ASP code.

The following question can help:

SQL Group with Order by

edited May 23 '17 at 11:54

Community

1
1

answered Sep 18 '12 at 18:21

Diodeus - James MacFarlane

112,730
33
157
176

I was unsure if it was a SQL answer or a ASP(table) answer i was searching for. I tried the solutions in your post, but kept getting errors that the column i was trying to order by was not part of an aggregate function. Thanks for the fast reply though! – Steve Linger Sep 18 '12 at 19:49
Try doing what you want in the MS Access visual query builder interface, it makes it brain-dead easy. – Diodeus - James MacFarlane Sep 18 '12 at 20:03

score 0 · Answer 3 · edited Sep 24 '12 at 09:24

Serious fails in the HTML.

Anyway, here's a quick solution by making a few mods to the logic for looping through the result set and displaying it.

<%
Set DataConn = Server.CreateObject("ADODB.Connection") 
Set RS = Server.CreateObject("ADODB.RecordSet") 
DataConn.Open "DBQ=" & Server.Mappath("/path/to/mydb") & ";Driver={Microsoft Access Driver (*.mdb)};Uid=user;Pwd=pass;" 

Set rsDsp = DataConn.Execute("SELECT SignUpLog.PatientFileNumber, SignUpLog.ArrivalDateTime, Notes.Note, SignUpLog.Called, SignUpLog.DrName FROM SignUpLog, Notes WHERE (((Notes.NoteKey)=[SignUpLog].[FirstNoteAddr])) ORDER BY SignUpLog.ArrivalDateTime DESC;") 

If rsDsp.EOF Then 
    Response.Write "Sorry, no entries in the database!" 
Else 
%> 
<div align="center">
    <table BORDER="0" width="700"> 
        <tr> 
            <th width="105">Name</th> 
            <th width="105">Arrival Time</th> 
            <th width="105">Doctor</th> 
            <th width="105">Notes</th> 
        </tr> 
 <%
   Dim LastPatient;
   Dim Notes = "";
   Dim x = 0;
   While Not rsDsp.EOF

     If LastPatient = rsDsp.Field.Item("PatientFileNumber").Value Then
       Response.Write "<br />" & rsDsp("Note").Value
     Else
       If LastPatient <> NULL Then
         Response.Write "</td></tr>"
       If x Mod 2 = 0 Then 
         Response.Write "<tr><td>" & rsDsp.Fields.Item("PatientFileNumber").Value & "</td>" 
         Response.Write "<td>" & rsDsp("ArrivalDateTime").Value & "</td>" 
         Response.Write "<td>" & rsDsp("DrName").Value & "</td>" 
         Response.Write "<td>" & rsDsp("Note").Value
       Else  
         Response.Write "<tr><td bgcolor=""E4E4E4"">" & rsDsp.Fields.Item("PatientFileNumber").Value & "</td>" 
         Response.Write "<td bgcolor=""E4E4E4"">" & rsDsp("ArrivalDateTime").Value & "</td>" 
         Response.Write "<td bgcolor=""E4E4E4"">" & rsDsp("DrName").Value & "</td" 
         Response.Write "<td bgcolor=""E4E4E4"">" & rsDsp("Note").Value
       End If
       x = x + 1
     End If

     LastPatient = rsDsp.Fields.Item("PatientFileNumber").Value
     rsDsp.MoveNext 
   Wend
   Response.Write "</td></tr>"   


   DataConn.Close 

End If
%> 
 </table> 
</div>

Thanks for all the help guys, i ended up using mdb-export to pipe the data directly into mysql, which i can then get my desired results using PHP and the functions built into mysql. group_concat for the win! — Steve Linger, Sep 24 '12 at 18:13

Sort by date but group similar fields

3 Answers3