0

I've an Access database that follows each Line Item across multiple stages where tasks are assigned to individuals. Each stage has a Comments field and are recorded in a Table which looks like:

Line Item     Stage    Title             Comments
    1           1      Introduction      Trial comment
    1           1      Introduction      Another one
    1           2      Abstract          Following one
    1           2      Abstract          Andi nexto
    1           3      Thesis            Nexto
    2           1      Introduction      Comment for next item
    2           1      Introduction      Andi another one
...

I want to be able to concatenate these comments for each stage and each Line Item as:

Line Item     Stage    Title           Comments
    1           1      Introduction    Trial comment, Another one
    1           2      Abstract        Nexto one, Andi next
    1           3      Thesis          Nexto
    2           1      Introduction    Comment for nexto item, Andi another one

I tried using Allen Brown's ConcatRelated() function with multiple WHERE criteria:

ConcatRelated("[Comments]","[CommentsT]","[LineItemNo]=" & "[txtLineItemNo] AND "[StageNo]=" & [txtStageNo])

but with no luck. Using a single WHERE clause does concatenate all the comments in the required field without considering StageNo and Title.

Kindly advise as to what is the best way for me to achieve this.

Thank you.

Ion Khan
  • 15
  • 4
  • 2
    The WHERE clause is wrong, try: `"[LineItemNo]=" & [LineItemNo] & " AND [StageNo]=" & [StageNo]` – Andre Jun 17 '21 at 11:10
  • Does this answer your question? [ConcatRelated() in Access, using two keys](https://stackoverflow.com/questions/13822588/concatrelated-in-access-using-two-keys) – June7 Jun 17 '21 at 16:10

0 Answers0