Combining Rows - Grouping On Non-Numeric Column Values? 1

[cbouknight]

I'm trying to query a table to produce results that are the "combined" results of rows with "some" matching columns that are not numeric.

Example:

MyTable

Key CustNo GA MA FS
--- ------ --- --- ---
1 123456 Yes No No
2 123456 No Yes Yes
3 123456 No Yes No
4 456789 Yes No No
5 456789 No Yes No

I want to end up with this as the query result:

CustNo GA MA FS
------ --- --- ---
123456 Yes Yes Yes
456789 Yes Yes No

I've read the FAQs and some of the posts here, but the examples I've found will only help if the GA, MA and FS columns above are numeric.

Thanks for any help.

 

[MajP]

I am not very good at SQL so I cheated and built a function

Public Function overallValue(theCustNo As Long, fldName As String) As Boolean
  Dim strWhere As String
  strWhere = "custNo = " & theCustNo & " AND " & fldName & " = True"
  If DCount("custNo", "myTable", strWhere) > 0 Then
    overallValue = True
 End If
End Function

Then used the function in a query.

SELECT DISTINCT myTable.custNo, overallValue([custNo],"GA") AS GA, overallValue([custNo],"MA") AS MA, overallValue([custNo],"FS") AS FS
FROM myTable;

 

[dhookom]

It looks like you need a simple totals/group by query:

SELECT CustNo, Max(GA) as MaxGa, Max(MA) as MaxMA, Max(FS) as MaxFS
FROM tblNoNameGiven
GROUP BY CustNo;

If this doesn't work, then you might not be providing enough information such as table name, data types, etc.


Duane
Hook'D on Access
MS Access MVP

 

[cbouknight]

Thanks MajP and Duane. Both approaches provided me with the insight I needed.

 

[MajP]

definitely go with Duanes it will be orders of magnitude faster than mine. If the overall evaluation was something very complicated than you might want to consider building a custom function.