Concatenate Field or Group by in Report or or or?

[giblets]

I'm looking for a way to summarize multiple fields of data from the same column in a single field. I've seen this page but have been unable to get it to work. I save the code listed but do not know which of the example fields would relate to my table.

SELECT CompanyName,  ConcatRelated("OrderDate", "tblOrders", "CompanyID = " & [CompanyID]) 
    FROM tblCompany;

I saw that I can create a report and select "group by," but then all the columns appear on different lines rather than summarized on one line. Here is an example of how I would like the data to appear, separated by commas or with specific dates within parentheses. The reason I want them all on one line is to provide an easier to read/more succinct summary.

e.g.

ITEM	LOCATION	DATE
GORP	EAST	1/1/2012
GORP	EAST	2/1/2012
GORP	WEST	1/1/2012
FLORP	WEST	1/1/2012

displayed as:

ITEM	LOCATION	DATE
GORP	EAST	1/1/2012, 2/1/2012
GORP	WEST	1/1/2012
FLORP	WEST	1/1/2012

or (even better):

ITEM	LOCATIONandDATE
GORP	EAST (1/1/2012, 2/1/2012), WEST (1/1/2012)
FLORP	WEST (1/1/2012)

not as:

ITEM	LOCATION	DATE
GORP	EAST	1/1/2012
		2/1/2012
GORP	WEST	1/1/2012
FLORP	WEST	1/1/2012

[June7]

VBA code is only way I know to do this. The link you reference provides sample code. I created a table of the posted example data. This query produces the first concatenated output:

SELECT DISTINCT ITEM, LOCATION, ConcatRelated("[Date]","tblCompany","ITEM='" & [ITEM] & "' AND LOCATION='" & [LOCATION] & "'","ITEM, LOCATION") AS DATES
FROM tblCompany;

Then I saw that your attempt includes CompanyID and the date fieldname is different. Try this:

SELECT DISTINCT tblCompany.CompanyID, tblCompany.ITEM, tblCompany.LOCATION, ConcatRelated("[OrderDate]","tblCompany","CompanyID=" & [CompanyID] & " AND ITEM='" & [ITEM] & "' AND LOCATION='" & [LOCATION] & "'","CompanyID, ITEM, LOCATION, OrderDate") AS DATES
FROM tblCompany;

Could adjust code for the result shown in your second output example or use first query in another query:

SELECT DISTINCT Query1.CompanyID, Query1.ITEM, ConcatRelated("Location & ' (' & Dates & ')'","Query1","CompanyID=" & [CompanyID] & " AND ITEM='" & [ITEM] & "'","CompanyID, ITEM, Location, Dates") AS LocDates
FROM Query1;

[giblets]

CompanyID? The code I posted was copy/pasted from the website, not something that relates to my data.

[June7]

Then show your attempted code (the query SQL statement) that uses your fieldnames. Why doesn't it work - error message, wrong results, nothing happens? Did you even try the first query I suggested but using your actual table and field names?

[giblets]

Thanks for the help so far, I'm still fiddling with it so far and will report back if (when) I have more questions!

[giblets]

Cool stuff. See attached example.

How can I make items within concatenated fields appear in alphabetical or chronological order?

test.2012-1218.accdb.zip

[June7]

Include COLLDATE in the function's ORDER BY argument.

SELECT DISTINCT Collections.SPECIES, Collections.COLLFROM, ConcatRelated("[COLLDATE]","Collections","SPECIES='" & [SPECIES] & "' AND COLLFROM='" & [COLLFROM] & "'","SPECIES,COLLFROM,COLLDATE") AS COLLDATE
FROM Collections;

[giblets]

MAGIC. Thanks again!