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

[MP]

One more question about Expression Service
After some googling it appears that is not available when hitting an mdb via
ado or dao?

"David W. Fenton" <XX*******@dfen ton.com.invalid wrote in message
news:Xn******** *************** **********@127. 0.0.1...

By passing the value, you're

telling the Access Expression Service "Hey! The value returned
can change with each row, so you can't just check it once!"

so I assume I need to create my own iterating function and determine
through examining the field.values when to call it or not call it...for each
record in the recordset...

am i correct in this assumption?

I tried the ReturnCounter function in my sql sent via
ado.connection. Execute(sSql)
and it appeared to work sometimes but not others...but I wasn't positive
which field value to send to it as the first argument..

I had understood from your description that i would pass a field object as
first arg.
the experession service would examine the value of that field and compare it
to a magically stored value from the previous time this function was called
(in the previous record), and if this value differed from previous value the
expression was evaluated (the counter was incremented) but if this value was
the same as the previous value, the expression would not be evaluated....
was this a correct understanding of how expressionservi ce would work(if it
were available...ie if i were using access...(which I'm not))
???

from the google information saying Expression Service is not available in a
call from ado or dao to a jet database,
i assume I need to save my own "PreviousVa lue" and when "thisValue" <>
"PreviousVa lue" then Increment Else DontIncrement

is that a correct assumption?

maybe something similar to:
Function IncrementUponCh ange (NewValue as variant, Optional Reset as
Boolean) as long
Static LastValue as variant
Static Counter as long
Const StartValue as Long = 1
If Reset Then
Counter = StartValue
Else
If NewValue <LastValue Then
Counter = Counter +1
LastValue = NewValue
End if
End if

IncrementUponCh ange = Counter
End Function

does that make any sense?

Thanks for any info
Marek

[Lyle Fairfield]

MP wrote:

One more question about Expression Service
After some googling it appears that is not available when hitting an mdb via
ado or dao?

This thread is becoming much more complicated and involved than it
needs be.

It seems to me that you want to update a table.

I suggest that you post six records from your table, including at least
two with the distinct set of values you want to deal with. Show them
(Input). Show what you want to achieve (Output). It's entirely likely
that someone with more patience than I have (almost everyone) will help
you.

[MP]

"Lyle Fairfield" <ly***********@ aim.comwrote in message
news:11******** **************@ m7g2000cwm.goog legroups.com...

MP wrote:

One more question about Expression Service
After some googling it appears that is not available when hitting an mdb

via

ado or dao?

This thread is becoming much more complicated and involved than it
needs be.

It seems to me that you want to update a table.

I suggest that you post six records from your table, including at least
two with the distinct set of values you want to deal with. Show them
(Input). Show what you want to achieve (Output). It's entirely likely
that someone with more patience than I have (almost everyone) will help
you.

Thanks for your response Lyle,
(I suspect you have more patience than you give yourself credit for since
you're one of two answering)<g>

I have no problem updating the table,
it's calculating the value to use for the update that involves a complex
decision "tree" which i'm still tweaking.
based on all response so far from you and David I'm very close to a solution
but have to adjust the IncrementCounte r function that David proposed so that
it works with ado.
I was trying to confirm if the Expression Service he was relying on is in
fact not active under ado environment as google research seems to suggest.

six records won't show the actual decisions required because there's too
many cases but, since you ask, here goes one possible example

input:(existing table with 5 fields filled in and one blank - to be filled
in)
f1 f2 f3 f4 f5 f6
A 1 0 36 NA
A 1 1 36 NA
A 1 1 36 L
A 1 1 36 R
A 1 1 36 B
A 1 1 24 NA
output(same table with 6th field filled in)
f1 f2 f3 f4 f5 f6
A 1 0 36 NA 1
A 1 1 36 NA 2 (incremented because f3 changed)
A 1 1 36 L 3 (incremented because f5 changed)
A 1 1 36 R 3x (not incremented, concatenated x because
last f5 was L and this is R)
A 1 1 36 B 4 (incremented because f5 changed)
A 1 1 24 B 5 (incremented because f4 changed)

that example shows 6 possible combinations
any number of records can share the same combination
any similar records get the same f6 value
While Not oRs.Eof
val1 = oRs.Fields.Item (f1).Value
val2 = oRs.Fields.Item (f2).Value
etc

'heres where i'm working out the bugs
'complicated selectcase on 5 fields to calculate "currentval ue"

'then
sSql = "Update Table Set f6 = currentvalue where table.f1 = val1 and
table.f2 = val2 and etc"
that update is to only be called once for every combination
(so if i have 5 instances of "combinatio n 1" i only need to call the
update once and not 5 times)

so i have
if sSql <sLastSql then
Execute(sSql)
sLastSql = sSql
End if
oRs.MoveNext

hope that's clear

I well understand that the decision is too complicated to be asking for help
on here so I'm trying to work it out with what i've been given so far.
I thought the counter function was going to solve part of the question but
it seems it may not work as David intended since i'm not using access and he
may have forgotten that part of my op

thanks again to all who have helped (past and future)
:-)
Mark

[Lyle Fairfield]

"MP" <no****@Thanks. comwrote in
news:YH******** **********@torn ado.rdc-kc.rr.com:

Thanks for your response Lyle,
(I suspect you have more patience than you give yourself credit for
since you're one of two answering)<g>

I have no problem updating the table,
it's calculating the value to use for the update that involves a
complex decision "tree" which i'm still tweaking.
based on all response so far from you and David I'm very close to a
solution but have to adjust the IncrementCounte r function that David
proposed so that it works with ado.

Part 1

There is ADO within Access and there is ADO outside Access.

These are all ADO (in a sense ... as they are calling JET SQL) and they
all work within Access:

Public Sub Test()
CurrentProject. Connection.Exec ute "UPDATE Table1 SET testnote =
String(257, 'a')"
CurrentProject. Connection.Exec ute "UPDATE Table1 SET testnote = CStr
(sin(ID) + rnd(ID))"
CurrentProject. Connection.Exec ute "UPDATE Table1 SET testnote =
UDFTest(ID)"
End Sub

Public Function UDFTest(ByVal dummy)
Static SomeVar&
UDFTest = String(300 + SomeVar, CStr(SomeVar))
SomeVar = SomeVar + 1
End Function

Will they work from outside Access? That is when we use ADO within
another application to connect to a JET DB that is housed in an Access
mdb with the UDF available (and of course the VBA which provides the
expression service)?

var c=new ActiveXObject(' ADODB.Connectio n');
c.ConnectionStr ing='PROVIDER=M icrosoft.Jet.OL EDB.4.0;DATA
SOURCE=C:/Documents and Settings/Lyle Fairfield/My
Documents/Access/db1.mdb;PERSIST SECURITY INFO=FALSE;Jet OLEDB:System
database=C:/Documents and Settings/Lyle Fairfield/Application
Data/Microsoft/Access/System.mdw'
c.Open();
c.Execute('UPDA TE Table1 SET testnote = String(257, "a")');
c.Execute('UPDA TE Table1 SET testnote = CStr(sin(ID) + rnd(ID))');
c.Execute('UPDA TE Table1 SET testnote = UDFTest(ID)');

In this js script the third sql call fails. The first tww work. This
suggests to me that the expression service and ado are fine, but that ado
knows zip of access and cannot use an access/vba public function.
(Although it seems to me that some suggested aw ayt to do this recently).
And a counter function would be a user defined public vba/access
function.

--
Lyle Fairfield

[David W. Fenton]

"MP" <no****@Thanks. comwrote in
news:1m******** **********@torn ado.rdc-kc.rr.com:

One more question about Expression Service
After some googling it appears that is not available when hitting
an mdb via ado or dao?

Well, I tried to investigate to figure out a proper answer on this,
but I am stumped now. There are references in Google and on MS's
website to the "Jet Expression Service" and then there are
references to the "Access Expression Service." It seems as though
they are the same thing, but it's not clear to me that they are.

Does anyone know the answer to that?

In any event, some things work and some don't. I'm pretty sure IIf()
will work via ODBC but Nz() won't, because Nz() is a VBA function
while IIf() is an internal Jet Expression Service function.

Maybe.

That's my understanding that it's something like that.

In any event, you shouldn't count on UDFs or any non-SQL functions
to work through any of the database interfaces, whether native Jet
(DAO) or not (ADO/ODBC).

"David W. Fenton" <XX*******@dfen ton.com.invalid wrote in message
news:Xn******** *************** **********@127. 0.0.1...

By passing the value, you're

>telling the Access Expression Service "Hey! The value returned
can change with each row, so you can't just check it once!"

so I assume I need to create my own iterating function and
determine through examining the field.values when to call it or
not call it...for each record in the recordset...

am i correct in this assumption?

Hmm. I thought you wanted it to be different on each row?

Let me outline what I understood:

1. you open a DISTINCT recordset to get the 5 unique groups.

2. you then run a SQL UPDATE on each group.

3. in each group, each row should increment.

Now, that's where I may have misunderstood. Is it that the first
group should be 1, the second 2 and so forth? If that's the case,
then you'd want to write you update SQL with a literal value, and
you could get that from the counter you use when walking through the
records of the DISTINCT query.

If, on the other hand, you need to increment within a group only
when certain values change, you'll want to make the parameter a
useful one, and evaluate it to see if you want to increment. If
you've got multiple arguments you may need to define multiple
parameters, or use a ParamArray(), though you'll have to be sure you
always pass the field values in the same order.

I tried the ReturnCounter function in my sql sent via
ado.connection. Execute(sSql)
and it appeared to work sometimes but not others...but I wasn't
positive which field value to send to it as the first argument..

I wouldn't expect it to work in ADO (I don't use ADO. Ever.), only
in DAO.

I had understood from your description that i would pass a field
object as first arg.

You pass the field:

SELECT ID, ReturnCounter([ID]) As Counter
etc.

the experession service would examine the value of that field and
compare it to a magically stored value from the previous time this
function was called (in the previous record),

No. Jet creates a query plan, deciding the order in which to do
operations in your SQL. If there's a join and WHERE clause, it will
decide which is the most efficient way to do it. Say, for instance,
you have a join between two tables of more-or-less the same number
of rows, but there's a criterion on one of the tables. The smart
thing to do is filter that table first, then do the join with the
other table, rather then joining all the records first and then
doing the select.

This is part of Jet's innards and is normal.

The last thing that happens is that anything that is *not* a SQL
command gets passed off to the Jet Expression Service. This is how
IIf() would get processed, and Left() and so forth. My understanding
(which could be completely wrong!) is that if it's not a Jet
function, it passes it off to the *Access* expression service, which
allows the query to use UDFs.

Now it could be that the Expression Service is one service that goes
by two names, but the point is that this happens last of all, after
the SQL has been optimized. And what happens is that the expression
service examines the SQL and sees if the function can be evaluated
just once (if you put Date() in there somewhere, it would be
evaluated once, since it has no dependency on data in each row) or
if it must be re-evaluated for each row.

What happens in each row with the function I wrote is that the
expression service evaluates it for each row, and the STATIC
variable stores the previous value.

and if this value differed from previous value the
expression was evaluated (the counter was incremented) but if this
value was the same as the previous value, the expression would not
be evaluated....

The expression service doesn't look at the return values. It only
looks at the *structure* of the function call. If a row value is
passed to the function, it evaluates it for each row, if not, it
evaluates it only once.

was this a correct understanding of how expressionservi ce would
work(if it were available...ie if i were using access...(which I'm
not))

Not entirely. I'd think you could still use this if you were using
DAO, but maybe I'm wrong. I never program in anything but Access.

from the google information saying Expression Service is not
available in a call from ado or dao to a jet database,
i assume I need to save my own "PreviousVa lue" and when
"thisValue" <"PreviousValue " then Increment Else DontIncrement

That's not possible in pure Jet SQL.

is that a correct assumption?

maybe something similar to:
Function IncrementUponCh ange (NewValue as variant, Optional Reset
as Boolean) as long
Static LastValue as variant
Static Counter as long
Const StartValue as Long = 1
If Reset Then
Counter = StartValue
Else
If NewValue <LastValue Then
Counter = Counter +1
LastValue = NewValue
End if
End if

IncrementUponCh ange = Counter
End Function

does that make any sense?

Not to me, as it won't get around the problem if DAO can't use the
expression service. It matters not what your function is -- either
DAO can evaluate it through the expression service or it can't. If
it can't, rewriting the function isn't going to change anything.

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

[David W. Fenton]

"MP" <no****@Thanks. comwrote in
news:YH******** **********@torn ado.rdc-kc.rr.com:

I well understand that the decision is too complicated to be
asking for help on here so I'm trying to work it out with what
i've been given so far. I thought the counter function was going
to solve part of the question but it seems it may not work as
David intended since i'm not using access and he may have
forgotten that part of my op

If DAO can't use the expression service (i.e., can't use a UDF),
then the recordset is the correct solution.

Indeed, it's become so complex that I think if it were me, I'd go
back to walking the recordset, even though it's not as efficient as
SQL. If you could do it with pure SQL without the function it would
be more efficient.

Of course, from your example data, I don't see why you couldn't
process your sample data with a counter (for each DISTINCT row in
the outer recordset) and make that DISTINCT operate on all five
columns. If you do that, you don't need any logic at all in the SQL
you execute.

It seems to me that you should be able to write your SQL by using
this logic, running this code on each row of the DISTINCT recordset.
It assumes that your recordset is sorted by column f5, so that L
comes before R (so that L will increment and L won't):

strSuffix = ""
SELECT CASE f5
CASE "R"
strSuffix = "x"
CASE ELSE
Counter = Counter + 1
END SELECT

SQL: "SET f6 = " & Counter & strSuffix

Now, I'm abbreviating everything here, and not spelling everything
out, but the point is that you'd do all the logic in the context of
walking through the DISTINCT recordset. This many mean that you add
another column to the DISTINCT and that you'd have 30 or 50 DISTINCT
rows instead of 5 or 6, but it's still going to be faster than
walking through all the records doing an .Edit and an .Update for
each row. That's where the slowdown is, in the recordlocking
overhead for the .Edit and .Update, not in the procedural code.

I hope all of this makes sense!

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

[MP]

David,
Thanks for the clarifications.

"David W. Fenton" <XX*******@dfen ton.com.invalid wrote in message
news:Xn******** *************** **********@127. 0.0.1...

"MP" <no****@Thanks. comwrote in
news:1m******** **********@torn ado.rdc-kc.rr.com:
Hmm. I thought you wanted it to be different on each row?

as outlined in my previous problem description in earlier posts...
it is either different or the same depending on the values of the five
'known' fields as compared to the previous row of the recordset being
traversed.
that's why i need to examine all 5 values,
see if they changed,
if they changed, in what way they changed,
depending on that combination of possibilities the counter is
incremented or not
and an "X" is appended or not.

that calculated value is then placed in another field in every row whose 5
fields have 5 matching values

The expression service doesn't look at the return values. It only
looks at the *structure* of the function call. If a row value is
passed to the function, it evaluates it for each row, if not, it
evaluates it only once.

ok, got that...so i need to do my own test for when to call it and when not.

Function IncrementUponCh ange (NewValue as variant, Optional Reset
as Boolean) as long

snip

Not to me, as it won't get around the problem if DAO can't use the
expression service. It matters not what your function is -- either
DAO can evaluate it through the expression service or it can't. If
it can't, rewriting the function isn't going to change anything.

what i'm saying is that in my vb code
inside a While Not oRs.Eof loop
i examine the data in each row,
compare to previous row
run my IncrementCounte r function, (which vb will evaluate...not the
expression service)
compose the appropriate sql update string using the calculated value
compare that sql string to the previously executed one (so as to not call
the same update several times if there are multiple rows with matching
values - which there will be)
then execute the sql (if new)
then loop till oRs.Eof

example
While Not rs.Eof
'do calc based on 5 fields data (using IncrementCounte r function when
required)

'compose sql string using resultant variable produced by calc above
ssql = "Update " & tableref & "Set " & fieldref & "=" & calculatedValue &
"Where" & fieldx = valuex etc
if sSql <sLastssql then
conn.Execute sSql
sLastsql = sSql
End if
rs.Movenext
loop

Anyway, thanks to all for their help and ideas.
I'm sure I'll get this eventually but it seems there's not a simple sql
statement that's going to replace my involved selectcase on the values of 5
columns for each row in the recordset.

so the difference is that if it were a simple 1,2,3 affair I could put the
"string equivalent" of a function call inside the actual sql string itself
(and let the expression service convert the string representation of a
function call into an actual function call, substituting the returned value
in the sql string and then evaluate the sql string).

In my situation i use my udf just like a normal vb function and use it's
return value to compose my sql string accordingly.
thats' why i think my Function IncrementUponCh ange (NewValue as variant,
Optional Reset as Boolean) as long
would work,
i call it on each row, pass it some field.value and if that changed from
previous row it increments, if not it doesn't.
:-)
That's what I misunderstood you to be saying the expression service was
doing in your earlier post.

