ms access - 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

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: