Query question

[jscriptor09]

Hi

I have a table with these two fields, among others
Item ............................ Related items
1 3,4,5
2 6,7,8
3
4
6 11
7 15,18

If [Item] 1 is late to arrive, I need to notify [Related Items] of the lateness. To do this, I would need to create intermediate table on the fly and looks like this:
Item Related Item
1 3
1 4
1 5
6 11
7 15
7 18
.
.
.

the table is large. Therefore, I am trying to avoid writing a VBA to parse the string to array and then write the arrays to a Table. It would take too long.

I would appreciate a pointer to how to structure a query to so.



thank
Mike

[June7]

What are related items that they would need 'notification'? What is nature of notification - email?

It is not necessary to write array to a table just to refer to the RelatedItem elements. A query cannot parse an unknown number of elements from a string into separate fields or records. This requires VBA.

Alternatives are multi-value field or a related table for RelatedItem records.

[jscriptor09]

Hi June7

thanks for the reply.
it appears that the text got altered ... spaces collapsed.

this line
Item ............................ Related items
1 3,4,5

is supposed to read as
Item
1
Related Items
3,4,5

so if Item 1 is late, I want the many to many table that I would like to generate via a query to show
pairs
1 and 3
1 and 4
1 and 5

the 1 will be in the Item Field
and the 3,4,and 5 will be in the Related Items field

based on that info, I then do another process to notify, via email or otherwise. But for here, the pasring the most important.

Thank you

[June7]

Don't think that alters my comments.

Item and RelatedItems are fields of the same table? RelatedItems is a text field with string value? Because the number of elements for each record is not consistent, requires VBA to 'expand' the elements. VBA can loop through recordset, parse the RelatedItems to an array, then loop through the array and do something with each element. Move to next Item record, if any, and repeat.

A multi-value field can be expanded by query.

A related table would have the records already in 'expanded' structure.