Using DISTINCT in Aggregate Functions

[nvwildfire]

Hi List!
I am having a problem with using the DISTINCT operator in and aggregate function like COUNT in Access 2002. I found some information on the Microsoft knowledge base acknowledging that there is a know problem with some of the ANSI-92 SQL standards in Access 2002 specifically using DISTINCT in aggregate functions (Microsoft Knowledge Base Article - 306250). They give a work around but I am not sure how to implement it under a complex SQL statement.

So if you have a query like this:
SELECT SUM (DISTINCT field1) from Table1

MS says to work around the error in Access 2002 to write the statement like this:
SELECT Sum(Field1) FROM (SELECT DISTINCT Field1 from table1)

My problem is that I have many joins and don't know how to through a sub select in. So here is what I have right now for a sql statement:

SELECT Count(tblstudent!studentID) AS NStudents, tblAssistantship.Assistantship, tblDegree.Degree, tblFiscalPay.FiscalYear, Round((Sum(tblsalary!salary*tblfiscalpay!duration))/((Sum(tblfiscalpay!duration))/30.416),0) AS MonthlySalary, Round(Max(tblsalary.salary*30.416),0) AS SMaxSalary, Round(Min(tblsalary.salary*30.416),0) AS SMinSalary, tbladvisor!lastname & ", " & tbladvisor!firstname AS AdvisorName
FROM tblStudent INNER JOIN ((tblDegree INNER JOIN (tblAdvisor INNER JOIN tblStudentAttributes ON tblAdvisor.FacultyID=tblStudentAttributes.FacultyID) ON tblDegree.DegreeID=tblStudentAttributes.DegreeID) INNER JOIN ((tblAssistantship INNER JOIN tblSalary ON tblAssistantship.AssistantshipID=tblSalary.AssistantshipID) INNER JOIN tblFiscalPay ON tblSalary.JobID=tblFiscalPay.JobID) ON tblStudentAttributes.StudentAttributesID=tblSalary.StudentAttributeID) ON tblStudent.StudentID=tblStudentAttributes.StudentID
GROUP BY tblAssistantship.Assistantship, tblDegree.Degree, tblFiscalPay.FiscalYear, tbladvisor!lastname & ", " & tbladvisor!firstname;

What I would like to add is another column called NStudents by adding this:

COUNT(DISTINCT tblStudent.StudentID) AS NStudents

But I cannot figure out how to do it with the MS workaround.

Any help would be greatly appreciated.

Karl Krauter
krauter@unr.edu

 

[nvwildfire]

Does anyone have any idea what I am talking about? I'm hoping that no one has responded because they don't understand my question. Please respond with some information.

kgk

 

[r937]

yes, i fully understand the problem

no, i have no idea how to proceed

the problem is your multiple one-to-many relationships

if you're counting the number of students per advisor per year per degree per assitantship, i don't understand where the multiple occurrences of a student come from, and even if i did, which would require an intimate knowledge of all your table relationships, that still might not help in deciding how to count only the distinct ones

if you know what i mean

suggestion: do some simple joins, like pull out the advisor's salary stuff, and save those queries, and build up more complex queries from simpler ones

at some point you will see where the duplications are coming from


rudy

 

[nvwildfire]

r937,

the dublicates are coming from my table design. The table I am calculating the average salary off of may have a student listed multiple times with in a fiscal year (this is because they pay the students off of different accounts throught the year so there are multiple entries in the database for each student). Anyway our department chairperson wants the average salary per faculty and the number of students used in that average. So since the salary table has many entries of a student the count ends up being more than the actual number of students.

I am about to give up on counting the number of students for this query. Maybe I should be moving this database into SQL server where the distinct works in aggregate functions.

Thanks for your input.

kgk

 

[MichaelRed]

in GENERAL, you can't mix aggregates and selects directly in a single query. An alternative might be to gnerate the student count recordset seperatly and JOIN it to your remaining query. This will still result in the multiple listing of the student count, but that can, perhaps, be dealt with sepeeratly in a later step of the process (such as a report) bu suppressing the display of the duplicate information.

I would suggest that you simplify the process through the layering (or nested) query approach. This process encourages the development of the overall process in sequential steps, with the various steps resolving a smaller issue, while proceeding toward the overall objective.

I would be ammenable to reviewing a sample db with the various tables populated with a reasonable subset of the information and suggesting at least some of the elements and approach.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[nvwildfire]

thanks for all the input everyone! I ended up breaking it up into a couple of querys and it work fine. I'm still wish that using DISTINCT in an Aggregate function would have worked it would have been a lot easier.

Again thanks for the input r937 and MichaelRed

kgk