Summarise query into word document (1 Viewer)

[OTWarrior]

Hello

I am exporting dynamic queries from Access to a word document, by means of replacing the bookmark text

.Bookmarks("FieldCategory" & i).Range.Text
.Bookmarks("memo" & i).Range.Text = VerbatimQryTable

The query I want to export would display in the following format when opened normally in Access

[FieldCategory], [count]
category1 3
category2 2
category3 1


I am building another SQL query in VBA, using the data from the query and linking with the original table, in order to get the data as following:


[FieldCategory], [memo]
category1 "text"
category1 "text2"
category1 "text3"
category2 "Text0"
category2 "Text05"
category3 "Text stuff"


I want to output into word as the following:

[FieldCategory]
category1
"text"
"text2"
"text3"
category2
"text0"
"text05"
category3
"text stuff"




1) Without setting multiple bookmarks, would this be possible?
2) How can I extract the unique "categories" value from a query for each unique value?

 

[Cronk]

1) Open a recordset based on your sql, loop through the recordset appending the data to a string and then put the string at the bookmark

do while not rst.eof
   str= str & vbcrlf & rst!FieldCategory & " " & rst!MemoField  'do not name a field 'memo'
   rst.movenext
loop

2) Create a query which contains unique records

 

[OTWarrior]

1) I am already doing this, I am asking about how to output in the following format:

[FieldCategory]
category1
"text"
"text2"
"text3"
category2
"text0"
"text05"
category3
"text stuff"

Your method would display as the following:


[FieldCategory]
category1 "text"
category1 "text2"
category1 "text3"
category2 "text0"
category2 "text05"
category3 "text stuff"

2) I don't want a single query with only the "Categories". I need to have the 2nd column for the "text". See above.

 

[OTWarrior]

I have come up with a solution that should work, but I am having trouble putting the query values into an array

sub
'//////////////////////////////other parts of sub

ReDim commentArray(qDCount, qDCount)
Dim C As Integer
C = 0

Call populateArrayWithQuery(commentArray, qdfNew.Name)

oldComArray = ""

For C = 0 To UBound(populateArrayWithQuery(C, C))

newComArray = qdfNew.Fields(0).Value
If newComArray = oldComArray Then
NewThemeString = NewThemeString & vbCrLf & Chr(149) & qdfNew.Fields(1).Value
Else
NewThemeString = NewThemeString & vbCrLf & newComArray & vbCrLf & Chr(149) & qdfNew.Fields(1).Value
End If

oldComArray = qdfNew.Fields(0).Value
Next C


'//////////////////////////// other parts of the sub
end sub
-----------------------------------------------------------------
Function populateArrayWithQuery(Xarray As Variant, Yquery As String)
Dim lngNum As Long
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset(Yquery)
rst.MoveLast
rst.MoveFirst

lngNum = rst.RecordCount
ReDim Xarray(1 To lngNum)
lngNum = 1

Do While rst.EOF = False
Xarray(lngNum) = rst.Fields(0).Value
lngNum = lngNum + 1
rst.MoveNext
Loop

rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

End Function

 

[Cronk]

strCategory =""
do while not rst.eof
       if rst!FieldCategory=strCategory then
             str= str & vbcrlf & rst!MemoField  'do not name a field 'memo'        
       else
            strCategory=rst!FieldCategory
            str=  str & vbcrlf  & rst!FieldCategory & vbcrlf & rst!MemoField
       endif
       rst.movenext
loop

 

[OTWarrior]

strCategory =""
do while not rst.eof
       if rst!FieldCategory=strCategory then
             str= str & vbcrlf & rst!MemoField  'do not name a field 'memo'        
       else
            strCategory=rst!FieldCategory
            str=  str & vbcrlf  & rst!FieldCategory & vbcrlf & rst!MemoField
       endif
       rst.movenext
loop

I can see why your post count is so high, you just post anything without explanation.

For anyone else who finds this post and is wanting to do similar, the below is a step in the right direction. It will go through a query, and put the "theme" value first with the first sub text. For the next record, if the theme has not changed, it will update with the sub text only. Once the theme changes, it will repeat these steps.

                Set rLq = CurrentDb.OpenRecordset(sqlString)
                oldComArray = ""
                NewThemeString = ""
                
                Dim g As Integer
                If i <= qcount Then
                    rLq.MoveFirst
                    Do While Not rLq.EOF
                     newComArray = rLq.Fields(0).Value
                        If newComArray = oldComArray Then ' testing if you are on the 'theme' or the comment of the query
                                'This section will update with the next sub text                            
                                g = g + 1
                            NewThemeString = NewThemeString & vbCrLf & Chr(9) & g & ". " & rLq.Fields(1).Value
                        Else
                            'this section will update with the theme, ignore the i& count & g, that is a work in progress to count the number of sub texts relating to the theme
                            g = 1
                            NewThemeString = NewThemeString & "({" & i & "count" & g & "} Comments)" & vbCrLf & "<b>" & newComArray & "</b>" & vbCrLf & Chr(9) & g & ". " & rLq.Fields(1).Value
                        End If
                        
                        
                        oldComArray = rLq.Fields(0).Value ' set oldvalue, to be used to test if you are on a theme or sub text
                    
                        Position = Position + 1
                        
                        rLq.MoveNext
        
                    Loop
                End If