Query Count Function Reporting Wrong Values

Question

I have a database that tracks four metrics for employee bonus.  I am writing a query to summarize all 4 metrics for all employees.  I have 3 of the metrics working but the fourth is not reporting the correct values.  I need to count the total dates an employee participated (participationdate).  I added the "Participation Date" field from the "Participation" table to my query and it results in showing all of the participation dates on separate lines for each employee (as expected).  When I change the totals line to "count", the result in the participation column shows numbers I don't recognize (ie, instead of 4, it shows 100). 

I am a novice and do not know SQL very well at all, but appreciate any assistance.  Below are three screenshots - the initial result before changing the total line to "count", the query design view, and the result after changing to "count". 

Thanks in advance!

Answer 1

As you want to restrict the results on the ProjectClosed column in Project having a value of FALSE I think the query would be:

    SELECT ContactName AS ContactID,

    COUNT(*) AS NumberOfParticipations

    FROM Participation INNER JOIN Project

    ON Participation.CMICjobNumber = Project.ProjectID

    WHERE NOT ProjectClosed

    GROUP BY ContactName;

I'm assuming that the ParticipationDate values are distinct per ContactName in Participation.

If you save the second query as qryParticipationCount say, you'd need to return the ContactID column from the Pepper Contact List table in your existing query, from which the Participation table would now be omitted with:

    ON  qryParticipationCount.ContactID = [Bonus Calc Query].ContactID

and return the NumberOfParticipations column in the final query's result table.

Answer 2

To return all rows from one table regardless of a match in another table you need to use either a LEFT or a RIGHT OUTER JOIN, depending on the direction of the JOIN.  You can do this in query design view by right clicking on the join line between the tables, selecting 'Join Properties' and then selecting the appropriate option in the dialogue.  However, where other tables are included in the query you might find that this results in conflicting joins, and you'll need to amend other joins accordingly.  Sometimes this is not possible, in which case you'd need to break down the query further into separate queries.

Answer 3

Thanks Ken!  That works perfectly.  A follow-up question if you don't mind:

For my Bonus Calc Query, I need to show all of the contacts on the contact list, even if they don't have any data in the four metrics.  It seems that I could enter a criteria under the ContactName to show all records, but I can't seem to get one to work. Can you assist?

Thanks again!

Answer 4

Thanks Ken.  I am struggling to write a second query that counts the rows per contactname that works without using the Access Totals row.  Can you assist me with this?  The table is "Participation", the fields are "ContactName" and "ParticipationDate". 

Also, once I have that query complete, how do I join it to my "Bonus Calc Query"? 

Thanks again.

Answer 5

The COUNT operator will return the number of rows per group before grouping.  As you have other tables in the query, which in some cases will have multiple rows per participation date per contact, the count is that of these multiple rows before the grouping, hence the inflated values.  You can return a count of distinct rows by counting the number of rows in the result set of a subquery which uses the DISTINCT predicate to return one instance of each value of the column in question, e.g. the following would return the number of contacts with the same last name per city

SELECT City, COUNT(*) AS NameCount

FROM (SELECT DISTINCT City, LastName

            FROM Contacts)  AS DistinctName

GROUP BY City;

However, if you are not experienced in writing SQL statements you might find it easier to break your query down into two queries, one of which returns the ContactName and the count of rows per ContactName (actually a long integer number value by the look of it, as you appear to have used the lookup field wizard) from the Participation table, and then join this to a version of your current query which omits the count.  You should then be able to join the two queries on the participant to return the correct count in the final result table.