I think I'm just not very good at making myself clear.
:-(

Thanks again,
Mark
ps thanks for introducing me to the static keyword. I had read about it but
didnt' understand.
That is way cool.

[MP]

Hi again,

"David W. Fenton" <XX*******@dfen ton.com.invalid wrote in message
news:Xn******** *************** ***********@127 .0.0.1...

"MP" <no****@Thanks. comwrote in
news:YH******** **********@torn ado.rdc-kc.rr.com:

I well understand that the decision is too complicated to be
asking for help on here so I'm trying to work it out with what
i've been given so far. I thought the counter function was going
to solve part of the question but it seems it may not work as
David intended since i'm not using access and he may have
forgotten that part of my op

If DAO can't use the expression service (i.e., can't use a UDF),
then the recordset is the correct solution.

Indeed, it's become so complex that I think if it were me, I'd go
back to walking the recordset,

that's what it seems to me i need to do

Of course, from your example data, I don't see why you couldn't
process your sample data with a counter (for each DISTINCT row in
the outer recordset) and make that DISTINCT operate on all five
columns. If you do that, you don't need any logic at all in the SQL
you execute.

well, since Lyle asked for 6 rows i supplied 6 rows, explaining with the
caveat that it wont' show even close the total decision making process...(or
I didn't think it would any way but i figure Lyle would know better than i)

