Prerequisite's

[Casey]

Hello,

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!

 

[Tom Ellison]

Dear Casey:

My analysis starts with the prerequisites of each project. Count them. So,
a project has, say, 5 prerequisites.

Now, a query joining the list of prerequisites for a project to the list of
prerequisites for each consultant will show the prerequisites a consultant
has out of the list of prerequisites for each project. Count these.

Now compare the counts. Where equal, that consultant has all the
prerequisites for those projects.

This is an outline of how I have done similar projects. It depends on
having proper constraints on the tables. No project or consultant may be
allowed to have the same prerequisite twice. As long as this is enforced,
the method will always work.

Can you put the query together from here? I could help with that, but I'd
need a copy of the data you have. I'm willing to put the query work
together, but I'm not willing to take the time to create the tables and
enter data. Also, the indexing you have is important, as outlined above.

Let me know how far this takes you, and what additional assistance you may
want.

Tom Ellison

 

[Casey via AccessMonster.com]

Dear Tom:

Thanks for the quick and helpful response. I now know what I need to do, its
just designing the query. I guess I am fairly new to working with SQL and
Access which is why I am having problems. I thought about it all weekend and
tried many different methods, but none of them worked. My main problem is
with the join query. I think I can design a query to count the number of
prereq's for each project using the COUNT(*) function but the join is evading
me. As of now I have this query that is not working...
_____________________________________________________________________________________

SELECT DISTINCT tblConsultantPrequals.CID, tblProjectPrequals.PID
FROM (tblProject INNER JOIN ((tblConsultant INNER JOIN tblConsultantPrequals
ON tblConsultant Firm.CID=tblConsultantPrequals.CID) INNER JOIN LOI ON
tblConsultant.CID=tblRequest.CID) ON tblProject.PID=tblRequest.PID) INNER
JOIN tblProjectPrequals ON tblProject.PID=tblProjectPrequals.PID;
_____________________________________________________________________________________

I currently have lets say this layout:

tblPrequals
Prequal
1
2
3
4
5

tblConsultant
CID Name
1 Cons1
2 Cons2

tblConsultantPrequals
CPID CID Prequal
1 1 1
2 1 4
3 2 1
4 2 2
5 2 5

tblProject
PID Name
1 Proj1
2 Proj2

tblProjectPrequals
PPID PID Prequal
1 1 1
2 2 4
2 2 5

tblRequest
PID CID
1 1
1 2
2 1
2 2

Running a query I wanted to get that Cons1 and Cons2 met the prequals for
Proj1. However with the query I ran above, I get that Cons1 and Cons2 meet
the prequal for Proj1, but also that Cons2 meets the Prequals for Proj2 which
is clearly does not. I really just need to do a join of the prequals b/w
Cons1 and Proj1 for example (like you suggested) and I cannot seem to do so...


If you get this message and could help, I would much appreciate it Tom.

Thanks again!

Dear Casey:

My analysis starts with the prerequisites of each project. Count them. So,
a project has, say, 5 prerequisites.

Now, a query joining the list of prerequisites for a project to the list of
prerequisites for each consultant will show the prerequisites a consultant
has out of the list of prerequisites for each project. Count these.

Now compare the counts. Where equal, that consultant has all the
prerequisites for those projects.

This is an outline of how I have done similar projects. It depends on
having proper constraints on the tables. No project or consultant may be
allowed to have the same prerequisite twice. As long as this is enforced,
the method will always work.

Can you put the query together from here? I could help with that, but I'd
need a copy of the data you have. I'm willing to put the query work
together, but I'm not willing to take the time to create the tables and
enter data. Also, the indexing you have is important, as outlined above.

Let me know how far this takes you, and what additional assistance you may
want.

Tom Ellison

Hello,

[quoted text clipped - 60 lines]

Thanks!

 

[Tom Ellison]

Dear Casey:

I put together this little thing:

SELECT PP.PID, C.CID
FROM tblProjectPrequals PP, tblConsultant 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 used more data than you provided, to make sure it excludes consultants
that do not meet the minimum qualifications.

Please let me know how this works for you. It may not require any
modifications, as I built the tables you provided and tested it against
that.

Tom Ellison

Dear Tom:

Thanks for the quick and helpful response. I now know what I need to do,
its
just designing the query. I guess I am fairly new to working with SQL and
Access which is why I am having problems. I thought about it all weekend
and
tried many different methods, but none of them worked. My main problem is
with the join query. I think I can design a query to count the number of
prereq's for each project using the COUNT(*) function but the join is
evading
me. As of now I have this query that is not working...
_____________________________________________________________________________________

