complex count and compare query

[jpatota]

I'm constructing a database that will track municipal government board and committee members. There are about 20 different committees in town, each having a different number of required members for different roles. A typical committee could require 5 "regular" and 3 "associate" members. I'd like to create a query that counts the actual number of members in each role for each committee, and returns those which are less than the required number.

Table definitions -

The requirements of each committee is stores in a ComMemReqs junction table that is fed by a committees and memberRoles table.

MemberRoles has the heading of MemberRoleID and MemberRole. Typical entries are of the form {1,Member} and {2,Associate}.

Committees have an committeeID, committeeName, and some other attributes that are irrelevant to this particular query.

ComMemReqs is the junction table. Its heading is CommitteeName (FK), MemberRole(FK), and RequiredNumber

There is a servesOn table that specifies the actual membership of each committee. It has a memberName attribute (FK from the members table), CommitteeName (FK from the committees table) and memberRole (FK) from the memberRoles table.

​

I'm new to SQL so I'm having a hard coming up with this query. I really appreciate your help.

Thank you,

John

 

[jzwp22]

Welcome to AWF!

I would recommend a couple changes to your table structure

In your ComMemReqs table, I would use the committeeID as the FK rather than the CommitteeName and memberRoleID rather than memberRole. All relational databases are more efficient with numerical fields rather than text fields. The same would hold for the servesOn table, use the corresponding ID fields rather than the text fields.

With those changes taken care of, you would build an aggregate or totals query based on the servesOn table to get the actual count for each role within each committee. The SQL text of the query would look like this:

query name: qryServesOnCounts
SELECT servesOn.committeeID, servesOn.memberRoleID, Count(servesOn.memberRoleID) AS CountOfmemberRole
FROM servesOn
GROUP BY servesOn.committeeID, servesOn.memberRoleID;


Since your ComMemReqs table already has the required counts summarized, I would create a second query that uses the ComMemReqs table and the query above. You would join the table and query via both the committeeID and the memberRoleID fields. The query would look like this:

SELECT ComMemReqs.comitteeID, ComMemReqs.MemberRoleID, ComMemReqs.RequiredNumber, qryServesOnCounts.CountOfmemberRole
FROM ComMemReqs INNER JOIN qryServesOnCounts ON (ComMemReqs.comitteeID=qryServesOnCounts.committeeID) AND (ComMemReqs.MemberRoleID=qryServesOnCounts.memberRoleID);

As written, the query above will have all records showing both the current count and the required number for each role within each committee, so now we can just filter the query to show only those records where the current count < required count. To do this, we just need to add that criteria via a WHERE clause. The query would look like this:

query name: qryComparisonAfterWHEREClauseAdded
SELECT ComMemReqs.comitteeID, ComMemReqs.MemberRoleID, ComMemReqs.RequiredNumber, qryServesOnCounts.CountOfmemberRole
FROM ComMemReqs INNER JOIN qryServesOnCounts ON (ComMemReqs.MemberRoleID=qryServesOnCounts.memberRoleID) AND (ComMemReqs.comitteeID=qryServesOnCounts.committeeID)
WHERE CountOfMemberRole< RequiredNumber;


Now, if you want to show the actual committee and member role names rather than the ID field values, just create a final query that joins the above query to the respective tables.

query name: qryFinal
SELECT Committees.committeeName, MemberRoles.MemberRole, qryComparisonAfterWHEREClauseAdded.RequiredNumber, qryComparisonAfterWHEREClauseAdded.CountOfmemberRole
FROM (qryComparisonAfterWHEREClauseAdded INNER JOIN MemberRoles ON qryComparisonAfterWHEREClauseAdded.MemberRoleID = MemberRoles.MemberRoleID) INNER JOIN Committees ON qryComparisonAfterWHEREClauseAdded.comitteeID = Committees.committeeID;

I've attached a DB as an example.

 

[jpatota]

WOW! thats how you do it!

Thanks so much for your help!

 

[jzwp22]

You're welcome! Glad I could help.

 

[jpatota]

How should I handle committees that require a position that it currently has no people serving? Ex - board C requires 1 associate and currently has nobody serving in that capacity. I think I have a conceptual way of solving the problem but I'm new to sql so I'm not sure how to write the exact query.

qryServesOnCounts counts the existing members of each role on each committee based on a junction table linking members and committees. qryComparison inner joins that table to the requirements based on their common memberrole and committeename id's. If there is no position in the servesOn table (because nobody serves in that capacity) it is not returned in the qryServesOnCounts query and is not joined to the requirements table.

My conceptual answer to generate the qryComparison query from comMemReqs table. Instead of querying the servesOn table for member counts and comparing that to required, is it better to start from the committeeRequirements table and count all of the members from servesOn that have a common committee? Those which have no members on the servesOn relation would return null and Nz'd to 0.

John

 

[jzwp22]

You are on the right track. You would still use the qryServedOn but you would left join the table ComMemReqs to that query and use an IIF() function to return a 0 when the count is null. The query would look like this.

SELECT ComMemReqs.comitteeID, ComMemReqs.MemberRoleID, ComMemReqs.RequiredNumber, IIF(isnull(qryServesOnCounts.CountOfmemberRole),0,qryServesOnCounts.CountOfmemberRole) AS CountOfMembers
FROM ComMemReqs LEFT JOIN qryServesOnCounts ON (ComMemReqs.MemberRoleID=qryServesOnCounts.memberRoleID) AND (ComMemReqs.comitteeID=qryServesOnCounts.committeeID)
WHERE IIF(isnull(qryServesOnCounts.CountOfmemberRole),0,qryServesOnCounts.CountOfmemberRole) < RequiredNumber;


You will then have to adjust the qryFinal to refer to the above query.

I've attached the revised DB; qryFinal2 shows the end result of the modifications

 

[jpatota]

genius - thank you

 

[jzwp22]

You're welcome; good luck with your project