Select multiple records as one 1

[tbonehwd]

I am sure my title doesn't make alot of sense.

On my form I have a combo box called cmbADVACCT the user has the ability to enter a customer account number or chose a customer by name in the drop down listing. Many of our customers have multiple account numbers for various reasons so if for example you choose BN I have a Select Case statement that does an IN ('xxx','xxx' etc... for all ten BN account numbers. This is all good and works fine however if I GROUP BY account number or Customer Name it gives a separate line for each one "like it should" but I really want the ten different accounts and or names to be one. in the output. Here the part of the code that I think is important:

Select Case (Me.cmbADVACCT.Value)
Case "BN"
strADVACCT = "IN ('000000111111','000022222222')
Case Else
strADVACCT = "='" & Format(Me.cmbADVACCT, "000000000000") & "'"
End Select

strSQL = "SELECT PROORD_M.CTM_NBR, Last(CDSADR_M.CMP_NME) As Company_Name" & _
         ", Sum(IIf(PROOLN_M.ORD_NUM<'90000000',PROOLN_M.QTY_SHP,PROOLN_M.QTY_SHP*-1)) AS CY_NET_UNITS " & _
         ", Sum(IIf(PROOLN_M.ORD_NUM<'90000000',PROOLN_M.ITM_NET,PROOLN_M.ITM_NET*-1)) AS CY_NET_DOLLARS " & _
         "FROM (((PROOLN_M INNER JOIN PROORD_M ON PROOLN_M.ORD_NUM=PROORD_M.ORD_NUM) INNER JOIN CDSITM_M ON PROOLN_M.ITM_NUM=CDSITM_M.ITM_NUM) INNER JOIN INVSAC_T ON CDSITM_M.ITM_SAC = INVSAC_T.SAC_CDE)  " & _
         "INNER JOIN CDSADR_M ON PROOLN_M.SHP_CTM=CDSADR_M.CTM_NBR " & _
         "WHERE PROORD_M.ORD_STA IN ('F','B') AND PROORD_M.ORD_TYPE IN ('C','I','P','V') And CDSADR_M.ADR_CDE='STANDARD' And CDSADR_M.ADR_FLG='0' " & _
         "AND PROOLN_M.SHP_CTM " & strADVACCT & _
         "GROUP BY PROORD_M.CTM_NBR ;"

Is there a way to combine them into one record

 

[PHV]

Have you tried this ?
strSQL = "SELECT Last(PROORD_M.CTM_NBR) As Customer_Number, Last(CDSADR_M.CMP_NME) As Company_Name" & _
...
"AND PROOLN_M.SHP_CTM " & strADVACCT

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[tbonehwd]

You are sooooooo good!