Maybe a Crosstab Query

[Proko]

I have a table called Tbl_Jewellery with the fields lng_JewelleryID and
str_Metal. Some jewellery can be made out of more than one metal so it is not
uncommon for a number of records to have the same lng_JewelleryID, each with
a different str_Metal.

lng_JewelleryID str_Metal
1 rose
1 white
1 yellow
2 platinum
2 silver
3 yellow

In the above example: ring with ID = 1 is made out of three metals; rose
gold, white gold and yellow gold. Ring 2, is made out of two; platinum and
silver. And ring three, just the one metal; yellow gold.
I would like to have a query that includes a calculated field called
"description" that would join all the str_metal field results into one long
string.
For example:

"Description" calculated field for ring with id =1 would be: rose white yellow
and for id =2: platinum silver
and for id =3: yellow

Is this possible? I've played around using a crosstab query but cannot find
a method of joining the different metal strings. If they were numeric values
I would sum them. Is there an equivalent of Sum for string values?

I hope this makes sense. Any help would be greatly appreciated. Thanks

 

[John Spencer]

Quoting Duane Hookom

I use a generic Concatenate() function. The code is listed below with
both ADO and DAO. There are comments regarding which lines to comment or
uncomment based on which library you prefer. Access 97 is mostly DAO
while the default for 2000 and newer is ADO.

See:
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

End quote

Go to the site download the example and copy the code to your database
into a VBA module.

Then you can build a query with a calculated field

Field: Metals: Concatenate("SELECT str_Metal FROM tbl_Jewellery WHERE
lng_jewelryID =" & lng_jewelryid, " ")

If you want the values in some specific order you will have to add an
order by clause to the SQL string that is built. If you want something
in place of the space as a separator between the items, you will have to
change the second argument from " " to ", " or " - " or whatever you want.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Proko]

Thanks John, Most informative! I'll give it a try when I get back to work
where the database lives. I'll let you know how I go. Proko