Hi
trying to begin to learn database using vb6, ado/adox, mdb format, sql
(not using access...just mdb format via ado)
i need to group the values of multiple fields
- get their possible variations(combination of fields),
- then act on each group in some way ...eg ProcessRs (oRs as RecordSet)...
the following query will get me the distinct groups
strSQL = "SELECT DISTINCT Field0, Field1, Field2, Field3 From " & TABLE_NAME
& _
" ORDER BY Field0, Field1, Field2, Field3"
Set oRs1 = moConn.Execute(strSQL)
Do While Not oRs1.EOF
Debug.Print "Group " & i
j = 0
For Each oFld In oRs1t.Fields
Debug.Print "Field" & j & vbTab & "-Value: " & oFld.Value
j = j + 1
Next oFld
oRs1.MoveNext
i = i + 1
Loop
....results in ....
Group 0
Field0-Value:A;Field1-Value:1;Field2-Value:0;Field3-Value:1NA;
Group 1
Field0-Value:A;Field1-Value:1;Field2-Value:1;Field3-Value:1NA;
Group 2
Field0-Value:A;Field1-Value:1;Field2-Value:1;Field3-Value:2L;
Group 3
Field0-Value:A;Field1-Value:1;Field2-Value:1;Field3-Value:3R;
Group 4
Field0-Value:A;Field1-Value:1;Field2-Value:1;Field3-Value:4B;
Group 5
Field0-Value:A;Field1-Value:1;Field2-Value:2;Field3-Value:1NA;
Group 6
Field0-Value:A;Field1-Value:1;Field2-Value:3;Field3-Value:1NA;
Group 7
Field0-Value:A;Field1-Value:1;Field2-Value:4;Field3-Value:1NA;
so I have 7 groups to process
do i need to loop back through the database 7 times to get all records for
each group?
Process Group0
Select * From Table_Name Where Field0 = A and Field1 = 1 and Field2 = 0 and
Field3 = 1Na
Process Group1
Select * From Table_Name Where Field0 = A and Field1 = 1 and Field2 = 1 and
Field3 = 1Na
Process Group2
Select * From Table_Name Where Field0 = A and Field1 = 1 and Field2 = 1 and
Field3 = 2L
Process Group3
Select * From Table_Name Where Field0 = A and Field1 = 1 and Field2 = 1 and
Field3 = 3R
Process Group4
Select * From Table_Name Where Field0 = A and Field1 = 1 and Field2 = 1 and
Field3 = 4B
....etc....
or is there some way to combine the grouping, and subsequent processing of
each distinct recordset group, all in one sql statement?
is there a one-step way to divide the recordset in that way?...some how
using aliases and joins when there's only one table???
something like this seems to work for the multiple loop way
Dim oRs2 As Recordset
Dim oFld2 As Field
'get the distinct group list
strSQL = "SELECT distinct fldTypeName, fldCondName, fldCondVar, fldCondLbr
From " & TABLE_NAME & _
" ORDER BY fldTypeName, fldCondName, fldcondvar, fldCondLbr"
Set oRs1 = moConn.Execute(strSQL)
If Not oRs1 Is Nothing Then
Do While Not oRs1.EOF
j = 0
strSQL = "Select * From " & TABLE_NAME & " WHERE "
For Each oFld In oRs1.Fields
strSQL = strSQL & oFld.Name & " = '" & oFld.Value & "' AND "
j = j + 1
Next oFld
'REMOVE LAST AND
strSQL = Left$(strSQL, Len(strSQL) - 4)
Set oRs2 = moConn.Execute(strSQL)
Do While Not oRs2.EOF
ProcessInnerGroup oRs2
oRs2.MoveNext
Loop
oRs1.MoveNext
i = i + 1
Loop
Thanks for any pointers.
Mark
