combining records

[Guest]

I would like to create a new field that will store all of the products
supplied by a company for a report. I would like to display the information
in a list with each product separated by a comma. I figured that this would
be the best way to store the data, so that it can be exported to a word
document at a later date.

 

[Duane Hookom]

I don't think you want to "store" this information. There is a generic
concatenate function with sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

 

[Guest]

You are correct, I do not want to store the data, i just need to be able to
pull it out for later use. I tried using the generic query that you supplied
however I am receiving an error: syntax error (missing operand). When i
close the dialog box Product_ID is highlighted. The SQL is as follows:

SELECT SuppliersXREFProducts.Supplier_ID, SuppliersXREFProducts.Report_ID,
Concatenate(“SELECT Product_ID FROM SuppliersXREFProducts WHERE Supplier_ID
=†& [Supplier_ID] & “ AND Report_ID =†& [Report_ID] &) AS ProductsSupplied
FROM SuppliersXREFProducts;

 

[Duane Hookom]

I think I responded in one of your other threads. You have an extra "&" and
we don't know your field data types.

--
Duane Hookom
MS Access MVP

You are correct, I do not want to store the data, i just need to be able
to
pull it out for later use. I tried using the generic query that you
supplied
however I am receiving an error: syntax error (missing operand). When i
close the dialog box Product_ID is highlighted. The SQL is as follows:

SELECT SuppliersXREFProducts.Supplier_ID, SuppliersXREFProducts.Report_ID,
Concatenate("SELECT Product_ID FROM SuppliersXREFProducts WHERE
Supplier_ID
=" & [Supplier_ID] & " AND Report_ID =" & [Report_ID] &) AS
ProductsSupplied
FROM SuppliersXREFProducts;

I don't think you want to "store" this information. There is a generic
concatenate function with sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.