Query Prob Re-Post

  • Thread starter Thread starter Ant
  • Start date Start date
A

Ant

I am trying to combine values in a column into a single
field in another column.

Below is an example of the data I am using in a plain
text, coma delimited format.

The colums are ID, Field1 and Field2

The first 2 columns are what I have and the third is what
I am hoping to get (although only one record per 'ID' is
required). I got this result using a Update SQL statement
in a VBA loop but this would eventually crash my PC and
that is why I want to know if there is a way to do it
pureley in a query or SQL.

Any help you can offer will be greatly appreciated.

Ant

ID,Field1,Field2
100000425,1,1
100000431,1,167
100000431,7,167
100000431,6,167
100000431,6,167
100000431,7,167
100000438,1,123811
100000438,2,123811
100000438,3,123811
100000438,3,123811
100000438,8,123811
100000438,11,123811
100000480,1,123
100000480,2,123
100000480,3,123
100000480,3,123
100000491,1,123
100000491,2,123
100000491,3,123
100000491,3,123
100000491,3,123
100000504,1,1237
100000504,2,1237
100000504,3,1237
100000504,3,1237
100000504,7,1237
100000515,1,13
100000515,3,13
100000519,1,1237
100000519,2,1237
100000519,3,1237
100000519,3,1237
100000519,7,1237
100000575,1,123
100000575,2,123
100000575,3,123
100000575,3,123
100000575,3,123
100000587,1,13910
100000587,3,13910
100000587,10,13910
100000587,9,13910
100000587,9,13910
100000587,9,13910
100000594,1,123
100000594,2,123
100000594,3,123
100000605,1,123
100000605,2,123
100000605,3,123
100000605,3,123
100000618,1,123
100000618,3,123
100000618,2,123
100000629,1,12371113
100000629,2,12371113
100000629,3,12371113
100000629,3,12371113
100000629,11,12371113
100000629,11,12371113
100000629,7,12371113
100000629,13,12371113
100000631,1,12
100000631,2,12
 
I did this in A2K using VBA. A reference to DAO 3.6 must be set. Change Table2 to your table name....
Watch for line wrap.....

==================
Option Compare Database
Option Explicit

Private Sub Command0_Click()
Dim db As DAO.Database
Dim strSQL As String
Dim rst As DAO.Recordset

Dim rstID As DAO.Recordset

'Dim strSQL_ALL As String

Dim strField2 As String
Dim varID As Long
Dim Field2Val As Long

Set db = CurrentDb

'get distinct ID numbers
'this is one line
Set rstID = db.OpenRecordset("SELECT DISTINCT Table2.id FROM Table2 ORDER BY Table2.id;")

' if no records - quit
If rstID.BOF And rstID.EOF Then
rstID.Close
Set rstID = Nothing
Exit Sub
End If

varID = 0
' loop thru the ID's
Do While Not rstID.EOF
varID = rstID("id")
'open second recordset to get field1 values where id = rstid.id
' this is one line
strSQL = "SELECT distinct Table2.id, Table2.field1 FROM Table2 WHERE Table2.id = " & varID & " ORDER BY Table2.id, Table2.field1;"

Set rst = db.OpenRecordset(strSQL)
If rst.BOF And rst.EOF Then
rst.Close
Set rst = Nothing
Exit Do
End If
rst.MoveFirst
strField2 = ""
' get field1 and make a new value
Do While Not rst.EOF
strField2 = strField2 & CStr(rst.Fields("Field1"))
rst.MoveNext
Loop
' convert to a long
Field2Val = CLng(strField2)
'update table2
CurrentDb.Execute "UPDATE Table2 SET Field2 = " & Field2Val & " WHERE ID = " & varID & ";"
rst.Close
rstID.MoveNext
Loop

rstID.Close

Set rstID = Nothing
Set rst = Nothing
Set db = Nothing
MsgBox "Done"
End Sub

'---------
'Sets Field2 to zeros (clears Field2)
Private Sub Command1_Click()
CurrentDb.Execute "UPDATE Table2 SET Field2 = 0"
MsgBox "Done"
End Sub

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

Steve
 
Back
Top