Reporting some values in a field sometimes but not at others....
I have a members database that consists of these two tables:
It is a one to many relationship from Members to History
The Event field records the 'events' of a member. It is a drop down list that might include: Death, Dropped NPD, Suspended, Expelled, Elected or <Reinstated>.
If the user is <Reinstated> there should be a previous record of one of the following: Death, Dropped NPD, Suspended, Expelled.
I can produce a Conditional Value List that will allow me to 'zero in' on just certain and appropriate values. This is not the problem.
But, the problem is, how to report those members who have an Event of "Reinstated" AND link back to the type of Event from which they were previously "Death", "Dropped NPD", "Suspended" or "Expelled" so that I can show the Date and Event of Reinstatement and the Date of and Event of "Death", "Dropped NPD", "Suspended" or "Expelled".
I suspect I need to redefine "Reinsted" to "Reinstated - Suspended", "Reinstated - Expelled" etc... for each type of Reinstatement. That way each Reinstatement will pair with it's "Negative" predecessor. OK. But, I still need a way to write an IF or CASE logic statement in the Report Layout to include not just the current record that shows Reinstated - Suspended for example, but the corresponding and preceeding Event Suspended"????
I hope all this is clear. Thanks for reading this missive. Thanks for any ideas you may have.