Can inner join replace multiple loop selects?

[MP]

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(comb ination 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
ProcessInnerGro up oRs2
oRs2.MoveNext
Loop

oRs1.MoveNext
i = i + 1
Loop

Thanks for any pointers.
Mark

[Larry Linson]

Re: Can inner join replace multiple loop selects?

Do you have Access itself? It's a really good investment if you are using
the Jet database (.mdb format).

If you simply want all the Records, which is what you'll get if you use the
combination of the Distinct Query and the original Table with a Join, or if
you use a loop for selecting, just use

strSQL = "SELECT * From " & TABLE_NAME & _
" ORDER BY Field0, Field1, Field2, Field3"

In your code traversing the records, you can detect when the key (or
group -- the combination of Fields 0,1,2,&3) changes for whatever special
processing you want to do for that.

However, if you anticipate creating business database applications with the
combination of tools you describe, give some thought to using Access itself.
It will handle the vast majority of business database applications,
individual desktop and multiuser, done with Jet and/or with server
databases, and those experienced with both Access and VB6 find at least a
3:1 advantage in time and effort to create the same application with Access
vs. creating it with VB6.

I'll also comment that, if you are using the Jet database (.mdb file
format), DAO is the "native language" of Jet. Thus, despite the Microsoft
hype on ADO (which in the VB world, did not survive past VB6, but did in the
Office world), getting/installing the MDAC 2.5 and using DAO rather than ADO
may provide some speed and development benefits.

A presentation on "Access and VB-Which One Should You Use for Database
Application Development?" that I did for my user group back in 2001 can be
found in the Downloads section of http://appdevissues.tripod.com. You might
find it of some use.

Larry Linson
Microsoft Access MVP






"MP" <nospam@Thanks. comwrote in message
news:L6ZYg.1948 8$F7.1287@torna do.rdc-kc.rr.com...

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(comb ination 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
ProcessInnerGro up oRs2
oRs2.MoveNext
Loop
>
oRs1.MoveNext
i = i + 1
Loop
>
Thanks for any pointers.
Mark
>
>
>
>
>

[MP]

Re: Can inner join replace multiple loop selects?

Thanks for the response Larry,

"Larry Linson" <bouncer@localh ost.notwrote in message
news:qd8Zg.4429 $qv6.2078@trndd c06...

Do you have Access itself? It's a really good investment if you are using
the Jet database (.mdb format).

not at work
I'm just developing small in-house productivity tools...not general mass
marketing programs
The small company I'm at will not purchase multiple seats of access to allow
users to run my tools.
So I'm happy to use ado/adox to achieve what i'm trying to do.

I'll also comment that, if you are using the Jet database (.mdb file
format), DAO is the "native language" of Jet. Thus, despite the Microsoft
hype on ADO (which in the VB world, did not survive past VB6, but did in

the

Office world), getting/installing the MDAC 2.5 and using DAO rather than

ADO

may provide some speed and development benefits.
>
A presentation on "Access and VB-Which One Should You Use for Database
Application Development?" that I did for my user group back in 2001 can be
found in the Downloads section of http://appdevissues.tripod.com. You

might

find it of some use.

I'll check out the link as I try to read everything i can find to try to get
my mind around this database world.
Thanks

so to recap, looping through my distinct groups is the only way to group
common records together for additional processing?
thanks

mark

[David W. Fenton]

Re: Can inner join replace multiple loop selects?

"MP" <nospam@Thanks. comwrote in
news:SYbZg.2259 5$IO2.21141@tor nado.rdc-kc.rr.com:

I'm just developing small in-house productivity tools...not
general mass marketing programs
The small company I'm at will not purchase multiple seats of
access to allow users to run my tools.

A copy of the developers tools for the version of Access you choose
would allow you to distribute Access runtime apps at no cost.

So I'm happy to use ado/adox to achieve what i'm trying to do.

Even without Access, DAO is the best data interface for Jet
databases (i.e., MDBs).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[David W. Fenton]

Re: Can inner join replace multiple loop selects?

