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!

 

[lespaul]

FAQ701-4233

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[Dave177]

Leslie,
Thanks for this. I have tried it but now it is saying:

Runtime Error 3163: The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.

The names that I am trying to put in each row may total to more than 200 characters. Is this what the problem is?

Thanks

David

 

[PHV]

What is your actual SQL code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Dave177]

I think I'm a bit out of my depth here. I've copied the tables mentioned in the example and still can't get it to work. It just comes out like a normal query. Will try doing it again.

 

[dhookom]

The concatenate() function can be used in a query, code, control source,... As per PH, you should provide additional information including your SQL view.

Are you attempting to take Access tables and export the results to Excel?

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]

I've now got a step closer, using the family example given in the FAQ. Sorry - quite new to this.

My SQL is

SELECT Concatenate("SELECT FirstName FROM tblFamily 
WHERE FamID =" & [FamID]) AS [Names]
FROM tblFamily;

However, I now get:

John, Jack, Jill
John, Jack, Jill
John, Jack, Jill

When all I want is:

John, Jack, Jill

Thanks for your help.

 

[dhookom]

To get rid of duplicates, you can use a totals/group by query. In my samples, the multiple/child records generally come from a related table so I can leave the child table out of the query/sql.

SELECT Concatenate("SELECT FirstName FROM tblFamily 
WHERE FamID =" & [FamID]) AS [Names]
FROM tblFamily
GROUP BY Concatenate("SELECT FirstName FROM tblFamily 
WHERE FamID =" & [FamID]);

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]

 

[PHV]

Another way is to use the DISTINCT predicate:
SELECT DISTINCT FamID, Concatenate("SELECT FirstName FROM tblFamily WHERE FamID =" & [FamID]) AS [Names]
FROM tblFamily;

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Dave177]

Thanks again!

If let's say under tblFamily there was a field [Gender] and you wanted to select Male members as for the above concatenation how would you change the SQL? And then, for example those aged over 40 with a given [BirthDate]?

Thanks,

 

[VRoscioli]

You would want to change the inner SELECT statement, for example:

SELECT DISTINCT FamID, Concatenate("SELECT FirstName FROM tblFamily WHERE FamID =" & [FamID] & " AND Gender="Male") AS [Names]
FROM tblFamily;

-V

 

[dhookom]

You need to change the quotes around Male

SELECT DISTINCT FamID, Concatenate("SELECT FirstName FROM tblFamily WHERE FamID =" & [FamID] & " AND Gender='Male'") AS [Names]
FROM tblFamily;

Regarding the age, I would grab the Age() function from

http://www.mvps.org/access/datetime/date0001.htm

SELECT DISTINCT FamID, Concatenate("SELECT FirstName FROM tblFamily WHERE FamID =" & [FamID] & " AND Gender='Male' AND Age(#" & [BirthDate] & "#)>40" ) As [Names]
FROM tblFamily;

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]

 

[VRoscioli]

Silly quotes and lack of sleep


-V

 

[dhookom]

I've never done that before ;-)

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 again for your help. I had been hoping that I would be able to apply the above to my own example but I have run into yet more difficulties...

I have a query qryARTop20CommentsReported0405Reports.

The SQL for this is as below

SELECT qryARRankingsOfCommentsReported20042005.Year, qryARRankingsOfCommentsReported20042005.description, 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 [qryARRankingsOfCommentsReported20042005].[description]) AND ((Year([StartDate])) Like [Year]))
ORDER BY qryARRankingsOfCommentsReported20042005.Year, qryARRankingsOfCommentsReported20042005.description, qryARRankingsOfCommentsReported20042005.Rank, qryARRankingsOfCommentsReported20042005.PositiveNegative DESC , qryARRankingsOfCommentsReported20042005.Comment;

This effectively lists and ranks the Top 20 Comments, and if for example 10 Boards had that ranked comment it repeats itself for each of the ten Boards.

