Parsing and combining values

[Smythe32]

I have data that looks like the following:
field1 field2
123456 0,2,4,6
234567 1,2,3
345678

(if there are no values in field2, it is implied that they are 0-9)
What I am trying to do is end up with data that looks like the following:

1234560
1234562
1234564
1234566
2345671
2345672
2345673
3456780
3456781
3456782
3456783
3456784
3456785
3456786
3456787
3456788
3456789

Does anyone have any clue about this?

Thanks

[Steve Jorgensen]

If it weren't for the default, 0-9 case, I'd just say you should normalize the
structure into a 1-m reationship instead of using a comma-delimited field. I
still think something like that would be a good target (any other takers?),
but the only simple answer I can think of required leaving the structure the
way it is.

OK, create another table called tblDigit...

digit
0
1
2
3
4
5
6
7
8
9

Now, here's the query...

SELECT tblTable1.field1 & tblDigit.digit
FROM tblTable1, tblDigit
WHERE tblTable1.field2 IS NULL OR
"," & tblTable1.field2 & "," LIKE "*," & tblDigit.digit & ",*"

Technically, all the comma stuff is not required here, but if the number of
digits per number were variable instead of a single digit, you would need it
to prevent false matches. Also, if you use ADO, use % instead of * for
wildcard characters.

On 15 Jan 2004 09:52:34 -0800, Sm******@aol.com (Smythe32) wrote:

I have data that looks like the following:
field1 field2
123456 0,2,4,6
234567 1,2,3
345678

(if there are no values in field2, it is implied that they are 0-9)
What I am trying to do is end up with data that looks like the following:

1234560
1234562
1234564
1234566
2345671
2345672
2345673
3456780
3456781
3456782
3456783
3456784
3456785
3456786
3456787
3456788
3456789

Does anyone have any clue about this?

Thanks

[Smythe32]

Steve Jorgensen <no****@nospam.nospam> wrote in message news:<nv********************************@4ax.com>. ..

If it weren't for the default, 0-9 case, I'd just say you should normalize the
structure into a 1-m reationship instead of using a comma-delimited field. I
still think something like that would be a good target (any other takers?),
but the only simple answer I can think of required leaving the structure the
way it is.

OK, create another table called tblDigit...

digit
0
1
2
3
4
5
6
7
8
9

Now, here's the query...

SELECT tblTable1.field1 & tblDigit.digit
FROM tblTable1, tblDigit
WHERE tblTable1.field2 IS NULL OR
"," & tblTable1.field2 & "," LIKE "*," & tblDigit.digit & ",*"

Technically, all the comma stuff is not required here, but if the number of
digits per number were variable instead of a single digit, you would need it
to prevent false matches. Also, if you use ADO, use % instead of * for
wildcard characters.

On 15 Jan 2004 09:52:34 -0800, Sm******@aol.com (Smythe32) wrote:

Thank you. I tried this but it only works if there is only one value
in field2. ie 2 instead of 0,2,3. I am not sure how to separate them
out so that it will work except for typing each new line with only 1
value in field2.

[Steve Jorgensen]

On 16 Jan 2004 05:09:46 -0800, Sm******@aol.com (Smythe32) wrote:

Steve Jorgensen <no****@nospam.nospam> wrote in message news:<nv********************************@4ax.com>. ..

If it weren't for the default, 0-9 case, I'd just say you should normalize the
structure into a 1-m reationship instead of using a comma-delimited field. I
still think something like that would be a good target (any other takers?),
but the only simple answer I can think of required leaving the structure the
way it is.

OK, create another table called tblDigit...

digit
0
1
2
3
4
5
6
7
8
9

Now, here's the query...

SELECT tblTable1.field1 & tblDigit.digit
FROM tblTable1, tblDigit
WHERE tblTable1.field2 IS NULL OR
"," & tblTable1.field2 & "," LIKE "*," & tblDigit.digit & ",*"

Technically, all the comma stuff is not required here, but if the number of
digits per number were variable instead of a single digit, you would need it
to prevent false matches. Also, if you use ADO, use % instead of * for
wildcard characters.

On 15 Jan 2004 09:52:34 -0800, Sm******@aol.com (Smythe32) wrote:

Thank you. I tried this but it only works if there is only one value
in field2. ie 2 instead of 0,2,3. I am not sure how to separate them
out so that it will work except for typing each new line with only 1
value in field2.

You shouldn't have to "separate them", just figure out what's the bug in the
solution I gave you. What does it do when there is more than one value in the
list?

[Smythe32]

..

You shouldn't have to "separate them", just figure out what's the bug in the
solution I gave you. What does it do when there is more than one value in the
list?

How are these two tables joined together for the query? Perhaps that
is where my problem is. Don't they have to be linked?

[Steve Jorgensen]

On 20 Jan 2004 07:29:26 -0800, Sm******@aol.com (Smythe32) wrote:

.

You shouldn't have to "separate them", just figure out what's the bug in the
solution I gave you. What does it do when there is more than one value in the
list?

How are these two tables joined together for the query? Perhaps that
is where my problem is. Don't they have to be linked?

The where clause is doing the joining - just like the "olden" days before
there was a join construct for doing joins.

[Smythe32]

Thank you, Steve. Works great. Thanks again