I'm having a counting problem within a crosstab query. Here is the SQL for my Query.
PARAMETERS [Enter First Year] Value, [Enter Second Year] Value;
TRANSFORM Nz(Count(Works.Class),0) AS [Journal Articles]
SELECT Faculty.Discipline, Count(Works.Class) AS Total, Count(Faculty.[Graduate faculty]) AS [Count]
FROM (Class INNER JOIN Works ON Class.Class = Works.Class) INNER JOIN (Faculty INNER JOIN [Work authors] ON Faculty.[Faculty Number] = [Work authors].[Faculty Number]) ON Works.[Works Number] = [Work authors].[Works Number]
WHERE (((Works.Year) Between [Enter First Year] And [Enter Second Year]) AND ((Faculty.[Graduate faculty])="Yes"))
GROUP BY Faculty.Discipline
Now My Problem Lies within the Counting of the Faculty.[Graduate faculty] area. I want it to count each instance of the graduate faculty once. Right now my totals column totals all records where graduate faculty is equal to yes. Now my Other Totals field called Count is supposed to total all the records where graduate faulty equals yes and their discipline is equal. Its counting it the same number of times as the Totals Column instead of just counting it once. Basically I want this column to just Count all instances where the Faculty Member's Discipline is Accounting and their Graduate Faculty is Equal to Yes One Time instead of once for every Work that they've authored. I've included a picture to help you get the idea of what i'm talking about.. Hopefully someone can help me out. Thank you.