Autofill field based on search

[rd26]

My database has four fields: ItemName, ItemNumber, Description, and RelatedItems. I would like to create a query that will search every Description in the database and return that data in the RelatedItems field. The query should fill the RelatedItems field with a list of every ItemName and ItemNumber that contains the ItemNumber of the current record.

Here is an example of what I would like:




I have no idea where to start. I would greatly appreciate your help!

[June7]

Consider this query:
SELECT Table1_1.ItemNumber, Table1_1.ItemName, Table1_1.Description, IIf([RelDataFound]>0,[Table1].[ItemNumber] & " - " & [Table1].[ItemName],Null) AS RelData, IIf(InStr([Table1_1].[Description],[Table1].[ItemNumber])>0,1,0) AS RelDataFound
FROM Table1, Table1 AS Table1_1
WHERE (((Table1_1.ItemNumber)<>[Table1].[ItemNumber]));

Then if you want to concatenate the related data into a single string, review http://allenbrowne.com/func-concat.html
Use suggested query as the source for another query that uses Allen Browne's code to concatenate the RelData field.

[rd26]

My sincerest thanks. Exactly what I needed