"MP" <nospam@Thanks. comwrote in
news:L6ZYg.1948 8$F7.1287@torna do.rdc-kc.rr.com:

or is there some way to combine the grouping, and subsequent
processing of each distinct recordset group, all in one sql
statement?

Without knowing what it is you want to do with each group, it's
impossible to say.

The way I'd do this is execute SQL on each unique row of your
original SELECT DISTINCT recordset. I would not use a recordset for
the processing unless it was not doable in a set operation.

I would also never use ADO, though the differences between ADO and
DAO in the code you're doing would be very small.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[MP]

Re: Can inner join replace multiple loop selects?

Thanks for your responses...see inline replies

"David W. Fenton" <XXXusenet@dfen ton.com.invalid wrote in message
news:Xns985FB4D 5121C4f99a49ed1 d0c49c5bbb2@127 .0.0.1...

"MP" <nospam@Thanks. comwrote in
news:L6ZYg.1948 8$F7.1287@torna do.rdc-kc.rr.com:
>

or is there some way to combine the grouping, and subsequent
processing of each distinct recordset group, all in one sql
statement?

>
Without knowing what it is you want to do with each group, it's
impossible to say.

I need to get all records belonging to 'group1', read various fields, make
calculations based on their values, enter data in other fields based on
those calcs then go to 'group2' and repeat .... to 'groupN'

>
The way I'd do this is execute SQL on each unique row of your
original SELECT DISTINCT recordset. I would not use a recordset for
the processing unless it was not doable in a set operation.

maybe i'm misunderstandin g what i'm getting back from the Distinct select.
I thought i was just getting "ONE" sample row with the values for
Fields0,1,2 for each possible combination of values
in the actual database each 'group' may have 1 or 10 or 100 matching rows
with that 'groups' criteria but the distinct just gives me one arbitrary
row, right?

example:databas e has 10 fields, 100 records
lets say Field0 has 2 possible values: "a", "b" (50 records = a, 50 = b)
Field1 may be 1,2
Field2 may be 4,5
assuming at least one record will contain every possible value for each
field,
Select Distinct Field0, Field1, Field2 is going to give me 8 records right?
"a",1,4
"b",1,4
"a",2,4
"b",2,4
"a",1,5
"b",1,5
"a",2,5
"b",2,5
so now I know I have 8 possible combinations to deal with,
I need to process all records Where Field0 = "a" And Field1 = 1 And Field2
= "4" (group1)-lets say that gives me 20 records...
then do the same for the other 7 groups.

so I have 20 records "in group 1" but I don't get all 20 in the original
select distinct call do I?
I thought i only got one of them(to make it distinct)
Does that make sense or am I completely confusing the situation?
:-)

so what i'm doing now is more or less:
strSQL = "SELECT distinct Field0 , Field1, Field2 From " & TABLE_NAME & _
" ORDER BY Field0 , Field1 , Field2"
Set oRsDistinct = moConn.Execute( strSQL)
Do While Not oRsDistinct.EOF
set oRs2 = moConn.Execute( "Select * From TableName Where Field0 =
oRsDistinct .Field0.Value And Field1 = oRsDistinct .Field1.Value And Field2
= oRsDistinct .Field2.Value")
'test other field values for conditions 1,2 and 3 (in that order)
and respond accordingly
Process oRs2 Condition1
Process oRs2 Condition2
Process oRs2 Condition3
Loop
'eg Condition1 = WHERE Field3.Value = "NA"
'eg Condition2 = WHERE Field3.Value = "L" OR Field3.Value = "R"
'eg Condition3 = WHERE Field3.Value = "B"
'(have to process these in this order...have to process "NA" before
"L or R" and "B" after "L or R") so i can't just do them all in one
recordset

If there's an easier way i'd like to learn it

I would also never use ADO, though the differences between ADO and
DAO in the code you're doing would be very small.

I appreciate your wisdom on the best tool for the job but at this point I'm
more interested in learning how to get this to work, then later I can look
at alternatives if performance requires a dao solution.

