Looping in query question

[Telsa]

I have a query where all the data in the fields will contain the same data except two. What I want to do is create a recordset that contains all the fields but when it comes to the two fields that have different data, I want that data to be together in one field. For instance,

part cd comp lang are the fields.

sj234 c1234 c2345 en
sj234 c1234 c2345 sp
sj234 c1234 c5678 en
sj234 c1234 c5678 sp

and I want to end up with

sj234 c1234 c2345 en,sp
sj234 c1234 c5678 en,sp

Not really sure how to go about this. Any ideas?

Mary

 

[kathryn]

Sorry, but I have to ask why you need this. If you need to show the en and sp together on a form or report, you can use a list box for a form or two detail records on a report.

By doing what you want to do, you are de-normalizing data, which is generally not a good idea.

If you really need to do this you would need to make sure that your query sorts on all the fields in order.

Then your code would have to step through each record and store the values of the first three fields. As long as those three stayed the same, you would build a concatenated string of the fourth field, lan.

You could put the results into a temporary table, call it tblTemp.

something like this

dim rstData as recordset
dim rstTemp as recordset
dim strPart as string
dim strCD as string
dim strComp as string
dim strLang as string

set rstData=currentdb.openrecordset("yourQueryName&quot

rstdata.movefirst
strPart = rstdata!part
strCD=rstdata!cd
strComp=rstdat!comp

do until rstdata.eof

'compare with previous record's data
'this is meaningless for first record

if (strPart = rstdata!part and strCD=rstdata!cd and strComp=rstdat!comp) then
strLang = strLang & ", " & rstData!lang
else
'if it does not equal then use the last value of
'the strings to write record to temp file
rstTemp!addnew
rsttemp!part = strPart
rsttemp!cd=strCD
rsttemp!comp = strComp
rsttemp!lang=strlang
rsttemp.update

'then reset the values of all the string to the new values
strPart = rstdata!part
strCD=rstdata!cd
strComp=rstdata!comp
strLang=rstdata!lang
end if
rstData.movenext
loop

NOTE: this is not exact code. It does not include any error checking and it it off the top of my head. I hope it gets you started. Kathryn

 

[Telsa]

I need this so I can output it to a Word document template.

Thanks for giving me an idea how to go about it. I'll play with it.

Mary

 

[MichaelRed]

Public Function basConCatLang()

    Dim rstData As Recordset
    Dim rstTemp As Recordset
    Dim qryDel As QueryDef

    Dim strCompare As String
    Dim strLang As String
    Dim blnFrstFlg As Boolean
    Dim blnLstFlg As Boolean

    Set rstData = CurrentDb.OpenRecordset("qryConcatLang")
    Set rstTemp = CurrentDb.OpenRecordset("tblTemp", dbOpenDynaset)
    Set qryDel = CurrentDb.QueryDefs("qryDelTemp")

    'This clears any OLD stuff from the temp recordset
    qryDel.Execute

    'This is the SQL for the query.  It lumps all of the
    'first fields into one thinnngggyyyyyy for the comparre.
    'SELECT tblConCatLang.part, tblConCatLang.cd, tblConCatLang.comp, tblConCatLang.lang,
    '[Part] & [Cd] & [Comp] AS Compare
    'FROM tblConCatLang;

    rstData.MoveFirst
    strCompare = rstData!Compare
    strLang = rstData!Lang

    Do Until rstData.EOF

        'Make SURE we skip the first one to init strCompare
        If (Not blnFrstFlg) Then
            blnFrstFlg = True
            GoTo SkipRec
        End If

        'compare with previous record's data
        'this is meaningless for first record

        If (strCompare = rstData!Compare) Then
            If (Len(strLang) > 0) Then
                strLang = strLang & ", "
            End If
            strLang = strLang & rstData!Lang
        Else
            'if it does not equal then use the last value of
            'the strings to write record to temp file
            rstData.MovePrevious
            rstTemp.AddNew
                rstTemp!Part = rstData!Part
                rstTemp!CD = rstData!CD
                rstTemp!Comp = rstData!Comp
                rstTemp!Lang = strLang
            rstTemp.Update
            rstData.MoveNext

            'then reset the value of the string to the new value
            strCompare = rstData!Compare
            strLang = rstData!Lang
        End If
SkipRec:
        rstData.MoveNext
        If (rstData.EOF) Then    'Last Record.  Special Handling
            blnLstFlg = True
        End If
    Loop

    'This is repeated for simple laziness.
    'It should be made into a seperate procedure and called as necessary

    If (blnLstFlg) Then
        'if it does not equal then use the last value of
        'the strings to write record to temp file
        rstData.MovePrevious
        rstTemp.AddNew
            rstTemp!Part = rstData!Part
            rstTemp!CD = rstData!CD
            rstTemp!Comp = rstData!Comp
            rstTemp!Lang = strLang
        rstTemp.Update
        rstData.MoveNext
    End If

End Function
[code]
 
MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over