Updating a Parent Record with Values from Multiple Child Record Values

[Michael Kennedy]

Rather odd SQL issue, at least for me.

===Data Sets===
--Parent Record
===================================
- Ticket key - i.e., 201
- Ticket No - i.e., 0012565410125
- Ticket Status - i.e.,; Partially Used, Suspended, Open

--Child Records (ticket coupons)
===================================
- Key to Ticket; i.e., 201
- Coupon ID; i.e., 500
- Coupon Status: five values (OPEN,SUSP,USED,VOID)

What I'm trying to accomplish is update the parent record with either the value Suspended, Open or Partially Used based on:

Any coupon status in the child record that are:

- Any SUSP value in child record = then Suspended
- Any Used AND Open but not SUSP, VOID = Partially Used
- All values are OPEN = OPEN

Have no clue how to get this written.