>
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Lyle Fairfield]

Re: Can inner join replace multiple loop selects?

I try not to open recordsets but, in this case, opening one recordset
of all the records you want to modify may be efficient.
One would order the recordset by the four fields you have identified.
And then one would scan through the recordset something like this air
code.

with r
while not .eof
var1=fld1.value
etc
..
..
while fld1.value=var1 and fld2.value=var2 .value and etc
do whatever
.movenext
wend
wend

On the other hand if the modifications you are making are dependent
only on the values of the four fields you have identified then, unless
you are making calculations that are resource and time demanding, it's
likely to be efficient not to store their results but just to make them
when they are needed, at form or report time.

Using ADO is likely to make your code portable to many applications. It
will also allow you to disconnect your recordset during calculations,
to reconnect later and to effect all the updates at once, or to discard
them should things not have gone well. You won't notice any difference
in the speed with ADO and DAO, but if you are measuring, DAO is likely
to be slightly faster.

In Office 12, JET seems to have been orphaned; I don't know what impact
this will have on applications such as you describe, but I believe that
under the covers, ACE and Access will handle it all OK. I notice that
the installation of Office 12 silently and without any notice creates
an ADO-ACE provider. I suppose MS might have told us ... if they knew.

[MP]

Re: Can inner join replace multiple loop selects?

Thanks for the response, Lyle

"Lyle Fairfield" <lylefairfield@ aim.comwrote in message
news:1161134134 .368614.40920@i 3g2000cwc.googl egroups.com...

I try not to open recordsets

may I ask why?

but, in this case, opening one recordset
of all the records you want to modify may be efficient.
One would order the recordset by the four fields you have identified.
And then one would scan through the recordset something like this air
code.

<snip>

I originally thought I could just sort a recordset and step through as you
indicated
I must not be doing something right or couldn't figure out the exact
algorithm because it seems I need to do a multiple step sort and process
regime.
I was hoping i could find some tricky sql select where group by order by
selfjoin inner join outer combine sortmeoutandgiv emetheanswer kind of sql
statement...but i don't know what it is....:-(

I have objects with 6 characteristics pertinent to the current decision
process
they can be almost any combination of the following....i need to sort and
order them so as to "name" each of them

Field1 = TypeName (a - z, aa - zz, aaa - zzz ...)indetermina te (String)
Field2 = ConditionName (1 - 8) fixed range (String)
Field3 = ConditionVariat ion (0 - n) indeterminate (String)
Field4 = "1none" "2left" "3right" "4both" for some condition (the 1 2 3 4
are added for sorting order only) (String)
Field5 = "1none" "2left" "3right" "4both" for some other condition
(String)
Field6 = (length value) indeterminate (Double)

Field7 will hold the calculated "name" for the object
other various fields contain addition information about the objects but the
6 above are what determine their "name"

my problem is very close to the following pseudocode(some what
oversimplified)
While not AllRecords.Eof
For Each DistinctGroupof Values
sPrefix = GroupOfValues'r eset prefix
i = 1 'reset counter
While field1,2 and 3 = group1Values
'Depending on value of Field2 (value = 1 through 8) -some of the
following may change
Select Case Field2
Case "1" 'simplest example

'Check field4 for "1" "2" "3" or "4"
'for each possible value of field4 check field5 for "1" "2" "3" or
"4" also

'for Field4.value "1" And Field5.Value = "1" (simple case)
'For each Field6 distinct value
'as long as field6 doesn't change then Then Field7 =
sPrefix & Cstr(i)
'when field6 changes, for each different field6.value,
increment counter (i = i +1)
'Next Field6

'if Field4 And or Field5 = "2" or "3" (less simple case)
'loop once through all Field6s in this group....
'if Any Field4's or any Field5's = "2" then' i have
lefts so equal length rights become opposite hand(x)
'loop through all field6's again, knowing ahead of
time if there are "2"'s to deal with
For this field6
If Field4 = "2"
If Field5 = "2"
'Field7= sPrefix & Cstr(i)
i = i +1
Else If Field5 = "3"
If there was no "2" for this
length, then increment counter
Else if there was a "2" for
this length, don't increment counter, append an x suffix
End if
End if
Else if field4 = "3"
If Field5 = "2"
'do something
Else if Field5 = "3"
'do something
End if'field5
End if'field4
Next field6

