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(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

Oct 17 '06 #1

Larry Linson

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.19488$F7.1287@tornado.rdc-kc.rr.com...

Oct 17 '06 #2

MP

Thanks for the response Larry,

"Larry Linson" <bouncer@localhost.notwrote in message
news:qd8Zg.4429$qv6.2078@trnddc06... 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.


the ADO might 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

Oct 17 '06 #3

David W. Fenton

"MP" <nospam@Thanks.comwrote in
news:SYbZg.22595$IO2.21141@tornado.rdc-kc.rr.com:
A copy of the developers tools for the version of Access you choose
would allow you to distribute Access runtime apps at no cost.
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/

Oct 17 '06 #4

David W. Fenton

"MP" <nospam@Thanks.comwrote in
news:L6ZYg.19488$F7.1287@tornado.rdc-kc.rr.com:
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/

Oct 17 '06 #5

MP

Thanks for your responses...see inline replies

"David W. Fenton" <XXXusenet@dfenton.com.invalidwrote in message
news:Xns985FB4D5121C4f99a49ed1d0c49c5bbb2@127.0.0. 1... 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'
maybe i'm misunderstanding 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:database 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 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.

Oct 18 '06 #6

Lyle Fairfield

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.

Oct 18 '06 #7

MP

Thanks for the response, Lyle

"Lyle Fairfield" <lylefairfield@aim.comwrote in message
news:1161134134.368614.40920@i3g2000cwc.googlegrou ps.com...
may I ask why?
<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 sortmeoutandgivemetheanswer 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 ...)indeterminate (String)
Field2 = ConditionName (1 - 8) fixed range (String)
Field3 = ConditionVariation (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(somewhat
oversimplified)
While not AllRecords.Eof
For Each DistinctGroupofValues
sPrefix = GroupOfValues'reset 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


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.dictionaries/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(handles) 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 :-)
i agree

You won't notice any difference 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

Oct 18 '06 #8

Lyle Fairfield

MP wrote: 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
.ActiveConnection = CurrentProject.Connection
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockBatchOptimistic
.Open "SELECT " _
& "o.OrderID, o.CustomerID, o.IDByCustomer " _
& "FROM Orders o " _
& "ORDER BY o.CustomerID, o.OrderDate"
.ActiveConnection = Nothing
Set fCustomerID = .Fields("CustomerID")
Set fIDByCustomer = .Fields("IDByCustomer")
While Not .EOF
If fCustomerID.Value <CustomerID Then
CustomerID = fCustomerID.Value
Iterator = 0
End If
Do While CustomerID = fCustomerID.Value
Iterator = Iterator + 1
fIDByCustomer.Value = CustomerID & " - " & Iterator
.MoveNext
If .EOF Then Exit Do
Loop
Wend
If MsgBox("Do you really wanna?", vbYesNoCancel, "Another Check") =
vbYes Then
.ActiveConnection = CurrentProject.Connection
.UpdateBatch
MsgBox "Update Completed", vbInformation
Else
MsgBox "Update Cancelled", vbInformation
End If
End With
End Sub

Oct 18 '06 #9

David W. Fenton

"MP" <nospam@Thanks.comwrote in
news:XAeZg.23631$IO2.11721@tornado.rdc-kc.rr.com:
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.
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.
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.
Yes, and you then use that to select the set of records in that
group for operating on.
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.
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/

Oct 18 '06 #10

MP

Thanks for staying with this David.


"David W. Fenton" <XXXusenet@dfenton.com.invalidwrote in message
news:Xns986090D2A7E0Ef99a49ed1d0c49c5bbb2@127.0.0. 1...
see my response to Lyle *if* you want to see a detailed explanation (which
probably no one wants to see <g>)
:-)


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.
as i say, see other post for detail...I think it's trickier than a simple
sql update but not sure...

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...unfortunately <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

Oct 18 '06 #11

David W. Fenton

"MP" <nospam@Thanks.comwrote in
news:YIuZg.22261$cc3.20115@tornado.rdc-kc.rr.com:
[]
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).
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.

[]
No, just inexperienced.
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/

Oct 19 '06 #12

MP

"David W. Fenton" <XXXusenet@dfenton.com.invalidwrote in message
news:Xns9860E88843A87f99a49ed1d0c49c5bbb2@127.0.0. 1...
:-)
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.fldLength
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

Oct 19 '06 #13

David W. Fenton

