How do I combine information from multiple rows (concatrelated) without getting duplicate values in my report?

Question

I have created a database that has these tables:

• Student list
• Student booklet numbers
• Student needs
• Teacher Information

In the Student Needs Table I have information entered similar to this:

Student ID Column, Last Name, First Name, Reading Special Need

123456             Mouse      Mickey      Dictionary  
123456             Mouse      Mickey      Extra Time  
123456             Mouse      Mickey      Small group  
123456             Mouse      Mickey      Type Answer Choices  
654321             Duck       Daffy       Dictionary  
654321             Duck       Daffy       Thesaurus  
654321             Duck       Daffy       Small Group  

I need this to be pulled similar to this:

Student ID Column, Last Name, First Name, Reading Special Need  

123456             Mouse      Mickey      Dictionary, Extra time, Small group, type answer choices  
654321             Duck       Daffy       Dictionary, Thesaurus, Small Group

I used the concatrelated function in my report control source and it works to put all of the needs together, but because my query has the student listed multiple times, it is listing the student multiple times on the report. Like this:

Student ID Column, Last Name, First Name, Reading Special Need

123456             Mouse      Mickey      Dictionary, Extra time, Small group, type answer choices  
123456             Mouse      Mickey      Dictionary, Extra time, Small group, type answer choices  
123456             Mouse      Mickey      Dictionary, Extra time, Small group, type answer choices  
123456             Mouse      Mickey      Dictionary, Extra time, Small group, type answer choices  
654321             Duck       Daffy       Dictionary, Thesaurus, Small Group  
654321             Duck       Daffy       Dictionary, Thesaurus, Small Group  
654321             Duck       Daffy       Dictionary, Thesaurus, Small Group  

I have tried all that I can think of to fix this - to the point of exporting the report and deleting duplicates - but then the export cuts off at 255 characters- so that doesn't work. Surely I am missing something that would be relatively easy- but I can't figure it out!

Answer 1

I created a module called "Get List From Table Field" and in it I have the following code:

Public Function GetList(sTable As String, sField As String, Optional sWhere As String, Optional sDelimiter As String, Optional UniqueValues As Boolean) As String
'   compiles all the data from a single field in a table and returns them in a string.
'   has options for providing a sql crieria, a delimiter, and if unique values should be returned
    Dim rs As Recordset
    Dim sList As String
    Dim sChar As String

    If sDelimiter = "" Then
        sChar = ","
    Else
        sChar = sDelimiter
    End If
    Set rs = CurrentDb.OpenRecordset("Select " & IIf(UniqueValues, "Distinct ", "") & sField & " From " & sTable & IIf(sWhere <> "", " Where " & sWhere, ""))
    Do While Not rs.EOF
        sList = sList & sChar & rs.Fields(sField)
        rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing

    sList = Mid(sList, 2) & sChar
    GetList = sList

End Function

In your select query, you can try:

SELECT [Student ID], [Last Name], [First Name], 
GetList("[Student Needs]","[Reading Special Need]","[Student ID] = " & [Student ID]) AS [Reading Special Needs]
FROM [Student Needs]
GROUP BY [Student ID], [Last Name], [First Name];

I also recommend that you use underscores or CamelCase instead of spaces on all your table and field names. That way you can avoid using brackets in your statements. It's a better programming habit to get into. For instance:

Underscore SELECT Student_ID, Last_Name, First_Name FROM Student_Needs

CamelCase SELECT StudentID, LastName, FirstName FROM StudentNeeds

Answer 2

Use my DJoin function and a query like this:

SELECT 
    [Student Needs].[Student ID], 
    [Student Needs].[Last Name], 
    [Student Needs].[First Name], 
    DJoin("[Reading Special Need]","[Student Needs]","[Student ID] = " & [Student ID] & "",", ") AS [Reading Special Needs]
FROM 
    [Student Needs]
GROUP BY 
    [Student Needs].[Student ID], 
    [Student Needs].[Last Name], 
    [Student Needs].[First Name];

Output: