Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2008
    Posts
    8

    Question Unanswered: Querying data in a table not directly linked to the others in the query

    That title probably doesn't make much sense. Here's what i'm trying to do...

    I have an Access 2002 database used to store details of projects worked on by a team, and the work done against these projects.

    I have 4 tables. Project, Log, Staff and Staff_on_project.

    Project stores details of err projects, Log stores work carried out against a project. THey are linked by virtue of the Log table carrying projectID as a foreign key.

    The Staff table holds details of team member. The staff on project table resolves the many/many relationship that exists between project and staff and links these two tables.

    I have a query which returns the sum of time spent on projects, by project and by staff id. This works fine using only the Log and Project tables. However I wanted to return staff names as well instead of just the id's. To do this i need to involve the Staff table, but as soon as I do this I get either tons of redundant data or a type mismatch error. I basically just want to say 'give me the staff name where staff id is equal to the ID i'm already returning' but can't figure out how.

    Any thoughts on how I can acheive this? I can post a screen shot of query design if that helps

  2. #2
    Join Date
    Nov 2008
    Posts
    8

    Sshots

    Ok so I have attached a sshot of the relationships/tables in question and also of the query as it is - working, but without the names of the staff that i also want to get in the query.

    Can anyone suggest what I should do to also return staff names?

    Click image for larger version. 

Name:	relationships.JPG 
Views:	22 
Size:	25.4 KB 
ID:	9114

    Click image for larger version. 

Name:	working_q.JPG 
Views:	19 
Size:	30.0 KB 
ID:	9115

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I think Log should be related to Staff_on_Project, not Project.

    You could just add the Staff table to your query and link them, then include the staff name field.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Nov 2008
    Posts
    8
    Can you explain why you think Log should be related to staff on project, not project? I guess I do kinda see why because a log entry is related to both a specific project and a specific member of staff, but just want to check what you're thinking.

    The problem with doing that is that the DB is already in use and i don't know what effect shifting that relationship is going to have on the data already entered, as well as the input forms and subforms already created

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Quote Originally Posted by miles_trooper
    Can you explain why you think Log should be related to staff on project, not project? I guess I do kinda see why because a log entry is related to both a specific project and a specific member of staff, but just want to check what you're thinking.
    Quite simply that both ProjectID and StaffID are in it.

    That strongly suggests that it's not related to project alone and it's not related to staff alone, it's related to each staff member's activity on a project.

    Quote Originally Posted by miles_trooper
    The problem with doing that is that the DB is already in use and i don't know what effect shifting that relationship is going to have on the data already entered, as well as the input forms and subforms already created
    That's another problem isn't it?! I'm only pointing out that the relationships are wrong. It's up to you if you want to actually make it right or continue on as is and deal with any issues that arise because of it
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    Nov 2008
    Posts
    8

    Talking thanks

    ta for the help. I agree I think I've messed up the relationship here. I think since the DB is still in it's infancy I will try and rectify and see whas chaos I cause.

    Expect more plea's for help soon

  7. #7
    Join Date
    Nov 2008
    Posts
    8
    Haha that didn't take long. Next question resulting from the changes I'm going to make....

    If I replace the relationship between Log and Project with a relationship between Log and Staff_on_project - what fields should I involve in the new relationship?

    Am I right in thinking that since staff on project uses a combo of project id and staff id as it's primary key, I should use these two fields to establish the relationship with Log?

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    please...
    new question...
    new thread
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •