Querying multiple records in two tables

[rmorrison]

Hi, in my database I have tables for users (UserID, UserName),
projects (ProjectID, Project Name), and qualifications (QualID,
QualName). I have join tables for users_qualifications (UserID,
QualID), and projects_qualifications. (ProjectID, QualID).

What I need to do is run a query for a project to show which users
have the exact matching qualificiations.

Users can have many qualifications, projects can require many
qualifications, users may only work on a project if the qualifications
required/held match exactly.

Please help.

 

[Dale Fye]

Assuming that ProjID, and QualID are numeric, the following should give you
the ProjectID and UserID of all those individuals who have the correct quals
for each project.

SELECT tbl_ProjQuals.ProjID,
tbl_UserQuals.UserID,
Count(tbl_ProjQuals.QualID) AS CountOfQualID
FROM tbl_ProjQuals INNER JOIN tbl_UserQuals
ON tbl_ProjQuals.QualID = tbl_UserQuals.QualID
GROUP BY tbl_ProjQuals.ProjID, tbl_UserQuals.UserID
HAVING Count(tbl_ProjQuals.QualID)=DCount("QualID","tbl_ProjQuals","[ProjID]
= " & [tbl_ProjQuals].[ProjID])
ORDER BY tbl_ProjQuals.ProjID, tbl_UserQuals.UserID;

Basically, what this does is join the ProjectQuals table and UserQuals
tables on the QualID field and count the number of matches for each Project,
UserID combination. It then filters that for those combinations where the
Count of matching QualID fields is the same as the number of QualIDs for
that ProjectID.

HTH
Dale

 

[rmorrison]

Assuming that ProjID, and QualID are numeric, the following should give you
the ProjectID and UserID of all those individuals who have the correct quals
for each project.

SELECT tbl_ProjQuals.ProjID,
tbl_UserQuals.UserID,
Count(tbl_ProjQuals.QualID) AS CountOfQualID
FROM tbl_ProjQuals INNER JOIN tbl_UserQuals
ON tbl_ProjQuals.QualID = tbl_UserQuals.QualID
GROUP BY tbl_ProjQuals.ProjID, tbl_UserQuals.UserID
HAVING Count(tbl_ProjQuals.QualID)=DCount("QualID","tbl_ProjQuals","[ProjID]
= " & [tbl_ProjQuals].[ProjID])
ORDER BY tbl_ProjQuals.ProjID, tbl_UserQuals.UserID;

Basically, what this does is join the ProjectQuals table and UserQuals
tables on the QualID field and count the number of matches for each Project,
UserID combination. It then filters that for those combinations where the
Count of matching QualID fields is the same as the number of QualIDs for
that ProjectID.

HTH
Dale

Hi, in my database I have tables for users (UserID, UserName),
projects (ProjectID, Project Name), and qualifications (QualID,
QualName). I have join tables for users_qualifications (UserID,
QualID), and projects_qualifications. (ProjectID, QualID).

What I need to do is run a query for a project to show which users
have the exact matching qualificiations.

Users can have many qualifications, projects can require many
qualifications, users may only work on a project if the qualifications
required/held match exactly.

Please help.- Hide quoted text -

- Show quoted text -

Thanks, that's almost what I want. How can I show a list of the users'
names with the appropriate qualifications. For example, if I am in a
project record and want to see who can work on the project, I'd like
to click on a button and bring up a list of the users. I've tried
modifying this query but I can't get it to work.

Hope you can help.

 

[Dale Fye]

Add your users table to the query grid, Join the Users.UserID to
UserQuals.UserID, then change tbl_UserQuals.UserID to tbl_Users. UserName in
the SELECT, GROUP BY, and ORDER BY clauses. Then modify the where clause so
that it points to the ProjectID control on your form, so all you get is the
names of the individuals who are qualified for that project.

HTH

Assuming that ProjID, and QualID are numeric, the following should give
you
the ProjectID and UserID of all those individuals who have the correct
quals
for each project.

SELECT tbl_ProjQuals.ProjID,
tbl_UserQuals.UserID,
Count(tbl_ProjQuals.QualID) AS CountOfQualID
FROM tbl_ProjQuals INNER JOIN tbl_UserQuals
ON tbl_ProjQuals.QualID = tbl_UserQuals.QualID
GROUP BY tbl_ProjQuals.ProjID, tbl_UserQuals.UserID
HAVING
Count(tbl_ProjQuals.QualID)=DCount("QualID","tbl_ProjQuals","[ProjID]
= " & [tbl_ProjQuals].[ProjID])
ORDER BY tbl_ProjQuals.ProjID, tbl_UserQuals.UserID;

Basically, what this does is join the ProjectQuals table and UserQuals
tables on the QualID field and count the number of matches for each
Project,
UserID combination. It then filters that for those combinations where
the
Count of matching QualID fields is the same as the number of QualIDs for
that ProjectID.

HTH
Dale

Hi, in my database I have tables for users (UserID, UserName),
projects (ProjectID, Project Name), and qualifications (QualID,
QualName). I have join tables for users_qualifications (UserID,
QualID), and projects_qualifications. (ProjectID, QualID).

What I need to do is run a query for a project to show which users
have the exact matching qualificiations.

Users can have many qualifications, projects can require many
qualifications, users may only work on a project if the qualifications
required/held match exactly.

Please help.- Hide quoted text -

- Show quoted text -

Thanks, that's almost what I want. How can I show a list of the users'
names with the appropriate qualifications. For example, if I am in a
project record and want to see who can work on the project, I'd like
to click on a button and bring up a list of the users. I've tried
modifying this query but I can't get it to work.

Hope you can help.

 

[rmorrison]

Add your users table to the query grid, Join the Users.UserID to
UserQuals.UserID, then change tbl_UserQuals.UserID to tbl_Users. UserName in
the SELECT, GROUP BY, and ORDER BY clauses. Then modify the where clause so
that it points to the ProjectID control on your form, so all you get is the
names of the individuals who are qualified for that project.

HTH

Assuming that ProjID, and QualID are numeric, the following should give
you
the ProjectID and UserID of all those individuals who have the correct
quals
for each project.
SELECT tbl_ProjQuals.ProjID,
tbl_UserQuals.UserID,
Count(tbl_ProjQuals.QualID) AS CountOfQualID
FROM tbl_ProjQuals INNER JOIN tbl_UserQuals
ON tbl_ProjQuals.QualID = tbl_UserQuals.QualID
GROUP BY tbl_ProjQuals.ProjID, tbl_UserQuals.UserID
HAVING
Count(tbl_ProjQuals.QualID)=DCount("QualID","tbl_ProjQuals","[ProjID]
= " & [tbl_ProjQuals].[ProjID])
ORDER BY tbl_ProjQuals.ProjID, tbl_UserQuals.UserID;
Basically, what this does is join the ProjectQuals table and UserQuals
tables on the QualID field and count the number of matches for each
Project,
UserID combination. It then filters that for those combinations where
the
Count of matching QualID fields is the same as the number of QualIDs for
that ProjectID.
HTH
Dale

Hi, in my database I have tables for users (UserID, UserName),
projects (ProjectID, Project Name), and qualifications (QualID,
QualName). I have join tables for users_qualifications (UserID,
QualID), and projects_qualifications. (ProjectID, QualID).
What I need to do is run a query for a project to show which users
have the exact matching qualificiations.
Users can have many qualifications, projects can require many
qualifications, users may only work on a project if the qualifications
required/held match exactly.
Please help.- Hide quoted text -
- Show quoted text -

Thanks, that's almost what I want. How can I show a list of the users'
names with the appropriate qualifications. For example, if I am in a
project record and want to see who can work on the project, I'd like
to click on a button and bring up a list of the users. I've tried
modifying this query but I can't get it to work.

Hope you can help.- Hide quoted text -

- Show quoted text -

Perfect! Thanks for your help!