Combine values from related rows into a single concatenated string value

by

This is easy using Allen Browne’s ConcatRelated() function. Copy the function from that web page and paste it into an Access standard code module.

Then this query will return what you asked for.

SELECT
    i.N_ID,
    i.F_Name,
    i.L_Name,
    ConcatRelated(
        "Course_ID",
        "tbl_Courses",
        "N_ID = '" & [N_ID] & "'"
        ) AS Course_IDs
FROM tbl_Instructors AS i;

Consider changing the data type of N_ID from text to numeric in both tables. If you do that, you don’t need the single quotes in the third argument to that ConcatRelated() expression.

"N_ID = " & [N_ID]

And whenever you need N_ID displayed with leading zeros, use a Format() expression.

Format(N_ID, "000")

More Related Contents:

  1. How do i add percentage an Access query?
  2. How does one decompile and recompile a database application?
  3. What are the rules governing usage of parenthesis in VBA function calls?
  4. Autonumber value of last inserted row – MS Access / VBA
  5. How to get random record from MS Access database
  6. Code to loop through all records in MS Access
  7. create form to add records in multiple tables
  8. Operation must use an updatable query. (Error 3073) Microsoft Access
  9. How do I force VBA/Access to require variables to be defined?
  10. Why is my query not updateable?
  11. Hand Install of 64-bit MS Access ODBC drivers when 32-bit Office is present
  12. Is it possible to create a recursive query in Access?
  13. VBA: Difference in two ways of declaring a new object? (Trying to understand why my solution works)
  14. What is the equivalent of Select Case in Access SQL?
  15. How to restart counting from 1 after erasing table in MS Access?
  16. How do I access the selected rows in Access?
  17. Insert SQL command with Datetime in MS-Access
  18. insert query with sequential primary key
  19. How do I execute multiple SQL Statements in Access’ Query Editor?
  20. How to select top 10 in Access query?
  21. Transform multiple rows into columns with unique key
  22. how to show query results in a datasheet in the same form?
  23. Selecting 2 tables from 2 different databases (ACCESS)
  24. connected combo boxes in continuous form – the second combo doesn’t show its value if dependent on first combo how could I fix that
  25. MS Access prepared statements
  26. Linked table ms access 2010 change connection string
  27. Create sequential ID value based on the year that a record is added
  28. How to use cross join in access?
  29. Get Name of Current VBA Function
  30. How to extract the schema of an Access (.mdb) database?

Leave a Comment