>
It seems to me that you should be able to write your SQL by using
this logic, running this code on each row of the DISTINCT recordset.
It assumes that your recordset is sorted by column f5, so that L
comes before R (so that L will increment and L won't):

strSuffix = ""
SELECT CASE f5
CASE "R"
strSuffix = "x"
CASE ELSE
Counter = Counter + 1
END SELECT

SQL: "SET f6 = " & Counter & strSuffix

for that one isolated example that is enough logic but it's unfortunately
way more complicated. :-(
for every distinct f1,f2,f3,f4 there may be f5 = L or may not be, if there
are none then f6 increments instead of suffix....etc
that's why i thought a 6 record sample was not adequate to the problem i
described in my original posts
(which as you so poetically pointed out <gwas way too much for this ng
forum context)
:-)

Now, I'm abbreviating everything here, and not spelling everything
out, but the point is that you'd do all the logic in the context of
walking through the DISTINCT recordset. This many mean that you add
another column to the DISTINCT and that you'd have 30 or 50 DISTINCT
rows instead of 5 or 6, but it's still going to be faster than
walking through all the records doing an .Edit and an .Update for
each row. That's where the slowdown is, in the recordlocking
overhead for the .Edit and .Update, not in the procedural code.

in fact i've given up the distinct recordset entirely and just ordered the
entire table on the fields in a specific order
now walk through entire table checking for when various fields change and
respond accordingly

I hope all of this makes sense!

it does and i'm continuing on my path of walking the recordset where i
began,
just trying to get the right algorithm in my select case on the 5 field
values for when to increment and when not.
I think after all the long drawn out thread I'm back to answering my own
original question,
"or is there some way to combine the grouping, and subsequent processing of
each distinct recordset group, all in one sql statement?"
or a refined paraphrase of it, after all the wisdom gleaned from this thread
so far.
"can i replace a complicated selectcase on 5 fields values comparing to
previous values with a one liner sql statement"

answer: "no"
:-)

