Conditional Record selection

W

wal50

I inherited a file where each record has (up to) 5 potential actions, each
with an associated date of completion. I produce various reports that do not
show actions that have been completed (have a date <> blank). I would also
like to have these reports print only those records that have incomplete
actions (no date in the associated field).
The problem is that there can be from 1 to 5 actions for any record, any of
the "action" fields can be used of not used, any they can be completed in any
order.
The selection criteria for the number of permutations of desired records
has me stumped.
Any ideas?
Thanks in advance
wal50
 
K

Klatuu

WHERE [Date1] IS NULL OR [Date2] IS NULL OR [Date3] IS NULL OR [Date4] IS
NULL OR [Date5] IS NULL
 
W

wal50

I tried that and it doen't work. I think the problem is the numerous
permutations.

For example, date5 is null. This could be either because there was no
action5 or action5 is not complete. I would not want the record in the first
case but would in the second. This is just for one action. If I don't want
the record because of the test for 5, I might want it because of the results
for date4 ...date 1.
It seems that I want to test each pair for date Null and if date null, is
action null. And do that for all the combinations of pairs. Am I over
analyzing this?
wal50

Klatuu said:
WHERE [Date1] IS NULL OR [Date2] IS NULL OR [Date3] IS NULL OR [Date4] IS
NULL OR [Date5] IS NULL

wal50 said:
I inherited a file where each record has (up to) 5 potential actions, each
with an associated date of completion. I produce various reports that do not
show actions that have been completed (have a date <> blank). I would also
like to have these reports print only those records that have incomplete
actions (no date in the associated field).
The problem is that there can be from 1 to 5 actions for any record, any of
the "action" fields can be used of not used, any they can be completed in any
order.
The selection criteria for the number of permutations of desired records
has me stumped.
Any ideas?
Thanks in advance
wal50
Click to expand...
Click to expand...
 
K

Klatuu

No, I guess I did not read your original post in enough detail. Just testing
the pairs is sufficient:

WHERE ([Date1] IS NULL AND [Action1] IS NULL) OR ([Date2] IS NULL AND
[Action2] IS NULL) OR ([Date3] IS NULL AND [Action3] IS NULL) OR ([Date4] IS
NULL AND [Action4] IS NULL) OR ([Date5] IS NULL AND [Action5] IS NULL)

Note the parenthises. This keeps them paired for the correct evaluation.


wal50 said:
I tried that and it doen't work. I think the problem is the numerous
permutations.

For example, date5 is null. This could be either because there was no
action5 or action5 is not complete. I would not want the record in the first
case but would in the second. This is just for one action. If I don't want
the record because of the test for 5, I might want it because of the results
for date4 ...date 1.
It seems that I want to test each pair for date Null and if date null, is
action null. And do that for all the combinations of pairs. Am I over
analyzing this?
wal50

Klatuu said:
WHERE [Date1] IS NULL OR [Date2] IS NULL OR [Date3] IS NULL OR [Date4] IS
NULL OR [Date5] IS NULL

wal50 said:
I inherited a file where each record has (up to) 5 potential actions, each
with an associated date of completion. I produce various reports that do not
show actions that have been completed (have a date <> blank). I would also
like to have these reports print only those records that have incomplete
actions (no date in the associated field).
The problem is that there can be from 1 to 5 actions for any record, any of
the "action" fields can be used of not used, any they can be completed in any
order.
The selection criteria for the number of permutations of desired records
has me stumped.
Any ideas?
Thanks in advance
wal50
Click to expand...
Click to expand...
Click to expand...
 
W

wal50

Thanks that sounds like it will work. Where do I put this?

Klatuu said:
No, I guess I did not read your original post in enough detail. Just testing
the pairs is sufficient:

WHERE ([Date1] IS NULL AND [Action1] IS NULL) OR ([Date2] IS NULL AND
[Action2] IS NULL) OR ([Date3] IS NULL AND [Action3] IS NULL) OR ([Date4] IS
NULL AND [Action4] IS NULL) OR ([Date5] IS NULL AND [Action5] IS NULL)

Note the parenthises. This keeps them paired for the correct evaluation.


wal50 said:
I tried that and it doen't work. I think the problem is the numerous
permutations.

For example, date5 is null. This could be either because there was no
action5 or action5 is not complete. I would not want the record in the first
case but would in the second. This is just for one action. If I don't want
the record because of the test for 5, I might want it because of the results
for date4 ...date 1.
It seems that I want to test each pair for date Null and if date null, is
action null. And do that for all the combinations of pairs. Am I over
analyzing this?
wal50

Klatuu said:
WHERE [Date1] IS NULL OR [Date2] IS NULL OR [Date3] IS NULL OR [Date4] IS
NULL OR [Date5] IS NULL

:

I inherited a file where each record has (up to) 5 potential actions, each
with an associated date of completion. I produce various reports that do not
show actions that have been completed (have a date <> blank). I would also
like to have these reports print only those records that have incomplete
actions (no date in the associated field).
The problem is that there can be from 1 to 5 actions for any record, any of
the "action" fields can be used of not used, any they can be completed in any
order.
The selection criteria for the number of permutations of desired records
has me stumped.
Any ideas?
Thanks in advance
wal50
Click to expand...
Click to expand...
Click to expand...
 
W

wal50

2nd thoughts:
Maybe it depends on where this goes, but I think your suggested Where
statement selects records where any pair in the record is null.
Any pair of the 5 pairs may be null and I still could want the record
because another pair(s) has a null date and an action. That's what I meant
by numerous permutations.
WAL50

wal50 said:
Thanks that sounds like it will work. Where do I put this?

Klatuu said:
No, I guess I did not read your original post in enough detail. Just testing
the pairs is sufficient:

WHERE ([Date1] IS NULL AND [Action1] IS NULL) OR ([Date2] IS NULL AND
[Action2] IS NULL) OR ([Date3] IS NULL AND [Action3] IS NULL) OR ([Date4] IS
NULL AND [Action4] IS NULL) OR ([Date5] IS NULL AND [Action5] IS NULL)

Note the parenthises. This keeps them paired for the correct evaluation.


wal50 said:
I tried that and it doen't work. I think the problem is the numerous
permutations.

For example, date5 is null. This could be either because there was no
action5 or action5 is not complete. I would not want the record in the first
case but would in the second. This is just for one action. If I don't want
the record because of the test for 5, I might want it because of the results
for date4 ...date 1.
It seems that I want to test each pair for date Null and if date null, is
action null. And do that for all the combinations of pairs. Am I over
analyzing this?
wal50

:

WHERE [Date1] IS NULL OR [Date2] IS NULL OR [Date3] IS NULL OR [Date4] IS
NULL OR [Date5] IS NULL

:

I inherited a file where each record has (up to) 5 potential actions, each
with an associated date of completion. I produce various reports that do not
show actions that have been completed (have a date <> blank). I would also
like to have these reports print only those records that have incomplete
actions (no date in the associated field).
The problem is that there can be from 1 to 5 actions for any record, any of
the "action" fields can be used of not used, any they can be completed in any
order.
The selection criteria for the number of permutations of desired records
has me stumped.
Any ideas?
Thanks in advance
wal50
Click to expand...
Click to expand...
Click to expand...
 
W

wal50

and 3rd thoughts.
Your suggestion gave me the right direction and I got it to work. The
solution is that I want to select records where: (Action1 is not null and the
Date1 is null) or (Action2 is not null and the Date2is null) or ....
Thanks for your help.

wal50 said:
2nd thoughts:
Maybe it depends on where this goes, but I think your suggested Where
statement selects records where any pair in the record is null.
Any pair of the 5 pairs may be null and I still could want the record
because another pair(s) has a null date and an action. That's what I meant
by numerous permutations.
WAL50

wal50 said:
Thanks that sounds like it will work. Where do I put this?

Klatuu said:
No, I guess I did not read your original post in enough detail. Just testing
the pairs is sufficient:

WHERE ([Date1] IS NULL AND [Action1] IS NULL) OR ([Date2] IS NULL AND
[Action2] IS NULL) OR ([Date3] IS NULL AND [Action3] IS NULL) OR ([Date4] IS
NULL AND [Action4] IS NULL) OR ([Date5] IS NULL AND [Action5] IS NULL)

Note the parenthises. This keeps them paired for the correct evaluation.


:

I tried that and it doen't work. I think the problem is the numerous
permutations.

For example, date5 is null. This could be either because there was no
action5 or action5 is not complete. I would not want the record in the first
case but would in the second. This is just for one action. If I don't want
the record because of the test for 5, I might want it because of the results
for date4 ...date 1.
It seems that I want to test each pair for date Null and if date null, is
action null. And do that for all the combinations of pairs. Am I over
analyzing this?
wal50

:

WHERE [Date1] IS NULL OR [Date2] IS NULL OR [Date3] IS NULL OR [Date4] IS
NULL OR [Date5] IS NULL

:

I inherited a file where each record has (up to) 5 potential actions, each
with an associated date of completion. I produce various reports that do not
show actions that have been completed (have a date <> blank). I would also
like to have these reports print only those records that have incomplete
actions (no date in the associated field).
The problem is that there can be from 1 to 5 actions for any record, any of
the "action" fields can be used of not used, any they can be completed in any
order.
The selection criteria for the number of permutations of desired records
has me stumped.
Any ideas?
Thanks in advance
wal50
Click to expand...
Click to expand...
Click to expand...
 
Top