"MP" <nospam@Thanks.comwrote in
news:%QLZg.24356$IO2.10596@tornado.rdc-kc.rr.com:
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(varInput 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(Null, 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.fldNumber = ReturnCounter(fldNumber)
& IIf([fldSide] = "Right", "x", Null)
WHERE table1.fldLength = rsLength!fldLength

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/

Oct 19 '06 #14

MP

"David W. Fenton" <XXXusenet@dfenton.com.invalidwrote in message
news:Xns9861C561251FAf99a49ed1d0c49c5bbb2@127.0.0. 1... 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(Optional 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(True)
and in query
SET table1.fldNumber = ReturnCounter() etc
???

Thanks again
Mark

Oct 19 '06 #15

David W. Fenton

"MP" <nospam@Thanks.comwrote in
news:zoUZg.25651$cc3.3600@tornado.rdc-kc.rr.com:
Because if you don't have a different value passed to it for each
row, the expression will be evaluated only once, and thus return the
same value for every row. 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!"

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

Oct 20 '06 #16

MP

"David W. Fenton" <XXXusenet@dfenton.com.invalidwrote in message
news:Xns9862BA15DFE2Ef99a49ed1d0c49c5bbb2@127.0.0. 1...
<snip> <snip>
ok that's interesting
is a udf passed to a jet db different in that way from a standard vb
function?
I'm not aware of AccessExpressionService I'll have to do some googleing to
catch up I see.
Thanks for the info
Is ReturnCounter a "defined function name" to Access that it knows that when
the value of the field passed into it changes then it reacts?

I was thinking it was just a 'user defined function with an arbitrary
name...but then it wouldnt' have that magic ability to react when it's first
argument changed....
otherwise I thought it would increment each time it's called and I would
only call it when i needed the next number and reset it for each group.
if that were a regular vb function that would increment each time it's
called with a false initialize argument wouldn't it?

Thanks again for your help on this
I think I'm starting to 'get' some things about how you can use sql more
efficiently

Mark

Oct 21 '06 #17

MP

"Lyle Fairfield" <lylefairfield@aim.comwrote in message
news:1161170625.494259.4380@i3g2000cwc.googlegroup s.com...
Thanks I'll work with that and see what I can use.

Thanks
Mark

Oct 21 '06 #18

David W. Fenton

"MP" <Nospam@Thanks.comwrote in
news:45396cce$0$47256$ae4e5890@news.nationwide.net :
No, there's no difference. Any function you invoke in a row of a
query that takes no parameter from that row will be evaluated only
once for the query as a whole, not for each row of the query.
Queries are Jet objects, while a VBA function or a UDF are VBA
commands. Jet can't evaluate those functions by itself (that's why
an ODBC connection to an Access query using UDFs cannot be run; try
it with a Word Merge using and ODBC connection to an Access query
with one or more UDFs), so it utilizes the Access Expression Service
to handle them.
The Access Expression Service does not examine the inner workings of
a function. It only asks the question: How many times do I need to
evaluate this function? If it's called in every row, but there the
functions have no arguments that draw values from each row (i.e.,
the function output is not dependent on any values in each of the
rows returned), then the function can be evaulated once. If the
function takes one or more arguments from each row, it has to be
re-evaluated for each row of the query.
There's nothing magic about the name. The "magic" is that the
Expression Service can tell from the structure of the function call,
because it takes a row-based argument, that it needs to re-evaluate
the function's return value for every row returned by the query.
Try it. You'll see.

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

Oct 21 '06 #19

MP

"David W. Fenton" <XXXusenet@dfenton.com.invalidwrote in message
news:Xns9862DB80CAC8f99a49ed1d0c49c5bbb2@127.0.0.1 ...
SNIP Got it. That's the magic...the Expression Service(which I had never heard
of till now)
Thanks for the clear explanation
I'm playing with the update query now and trying to iron out all my errors
:-)

Oct 21 '06 #20

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" <XXXusenet@dfenton.com.invalidwrote in message
news:Xns9862DB80CAC8f99a49ed1d0c49c5bbb2@127.0.0.1 ...

By passing the value, you're 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 expressionservice 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 "PreviousValue" and when "thisValue" <>
"PreviousValue" then Increment Else DontIncrement

is that a correct assumption?

maybe something similar to:
Function IncrementUponChange (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

IncrementUponChange = Counter
End Function

does that make any sense?

Thanks for any info
Marek

Oct 23 '06 #21

Lyle Fairfield

MP wrote: 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.

Oct 23 '06 #22

MP

"Lyle Fairfield" <lylefairfield@aim.comwrote in message
news:1161628067.757961.245000@m7g2000cwm.googlegro ups.com... via 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 IncrementCounter 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 "currentvalue"

'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 "combination 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

Oct 23 '06 #23

Lyle Fairfield

"MP" <nospam@Thanks.comwrote in
news:YH8%g.26935$cc3.7436@tornado.rdc-kc.rr.com:
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.Execute "UPDATE Table1 SET testnote =
String(257, 'a')"
CurrentProject.Connection.Execute "UPDATE Table1 SET testnote = CStr
(sin(ID) + rnd(ID))"
CurrentProject.Connection.Execute "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.Connection');
c.ConnectionString='PROVIDER=Microsoft.Jet.OLEDB.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('UPDATE Table1 SET testnote = String(257, "a")');
c.Execute('UPDATE Table1 SET testnote = CStr(sin(ID) + rnd(ID))');
c.Execute('UPDATE 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

Oct 23 '06 #24

David W. Fenton

"MP" <nospam@Thanks.comwrote in
news:1m7%g.30140$F7.11584@tornado.rdc-kc.rr.com:
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).
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 wouldn't expect it to work in ADO (I don't use ADO. Ever.), only
in DAO.
You pass the field:

SELECT ID, ReturnCounter([ID]) As Counter
etc.
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.
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.
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.
That's not possible in pure Jet SQL.
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/

Oct 23 '06 #25