Results 1 to 2 of 2

Thread: Prerequisite's

  1. #1
    Join Date
    Apr 2006
    Posts
    2

    Unanswered: Prerequisite's

    Hello all,

    I am using Access 2002 and am I am having troubles with the database I am working on. I have a database
    that needs to filter out people according to whether or not they meet some
    prerequisites for a project. When entering a new project the user will input
    what prerequisites that consultants need to satisfy to be considered for the
    project, from a bank of possible prerequisites. The consultants therefore
    have a list of prerequisites that they meet, picked out of the bank of
    possible prerequisites. Ok, so when a consultant decides they want to try to
    get that project, they submit a request (linking the consultant to a project.)
    When this happens, (there could be many consultants interested in each
    project or also many projects) I need to be able to filter out which
    consultants meet the prerequisites.

    I currently have the tables set up as follows:
    tblConsultant
    CID - int
    Name - text
    PRIMARY KEY (CID)

    tblConsultantPrequals
    CPID - int
    CID - int
    Prequal - text
    PRIMARY KEY (CPID)
    FOREIGN KEY (CID, Prequal)
    (Most likely the consultant will have numerous entries in this table for
    multiple qualifications)

    tblProject
    PID - int
    Name - text
    PRIMARY KEY (PID)

    tblProjectPrequals
    PPID - int
    PID - int
    Prequal - text
    PRIMARY KEY (PPID)
    FOREIGN KEY (PID, Prequal)
    (Each project will also have
    tblRequest
    PID
    CID
    PRIMARY KEY (CID, PID)
    FOREIGN KEY (PID,CID)

    tblPrequals
    Prequal - text
    PRIMARY KEY (Prequal)

    I am lost on how to do this, I have read some other forums and even searched
    this one for the answer to a similar problem but I am not grasping what the
    solution is. Considering that there are about 30 possible prequalifications I
    am getting overwhelmed. I would appreciate any help that could be offered.

    Thanks!

    Casey
    College Student

  2. #2
    Join Date
    Apr 2006
    Posts
    2

    Resolved...

    Just so you all know, the problem has been solved with the following code:

    SELECT PP.PID, C.CID
    FROM tblProjectPrequals AS PP, tblConsultant AS C
    GROUP BY PP.PID, C.CID
    HAVING (((Count(PP.Prequal))=(SELECT COUNT(CP.Prequal)
    FROM tblConsultantPrequals CP
    WHERE CP.CID = C.CID AND CP.Prequal IN
    (SELECT PP1.Prequal
    FROM tblProjectPrequals PP1 WHERE PP1.PID = PP.PID))));

    I know in the FAQ about posts you said to always give an update when the project was solved.

    Casey

Posting Permissions

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