Abbreviate/combine records from same field?

[dayna]

And now for another rambling, poorly articulated question from yours truly…

I’m working on a db that, amongst other things, stores schedule information for the various classes that my organization offers at various times. Since different classes meet at different times on different days, and even a different number of days per week, I designed tblClassSchedule as follows:

Schedule ID (PK)
ClassID (FK from tblClasses)
Day (FK from lkpDaysOfWeek)
StartTime
EndTime

This design seems to fulfill its purpose very well when it comes to things like searching and calculating and so forth, but I’m having trouble getting rptClassSchedule to display according to my supervisors’ specs. They don’t like seeing each day that a class meets represented in a separate row and want to see the schedule info in a single row with two columns. So, for example, the schedule for a class that meets Monday through Thursday from 9:00am to 12:00pm appears on my report as:

DAY START TIME END TIME
Monday 9:00am 12:00pm
Tuesday 9:00am 12:00pm
Wednesday 9:00am 12:00pm
Thursday 9:00am 12:00pm

My supervisors want to see something along the lines of:

DAYS TIMES
MTWTh 9:00am – 12:00pm

Of course, I agree that the latter is more aesthetically pleasing, but I can’t seem to figure out how to get there. Is it even possible to concatenate or programmatically abbreviate records from the same field? More specifically, is it conceivable that someone with very modest VBA skills might be able to accomplish this? (I embed some fairly basic event procedures into my forms from design view, but that’s about the extent of it.)

As always, if someone can help me formulate my question in searchable terms, I’m happy to do the research and grunt work myself. If what I’m trying to do is possible, what is it called? If what I’m trying to do isn’t possible, will you kindly end my misery and tell me so? What about a third way: something that gets me closer to my supervisors’ format preferences, but isn’t totally beyond the scope of my abilities? Thanks!

 

[namliam]

Well, how about this:
Make a crosstab query using Weekday(schedule) as your column headers, then you have what days the schedule is on one row = reportable.

Depending on your need you can then use an IIF or Check box or something you prever to display the report as desired.

Good luck

 

[dayna]

Thanks, backwards mailman! I’ve played around with your idea quite a bit, but I’m afraid I don’t have much success to report.

The crosstab does get all of my days on one row, but I’m having trouble hiding the days for which there are no class times. Basically, I made a report based on the crosstab and hid all the controls except ClassID (row headings) and the ClassDay labels (column headings). Then, I grouped the report on ClassID and put the ClassID and ClassDay controls in the group header section. In the OnFormat event of the group header section, I wrote a bunch of If statements to hide the ClassDay labels if the hidden detail controls have no value. This works, but only for the first class on the list. I can’t figure out how to get the labels to re-format for each class. Does that make any sense at all?

I’ve also been experimenting with a sample db that I found here. I was able to mimic khawar’s example well enough to get the days displaying in a textbox the way I’d like, but there are a few catches:

1) The example is based on forms, not reports. I created some forms just to learn how the sample worked and replicate it as closely as possible, but what I need is a report. It’s probably not a huge issue, but at this point I’m not entirely sure how to adapt this method to my actual needs.

2) Although this method does concatenate all the days for each class in tblClassSchedule, some classes meet at different times on different days (just to makes things even more complex). So, a class may meet from 1:00 – 3:00 on Monday and Wednesday, and then from 12:00 – 5:00 on Friday. In that case, I’d want two rows:

MW 1:00 – 3:00
F 12:00 – 5:00

…you know, like a continuous subform. Since it’s probably an SQL issue, I’ll go ahead and post the code I’m calling in the OnCurrent event of my form:

Function AbbrDays(LngMainId As Long) As String

Dim StrSql As String
    
    StrSql = "SELECT DISTINCT tblClassSchedule.ClassID, lkpDay.Abbr, lkpDay.DayID "
    StrSql = StrSql & "FROM lkpDay INNER JOIN tblClassSchedule "
    StrSql = StrSql & "ON lkpDay.DayID = tblClassSchedule.ClassDay "
    StrSql = StrSql & "WHERE tblClassSchedule.ClassID= " & LngMainId & " "
    StrSql = StrSql & "ORDER BY tblClassSchedule.ClassID, lkpDay.DayID;"
    
    Set db = CurrentDb()
    Set rs = db.OpenRecordset(StrSql)
If rs.RecordCount > 0 Then
    rs.MoveFirst
    
        Do While Not rs.EOF
        AbbrDays = AbbrDays & ", " & rs.Abbr
        
        rs.MoveNext
    Loop
Else
End If
    rs.Close

If AbbrDays = "" Then
Else
AbbrDays = Right(AbbrDays, Len(AbbrDays) - 2)
End If
End Function

Sub UpdateAbbr()
Dim LngId As Long

    If IsNull(Forms!XfrmClassSchedule!ClassID) Or Forms!XfrmClassSchedule!ClassID = "" Then
        LngId = 0
    Else
        LngId = Forms!XfrmClassSchedule!ClassID
    End If
    
Forms!XfrmClassSchedule!txtDays = AbbrDays(LngId)
Forms!XfrmClassSchedule.Refresh
End Sub

 

[dayna]

the concatenation gods mock me still...

I found another (seemingly) promising example of concatenating records from the same field in this thread. It calls a custom function from a query. Although the sample works beautifully on my computer, when I run the same module in my db, I get this compile error:

“User-defined type not defined.”

The first line of this code is highlighted:

Public Function Conc(Fieldx, Identity, Value, Source) As Variant
  Dim cnn As ADODB.Connection
  Dim rs As ADODB.Recordset
  Dim SQL As String
  Dim vFld As Variant
  
  Set cnn = CurrentProject.Connection
  Set rs = New ADODB.Recordset
  vFld = Null
  
  SQL = "SELECT [" & Fieldx & "] as Fld" & _
        " FROM [" & Source & "]" & _
        " WHERE [" & Identity & "]=" & Value
  
  ' open recordset.
  rs.Open SQL, cnn, adOpenForwardOnly, adLockReadOnly
  
  ' concatenate the field.
  Do While Not rs.EOF
    If Not IsNull(rs!Fld) Then
      vFld = vFld & ", " & rs!Fld
    End If
    rs.MoveNext
  Loop
  ' remove leading comma and space.
  vFld = Mid(vFld, 3)
  
  Set cnn = Nothing
  Set rs = Nothing
  
  ' return concatenated string.
  Conc = vFld
End Function

It serves me right that the code wouldn’t work; I only understand about half of it. Can anyone help me figure out where I’ve gone astray or how else I might tackle this problem of concatenating records? Thanks!

 

[georgedwilkinson]

I'm guessing that your project can't see the ADO library. In the VB Editor, go to Tools|References and find/select the appropriate Microsoft ActiveX Data Objects reference.

Of course it could be something else but ADO is not included in VB projects by default.

 

[dayna]

You would be guessing right, George, you dear, sweet, wonderful man!! Someone in Austin loves you!

 

[georgedwilkinson]

You would be guessing right, George, you dear, sweet, wonderful man!! Someone in Austin loves you!

Dang, I need to help you more often!

I'm happy to help.

I get over to Austin every now and then to do work for Dell on one of their databases. Nice people.

 

[namliam]

This is probably mute, since I think you allready fixed your problem...

However, how about taking that crosstab query then running another query on top of that.

This second query would contain the IIFs and execute that per line to concatinate all into one textstring.
This query would then be the base of your report.

 

[khawar]

Download the attached sample i have worked out two solutions for you one by using a crosstab query and second using VBA

 

[dayna]

George: I don’t know if folks in Austin are especially nice, but we can definitely come off that way, given our legendary predilection for “self-medication.” Besides, in a town where jeans qualify as “business casual,” and roads close for Eeyore’s birthday celebration, what else can one do but smile, smile, smile? Thanks again.

Mailman: Now that you mention it, it seems pretty obvious. If this forum offered a “My God, I’m astonished at my own stupidity!” emoticon, I would definitely employ it in this context. At any rate, I’ll play around with that and see what I can come up with. Thanks for bearing with me.

Khawar: I really appreciate the sample. Thank you so much for taking the time to do that. However, if you thought I was hopelessly ignorant before, just wait ‘til you hear this: I can’t figure out how to access the navigation pane or view the objects in your sample! The VBA solution is definitely what I’m after, but I don’t see any code embedded into the report itself, and like I said, there isn’t a navigation pane on the left, so I can’t find the tables, queries, or modules.

All: I was able to get my ClassDay field concatenated by calling the custom Conc function (posted above) in a query. I’ve got the basic idea working well, but there are a few minor glitches. I posted a sample to show you what I’ve got.

Basically, I need some help adjusting the function to accept two criteria rather than just one. That ought to get classes that meet at different times on different days to display properly. (See the Advanced GED class in the sample.) How can I squeeze my ClassTime field into that Where clause?

This may be a little too complicated, but there are some classes (see Beginning GED) that meet in the morning, take a lunch break, and then meet again in the afternoon. Is it possible not to concatenate records if their values are equal?

Finally, when I concatenate my StartTime and EndTime fields, I lose the Medium Date formatting that I specified in the table. I really have no need to display seconds on my report. Can anyone help me fix that time formatting?

Thanks so much!

 

[georgedwilkinson]

Howz about:

Public Function Conc2(Fieldx, Identity1, Identity2, Value1, Value2, Source) As Variant
  Dim cnn As ADODB.Connection
  Dim rs As ADODB.Recordset
  Dim SQL As String
  Dim vFld As Variant
  
  Set cnn = CurrentProject.Connection
  Set rs = New ADODB.Recordset
  vFld = Null
  
  SQL = "SELECT [" & Fieldx & "] as Fld" & _
        " FROM [" & Source & "]" & _
        " WHERE [" & Identity1 & "]=" & Value1 & _
        " AND [" & Identity2 & "]=" & Value2
  
  ' open recordset.
  rs.Open SQL, cnn, adOpenForwardOnly, adLockReadOnly
  
  ' Concatenate the field.
  Do While Not rs.EOF
    If Not IsNull(rs!Fld) Then
      vFld = vFld & ", " & rs!Fld
    End If
    rs.MoveNext
  Loop
  ' remove leading comma and space.
  vFld = Mid(vFld, 3)
  
  Set cnn = Nothing
  Set rs = Nothing
  
  ' return Concatenated string.
  Conc2 = vFld
End Function

You could have also achieved the same thing by just sending the whole where clause (except the last "=") in the field called Value.

 

[khawar]

Press the shift key and hold till database opens navigation pane will be visible

 

[namliam]

George:
Mailman:
Khawar:
All:

What is all this about then??

 

[dayna]

It's a miracle!

Yaaaaaaaay!!! The noble Khawar has succeeded in appeasing the angry gods of concatenation, and mighty gifts exquistely sorted and abbreviated records rain down from my query like light from the heavens. Praise be to Khawar, and to George, and to Mailman for delivering my db from the clutches of damnation and allowing me to proceed with the rest of my life. Thanks so much, guys! XOXOXO