Group By is not returning one record per ProjectID

[Guest]

FYI: I'm new to Access, but have some knowledge of SQL and VBA. I'm using
Access 2000.

I have a form which allows users to supply one or more criteria to subset a
recordset. The "Execute" button on the form kicks off VBA which builds and
runs a SQL statement using the selections made in the form.

The recordset that the SQL runs against can have multiple rows for each
ProjectID -- based on a combination of a couple of fields. For instance, the
following is possible:

ProjectID StartDate Employee ProjectCategory ... <other
fields>
1 01/01/05 Herman App Dvlpmt
1 01/01/05 Hortence Consulting
1 01/01/05 Herman Consulting
1 01/01/05 Hortence App Dvlpmt

If the user wants to select ProjectIDs where Employee="Herman" -- without
making a selection on ProjectCategory, I want only one of the two "Herman"
rows above to be returned -- and I don't really care which one.

The problem is that, using the code below on the example above, both
"Herman" rows are returned. I've tried numerous approaches -- this being the
most recent. The SQL statement is being built as I expect. But it's not
executing as I expect. What am I doing wrong?

Private Sub cmdExecuteQuery_Click()
Dim strSQL As String

strSQL = "SELECT ProjectID, first(ProjectName), " & _
"first(ProjectStart), first(ProjectEnd), first(ProjectActive),
" & _
"first(Sector1), first(Sector2), first(Sector3),
first(Sector4), " & _
first(ClientShortName), first(Employee),
first(ProjectCategory) " & _
"FROM qryProjectsForReport WHERE (ProjectActive = "
Select Case optStatus
Case 1
strSQL = strSQL & "True) "
Case 2
strSQL = strSQL & "False) "
Case 3
strSQL = strSQL & "True or ProjectActive = false) "
End Select
If Len(cmbCategory) Then strSQL = strSQL & " AND ProjectCategory =
" & cmbCategory
If Len(cmbMember) Then strSQL = strSQL & " AND Employee = " & cmbEmp
If Len(dtStartDate) Then strSQL = strSQL & " AND ProjectStart >= #"
& dtStartDate & "#"
If Len(dtEndDate) Then strSQL = strSQL & " AND ProjectEnd <= #" &
dtEndDate & "#"
If Len(cmbClient) Then strSQL = strSQL & " AND ClientID= " & cmbClient
If Len(cmbSector) Then
Select Case cmbSector
Case 1
strSQL = strSQL & " AND Sector1 = True"
Case 2
strSQL = strSQL & " AND Sector2 = True"
Case 3
strSQL = strSQL & " AND Sector3 = True"
Case 4
strSQL = strSQL & " AND Sector4 = True"
End Select
End If
strSQL = strSQL & " GROUP BY ProjectID ORDER BY ProjectID;"
MsgBox strSQL
OpenReport strSQL, chkDatasheet

End Sub

Thanks in advance!
CarpeDM

 

[Guest]

I'm definately not an Access guru, but I was having this problem myself and
just figured it out. In the help manuel, it says "All fields in the SELECT
field list must either be included in the GROUP BY clause or be included as
arguments to an SQL aggregate function."

For me, the answer was to simply copy my SELECT statement into my GROUP BY
statement, though my SELECT statement was a little simpler than yours.

Hope that helps, or at least sets you on the path to an answer.