at least now I know my monster vb selectcase on each row isn't just my dumb
newbie's replacement for one elegantly simple but unknown sql update
statement.
:-)

Thanks for all your help on this
Mark

[David W. Fenton]

"MP" <no****@Thanks. comwrote in
news:Qu******** ***********@tor nado.rdc-kc.rr.com:

David,
Thanks for the clarifications.

"David W. Fenton" <XX*******@dfen ton.com.invalid wrote in message
news:Xn******** *************** **********@127. 0.0.1...

>"MP" <no****@Thanks. comwrote in
news:1m******* ***********@tor nado.rdc-kc.rr.com:
Hmm. I thought you wanted it to be different on each row?

as outlined in my previous problem description in earlier posts...
it is either different or the same depending on the values of the
five 'known' fields as compared to the previous row of the
recordset being traversed.
that's why i need to examine all 5 values,
see if they changed,
if they changed, in what way they changed,
depending on that combination of possibilities the
counter is
incremented or not
and an "X" is appended or not.

that calculated value is then placed in another field in every row
whose 5 fields have 5 matching values

This is why I would say you should do a SELECT DISTINCT on the five
fields, and then use VBA to manage the logic and the counter. That
counter will then be used to update the records that have the same
values in the 5 columns as the current row of the SELECT DISTINCT.

