Concatenate HELP!!! 2

[ninepro]

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

 

[dhookom]

Did you try faq701-4233?
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

 

[ninepro]

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...

 

[dhookom]

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

 

[ninepro]

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

 

[dhookom]

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

 

[PHV]

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
FAQ181-2886

 

[dhookom]

Exactly, thanks PH ;-)

Duane
Hook'D on Access
MS Access MVP

 

[ninepro]

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...

 

[dhookom]

Where are the tags coming from?

Duane
Hook'D on Access
MS Access MVP