I have a database with two tables, X and Y
Name, Event Type A Time, Event Type B Time, Event Type C Time < --Fields
Name, Event Type, Time, Join (Of Name and Event Type), Dupes <--Fields
Joe, Event A, 4:30, JoeEvent A,
Joe, Event B, 4:45, JoeEvent B,
Emily, Event A, 5:01, EmilyEvent A,
Fred, Event A, 2:12, FredEvent A,
Fred, Event B,4:12, FredEvent B,
Fred, Event C, 9:06, FredEvent C,
Fred, Event C, 10:01, FredEvent C, X
I have a simply script that marks the dupes based on the Join field (which is Name & Event Type).
The problem I am trying to solve is to auto populate the Table X Event Type A, Event Type B and Event Type C fields through auto-entry calculation.
For instance, For Event A, I would have the following calculation: If(Table Y::Event Type="A";Table Y::Time,"")
For Event B, I would have the following calculation: If(Table Y::Event Type="B" and Dupes != "X";Table Y::Time,"")
Lastly, For Event C, I would have the following calculation: If(Table Y::Event Type="C" and Dupes !="X";Table Y::Time,"")
Obviously it doesn't work. I think it is because the records at the beginning of Table Y are all Event A's - which results in Table X getting the Event Type A Time populated for every record. However, for Event Type B's and C's, it doesn't;t pull the right value. I'm guessing this is because it sees the the related Name in Table Y for the calculation, but the first record listed in an Event Type A and it just stops...
It should noted that the Dupes comes into play because Event Types B and C can occur more than once - I'm just looking for a timestamp for the first one - hence the Dupes field (which I use a script for). Event Type A never has a dupe.
Any help you could provide would be great. I am a neophyte at best, but this kind of issue comes into play for me a lot. Thanks again!