3 Replies Latest reply on Mar 14, 2009 7:30 PM by fitch

    Reporting some values in a field sometimes but not at others....

    synergy46

      Title

      Reporting some values in a field sometimes but not at others....

      Post



      I have a members database that consists of these two tables:

      Members:
      MemberID.pk
      LastName
      FirstName
      etc

      History:
      MemberID.FK
      Date
      Event

      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.

       

       

       

      Ron