What I was hoping for was for each ranked comment to list the appropriate Boards, concatenated so that all Boards fit on one line.

I tried the following SQL

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

However, it doesn't work ("Run-time error 3078: The Microsoft Jet Database engine cannot find the input table or query 0. Make sure it exists and that its name is spelled correctly"). Please could anybody advise me what I am doing wrong?

 

[VRoscioli]

I think it might be an error in your quotes somewhere...

Mess with them, just from a cursory glance I would start by trying

SELECT DISTINCT  qryARTop20CommentsReported0405Reports.qryARRankingsOfCommentsReported20042005.description, qryARTop20CommentsReported0405Reports.PositiveNegative, qryARTop20CommentsReported0405Reports.Rank, qryARTop20CommentsReported0405Reports.Comment, qryARTop20CommentsReported0405Reports.CountOfCommentID, Concatenate("SELECT Board FROM qryARTop20CommentsReported0405Reports 
WHERE  qryARTop20CommentsReported0405Reports.qryARRankingsOfCommentsReported20042005.description = """ & qryARTop20CommentsReported0405Reports.qryARRankingsOfCommentsReported20042005.description & [b][red]"""[/red][/b] AND qryARTop20CommentsReported0405Reports.PositiveNegative =""" & qryARTop20CommentsReported0405Reports.PositiveNegative & [b][red]"""[/red][/b] AND qryARTop20CommentsReported0405Reports.Rank = " & qryARTop20CommentsReported0405Reports.Rank [b][red]& "[/red][/b] AND qryARTop20CommentsReported0405Reports.Comment = """ & qryARTop20CommentsReported0405Reports.Comment & [b][red]"""[/red][/b] AND qryARTop20CommentsReported0405Reports.CountOfCommentID = " & qryARTop20CommentsReported0405Reports.CountOfCommentID) AS ReportsCon
FROM qryARTop20CommentsReported0405Reports;

Might not be exactly right though...its still a bit early for me


-V

 

[lespaul]

why do you have:
[tt]qryARTop20CommentsReported0405Reports.qryARRankingsOfCommentsReported20042005.description[/tt]
as your field name...I don't think that's a valid field qualifier.....

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[Dave177]

Leslie,

I've now change the field name as follows:

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

VRoscioli,

I had a look at the quote marks and am sure that they are correct with regards to faq701-4233....

?????

 

[VRoscioli]

I still think your quotes are incorrect. You need to understand what the multiple quotations signify. The Concatenate function takes a string as its only parameter, meaning that whatever is in it must be surrounded by quotes:

... Concatenate("SELECT * FROM tblTable;") ...

However, since your query has a WHERE clause that compares strings to values, the values they are compared to must also be surrounded by quotes:

... WHERE FieldName = "Field Value" ...

However if we put these together we see the problem:

... Concatenate("SELECT * FROM tblTable WHERE FieldName = "Field Value";") ...

The quotes in the WHERE clause end the string and cause an error. This can be avoided in one of two ways:

1)Change the inner quotes to single quotes:

... Concatenate("SELECT * FROM tblTable WHERE FieldName = 'Field Value';") ...

2)Change the inner quotes to double double quotes (this, in effect escapes the quotes so they are treated as characters and not the end of the string):

... Concatenate("SELECT * FROM tblTable WHERE FieldName = ""Field Value"";") ...

Your case is even more complex, because you are attempting to piece together multiple things to create the final string:

... Concatenate("SELECT * FROM tblTable WHERE FieldName = """ & FieldValue & """;") ...

OR

... Concatenate("SELECT * FROM tblTable WHERE FieldName = '" & FieldValue "';") ...

I recommend you take a look at your quotes.


-V

 

[VRoscioli]

Er... the last line in that code box should be:

... Concatenate("SELECT * FROM tblTable WHERE FieldName = '" & FieldValue [blue][b]&[/b][/blue] "';") ...

-V