Self Join relationship with multiple tables' data involved
I appreciate your time. My query is the following: I have STAFF and EVENTS they work through ASSIGNMENTS as well as a CLIENTS table. Under STAFF i have a relationship to ASSIGNMENTS so I can see the total experience each staff member has (total hours worked). I would like also to be able to see the experience they have relative to each client when on an assignment. The totaling of experience also determines their pay and therefore must not include expeirence for event assignments coming up, just the events they have worked in the past.
I tried a self join of ASSIGNMENTS but couldn't get the relationship to use the "date" and "client" fields to properly omit (in a portal- it just stays blank) and total the hours of experience each person has AT THAT moment relative TO THAT client for that particular assignment. The Date field in ASSIGNMENTS is from the EVENTS and the client field in ASSIGNMENTS comes from CLIENTS through EVENTS. I am wondering if that is the issue- I have no trouble with the self join showing assignments for that individual. Each entry has a uid that links information across tables.
I've little experience with forums and did my best to explain the situation.