'iF Field4 and or field5 = "B" then 'simple
For each field6
Field7 = calculated value
increment counter
Next Field6
End if'"B"

Next Field5

Next Field4

Case "2"
'similar to above with variations
Case "3"
'similar other variations
etc up to case "8"
End select
Wend' 'group123
Next group 123
Loop 'till eof

On the other hand if the modifications you are making are dependent
only on the values of the four fields you have identified then, unless
you are making calculations that are resource and time demanding, it's
likely to be efficient not to store their results but just to make them
when they are needed, at form or report time.

actually this *is* when they are needed-
this is "Report time".
That's the only reason I'm using this database is to make this calculation
I've tried sorting via scripting.dicti onaries/collections/arrays etc.
just seemed a database would be easier...except i don't know how to use
them yet, but i'm trying to learn.<g>
once this calculation (just naming items actually) is done I can throw the
database away since the calculated values will then be entered in another
program in another form for permanent storage (though as this develops i
probably wont' throw it away but keep it for storing other information also)
I understand you aren't "supposed" to store "calculated values" but rather
calc them on the fly at report time
In this instance the "calculated " "name" is a critical piece of information
that has to be attached to these objects and is non-changing once calculated
and must remain intact.

I don't necessarily have to store the results in "Field7" for example...it's
just a convienient place to store them with their associated entity (a
handle in field8 for example) until they are all identified, at which time
all the entities(handle s) and their "calculated " "names" will be placed in
the other program which is the actual end result i need.

For this project the database is just a storing/sorting tool...
that is if i can ever get it working :-)

Using ADO is likely to make your code portable to many applications.

i agree

You won't notice any difference

in the speed with ADO and DAO, but if you are measuring, DAO is likely
to be slightly faster.

If i can't notice, in this case, I don't care :-)....
Actually I'm so buried in just trying to get the sorting algorithm figured
out I could care less if it takes an hour to run.
Hell I'd be happy to turn it on at night and come back in the morning to
find the answer :-)
though in reality it will only be a few seconds or minutes depending on
number of objects ...(in my tests so far)
I've been struggling with this for so long i'm so far from caring about
milliseconds that it's not even funny...though i know that's not a good
attitude to have...sorry... .<sheepish grin>


