I have a database with multiple files. There is a 'Members' file with each record containing details of an individual's membership of our organisation. There is a 'Worksites' file with each record containing information about the worksite that a member may be working at. There is a 'Staff Reps' File with each record detailing one of our staff members, who have responsibility for looking after one or more worksites and all the members working at that/those worksite(s). There are other files, similarly related to the Worksites file (eg Regions - the regions of the state where the worksite maybe located, Awards - the industrial awards that applies to all members working at a particular worksite).

What I would like to do is look at a Member record and display the name of the staff member who has responsibility for the worksite that the member works at.

Similarly, be able to show the name of the Region or Award that the member works in.

I have created a relationship between the Members and Worksites files, and relationships between the Worksites and Staff Reps file, the Worksites and Regions files and the Worksites and Awards files.

I cannot seem to find a neat way to create a relationship between the Members and Staff Reps files (and similarly between the Members and Regions files, and again between the Members and Awards files).

I also would like to do summary reports counting the number of particular types of Members in each Region, Award or for each Staff Rep - but that is going to be easy once I have worked out ways to link the Members file to the other files.

Hopefully somebody can give me some pointers on what to do...

You can put calculation fields in your tables to pull the data you're looking for across the relationships. So in your worksites file create a calculation field that equals the name of the staff member from that relationship. Then from the members file just show that calculated field you just created.

I'm sure there's a more articulate way to put it, but this should get you started.

Lonn

Lonn

Thank you so much, I eventually found that was the way to do it... it was just taking me so long to track down the method, that I tried asking the question on the forums.

Thanks again for your response

Derek