How to move multiple records about same person to one longer record?

[Promise007]

I am trying to give a the OGT test to several of the school's students. There are five possible tests that a student may need to take. Some only needs one, and so on. The information that I am getting from the student information system displays the information about each student in one to five rows or records, depending on the number of tests the student has to take. The student's name and ID number are repeated in each row, but the name of the test is unique and allowing one test name in each row. For example Bill Smith might have up to 5 rows with each row ending with one of the following 5 test names: 10R, 10W,10S,10C, 10M. How can I limit the rows to one row for each student with the 5 different test listed at the end of the row. For example:

Bill Smith StudentID 10R 10M 10S 10C
Robert Williams StudentID 10M 10W 10S
Betty Lee StudentId 10M 10s 10c

Thanks.

[PhilOfWalton]

Tad short of information, so I can only give a guide.

I am assuming you hava a table of Students, a table of Tests and a table JoinStudentTest.

On that assumption you, need to create a function that with a recordset based on the JoinStudentTest and the Test. A query passed the StudentID to the function, the function reads all the tests taken by that student and adds each test name to a string, (probably with a comma after each test name). The output of the function is the string (minus the final comma) which is passed back to the query. The query is based only on the Student table.

If you get stuck, let's have details of your tables.

Phil

[jforbes]

Most people use this function from Allen Browne to merge Rows, Concatenate values from related records It's pretty much the function that Phil is describing.

[Promise007]

I followed the suggestions that you both provided. I did get exactly what I was looking for. I used the following expression: Which_Tests: ConcatRelated("[OGT Test]","[Copy Of Copy Of WhoNeedsOGT]","[State Student ID] = """ & [StateStudentId] & """")

But when I went to close everything down and open it back up again, I got the following error message:
"Undefined function in expression"

What happened and what should I do?

Thanks.

[Promise007]

I think I found the problem. I located a post on the following website:
http://datapigtechnologies.com/blog/...ons-in-access/

The person was responding to someone else who had a similar problem. The issue is making the mistake of naming the module the same as the function. I am attempting to correct this error.

Thanks.

[Promise007]

I was able to finally figure out the various issues I was having. I did get things working for a second on Friday, but then I got an error message saying that there was an undefined function in the expression. I finally found that the module and the function have to have different names. Then I got another error message saying that there was an ambiguous in the expression. The problem was that I had two modules with different names, but the same function with the same name. Once I deleted one of the two, then the error message changed once more. The final error involved a space between the parentheses and the brackets. Once I removed the space, everything worked fine. Thanks for your help.