Thanks again for your insights.
that whole complicated mess above is just to decide when to increment (i = i
+ 1) and when to add (& "X") suffix
is that a joke or what? <g>
I've been working on this problem for a long long time (without pay even)
:-(
...someday I'll get it figured out...(rare moment of optimism)

maybe i'll even get a pat on the head if i ever get it working <g>
nah...probably not :-(

:-)
Thanks for your pointers
Mark

[Lyle Fairfield]

Re: Can inner join replace multiple loop selects?

MP wrote:

Thanks for the response, Lyle

This updates a new (created separately) field that identifies each
order in the Northwind Orders Table in a manner similar (I'm guessing)
to your description of what you want to do. Note, that it does not
bother with identifying unique values first. Perhaps you can find some
pointers in it. I do not know if this is how I might do the job in my
own work. I doubt that this code is exemplary and I expect a few lines
may be redundant. Regardless, it works (here in Canada).

Sub blah()
Dim r As ADODB.Recordset
Dim fCustomerID As ADODB.Field
Dim fIDByCustomer As ADODB.Field
Dim CustomerID$
Dim Iterator&
Set r = New ADODB.Recordset
With r
.ActiveConnecti on = CurrentProject. Connection
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockBatchOpti mistic
.Open "SELECT " _
& "o.OrderID, o.CustomerID, o.IDByCustomer " _
& "FROM Orders o " _
& "ORDER BY o.CustomerID, o.OrderDate"
.ActiveConnecti on = Nothing
Set fCustomerID = .Fields("Custom erID")
Set fIDByCustomer = .Fields("IDByCu stomer")
While Not .EOF
If fCustomerID.Val ue <CustomerID Then
CustomerID = fCustomerID.Val ue
Iterator = 0
End If
Do While CustomerID = fCustomerID.Val ue
Iterator = Iterator + 1
fIDByCustomer.V alue = CustomerID & " - " & Iterator
.MoveNext
If .EOF Then Exit Do
Loop
Wend
If MsgBox("Do you really wanna?", vbYesNoCancel, "Another Check") =
vbYes Then
.ActiveConnecti on = CurrentProject. Connection
.UpdateBatch
MsgBox "Update Completed", vbInformation
Else
MsgBox "Update Cancelled", vbInformation
End If
End With
End Sub

[David W. Fenton]

Re: Can inner join replace multiple loop selects?

"MP" <nospam@Thanks. comwrote in
news:XAeZg.2363 1$IO2.11721@tor nado.rdc-kc.rr.com:

Thanks for your responses...see inline replies
>
"David W. Fenton" <XXXusenet@dfen ton.com.invalid wrote in message
news:Xns985FB4D 5121C4f99a49ed1 d0c49c5bbb2@127 .0.0.1...

>"MP" <nospam@Thanks. comwrote in
>news:L6ZYg.194 88$F7.1287@torn ado.rdc-kc.rr.com:
>>

or is there some way to combine the grouping, and subsequent
processing of each distinct recordset group, all in one sql
statement?

>>
>Without knowing what it is you want to do with each group, it's
>impossible to say.

>
I need to get all records belonging to 'group1', read various
fields, make calculations based on their values, enter data in
other fields based on those calcs then go to 'group2' and repeat
.... to 'groupN'

Can it be done in a single UPDATE statement? That is, are the
changes you are making to the fields based on data in other fields
in the same record, or do you need to make changes based on other
records in the same table? If the former, then it's completely
doable in a SQL statement. It the latter, it's possible it's doable
as a SQL statement, but also possible that you'll need to walk the
recordset to do it.

But many people mistakenly use recordsets when they could do the
same job with SQL.

>The way I'd do this is execute SQL on each unique row of your
>original SELECT DISTINCT recordset. I would not use a recordset
>for the processing unless it was not doable in a set operation.

>
maybe i'm misunderstandin g what i'm getting back from the Distinct
select. I thought i was just getting "ONE" sample row with the
values for Fields0,1,2 for each possible combination of values
in the actual database each 'group' may have 1 or 10 or 100
matching rows with that 'groups' criteria but the distinct just
gives me one arbitrary row, right?

Yes, and then you'd use that as the definition of each unique group.
You'd then operate on that group by restricting the SQL UPDATE to
the records matching the values in that row.

example:databas e has 10 fields, 100 records
lets say Field0 has 2 possible values: "a", "b" (50 records = a,
50 = b) Field1 may be 1,2
Field2 may be 4,5
assuming at least one record will contain every possible value for
each field,
Select Distinct Field0, Field1, Field2 is going to give me 8
records right? "a",1,4
"b",1,4
"a",2,4
"b",2,4
"a",1,5
"b",1,5
"a",2,5
"b",2,5
so now I know I have 8 possible combinations to deal with,
I need to process all records Where Field0 = "a" And Field1 = 1
And Field2
>= "4" (group1)-lets say that gives me 20 records...
then do the same for the other 7 groups.
>
so I have 20 records "in group 1" but I don't get all 20 in the
original select distinct call do I?

No, you just want the fields that identify the unique group. You'd
then use those values to run another SQL statement. Right now you're
opening a second recordset and handing it off to a subroutine. I
don't know what's in the subroutine, so I can't say if it's doable
with a SQL update or if you really do need to walk the recordset to
perform the calcuations and update the records.

I thought i only got one of them(to make it distinct)
Does that make sense or am I completely confusing the situation?

Yes, and you then use that to select the set of records in that
group for operating on.

so what i'm doing now is more or less:
strSQL = "SELECT distinct Field0 , Field1, Field2 From " &
TABLE_NAME & _
" ORDER BY Field0 , Field1 , Field2"
Set oRsDistinct = moConn.Execute( strSQL)
Do While Not oRsDistinct.EOF
set oRs2 = moConn.Execute( "Select * From TableName Where
Field0 =
oRsDistinct .Field0.Value And Field1 = oRsDistinct .Field1.Value
And Field2
>= oRsDistinct .Field2.Value")
'test other field values for conditions 1,2 and 3 (in that
order)
and respond accordingly
Process oRs2 Condition1
Process oRs2 Condition2
Process oRs2 Condition3
Loop
'eg Condition1 = WHERE Field3.Value = "NA"
'eg Condition2 = WHERE Field3.Value = "L" OR Field3.Value = "R"
'eg Condition3 = WHERE Field3.Value = "B"
'(have to process these in this order...have to process
"NA" before
"L or R" and "B" after "L or R") so i can't just do them all in
one recordset
>
If there's an easier way i'd like to learn it

It's not possible to say, as I don't know what you're doing to each
group of records.

It does seem like you have a denormalized structure because there's
way too much duplicate data. The rows in your DISTINCT query may
belong in another table, and the records in your current table would
then be linked as child records of the parent table.

>I would also never use ADO, though the differences between ADO
>and DAO in the code you're doing would be very small.

>
I appreciate your wisdom on the best tool for the job but at this
point I'm more interested in learning how to get this to work,
then later I can look at alternatives if performance requires a
dao solution.

ADO is completely senseless. There is no reason to use it with a Jet
database. And Microsoft agrees with that now.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[MP]

Re: Can inner join replace multiple loop selects?

Thanks for staying with this David.


"David W. Fenton" <XXXusenet@dfen ton.com.invalid wrote in message
news:Xns986090D 2A7E0Ef99a49ed1 d0c49c5bbb2@127 .0.0.1...

Without knowing what it is you want to do with each group, it's
impossible to say.

see my response to Lyle *if* you want to see a detailed explanation (which
probably no one wants to see <g>)
:-)