The reason for this is that it means you don't have to do any
complex logic in the SQL that you're executing.

>The expression service doesn't look at the return values. It only
looks at the *structure* of the function call. If a row value is
passed to the function, it evaluates it for each row, if not, it
evaluates it only once.

ok, got that...so i need to do my own test for when to call it and
when not.

If you do it the way I suggested above, you won't need any function
at all. You can do the logic and incrementing in the loop through
the top-level recordset.

Function IncrementUponCh ange (NewValue as variant, Optional
Reset as Boolean) as long

snip

>Not to me, as it won't get around the problem if DAO can't use
the expression service. It matters not what your function is --
either DAO can evaluate it through the expression service or it
can't. If it can't, rewriting the function isn't going to change
anything.

what i'm saying is that in my vb code
inside a While Not oRs.Eof loop
i examine the data in each row,
compare to previous row
run my IncrementCounte r function, (which vb will evaluate...not
the
expression service)
compose the appropriate sql update string using the calculated
value compare that sql string to the previously executed one (so
as to not call the same update several times if there are multiple
rows with matching values - which there will be)
then execute the sql (if new)
then loop till oRs.Eof

That's just too complicated. If you make your DISTINCT include all
the columns then each row is a new value, and all you have to test
for is whether or not it's L or R.

example
While Not rs.Eof
'do calc based on 5 fields data (using IncrementCounte r function
when required)

