if statement

[MarcLara]

Need to get an idea how to handle problem using If statements. Example is not the real problem but similar.

I have a set of constant records that is used to check a table that has varying records. The problem is each constant field needs to be used to check each record on the database. For example,

Constants Table
A B C C B A ------> Accepted
B C A ------> Accepted
A C D ------> NOT Accepted

If all constants is on the table regardless of field location, accept record. Each letters represents a field. Any help would be greatly appreciated

 

[SJMcAbney]

Can you cut out the A, B, C and give better detailed information about what it is you want as, currently, that makes no sense?

What are these two tables? How are they related? What are the fields' data types? Is your data properly normalised (I'm guessing that they aren't)?

Thanks.

Stewart J. McAbney | Talk History

 

[MarcLara]

This is a database for gene codes. The tables contains complex data that are hard to explain. I didn't want to use real data because it will be long and more confusing. All datatypes are text. The given example is the simplest I can put it. There are over 50 fields in the table. For simplicity, let's just talk about one table with three fields. Lets just name the Constants field as C1, C2, C3 and the table field as T1, T2, T3

If all the constants are in the record regardless of order accept the record then goto next record. If a constant is repeated on the record it is not accepted. If not all the constants are in the record it is not accepted. Accepting the current record involves updating a field in the current record. I just needed a good starting point for the if statement part . I already have the loop part but I need to polish the If statement part.

The data will never be normalized because they come in as raw data. This is beyond my control, but the finished data will be normalized.
I have something similar to this right now

If C1=T1 or C1=T2 or C1=T3 then T1 is ok
If C2=T1 or C2=T2 or C2=T3 then T2 is ok
If C3=T1 or C3=T2 or C3=T3 then T3 is ok

If T1, T2, T3 is all ok then accept record

This is really hard to explain

 

[PHV]

Something like this ?
If Eval(C1 & " In ('" & T1 & "','" & T2 & "','" & T3 & "')") _
And Eval(C2 & " In ('" & T1 & "','" & T2 & "','" & T3 & "')") _
And Eval(C3 & " In ('" & T1 & "','" & T2 & "','" & T3 & "')") _
And T1 <> T2 And T1 <> T3 And T2 <> T3 Then
' accept record


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244