No, you just want the fields that identify the unique group. You'd
then use those values to run another SQL statement. Right now you're
opening a second recordset and handing it off to a subroutine.

ah! now I get what you're saying....I thought I *was* running another sql
statement with the line
set oRs2 = moConn.Execute( "Select * From TableName Where Field0 =
oRsDistinct .Field0.Value And Field1 = oRsDistinct .Field1.Value And Field2
= oRsDistinct .Field2.Value")

You're distinguishing between running an sql statement that doesn't return a
recordset versus returning a recordset and setting the values there

I don't know enough (obviously) about databases to see the difference
between the two methods.
I don't know why there is an apparent implication (based on comments by you
and lyle) that getting a recordset is a bad thing to do...i didn't know
that.

don't know what's in the subroutine, so I can't say if it's doable
with a SQL update or if you really do need to walk the recordset to
perform the calcuations and update the records.

as i say, see other post for detail...I think it's trickier than a simple
sql update but not sure...

It does seem like you have a denormalized structure because there's
way too much duplicate data. The rows in your DISTINCT query may
belong in another table, and the records in your current table would
then be linked as child records of the parent table.

you are absolutely correct there. Since this is just a quickie db written
on the fly for this one time calculation (and then disposed of) I didn't
take time to create multiple tables and relationships.. . it is a one table
db...entirely flat...bad database design I know...just didn't think it was
worth normalizing but maybe that's what's causeing me troubles.
I'm not sure how dividing into multiple tables would help with the sql
statement though, since I'm just beginning to learn this and at this point
am as dumb as a box of rocks...unfortu nately <g>
Maybe I need to look at that issue...
If you care to look at my pseudocode problem domain in the other post and
think normalizing would ease a solution I'd be happy to hear about it.

Thanks again for your insights
I appreciate you taking time to look at this
Mark

