array query

[Joel Allen]

Hello,

I am doing an inner join query. Typically, you would match up the ID's and
away you go.

I have a query that is a little different.

I now have to join an ID from one table, to an ID in another table that
might have values like 1,2,3,4,5.

So for instance, I need the query to say - match the ID from one table and
to the ID in the other table that has values 1,2,3,4,5....

So ID 1 would find the 1 in the 1,2,3,4, etc.....

I found the function InStr() on the web and belive that might work, but not
exactly sure abou the syntax.

I hope that makes sense. Can anybody help.

Thanks, Joel

 

[John W. Vinson]

Hello,

I am doing an inner join query. Typically, you would match up the ID's and
away you go.

I have a query that is a little different.

I now have to join an ID from one table, to an ID in another table that
might have values like 1,2,3,4,5.

So for instance, I need the query to say - match the ID from one table and
to the ID in the other table that has values 1,2,3,4,5....

So ID 1 would find the 1 in the 1,2,3,4, etc.....

I found the function InStr() on the web and belive that might work, but not
exactly sure abou the syntax.

I hope that makes sense. Can anybody help.

Thanks, Joel

This is difficult because your comma separated list of values is SIMPLY WRONG.
Fields should be "atomic", having only one value. This field should be *five
records* in a table, not five values in one field!

That said... you need to be *absolutely certain* that the composite field is
in EXACTLY this format (just numbers and commas, no other punctuation). Let's
call the table containing just a single number tblGood and the table
containing the composite field tblBad (because it's BAD).

You could use

SELECT <whatever fields>
FROM tblGood, tblBad
WHERE "," & tblBad.ID & "," LIKE "*," & tblGood.ID & ",*"

This Cartesian join will be slow and will not be updateable. That's part of
the price of the flawed design.