List Items Seperated by Commas 4

[Dave177]

Hello,

I am extracting data to Excel in order to present an analysis of data. Although a query is listing the data I was wondering, for presentation purposes, if I could list the items in one row with items separated by a comma.

i.e instead of

White
Orange
Blue
Green

have

White, Orange, Blue, Green.


This would save a lot of time because, L want to list them all in one cell.
Thanks for any help!

 

[dhookom]

You might want to try the following which assumes NCArea, PositiveNegative and Comment are all text while Rank and CountOfCommentID are numeric

SELECT DISTINCT q.NCArea, q.PositiveNegative, q.Rank, q.Comment, q.CountOfCommentID, 
Concatenate("SELECT Board FROM qryARTop20CommentsReported0405Reports 
WHERE  NCArea = """ & q.NCArea & """ And PositiveNegative=""" & q.PositiveNegative & """ AND Rank = " & q.Rank & " And Comment=""" & q.Comment & """ AND CountOfCommentID = " & q.CountOfCommentID) AS ReportsCon
FROM qryARTop20CommentsReported0405Reports q;

If I understood your table structure, I would probably do all of this differently so the FROM query/tables wouldn't match. If you want the top 20 by something, I would probably use SELECT TOP 20 within the concatenate function.

For instance in Northwind, if I wanted to get the TOP 3 OrderID per CustomerID based on the 3 greatest Freight costs.

SELECT Customers.CustomerID, Customers.CompanyName, Concatenate("SELECT TOP 3 OrderID FROM Orders WHERE CustomerID = """ & [CustomerID] & """ ORDER BY Freight DESC") AS TopFreightOrderIDs
FROM Customers;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Dave177]

Thank you both very much for your help.

Dhookom,
the structure of my tables is as follows.

tblARReports
with fields: ReportNumber (primary key), Board, StartDate etc.

tblARComments
with fields: CommentID (primary key), Comment, PositiveNegative

tblARCommentsReported
with fields: CommentReportedID (primary key), ReportID, CommentID

tblBoards
with fields: board (primary key), nac_area.

and NAC Area
with fields: Code (primary key), description

So now that I have a lot of entries for tblARCommentsReported I am trying to do a top 10 ranking of both positives and negatives, by NCArea for those with StartDates of 2004 to 2005.

To do this (and being rather inexperienced) I made a query qryARCommentCountByArea:

SELECT [NAC area].description, tblARCommentsReported.CommentID, Count(tblARComments.CommentID) AS CountOfCommentID, tblARComments.Comment, tblARComments.PositiveNegative, Year([StartDate]) AS [Year]
FROM ((tblARComments RIGHT JOIN (tblARReports LEFT JOIN tblARCommentsReported ON tblARReports.ReportNumber = tblARCommentsReported.ReportNumber) ON tblARComments.CommentID = tblARCommentsReported.CommentID) LEFT JOIN Boards ON tblARReports.Board = Boards.board) LEFT JOIN [NAC area] ON Boards.nac_area = [NAC area].code
GROUP BY [NAC area].description, tblARCommentsReported.CommentID, tblARComments.Comment, tblARComments.PositiveNegative, Year([StartDate])
ORDER BY [NAC area].description, Count(tblARComments.CommentID) DESC;

I then ranked the above using: qryARRankingsOfCommentsReported20042005

SELECT A.CommentID, A.Comment, A.PositiveNegative, A.description AS NCArea, A.Year, A.CountOfCommentID, Count(*) AS Rank
FROM qryARCommentCountByArea AS A INNER JOIN qryARCommentCountByArea AS B ON (A.CountOfCommentID<=B.CountOfCommentID) AND (A.PositiveNegative = B.PositiveNegative) AND (A.description = B.description) AND (A.Year=B.Year)
WHERE A.Year>2003 AND A.Year<2006
GROUP BY A.Year, A.description, A.PositiveNegative, A.CountOfCommentID, A.Comment, A.CommentID
ORDER BY A.Year, A.description, 6;

The following query I used to get the top 20 positive and negative comments, by area and year and also identified the exact Board for which those comments were reported. qryARTop20CommentsReported0405Reports:

SELECT qryARRankingsOfCommentsReported20042005.Year, qryARRankingsOfCommentsReported20042005.NCArea, qryARRankingsOfCommentsReported20042005.Rank, qryARRankingsOfCommentsReported20042005.PositiveNegative, qryARRankingsOfCommentsReported20042005.Comment, qryARRankingsOfCommentsReported20042005.CountOfCommentID, qryARRankingsOfCommentsReported20042005.CommentID, tblARReports.Board, [NAC area].description, Year([StartDate]) AS StartDateYear
FROM qryARRankingsOfCommentsReported20042005 LEFT JOIN (((Boards LEFT JOIN [NAC area] ON Boards.nac_area = [NAC area].code) RIGHT JOIN tblARReports ON Boards.board = tblARReports.Board) RIGHT JOIN tblARCommentsReported ON tblARReports.ReportNumber = tblARCommentsReported.ReportNumber) ON qryARRankingsOfCommentsReported20042005.CommentID = tblARCommentsReported.CommentID
WHERE (((qryARRankingsOfCommentsReported20042005.Rank)<20) AND (([NAC area].description) Like [NCArea]) AND ((Year([StartDate])) Like [Year]))
ORDER BY qryARRankingsOfCommentsReported20042005.Year, qryARRankingsOfCommentsReported20042005.NCArea, qryARRankingsOfCommentsReported20042005.Rank, qryARRankingsOfCommentsReported20042005.PositiveNegative DESC , qryARRankingsOfCommentsReported20042005.Comment;

My original query above was to try and get those Boards who had reported such a comment to be concatenated against those comments/rankings.

I am a bit embarassed to have used so many queries where probably only one or two would be required but as you can probably tell I'm very much a beginner.

I did try your suggestion but the computer sort of froze up...

Thanks for any help.

 

[Dave177]

Duane,

Anyway, thanks for your help. I made the query a lot more simple by only select CommentID and then concatenating Boards after each Comment ID. It did work but took up to 15 seconds for each row to load..! It would be quick to type it out manually.

Thanks

David

 

[dhookom]

You could experience a huge gain in performance if you used an intermediate table of your counts. I would probably create a table with the appropriate indexes and then clear it and append counts. Then use the concatenate against the intermediate table.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Dave177]

That's brilliant, thanks a lot!

 

[Dave177]

Further to this, would somebody know how to order the concatenated items? My SQL is as follows:

SELECT DISTINCT tblARTempForConcatenationRanking.NCArea, tblARTempForConcatenationRanking.Year, tblARTempForConcatenationRanking.PositiveNegative, tblARTempForConcatenationRanking.Rank, tblARTempForConcatenationRanking.Comment, tblARTempForConcatenationRanking.CountOfCommentID, Concatenate("SELECT Board FROM tblARTempForConcatenationRanking 
WHERE  NCArea = """ & tblARTempForConcatenationRanking.NCArea & """ And PositiveNegative=""" & tblARTempForConcatenationRanking.PositiveNegative & """ AND Rank = " & tblARTempForConcatenationRanking.Rank & " And Comment=""" & tblARTempForConcatenationRanking.Comment & """ AND CountOfCommentID = " & tblARTempForConcatenationRanking.CountOfCommentID) AS ReportsCon
FROM tblARTempForConcatenationRanking
ORDER BY tblARTempForConcatenationRanking.Year, tblARTempForConcatenationRanking.PositiveNegative DESC , tblARTempForConcatenationRanking.Rank;

The values of the field [Board] are being concatenated. I was hoping to get these in alphabetical order. For example: "Apple, Banana, Cherry" instead of "Banana, Apple, Cherry".

Also how would I include "Year" in the Where clause above?

Thanks for any help.

David

 

[dhookom]

Try send an ORDER BY into the Concatenate() function:

Concatenate("SELECT Board FROM tblARTempForConcatenationRanking 
WHERE  NCArea = """ & NCArea & """ And PositiveNegative=""" & PositiveNegative & """ AND Rank = " & Rank & " And Comment=""" & Comment & """ AND CountOfCommentID = " & CountOfCommentID & " ORDER BY Board") AS ReportsCon

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Dave177]

Thanks! It works. What about the Year bit?

 

[dhookom]

You would add Year in the same method as others

Concatenate("SELECT Board FROM tblARTempForConcatenationRanking 
WHERE  NCArea = """ & NCArea & """ And PositiveNegative=""" & PositiveNegative & """ AND Rank = " & Rank & " And Comment=""" & Comment & """ AND CountOfCommentID = " & CountOfCommentID & " AND [Year]=" & [Year] & " ORDER BY Board") AS ReportsCon

This assumes the Year field is numeric. BTW "Year" is a function so I would never use it as the name of anything in Access especially a field. I would probably have named the field something like "ARYear".

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Dave177]

Dhookom,

Thanks a lot. I was just going a bit mad. Just a quick question - what book(s) would you recommend. I haven't been doing this very long and am missing the basics (syntax etc.)?

 

[dhookom]

I generally send Access programmers looking for good books to

http://www.viescas.com

and check his links on books. He is a very good author and I have several of his books.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]