Grouping

[mslenker]

So I'm trying to group the employees and have which families they are qualified under. The query the data is coming from each employee is in there multiple times for each family they are qualified for. I want to bring that data together and only have one row per employee. The SQL I have written is what I've done so far, but it does not do as i wish. If any one could help me fix it I would appreciate it. I'm some what new to SQL, so there may be a better way of doing it let me know what you guys think.


SELECT Employees.[SS #], Employees.Status, Employees.[Senior Date], Employees.[Last Name]+", "+Employees.[First Name]+" "+Employees.[Middle Name] AS Employee, IIf([Employee Qualifications].[Family Number]=1,QryEmployeeQualConvertGrade.Grade,Null) AS [Family 1], IIf([Employee Qualifications].[Family Number]=3,QryEmployeeQualConvertGrade.Grade,Null) AS [Family 3], IIf([Employee Qualifications].[Family Number]=4,QryEmployeeQualConvertGrade.Grade,Null) AS [Family 4], IIf([Employee Qualifications].[Family Number]=5,QryEmployeeQualConvertGrade.Grade,Null) AS [Family 5], IIf([Employee Qualifications].[Family Number]=7,QryEmployeeQualConvertGrade.Grade,Null) AS [Family 7], IIf([Employee Qualifications].[Family Number]=8,QryEmployeeQualConvertGrade.Grade,Null) AS [Family 8], IIf([Employee Qualifications].[Family Number]=9,QryEmployeeQualConvertGrade.Grade,Null) AS [Family 9], IIf([Employee Qualifications].[Family Number]=11,QryEmployeeQualConvertGrade.Grade,Null) AS [Family 11], IIf([Employee Qualifications].[Family Number]=13,QryEmployeeQualConvertGrade.Grade,Null) AS [Family 13], IIf([Employee Qualifications].[Family Number]=14,QryEmployeeQualConvertGrade.Grade,Null) AS [Family 14]
FROM (([Employee Qualifications] INNER JOIN Employees ON [Employee Qualifications].[SS #] = Employees.[SS #]) INNER JOIN Families ON [Employee Qualifications].[Family Number] = Families.[Family Number]) INNER JOIN QryEmployeeQualConvertGrade ON (Families.[Family Number] = QryEmployeeQualConvertGrade.[Family Number]) AND (Employees.[SS #] = QryEmployeeQualConvertGrade.[SS #])
GROUP BY Employees.[SS #], Employees.Status, Employees.[Senior Date], Employees.[Last Name]+", "+Employees.[First Name]+" "+Employees.[Middle Name], IIf([Employee Qualifications].[Family Number]=1,QryEmployeeQualConvertGrade.Grade,Null), IIf([Employee Qualifications].[Family Number]=3,QryEmployeeQualConvertGrade.Grade,Null), IIf([Employee Qualifications].[Family Number]=4,QryEmployeeQualConvertGrade.Grade,Null), IIf([Employee Qualifications].[Family Number]=5,QryEmployeeQualConvertGrade.Grade,Null), IIf([Employee Qualifications].[Family Number]=7,QryEmployeeQualConvertGrade.Grade,Null), IIf([Employee Qualifications].[Family Number]=8,QryEmployeeQualConvertGrade.Grade,Null), IIf([Employee Qualifications].[Family Number]=9,QryEmployeeQualConvertGrade.Grade,Null), IIf([Employee Qualifications].[Family Number]=11,QryEmployeeQualConvertGrade.Grade,Null), IIf([Employee Qualifications].[Family Number]=13,QryEmployeeQualConvertGrade.Grade,Null), IIf([Employee Qualifications].[Family Number]=14,QryEmployeeQualConvertGrade.Grade,Null);

 

[mslenker]

Thank you, I'm glad to be working with databases. The ssn is just used as a unique way to describe each employee and it does not show up on any report. No one but Hr personnel sees beneath the reports. But I will mention it to my employer to see if it should be changed. The families are for the job codes so there is a maintenence family, machinist family and so on there were.more but the union chose to eliminate them. Well the query is suppose to display the name of the employee and the max grade in each of the family they are qualified for a job in. I have everything displaying the correct information the only issue is the employees that are qualified in more than one family it shows more than one record for that employee. I want each employee to have one record. I'm not sure how to combine these records.

 

[Brianwarnock]

I believe that the poster wants one line per employee

Eg
Employee family1 family2 ....

This type of question has been asked before, I suggest a search using transpose as the keyword although many of the results will not be relevant, also I believe that there is a sample in the Microsoft tech base so a search using google might prove useful.

Brian

 

[mslenker]

Thank you everyone i figured it out. It was as simple as using distinctrow. I dont know why I didnt think of it before.

 

[mslenker]

Thank you I will look into changing it.