Group by clause in VBA

[norhaya]

Hi

I am new in this group. I seek help from anyone here. I writing a cod
below to email a report in a text format. It works, but I wonder if
could do group and print the rs in header. say i have 2 records wit
same po #. I would like to show the po once then follow by the detail
I've did alot a research that i cannot select all for group by. I ca
use count..but I am not sure how to do it. anykind of assistance i
highly appreciated

Set dbs = CurrentDb
strSQL = "Select [2004 SHIP].* FROM [2004 ship] " & _
"WHERE ([2004 SHIP].[SUPPLIER]) ='" & strSupplier & "'"
strSQL = strSQL & "AND ((([2004 ship].[V COMM DATE]) Between Now(
And Now()+7))"
strSQL = strSQL & "order by ([2004 SHIP].[J PO NO]),([200
SHIP].[ITEM]);"

Set rs = dbs.OpenRecordset(strSQL)
b = rs.RecordCount
If Not rs.EOF Then
For b = 1 To rs.RecordCount
strPartsLine = strPartsLine & "Our Ref.: "
rs.Fields("J PO NO") & String(3, " ") & "PO Date: " & rs.Fields("J P
DATE") & _
String(3, " ") & "Your Ref.: "
IIf(IsNull(rs.Fields("vendor Ref")), "*To be advised* No formal orde
confirmation received yet.", rs.Fields("vendor ref")) & vbCrLf & _
String(2, " ") & rs.Fields("item") & String(5
Len(rs.Fields("item")), " ") & rs.Fields("PN") & String(20
Len(rs.Fields("PN")), " "

 

[TC]

Sorry, your question is really not very clear. Try providing a few
lines of example data to show us (1) what you are currently getting,
and (2) how you would rather get it instead.

Also, in any question involving tables, you need to state the primary
key field(s) of each table. We can't review your SQL, unless we know
the primary keys.

HTH,
TC (MVP Access)
http://tc2.atspace.com

 

[norhaya]

Hi TC

Thank you for the reply. Below is the another sample (complete quote)
This quote to is provide status report to my customer for their ope
PO.

---------------MY CODE--------------------------

Private Sub Email_Status_Report_AfterUpdate()
Dim dbs As Database
Dim rs As Recordset
Dim strSQL As String, strCPO As String, StrJPL1 As String
strSubject As String, StrJPO As String, strPN As String, strPriority A
String, strDesc As String
Dim strPartsLine, strTxt1, strTxt2, strTxt3, strTxt4, strTxt5
strTxt6, strTxt7, strShipDtls As String
Dim strCPOLine As String
Dim strFltNo, strAwb, strWho, strAcSn As String
Dim strFltDate As Date
Dim strShipQty1 As Integer
Dim a As Integer
Dim b As Integer
Dim strKeyAcctHolder, strRemarks, strAirlines, strCriteria
strStatus As String
Dim varTo, varCC, varBcc As Variant
Dim strPO, strPOHeader As String


varTo = " "
varCC = " "
varBcc = " "

strAirlines = Me.Combo181
strCriteria = "OPEN"
strAcSn = IIf(IsNull(Me.a_c_regn), "", Me.a_c_regn)
strWho = StrConv(Format(Me.BUYER), 3)
StrJPO = Me.Text106
strFltNo = Me.Text128
strAwb = Me.AWB1
strCPO = Me.C_PO_NO
strPN = Me.PN
strDesc = StrConv(Format(Me.DESCRIPTION), 3)
strShipQty1 = Me.SHIP_QTY1
StrJPL1 = IIf(IsNull(Me.JPL1), "", Me.JPL1)
strKeyAcctHolder = StrConv(Format(Me.Text88), 3)
strPriority = Me.Priority
strSubject = "Status Report for Open Orders as of " & Format(Now()
"medium date")

strTxt1 = "Dear " & strWho & ", " & vbCrLf & vbCrLf
strTxt2 = String(75, "-") & vbCrLf & "Customer Purchase Orde
Status Report As Of : " & Format(Now(), "Medium date") & vbCrLf
strTxt3 = String(5, " ") & "For :" & String(3, " ")
UCase([strAirlines]) & vbCrLf & _
String(75, "-") & vbCrLf & String(8, " ") & "Part No"
String(13, " ") & String(4, "-") & "Quantity" & String(5, "-") & vbCrL
& _
String(1, " ") & "Item" & String(4, " ") & "Description
& String(8, " ") & "Ord Shp Bal" & String(4, " ") & "Due Date"
String(4, " ") & "Status" & vbCrLf
strTxt4 = String(75, "-") & vbCrLf & vbCrLf
strTxt6 = String(75, "=") & vbCrLf & vbCrLf

Set dbs = CurrentDb

strSQL = "Select [2004 Ship].* FROM [2004 ship] " & _
"WHERE ([2004 Ship].[AIRLINES])='" & strAirlines & "'"
strSQL = strSQL & " And ([2004 Ship].[STATUS (OPEN/CLOSE)]) = '"
strCriteria & "'"
strSQL = strSQL & " ORDER BY [2004 Ship].[AIRLINES], [2004 Ship].[
PO NO], [2004 Ship].ITEM;"

Set rs = dbs.OpenRecordset(strSQL)
strPO = rs.RecordCount

b = rs.RecordCount
If Not rs.EOF Then
For b = 1 To rs.RecordCount
strPartsLine = strPartsLine & "CPO No: " & rs.Fields("C P
NO") & String(20 - Len(rs.Fields("C PO NO")), " ") & "Our Ref: "
rs.Fields("J PO NO") & " / " & Format(rs.Fields("J PO DATE"), "MEDIU
DATE") & vbCrLf
strPartsLine = strPartsLine & _
String(3, " ") & rs.Fields("item") & String(5
Len(rs.Fields("item")), " ") & rs.Fields("PN") & String(20
Len(rs.Fields("PN")), " ") & rs.Fields("PO Qty") & String(7
Len(rs.Fields("PO QTY")), " ") & rs.Fields("SHIP QTY1") & _
String(7 - Len(rs.Fields("SHIP QTY1")), " ") & _
(IIf(IsNull(rs.Fields("PO QTY")), 0, rs.Fields("PO QTY"))
IIf(IsNull(rs.Fields("SHIP QTY1")), 0, rs.Fields("SHIP QTY1"))
IIf(IsNull(rs.Fields("SHIP QTY2")), 0, rs.Fields("SHIP QTY2"))) & _
String(7 - Len((IIf(IsNull(rs.Fields("PO QTY")), 0
rs.Fields("PO QTY")) - IIf(IsNull(rs.Fields("SHIP QTY1")), 0
rs.Fields("SHIP QTY1")) - IIf(IsNull(rs.Fields("SHIP QTY2")), 0
rs.Fields("SHIP QTY2")))), " ") & _
IIf(IIf(IsNull(rs.Fields("J COMM DATE")), 0
Format(rs.Fields("J COMM DATE"), "Medium Date")) = 0, ""
Format(rs.Fields("J COMM DATE"), "Medium Date")) & String(12
Len(Format(rs.Fields("J COMM DATE"), "Medium Date")), " ") & _
StrConv(rs.Fields("STATUS (OPEN/CLOSE)"), 3) & vbCrLf
String(9, " ") & StrConv(rs.Fields("DESCRIPTION"), 3) & vbCrLf
strPartsLine = strPartsLine & IIf((rs.Fields("STATU
(OPEN/CLOSE)") = "OPEN" And rs.Fields("SHIP QTY1") >= 1 An
rs.Fields("BAL QTY") >= 1), "Remarks: " & rs.Fields("SHIP QTY1") & _
" ea," & IIf(IsNull(rs.Fields("SN")), " S/N N/A", " S/N "
rs.Fields("SN") & " ") & IIf(IsNull(rs.Fields("type of certs")), ""
"w/" & rs.Fields("type of certs") & " ") &
IIf(IsNull(rs.Fields("Material Cert No")), "", rs.Fields("material cert
no") & ", ") & _
"shipped on " & Format(rs.Fields("SHIP/FLT DATE1"), "Medium
Date") & ". " & vbCrLf & _
String(9, " ") & "Airway Bill # " &
UCase(rs.Fields("AWB1")) & ". " & vbCrLf & String(9, " ") & _
"Balance due on " & Format(rs.Fields("J COMM DATE"),
"medium date") & vbCrLf & _
vbCrLf, IIf(rs.Fields("STATUS (OPEN/CLOSE)") = "OPEN" And
IIf(IsNull(rs.Fields("J COMM DATE")), 0, Format(rs.Fields("J COMM
DATE"), "medium date")) = 0, "Remarks: " & "No estimated delivery date
yet. Will check and advise." & vbCrLf & vbCrLf, "" & vbCrLf))

rs.MoveNext
Next b

Else
MsgBox "The recordset contained no Records", vbOKOnly, "Email
Status Report"
Exit Sub
End If
Beep


Dim olApp As Outlook.Application
Dim olNS As Outlook.Namespace
Dim olfolder As Outlook.MAPIFolder
Dim olMailItem As Outlook.MailItem
Dim olAttmt As Outlook.Attachment

Dim strBodyText As String
Dim strSignature As String


Set olApp = CreateObject("Outlook.Application")

Set olNS = olApp.GetNamespace("MAPI")
Set olfolder = olNS.GetDefaultFolder(olFolderInbox)
Set olMailItem = olfolder.Items.Add("IPM.Note")


strSignature = "If you have any further queries, please do not
hesitate to contact the undersigned." & vbCrLf & vbCrLf & _
"Thank you and best regards " & Chr$(13) & _
strKeyAcctHolder & Chr$(13) & Chr$(13)
With olMailItem
.Subject = strSubject
.To = varTo
.CC = varCC
.BCC = varBcc
.Body = strTxt1 & strTxt2 & strTxt3 & strTxt4 & strPartsLine &
strTxt6 & strSignature & vbCrLf & vbCrLf
.Display
'.Send
End With

Set olMailItem = Nothing
Set olfolder = Nothing
Set olNS = Nothing
Set olApp = Nothing
Set olAttmt = Nothing
rs.Close
Set rs = Nothing

End Sub
----------------------------END OF MY CODE---------------------

Here is the report will look like. I need to group the CPO No. n
detailed the items.

--------------------------------------------------------------------------
Customer Purchase Order Status Report As Of : 27-Mar-06
For : SINGAS
--------------------------------------------------------------------------
Part No ----Quantity-----
Item Description Ord Shp Bal Due Date Status
--------------------------------------------------------------------------

CPO No: 10060881 Our Ref: JP20060044-20 / 11-Feb-06
1 AMU4032AAL20606 1 0 1 05-Oct-06 Open
Audio Management Unit

CPO No: 10060881 Our Ref: JP20060044-20 / 11-Feb-06
2 RMP8000AAL20607 2 0 2 05-Oct-06 Open
Radio Frequency Management Panel

CPO No: 10066306 Our Ref: JP20060080-20 / 13-Mar-06
1 106781 500 0 500 27-Apr-06 Open
Pin

CPO No: 4810063790-DAFDNB Our Ref: JP20060064-20 / 27-Feb-06
1 OL7152IBPEAS15 40 0 40 17-Apr-06 Open
Lamp. Cl7152ibpe-as15gpl (replaces 400045)

=========================================================

If you have any further queries, please do not hesitate to contact the
undersigned.

 

[TC]

Sorry, there's no way I can go through all that code. Providing lots of
lines of code, is not the way to ask your question. You really need to
provide what I asked for, which was:

(1) A few lines of example data to show us what you are currently
getting, and how you would rather get it instead. (That's /two/ sets of
data - not one.) And,

(2) The primary key field(s) of each table. "We can't review your SQL,
unless we know the primary keys."

If you don't know what a primary key is, you have some research to do!

Cheers,
TC (MVP Access)
http://tc2.atspace.com