[David W. Fenton]

Re: Can inner join replace multiple loop selects?

"MP" <nospam@Thanks. comwrote in
news:YIuZg.2226 1$cc3.20115@tor nado.rdc-kc.rr.com:

"David W. Fenton" <XXXusenet@dfen ton.com.invalid wrote in message
news:Xns986090D 2A7E0Ef99a49ed1 d0c49c5bbb2@127 .0.0.1...

[]

>No, you just want the fields that identify the unique group.
>You'd then use those values to run another SQL statement. Right
>now you're opening a second recordset and handing it off to a
>subroutine.

>
ah! now I get what you're saying....I thought I *was* running
another sql statement with the line
set oRs2 = moConn.Execute( "Select * From TableName Where Field0 =
oRsDistinct .Field0.Value And Field1 = oRsDistinct .Field1.Value
And Field2
>= oRsDistinct .Field2.Value")
>
You're distinguishing between running an sql statement that
doesn't return a recordset versus returning a recordset and
setting the values there

A SQL UPDATE operates on the data and changes the records, where a
SQL SELECT just returns data. A SQL UPDATE (or APPEND or DELETE)
does not return records because it isn't a SELECT -- it's an
operation that changes the records in a table.

A recordset can opened only on a SELECT statement. Once you've
returned the records, then you have to walk through them one by one
to make changes record by record (see below).

I don't know enough (obviously) about databases to see the
difference
between the two methods.
I don't know why there is an apparent implication (based on
comments by you and lyle) that getting a recordset is a bad thing
to do...i didn't know that.

Because updates to a recordset are done sequentially, by stepping
through the records in the recordset.

A SQL UPDATE operates on a set of records and is much, much faster
than the equivalent operation of walking through all the records and
changing them one by one.

This is a basic mistake that many people with more programming than
database experience often make. They are accustomed to thinking
sequentially and don't understand the set operations aroung which
all of SQL is built.

[]

>It does seem like you have a denormalized structure because
>there's way too much duplicate data. The rows in your DISTINCT
>query may belong in another table, and the records in your
>current table would then be linked as child records of the parent
>table.

>
you are absolutely correct there. Since this is just a quickie db
written on the fly for this one time calculation (and then
disposed of) I didn't take time to create multiple tables and
relationships.. . it is a one table db...entirely flat...bad
database design I know...just didn't think it was worth
normalizing but maybe that's what's causeing me troubles. I'm not
sure how dividing into multiple tables would help with the sql
statement though, since I'm just beginning to learn this and at
this point am as dumb as a box of rocks...unfortu nately <g>

No, just inexperienced.

Maybe I need to look at that issue...
If you care to look at my pseudocode problem domain in the other
post and think normalizing would ease a solution I'd be happy to
hear about it.

My eyes glazed over and I skimmed through it without reaching a
state of enlightenment. :)

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[MP]

Re: Can inner join replace multiple loop selects?


"David W. Fenton" <XXXusenet@dfen ton.com.invalid wrote in message
news:Xns9860E88 843A87f99a49ed1 d0c49c5bbb2@127 .0.0.1...

My eyes glazed over and I skimmed through it without reaching a
state of enlightenment. :)

:-)
Now we're finally at the real root of the problem!<g>
I cant' figure out a way to describe the "6 level deep decision tree" i have
without giving the whole ugly enchilada!

questioner: Can I do this with sql?
answerer: What are you trying to do?
questioner: description of problem
answerer: My eyes just glazed over.
:-)

let me try this
table1
fields(3) fldSide fldLength fldNumber
3 records
Right 12
Left 12
Left 16
so i have two pieces of data on each record and have to calculate the third
piece and enter it

Set rsLength = Select Distinct fldLength gives (12 16)
While not rsLength.eof
sql update where fldLength = rsLength.fldLen gth
if fldSide = Left then
fldNumber = i
elseif fldSide = Right then
fldNumber = i & "x"
End if
i = i +1
Wend

is there a way to do that without the distinct length set ?

