Search Multi valued fields

[aerosmith2k1]

Good evening,

Im trying to program a way to search a way to search a field that has 3 sets of codes separated by commas,

Example 089,063,071

There is a list of about 13 different codes. Programmed in a Table if there is a way to compare.

What i need to do is
Compare to see if its a valid code, If it is,

Create a table with all the fields and append the good values.

 

[BigRed1212]

So in any given database record you will have a field that has exactly 3 3 digit codes seperated by commas.

There are about 13 different codes.

You need to compare the list of codes in a given field to a list of 13 codes and make sure at least one of the codes in the field are on the list.

If there is a match are you want to append the valid values to something.

I'm sure I didn't get that right. Maybe just a tad more info?

 

[aerosmith2k1]

No problem , i appreciate you taking the time to read the post.


The issue again ill try to explain it better.
1. I have a table which has one field that is called " Action Required"

2.That one field contains 3 sets of codes separated by commas.
Example 089,063,071

Those codes are also stored in a table, If there is a way to compare each set from the Field to the table.

My goal is to. somehow if possible,

Run a query or some vb to compare each set of the codes to the preset codes,

The idea is that every field is to contain those numbers, If it does not its considered invalid.

 

[BigRed1212]

So then your desired output is the records with invalid codes in their "action required" field?

Or do you want only the records with good codes?

 

[RoyVidar]

There we have one of the reasons there is an "atomicity" rule, and first normal form.

When you store information in a "non relational" way, it becomes hard to use relational means to enquire the information.

Had you stuffed them into three separate fields, the process could have been performed with joins (perhaps outer joins?), quite fast.

You could probably join here too, with part of this field, but I think perhaps a VBA snippet might be better, here's a small attempt that should return the found numbers, and if none is found, N/A (note - no errorhandling...)

[tt]Public Function mvd(TheString As String) As String

Dim rs As DAO.Recordset
Dim s() As String

s() = Split(TheString, ",")
Set rs = CurrentDb.OpenRecordset( _
"SELECT TheField FROM TheTable WHERE TheField IN ('" & _
Join(s, "', '") & "')")
If rs.RecordCount > 0 Then
Do While Not rs.EOF
mvd = mvd & "," & rs.Fields(0).Value
rs.MoveNext
Loop
mvd = Mid$(mvd, 2)
Else
mvd = "N/A"
End If
rs.Close
Set rs = Nothing

End Function[/tt]

This requires that there i a value in this "Action Required" field, else you'd need to pass as a variant, and probably do some test prior to Split/Opening the recordset.

Roy-Vidar

 

[dhookom]

There is a SQL method for parsing out the valid values from each field. I wish you would have provided actual table and field names rather than "I have a table with a field..."

Assuming
tblCodes with one field Codes and values:
123
234
345

tblMultiValue with primary key of MVID and Action Required:
MVID Action Required
1 123,234,345
2 234,345,111
3 123,345,333

You can create a query to parse the legitimate values from each record:

SELECT tblMultiValue.MVID, tblCodes.Codes, tblMultiValue.[Action Required]
FROM tblCodes, tblMultiValue
WHERE ((("," & [Action Required] & ",") Like "*," & [Codes] & ",*"));

Result:

MVID	Codes	Action Required
1	123	123,234,345
1	234	123,234,345
1	345	123,234,345
2	234	234,345,111
2	345	234,345,111
3	123	123,345,333
3	345	123,345,333

Duane
Hook'D on Access
MS Access MVP

 

[RoyVidar]

Here's another thingie using join on parts of the field

[tt]SELECT m.id, m.test, t1.TheField, t2.TheField, t3.TheField
FROM

((TheMainTable AS m LEFT JOIN TheTable t2 ON
Mid(m.test,5,3)= T2.TheField) LEFT JOIN TheTable t3 ON
Right(m.test,3) = t3.TheField) LEFT JOIN TheTable AS t1 ON
Left(m.test,3) = t1.TheField;[/tt]

Roy-Vidar