I should have possibly posted this thread on the VB forum, But the truth is there is so much to learn.
I have descriptions (or narratives) that span several records eg:
ID, Name, Date/PersonalID, TextDescription
1, Jon Smith, “2009-01-01/100”, “Training in”
1, Jon Smith, “2009-01-01/100”, “Finance and “
1, Jon Smith, “2009-01-01/100”, “Administration”
2, Sam Brown, “2009-01-01/200”, “Excel Training”
3, Albert Cox, “2009-01-02/500”, “Winning in Sales”
4, Alfi Burns, “2009-01-02/900”, “Finance for Senior ”
4, Alfi Burns, “2009-01-02/900”, “Managers of ”
4, Alfi Burns, “2009-01-02/900”, “Diverse Operational ”
4, Alfi Burns, “2009-01-02/900”, “Teams ”
5, Bill Shakespeare, “2009-01-02/1000”, “Creative Writing”
I need to concatenate the contents of the same field across up to three or four records (sometimes less).
So far I have created field in a table from a date and an personal id. This is necessary because I wish to keep the events on the different dates separate and those on the same date grouped together. The field is of the form “YYYY-MM-DD/PersonalID” as can be seen in the example above. There are thousands of these…
I have assumed that in a query it is not possible to take a value from one record to the next? So I believe I need to use some VB.. in a Module?
I thought a way of doing this would be to add a field that I can increments with the number of occurrences (though I don’t know how to write this code) which would be 4 in the case of Alfi Burns and 3 for Jon Smith etc. and then I would use another query that concatenates (into the last record) the values for occurrences greater than 1. But that is the limit to my understanding what I need to do. As you can see any help would be gratefully received.
Cheers
Following on from my earlier post http://www.access-programmers.co.uk/forums/showthread.php?t=173947
I have made a text based field strEmpDateID (of the form “2009-06-17/A180980”) from which I can isolate the event and the person. I copied VB code to right concatenated values to another table from here. But it doesn’t seem to work is it because I am using a text field to compare the records or is the syntax wrong?
Ideally I'd like the values to be concatenated in a new field in the same table but I am happy to work with whatever I can learn getting this to work. Any suggestions where I need to look?
Thanks
Option Compare Database
Sub Write_To_Grpd()
Dim db As Database
Dim rsGet As Recordset
Dim rsWrite As Recordset
Dim varEmpID As Variant
Dim varNextEmpID As Variant
Dim strBuild As String
Dim intNumElements As Integer
Set db = CurrentDb()
Set rsGet = db.OpenRecordset("SELECT strEmpDateID, strTrngCrs FROM tblAllYrs ORDER BY strEmpDateID, strTrngCrs”)
Set rsWrite = db.OpenRecordset("tblAllYrsGrpd")
With rsGet
Do While Not .EOF
varEmpID = ![strEmpDateID]
.MoveNext
If Not .EOF Then
varNextEmpID = ![strEmpDateID]
Else
varNextEmpID = "EOF"
End If
.MovePrevious
strBuild = strBuild & ![strTrngCrs] & ","
intNumElements = intNumElements + 1
If Not (varOrgID = varNextOrgID) Then
'add record to table
strBuild = Left(strBuild, Len(strBuild) - 1)
With rsWrite
.AddNew
!strEmpDateIDG = rsGet![strEmpDateID]
!strTrngCrsG = strBuild
!lngProcCountG = intNumElements
.Update
End With
're-initialize variables
strBuild = ""
intNumElements = 0
End If
.MoveNext
Loop
End With
rsGet.Close
rsWrite.Close
Set rsGet = Nothing
Set rsWrite = Nothing
Set db = Nothing
End Sub
I have descriptions (or narratives) that span several records eg:
ID, Name, Date/PersonalID, TextDescription
1, Jon Smith, “2009-01-01/100”, “Training in”
1, Jon Smith, “2009-01-01/100”, “Finance and “
1, Jon Smith, “2009-01-01/100”, “Administration”
2, Sam Brown, “2009-01-01/200”, “Excel Training”
3, Albert Cox, “2009-01-02/500”, “Winning in Sales”
4, Alfi Burns, “2009-01-02/900”, “Finance for Senior ”
4, Alfi Burns, “2009-01-02/900”, “Managers of ”
4, Alfi Burns, “2009-01-02/900”, “Diverse Operational ”
4, Alfi Burns, “2009-01-02/900”, “Teams ”
5, Bill Shakespeare, “2009-01-02/1000”, “Creative Writing”
I need to concatenate the contents of the same field across up to three or four records (sometimes less).
So far I have created field in a table from a date and an personal id. This is necessary because I wish to keep the events on the different dates separate and those on the same date grouped together. The field is of the form “YYYY-MM-DD/PersonalID” as can be seen in the example above. There are thousands of these…
I have assumed that in a query it is not possible to take a value from one record to the next? So I believe I need to use some VB.. in a Module?
I thought a way of doing this would be to add a field that I can increments with the number of occurrences (though I don’t know how to write this code) which would be 4 in the case of Alfi Burns and 3 for Jon Smith etc. and then I would use another query that concatenates (into the last record) the values for occurrences greater than 1. But that is the limit to my understanding what I need to do. As you can see any help would be gratefully received.
Cheers
Following on from my earlier post http://www.access-programmers.co.uk/forums/showthread.php?t=173947
I have made a text based field strEmpDateID (of the form “2009-06-17/A180980”) from which I can isolate the event and the person. I copied VB code to right concatenated values to another table from here. But it doesn’t seem to work is it because I am using a text field to compare the records or is the syntax wrong?
Ideally I'd like the values to be concatenated in a new field in the same table but I am happy to work with whatever I can learn getting this to work. Any suggestions where I need to look?
Thanks
Option Compare Database
Sub Write_To_Grpd()
Dim db As Database
Dim rsGet As Recordset
Dim rsWrite As Recordset
Dim varEmpID As Variant
Dim varNextEmpID As Variant
Dim strBuild As String
Dim intNumElements As Integer
Set db = CurrentDb()
Set rsGet = db.OpenRecordset("SELECT strEmpDateID, strTrngCrs FROM tblAllYrs ORDER BY strEmpDateID, strTrngCrs”)
Set rsWrite = db.OpenRecordset("tblAllYrsGrpd")
With rsGet
Do While Not .EOF
varEmpID = ![strEmpDateID]
.MoveNext
If Not .EOF Then
varNextEmpID = ![strEmpDateID]
Else
varNextEmpID = "EOF"
End If
.MovePrevious
strBuild = strBuild & ![strTrngCrs] & ","
intNumElements = intNumElements + 1
If Not (varOrgID = varNextOrgID) Then
'add record to table
strBuild = Left(strBuild, Len(strBuild) - 1)
With rsWrite
.AddNew
!strEmpDateIDG = rsGet![strEmpDateID]
!strTrngCrsG = strBuild
!lngProcCountG = intNumElements
.Update
End With
're-initialize variables
strBuild = ""
intNumElements = 0
End If
.MoveNext
Loop
End With
rsGet.Close
rsWrite.Close
Set rsGet = Nothing
Set rsWrite = Nothing
Set db = Nothing
End Sub
Last edited by a moderator:
