Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Posts
    100

    Unanswered: Complicated? Query Question

    I'm dealing with two tables:


    Table 1: (individuals and the projects they have accrued hours on)

    Name..........Project..........Hours
    John.............A.................5
    John.............B.................8
    John.............C.................12
    Sam.............A...................3
    Sam.............D...................5
    Sam.............E..................24
    Wayne.........C...................22


    Table 2: (project teams)

    Project..........Member1...........Member2.....Mem ber3
    A...................Wayne..............John....... ..Sam
    B....................John.................Billy... .......

    Notice that Wayne is listed as a member in project A (Table 2) but has not worked any hours for project A (Table 1).
    Also, Billy is listed as a member of project B (Table 2) but is not listed in Table 1 at all.

    Is there a way I can show each circumstance of:

    1) showing when somebody is listed as being on a team in table 2 but does not have any hours for that project in table 1 (they could have hours on other projects)
    2) showing people listed as being on a project in Table 2 but not even listed in Table 1

    I'm trying to create some exception reports that will help me maintain a database that has become sloppy over the years (and to prevent any future situations)

    Thanks!

  2. #2
    Join Date
    Sep 2004
    Posts
    161
    if i can suggest...
    I think you must change the conception of your table2.
    Why ?
    You can not find information that you require easily
    You can not have more tree member in a project
    You must change like this
    Project
    Member
    Order (if it's important for you)
    But this implies can be of large modification of your application... and i dont no find any other thing for help you

  3. #3
    Join Date
    Jan 2004
    Posts
    100
    Is there a way to run a query on table 2 to get it in the format you spoke of?

    I believe this would be it: (How can I get here?)

    Project........Name
    A...............Wayne
    A...............John
    A...............Sam
    B...............John
    B...............Billy

    I'm working with a legacy database that is using a different front-end that isn't going away. (The order of the names does not matter)

Posting Permissions

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