SELECT DISTINCT COUNT

[Robert Painter]

Hi
Not overgood with SQL but learning every day.
Have a problem sorting this out though.

I have a table tblEmployeeSkills with: EmployeeSkillsid, Employeeidfk,
Skillidfk.

I have entered 7 employees for testing with various skills and amy trying to
get out of table how many employees i have with:

Private Sub cmdSearch_Click()
On Error GoTo errhandler:
Dim rs As DAO.Recordset
Dim buttonpress As Integer
'/ Create your recordset..
mySql = "SELECT DISTINCT COUNT(EmployeeIDFK) AS CountOfEmployeeIDFK"
mySql = mySql & " FROM tblEmployeeSkills;"

Set rs = CurrentDb.OpenRecordset(mySql)
'/ Populate your message boxl..
buttonpress = MsgBox("You have " & rs!CountOfEmployeeIDFK & "
candidates." & vbCrLf & "Continue search", 4 + 0, "Candidate Search")


'/ Close shop..
rs.Close
Set rs = Nothing
If buttonpress = 6 Then
DoCmd.OpenForm "frmcbosearch"
Else
End If

Exit Sub
errhandler: MsgBox "error halted"
End Sub

but all i get is a result of 28.

Am i doing some thing wrong ??

 

[Roger Carlson]

Forget the DISTINCT. Add a GROUP BY clause:
SELECT COUNT(EmployeeIDFK) AS CountOfEmployeeIDFK FROM tblEmployeeSkills
GROUP BY EmployeeIDFK;

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Robert Painter]

Many thanks Roger Worked a treat.

Robert

Forget the DISTINCT. Add a GROUP BY clause:
SELECT COUNT(EmployeeIDFK) AS CountOfEmployeeIDFK FROM tblEmployeeSkills
GROUP BY EmployeeIDFK;

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L