如何组合来自多行(concatrelated)的信息而不会在我的报告中获得重复值? [英] How do I combine information from multiple rows (concatrelated) without getting duplicate values in my report?

问题描述

我创建了一个包含这些表的数据库:

• 学生名单
• 学生手册编号
• 学生需求
• 教师信息

在学生需求表中，我输入了与此类似的信息:

Student ID Column, Last Name, First Name, Reading Special Need123456 鼠标米奇词典123456 老鼠米奇加时赛123456老鼠米奇小团123456 鼠标米奇类型答案选择654321 Duck Daffy 字典654321 Duck Daffy 词库654321 鸭达菲小团

我需要像这样拉出它:

Student ID Column, Last Name, First Name, Reading Special Need123456 鼠标米奇词典，加时赛，小组，输入答案选项654321 Duck Daffy 词典、同义词库、小组

我在我的报表控件源中使用了 concatrelated 函数，它可以将所有需求放在一起，但是因为我的查询多次列出学生，所以它在报表上多次列出学生.像这样:

Student ID Column, Last Name, First Name, Reading Special Need123456 鼠标米奇词典，加时赛，小组，输入答案选项123456 鼠标米奇词典，加时赛，小组，输入答案选项123456 鼠标米奇词典，加时赛，小组，输入答案选项123456 鼠标米奇词典，加时赛，小组，输入答案选项654321 Duck Daffy 词典、同义词库、小组654321 Duck Daffy 词典、同义词库、小组654321 Duck Daffy 词典、同义词库、小组

我已经尝试了所有我能想到的来解决这个问题 - 导出报告并删除重复项 - 但随后导出在 255 个字符处被切断 - 所以这不起作用.当然，我遗漏了一些相对容易的东西 - 但我想不通！

解决方案

使用我的

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!

解决方案

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:

这篇关于如何组合来自多行(concatrelated)的信息而不会在我的报告中获得重复值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！