I have attempted most of the Concatenate FAQ's on here and nothing seems to work.

I have a existing query which I run from a my calls table which produces a daily report for each day, however, I have now created another table to store comments which are linked by issue id to the main table and I want to have the comments included for each ID on the main calls table.

Structure Below:

Comments Table:
ID    Issue ID   Comments        etc     etc
1       4211     4211 Comment
2       4211     4211 Comment 2
3       4212     4212 Comment
4       4213     4213 comment

Calls Table

ID     Comments

4211
4212
4213
4214
4215

So basically I want to allow the comments to be in a single field for the corresponding ID on the calls table

Many Thanks,

Jason

Did you try FAQ701-4233: How to concatenate multiple child records into a single value?
What were the results?
Did you add the function into a module in your Access front-end file?

Duane
Hook'D on Access
MS Access MVP

Hi,

I have the function added and have included the following in my daily report query from the main calls table:

Comments.Comment, Concatenate("SELECT Comment FROM Comments WHERE IssueID =" & [IssueID]) AS Comments

The field comments is now added to the query results for each ID in the query, however the ID still repeats in the query. I have attached a sample Excel file to show this.

The whole query looks like this:


SELECT Calls.ID, Calls.Title, Calls.[Opened By], Calls.[Opened Date], Calls.[Resolved By], Calls.[Resolved Date], Calls.Store, Calls.[Call Time], Calls.Status, Calls.Category, Calls.[CCTV Required?], Calls.[Due Date], Calls.Comments, Calls.[Value of event], Calls.Product, Calls.[Police Ref], Calls.[CCTV Complete], Calls.Callers, Calls.[Incident Date], Calls.[Incident Time], Comments.Comment, Concatenate("SELECT Comment FROM Comments WHERE IssueID =" & [IssueID]) AS Comments1
FROM Comments INNER JOIN Calls ON Comments.IssueID = Calls.ID
WHERE (((Calls.[Opened Date]) Between [Enter Start Date:] And [Enter End date:]));

Many Thanks,

Jason...
 

• http://www.mediafire.com/?eelvy913h2rzqe2

I can't download anything from my work PC. Just try removing the Comments table from the "FROM ... " statement since you are retrieving all the comments within the Concatenate function call.

Duane
Hook'D on Access
MS Access MVP

Ok,

I have taken the FROM out and the following error appears:

The SELECT statment includes a reserved word or argument name that is mispelled or missing, or the punctuation is incorrect.

The query is now:


SELECT Calls.ID, Calls.Title, Calls.[Opened By], Calls.[Opened Date], Calls.[Resolved By], Calls.[Resolved Date], Calls.Store, Calls.[Call Time], Calls.Status, Calls.Category, Calls.[CCTV Required?], Calls.[Due Date], Calls.Comments, Calls.[Value of event], Calls.Product, Calls.[Police Ref], Calls.[CCTV Complete], Calls.Callers, Calls.[Incident Date], Calls.[Incident Time], Comments.Comment, Concatenate("SELECT Comment FROM Comments WHERE IssueID =" & [IssueID]) AS Comments1

WHERE (((Calls.[Opened Date]) Between [Enter Start Date:] And [Enter End date:]));


J

I only wanted you to remove the comments table from the FROM clause.  Leave  the remainder of the SQL as it was   

Duane
Hook'D on Access
MS Access MVP

I'd try this:
SELECT ID, Title, [Opened By], [Opened Date], [Resolved By], [Resolved Date], Store, [Call Time], Status, Category, [CCTV Required?], [Due Date], Comments, [Value of event], Product, [Police Ref], [CCTV Complete], Callers, [Incident Date], [Incident Time], Concatenate("SELECT Comment FROM Comments WHERE IssueID=" & ID) AS Comments1
FROM Calls
WHERE [Opened Date] Between [Enter Start Date:] And [Enter End date:];

Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

Exactly, thanks PH

Duane
Hook'D on Access
MS Access MVP

Guys,

Absolutely awesome worked like a charm, one last thing is there any reason that the <div> tags are visible in the query results for the comments field?

Jason...

Where are the tags coming from?  

Duane
Hook'D on Access
MS Access MVP