the result should look like
fields(3) fldSide fldLength fldNumber
Right 12 1x
Left 12 1
Left 16 2

would you like some coffee to go with those glazed donuts er... eyes.
:-)

thanks for any ideas
Mark

[David W. Fenton]

Re: Can inner join replace multiple loop selects?

"MP" <nospam@Thanks. comwrote in
news:%QLZg.2435 6$IO2.10596@tor nado.rdc-kc.rr.com:

table1
fields(3) fldSide fldLength fldNumber
3 records
Right 12
Left 12
Left 16
so i have two pieces of data on each record and have to calculate
the third piece and enter it
>
Set rsLength = Select Distinct fldLength gives (12 16)
While not rsLength.eof
sql update where fldLength = rsLength.fldLen gth
if fldSide = Left then
fldNumber = i
elseif fldSide = Right then
fldNumber = i & "x"
End if
i = i +1
Wend
>

OK. You'll need a UDF to return the counter. That can take any field
as an argument and will discard the value. It would be something
like this:

Function ReturnCounter(v arInput As Variant, _
Optional bolInitialize As Boolean = False) As Long
Static lngCounter As Long

If bolInitialize Then lngCounter = 0
lngCounter = lngCounter + 1

ReturnCounter = lngCounter
End Function

Before running your query, you'd initialize your counter by calling:

ReturnCounter(N ull, True)

Then for each row of the query, you'd use:

ReturnCounter([somefield])

to get the new counter value.

The query would be something like this:

UPDATE table1
SET table1.fldNumbe r = ReturnCounter(f ldNumber)
& IIf([fldSide] = "Right", "x", Null)
WHERE table1.fldLengt h = rsLength!fldLen gth

If that doesn't make sense, ask and I'll explain.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[MP]

Re: Can inner join replace multiple loop selects?


"David W. Fenton" <XXXusenet@dfen ton.com.invalid wrote in message
news:Xns9861C56 1251FAf99a49ed1 d0c49c5bbb2@127 .0.0.1...

"MP" <nospam@Thanks. comwrote in
news:%QLZg.2435 6$IO2.10596@tor nado.rdc-kc.rr.com:
>

table1
fields(3) fldSide fldLength fldNumber
3 records
Right 12
Left 12
Left 16
so i have two pieces of data on each record and have to calculate
the third piece and enter it

Set rsLength = Select Distinct fldLength gives (12 16)
While not rsLength.eof
sql update where fldLength = rsLength.fldLen gth
if fldSide = Left then
fldNumber = i
elseif fldSide = Right then
fldNumber = i & "x"
End if
i = i +1
Wend

>
OK. You'll need a UDF to return the counter. That can take any field
as an argument and will discard the value. It would be something
like this:
>
Function ReturnCounter(v arInput As Variant, _
Optional bolInitialize As Boolean = False) As Long
Static lngCounter As Long
>
If bolInitialize Then lngCounter = 0
lngCounter = lngCounter + 1
>
ReturnCounter = lngCounter
End Function
>
Before running your query, you'd initialize your counter by calling:
>
ReturnCounter(N ull, True)
>
Then for each row of the query, you'd use:
>
ReturnCounter([somefield])
>
to get the new counter value.
>
The query would be something like this:
>
UPDATE table1
SET table1.fldNumbe r = ReturnCounter(f ldNumber)
& IIf([fldSide] = "Right", "x", Null)
WHERE table1.fldLengt h = rsLength!fldLen gth
>
If that doesn't make sense, ask and I'll explain.
>
--

Thank you, I'll chew on that for a while
The only thing I don't understand off the top is why pass the field into the
function since it's not used?
why not
Function ReturnCounter(O ptional bolInitialize As Boolean = False) As Long
Static lngCounter As Long
If bolInitialize Then lngCounter = 0
lngCounter = lngCounter + 1
ReturnCounter = lngCounter
End Function

Then to init
ReturnCounter(T rue)
and in query
SET table1.fldNumbe r = ReturnCounter() etc
???

Thanks again
Mark