Do Until Loop ? II

[JJT]

I have an Access database with about 2,000 records. Each record is made up of 1 text field and 2 numeric fields.

position|male|female|
--------|----|------|
clerk | 1 | 0
clerk | 0 | 1
cook | 0 | 1


Each name field may be repeated several times, which is ok.

I need to be able to loop through the table, for each like name field,total the like numeric values and put the result into a new table with just one occurance of the name with the total of the numeric values.

Any suggestions ?

Thanks.

 

[Fornatian]

This is how I do it to get my database to concatenate text strings to form one string

Public Function Names(lngCurrentRecord As Long) As String
Dim db As DAO.Database
Dim rst As Recordset
Dim strReturn As String
Dim IntB As Integer
Set db = CurrentDb()
'Get the recordset
Set rst = db.OpenRecordset("Table")
rst.MoveLast
rst.MoveFirst
Do Until rst.EOF = True
If rst("Number") = lngCurrentRecord Then
If strReturn = "" Then
strReturn = rst("Name")
Else
strReturn = strReturn & "," & rst("Name")
End If
End If
rst.MoveNext
If rst.EOF = True Then Exit Do
Loop
If Len(strReturn) > 255 Then
strReturn = "Text is Too Large To Import"
Else
End If
Names = strReturn
End Function

I then put the function in as a column header in a query such as:

fNames([CurrentRecordPrimaryRef])

You could do the same with a little manipulation to cause the fNames function to return a calculated total if that's what you want.

HTH

Ian