'compose sql string using resultant variable produced by calc
above ssql = "Update " & tableref & "Set " & fieldref & "=" &
calculatedValue & "Where" & fieldx = valuex etc
if sSql <sLastssql then
conn.Execute sSql
sLastsql = sSql
End if
rs.Movenext
loop

Anyway, thanks to all for their help and ideas.
I'm sure I'll get this eventually but it seems there's not a
simple sql statement that's going to replace my involved
selectcase on the values of 5 columns for each row in the
recordset.

No, but you shouldn't need to open a second recordset, which was the
point of the criticism.

so the difference is that if it were a simple 1,2,3 affair I could
put the "string equivalent" of a function call inside the actual
sql string itself (and let the expression service convert the
string representation of a function call into an actual function
call, substituting the returned value in the sql string and then
evaluate the sql string).

Well, in Access you could. Not sure what would happen in DAO/ADO
called outside Access.

But my point is that you don't need a function to generate the
counter in the SQL you're executing because you can put the logic
inside the loop through the top-level recordset.

In my situation i use my udf just like a normal vb function and
use it's return value to compose my sql string accordingly.

I'd just use a counter inline in code, rather than using a function
for it. There is no purpose that I can see in putting the counter
logic outside the main code body.

thats' why i think my Function IncrementUponCh ange (NewValue as
variant, Optional Reset as Boolean) as long
would work,
i call it on each row, pass it some field.value and if that
changed from previous row it increments, if not it doesn't.

I don't see why you even need to check previous values. If you do a
DISTINCT on the 5 columns you're testing and sort it accordingly,
you can just execute SQL for each row and don't need to ever look at
previous rows. The only wrinkle is not incrementing between R and L
and adding the x to the last field for the L. If all your L/R
records come in pairs, then you have no problem at all.

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

[David W. Fenton]

"MP" <no****@Thanks. comwrote in
news:40******** **********@torn ado.rdc-kc.rr.com:

Hi again,

"David W. Fenton" <XX*******@dfen ton.com.invalid wrote in message
news:Xn******** *************** ***********@127 .0.0.1...

>"MP" <no****@Thanks. comwrote in
news:YH******* ***********@tor nado.rdc-kc.rr.com:

I well understand that the decision is too complicated to be
asking for help on here so I'm trying to work it out with what
i've been given so far. I thought the counter function was
going to solve part of the question but it seems it may not
work as David intended since i'm not using access and he may
have forgotten that part of my op

If DAO can't use the expression service (i.e., can't use a UDF),
then the recordset is the correct solution.

Indeed, it's become so complex that I think if it were me, I'd go
back to walking the recordset,

that's what it seems to me i need to do

>Of course, from your example data, I don't see why you couldn't
process your sample data with a counter (for each DISTINCT row in
the outer recordset) and make that DISTINCT operate on all five
columns. If you do that, you don't need any logic at all in the
SQL you execute.

well, since Lyle asked for 6 rows i supplied 6 rows, explaining
with the caveat that it wont' show even close the total decision
making process...(or I didn't think it would any way but i figure
Lyle would know better than i)

>>
It seems to me that you should be able to write your SQL by using
this logic, running this code on each row of the DISTINCT
recordset. It assumes that your recordset is sorted by column f5,
so that L comes before R (so that L will increment and L won't):

strSuffix = ""
SELECT CASE f5
CASE "R"
strSuffix = "x"
CASE ELSE
Counter = Counter + 1
END SELECT

SQL: "SET f6 = " & Counter & strSuffix

for that one isolated example that is enough logic but it's
unfortunately way more complicated. :-(
for every distinct f1,f2,f3,f4 there may be f5 = L or may not be,
if there are none then f6 increments instead of suffix....etc
that's why i thought a 6 record sample was not adequate to the
problem i described in my original posts
(which as you so poetically pointed out <gwas way too much for
this ng forum context)

How complex is the logic beyond what you've outlined?

In any event, if you're writing and executing on-the-fly SQL to make
the updates, you're going to get the performance benefit, and also
vastly reduce the amount of time the data is locked.

>Now, I'm abbreviating everything here, and not spelling
everything out, but the point is that you'd do all the logic in
the context of walking through the DISTINCT recordset. This many
mean that you add another column to the DISTINCT and that you'd
have 30 or 50 DISTINCT rows instead of 5 or 6, but it's still
going to be faster than walking through all the records doing an
.Edit and an .Update for each row. That's where the slowdown is,
in the recordlocking overhead for the .Edit and .Update, not in
the procedural code.

in fact i've given up the distinct recordset entirely and just
ordered the entire table on the fields in a specific order
now walk through entire table checking for when various fields
change and respond accordingly

I'd say that's a mistake. I'd still write the SQL, on the assumption
that there were lots of records for each DISTINCT combination of the
5 fields.

>I hope all of this makes sense!

it does and i'm continuing on my path of walking the recordset
where i began,
just trying to get the right algorithm in my select case on the 5
field
values for when to increment and when not.

The logic would be much simpler for the DISTINCT, as you'd know that
with each row *something* had changed. Without it, you have to check
all the fields each time to decide if something has changed. A SQL
DISTINCT in a batch is much faster for making that decision than
code.

Secondly, you lose the benefit of executing SQL UPDATES on batches
of records and make your navigation much harder.

I think after all the long drawn out thread I'm back to answering
my own original question,
"or is there some way to combine the grouping, and subsequent
processing of each distinct recordset group, all in one sql
statement?" or a refined paraphrase of it, after all the wisdom
gleaned from this thread so far.
"can i replace a complicated selectcase on 5 fields values
comparing to previous values with a one liner sql statement"

answer: "no"

No, but you can still get the efficiency of SQL for the actual
updates. The only question is whether the logic is in the SQL or in
VBA, and with the DISTINCT you can easily write the SQL based on the
logic (even if tracking previous values for various fields).

at least now I know my monster vb selectcase on each row isn't
just my dumb newbie's replacement for one elegantly simple but
unknown sql update statement.

If you were using Access the answer *might* be different (but
probably not).

I still think you should keep the DISTINCT, as it makes no sense to
me to walk through a bunch of records whose relevant values are
identical -- that's just a waste of time. And you should write your
SQL UPDATE to update batches of records (I'm assuming your executing
a SQL UPDATE for the first of each batch of records, and then
skipping through them to the next one with a different combination
of the 5 fields).

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