Relationship between staff and trial

[rclarke]

Hi guys,

Trying to get my head around how to setup my tables and relationships.

It's more complicated than any of the references and tutorials that I have read through so have no choice but to post asking for help. I did try to work it out on my own, honest!

I have several tables and queries based on tables and cannot get them to generate the results I want when querying them (just one record shows when many should be showing), so this leads me to believe there's a problem with the way I have set my relationships;

1. tblStaff (Holds lists of all staff and their contact details)
   
2. queryCoordinator (Filters list of staff from all staff list based on whether they are a projectmanager)
   
3. queryLeader (Filters project leaders from list)
   
4. queryMinion (Filters minions from list)
   
5. tblProject Contains all details from project

I have the staff queries so I can choose from a list of names that are relevant to that job in a form, so we don't have to look through a huge list of staff

I then want to use a query to pull all this information together (project details and contact details of each person involved) so that I can print a single record on a report, however the query isn't working correctly and returns only one project record, not all of them.

There is only one leader, one project coordinator and one minion per project (as far as I am concerned) and I want to reflect this in the query by providing their name and contact details from the single staff list.

Would this work?

Is the relationship between the project and the individual roles Many-to-Many or One-to-Many.

I have many projects, and many staff, however each project only has one leader, one coordinator and one minion. I'm thinking One-to-Many, however when I relate the tables together, using the name as the relationship on each form this is where I have problems.

Is it because I am using a name, and not an ID?

 

[KenHigg]

You need an autonumber pk field in the staff table and a project manger integer field in the project table which will be the fk back to the staff table.

 

[rclarke]

You need an autonumber pk field in the staff table and a project manger integer field in the project table which will be the fk back to the staff table.

I tried that, but because all the contact details are coming from one staff table, when I try to obtain the address telephone number for the project coordinator, the minion P) and the mananger it doesn't query correctly, because they are all coming from the same table.

I can't put a pk in a query, aside from the ID from the staff table, however, how can I then link the staff query to the project and back to the details on the staff form?

 

[KenHigg]

Sounds like you need a minion fk field in the project table in addition to the manager field.

To be honest I don't completely follow your logic

 

[rclarke]

Yes I did explain it rather poorly, for that I do apologise.

I did fix it with a bit of trial and error, without the use of pks and fks...

It was a one to many relationship, rather than a many to many, hence the confusion.

 

[KenHigg]

Cool - Glad you have it working.

 

[jdraw]

As Ken said, "Glad you have it working".
For reference, you might wish to review these articles.
Tables and relationships are not normally resolved/identified with

I did fix it with a bit of trial and error, without the use of pks and fks..

see:
http://www.rogersaccesslibrary.com/Tutorials/Entity_Relationship.zip
http://www.rogersaccesslibrary.com/forum/uploads/5/12-Steps_to_Better_Databases.zip


Good luck with your project.