SELECT DISTINCT tblConsultantPrequals.CID, tblProjectPrequals.PID
FROM (tblProject INNER JOIN ((tblConsultant INNER JOIN
tblConsultantPrequals
ON tblConsultant Firm.CID=tblConsultantPrequals.CID) INNER JOIN LOI ON
tblConsultant.CID=tblRequest.CID) ON tblProject.PID=tblRequest.PID) INNER
JOIN tblProjectPrequals ON tblProject.PID=tblProjectPrequals.PID;
_____________________________________________________________________________________

I currently have lets say this layout:

tblPrequals
Prequal
1
2
3
4
5

tblConsultant
CID Name
1 Cons1
2 Cons2

tblConsultantPrequals
CPID CID Prequal
1 1 1
2 1 4
3 2 1
4 2 2
5 2 5

tblProject
PID Name
1 Proj1
2 Proj2

tblProjectPrequals
PPID PID Prequal
1 1 1
2 2 4
2 2 5

tblRequest
PID CID
1 1
1 2
2 1
2 2

Running a query I wanted to get that Cons1 and Cons2 met the prequals for
Proj1. However with the query I ran above, I get that Cons1 and Cons2 meet
the prequal for Proj1, but also that Cons2 meets the Prequals for Proj2
which
is clearly does not. I really just need to do a join of the prequals b/w
Cons1 and Proj1 for example (like you suggested) and I cannot seem to do
so...


If you get this message and could help, I would much appreciate it Tom.

Thanks again!

Dear Casey:

My analysis starts with the prerequisites of each project. Count them.
So,
a project has, say, 5 prerequisites.

Now, a query joining the list of prerequisites for a project to the list
of
prerequisites for each consultant will show the prerequisites a consultant
has out of the list of prerequisites for each project. Count these.

Now compare the counts. Where equal, that consultant has all the
prerequisites for those projects.

This is an outline of how I have done similar projects. It depends on
having proper constraints on the tables. No project or consultant may be
allowed to have the same prerequisite twice. As long as this is enforced,
the method will always work.

Can you put the query together from here? I could help with that, but I'd
need a copy of the data you have. I'm willing to put the query work
together, but I'm not willing to take the time to create the tables and
enter data. Also, the indexing you have is important, as outlined above.

Let me know how far this takes you, and what additional assistance you may
want.

Tom Ellison

Hello,

[quoted text clipped - 60 lines]

Thanks!

--
Casey
College Student

Message posted via AccessMonster.com

 

[Casey via AccessMonster.com]

Dear Tom:

You are the best!!!! I have been working on this massive headache for over a
week. I now feel the pressure subsiding, thank you so much for your help. I
did not even think the way that you arranged that query. I would have
probably ambled on for MUCH longer without your help. Thank you so much!

Dear Casey:

I put together this little thing:

SELECT PP.PID, C.CID
FROM tblProjectPrequals PP, tblConsultant 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 used more data than you provided, to make sure it excludes consultants
that do not meet the minimum qualifications.

Please let me know how this works for you. It may not require any
modifications, as I built the tables you provided and tested it against
that.

Tom Ellison

Dear Tom:

[quoted text clipped - 105 lines]

 

[Tom Ellison]

Dear Casey:

I got a little nervous about one feature, and put back in something I took
out just before I posted. You may need this at some point.

SELECT PP.PID, C.CID
FROM tblProjectPrequals AS PP, tblConsultant AS C
GROUP BY PP.PID, C.CID
HAVING (SELECT COUNT(PP1.Prequal)
FROM tblProjectPrequals PP1
WHERE PP1.PID = PP.PID)
=
(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));

Tom Ellison

Dear Tom:

You are the best!!!! I have been working on this massive headache for over
a
week. I now feel the pressure subsiding, thank you so much for your help.
I
did not even think the way that you arranged that query. I would have
probably ambled on for MUCH longer without your help. Thank you so much!

Dear Casey:

I put together this little thing:

SELECT PP.PID, C.CID
FROM tblProjectPrequals PP, tblConsultant 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 used more data than you provided, to make sure it excludes consultants
that do not meet the minimum qualifications.

Please let me know how this works for you. It may not require any
modifications, as I built the tables you provided and tested it against
that.

Tom Ellison

Dear Tom:

[quoted text clipped - 105 lines]

Thanks!