Table Driven Criteria, or Criteria table?

[CptCrusty1]

I have some colleagues that swear by the use of table driven criteria, i.e. I have criteria that is duplicated over and over in numerous queries. They suggested having a table joined to the query which would have my criteria. I can't seem to make this work right. Any pointers?

I live to work and I work to live.

 

[PHV]

I can't seem to make this work right
What have you tried so far ?

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

 

[CptCrusty1]

Background: I have three fields with numerical values. Anything from 2-100. They are called Stop1, Stop2, Stop3. They are exclusionary in that if they have certain values, certain things can't happne. So. If Stop1 has a value of 2 then that record is excluded. Same with Stop2, and Stop3. The SQL is written:

Stop1 not in (2,3,5,7,8,9) and Stop2 not in (2,3,5,7,8,9), etc. You'll notice that the criteria is exactly the same. This happens a bajillion times throughout the macro with only sutble differences in the other queries (stops all stay the same).

I build a table with the following fields:

Process (Text)
Stop1(Number)
Stop2(Number)
Stop3(Number)

and then instead of putting "Not In (yadda)" in the criteria boxes In QBE, I joined the three stop fields from my primary table to the Stop fields in my criteria table. I should have about 35000 records, I'm getting none. Did I loose you?


I live to work and I work to live.

 

[PHV]

Something like this ?
tblCriteria:
StopNum
2
3
5
...

SELECT ...
FROM ((yourTable A
LEFT JOIN tblCriteria C1 ON A.Stop1 = C1.StopNum)
LEFT JOIN tblCriteria C2 ON A.Stop2 = C2.StopNum)
LEFT JOIN tblCriteria C3 ON A.Stop3 = C3.StopNum
WHERE (C1.StopNum Is Null AND C2.StopNum Is Null AND C3.StopNum Is Null)

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

 

[CptCrusty1]

PHV,

When I can see daylight againt, I'll try your process and see what I get. Its very similar to what I was trying so I guess I was on the right track, but needed a boost. We'll see. I appreciate your help.

Crusty.

I live to work and I work to live.