1 Reply Latest reply on Jul 13, 2011 9:56 AM by philmodjunk

    Self Join relationship with multiple tables' data involved



      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.

        • 1. Re: Self Join relationship with multiple tables' data involved

          It would help to know the specific relationships you've established between each table. You can use text to document the relationships in this format:

          TableOccurrenceName-----<TableOccurrenceName (----< means "one to many")

          TableOccurrenceName::FieldName = TableOccurrenceName::FieldName

          You could also click Edit Post on your original message and upload a screen shot of this from your database.

          It appears you have:


          Staff::StaffID = Assignments::StaffID

          But I don't know how you've linked in records from your Clients table here and we'd need to know that in order to suggest a solution to your issue. (knowing whether an assignment can be linked to multiple client records or only one client record is a very important detail here.)