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
52  6259 
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" <no****@Thanks.comwrote in message
news:L6*****************@tornado.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(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
Thanks for the response Larry,
"Larry Linson" <bo*****@localhost.notwrote in message
news:qd8Zg.4429$qv6.2078@trnddc06...
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
"MP" <no****@Thanks.comwrote in
news:SY*******************@tornado.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/
"MP" <no****@Thanks.comwrote in
news:L6*****************@tornado.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/
Thanks for your responses...see inline replies
"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@127.0.0. 1...
"MP" <no****@Thanks.comwrote in
news:L6*****************@tornado.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 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 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/
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.
Thanks for the response, Lyle
"Lyle Fairfield" <ly***********@aim.comwrote in message
news:11*********************@i3g2000cwc.googlegrou ps.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 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
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.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 :-)
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
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
.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
"MP" <no****@Thanks.comwrote in
news:XA*******************@tornado.rdc-kc.rr.com:
Thanks for your responses...see inline replies
"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@127.0.0. 1...
>"MP" <no****@Thanks.comwrote in
news:L6*****************@tornado.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 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?
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: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?
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/
Thanks for staying with this David.
"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@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...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
"MP" <no****@Thanks.comwrote in
news:YI*******************@tornado.rdc-kc.rr.com:
"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@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...unfortunately <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/
"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@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.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
"MP" <no****@Thanks.comwrote in
news:%Q*******************@tornado.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.fldLength
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(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/
"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@127.0.0. 1...
"MP" <no****@Thanks.comwrote in
news:%Q*******************@tornado.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.fldLength
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(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.
--
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
"MP" <no****@Thanks.comwrote in
news:zo******************@tornado.rdc-kc.rr.com:
>
"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@127.0.0. 1...
>"MP" <no****@Thanks.comwrote in
news:%Q*******************@tornado.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.fldLength
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(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.
--
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?
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/
"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@127.0.0. 1...
<snip>
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
<snip>
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/
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 bolInitialize Then lngCounter = 0
lngCounter = lngCounter + 1
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
"Lyle Fairfield" <ly***********@aim.comwrote in message
news:11********************@i3g2000cwc.googlegroup s.com...
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()
Thanks I'll work with that and see what I can use.
Thanks
Mark
"MP" <No****@Thanks.comwrote in
news:45***********************@news.nationwide.net :
"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@127.0.0. 1...
<snip>
> 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
<snip>
>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!"
ok that's interesting
is a udf passed to a jet db different in that way from a standard
vb function?
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.
I'm not aware of AccessExpressionService I'll have to do some
googleing to catch up I see.
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.
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?
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.
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....
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.
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.
Try it. You'll see.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn*********************************@127.0.0.1 ...
SNIP
>
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.
Got it. That's the magic...the Expression Service(which I had never heard
of till now)
Thanks for the clear explanation
Try it. You'll see.
I'm playing with the update query now and trying to iron out all my errors
:-)
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*******@dfenton.com.invalidwrote 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 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
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.
"Lyle Fairfield" <ly***********@aim.comwrote in message
news:11**********************@m7g2000cwm.googlegro ups.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 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
"MP" <no****@Thanks.comwrote in
news:YH******************@tornado.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 IncrementCounter 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.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
"MP" <no****@Thanks.comwrote in
news:1m******************@tornado.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*******@dfenton.com.invalidwrote 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 expressionservice 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 "PreviousValue" 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 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?
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/
"MP" <no****@Thanks.comwrote in
news:YH******************@tornado.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/
David,
Thanks for the clarifications.
"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn*********************************@127.0.0.1 ...
"MP" <no****@Thanks.comwrote in
news:1m******************@tornado.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 IncrementUponChange (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 IncrementCounter 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 IncrementCounter 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 IncrementUponChange (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.
Hi again,
"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@127.0.0. 1...
"MP" <no****@Thanks.comwrote in
news:YH******************@tornado.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
"MP" <no****@Thanks.comwrote in
news:Qu*******************@tornado.rdc-kc.rr.com:
David,
Thanks for the clarifications.
"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn*********************************@127.0.0.1 ...
>"MP" <no****@Thanks.comwrote in
news:1m******************@tornado.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 IncrementUponChange (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 IncrementCounter 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 IncrementCounter 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 IncrementUponChange (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/
"MP" <no****@Thanks.comwrote in
news:40******************@tornado.rdc-kc.rr.com:
Hi again,
"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@127.0.0. 1...
>"MP" <no****@Thanks.comwrote in
news:YH******************@tornado.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/
"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn*********************************@127.0.0.1 ...
"MP" <no****@Thanks.comwrote in
news:40******************@tornado.rdc-kc.rr.com:
<SNIP>
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).
right,
I get what you're saying about the distinct now on the multiple fields
makes perfect sense
I'll check that out.
thanks for hanging in there *way* above the 'call of duty'
:-)
this has been a great education.
Mark
"MP" <no****@Thanks.comwrote in
news:BT*******************@tornado.rdc-kc.rr.com:
"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn*********************************@127.0.0.1 ...
>"MP" <no****@Thanks.comwrote in
news:40******************@tornado.rdc-kc.rr.com:
<SNIP>
>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).
right,
I get what you're saying about the distinct now on the multiple
fields makes perfect sense
I'll check that out.
thanks for hanging in there *way* above the 'call of duty'
Well, I wanted to show the superiority of SQL UPDATES to walking and
updating a recordset.
this has been a great education.
I found it useful, as well, because it made clear where the line is
crossed into becoming easier with a recordset. In your case, you
don't need it, it seems to me, but it's a close call, and it's only
the fact that you've got the repeating data that makes the DISTINCT
recordset -row-by-row SQL UPDATE possible. In cases where you had
to evaluate based on groups of records, it could tip things in favor
of walking the recordset.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
I fear i'm on the verge of wearing out my welcome here :-)
I don't see my first reply to this post, maybe it's taking a while getting
to the ng.
but here's a followup reply as well
"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@127.0.0. 1...
"MP" <no****@Thanks.comwrote in
news:Qu*******************@tornado.rdc-kc.rr.com:
David,
Thanks for the clarifications.
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.
thats one wrinkle i'm working on now...they don't necessarily come in
pairs...(see examples below)
The treatment of F5="3R" varies according to whether or not I have any
F5="2L" anywhere in the group in that particular family of F1:F2:F3
If I get to "2L" before i get to "3R" it's easy...if I get to "3R" first I
don't know what to do yet
So somehow I need to read the entire group, set a boolean bHaveL and then
re-traverse the group accordingly
Yes, i like the distinct idea on all 5 fields
so if my distinct gives me a return similar to:
F1 : F2 : F3 : F4 : F5 : F6
A: 1: 1: 36: 1NA: 6:
A: 1: 1: 36: 2L: 7:
A: 1: 1: 36: 3R: 7X:
A: 1: 1: 36: 4B: 8:
A: 1: 1: 24: 1NA: 9:
A: 1: 1: 24: 2L: 10:
A: 1: 1: 24: 3R: 10X:
A: 1: 1: 24: 4B: 11:
it's a simple case and i can step through them in order and assign field 6
because:
in that case when i get to record 3
(A: 1: 1: 36: 3R: 7X: )
I know how to treat F5="3R" because record 2 had F5="2L"
however in a case where my distinct would yield the following(shown before
assigning F6)
A: 1: 1: 36: 1NA:
A: 1: 1: 36: 3R:
A: 1: 1: 36: 4B:
A: 1: 1: 24: 1NA:
A: 1: 1: 24: 2L: <<<< this L means the previous R is treated accordingly
but i don't know that yet till i get here
A: 1: 1: 24: 3R:
A: 1: 1: 24: 4B:
for this grouping i get to R (record2) before getting to L(record 5) so I
don't know at this point what to do
so I can't just step through one record at a time and make the decision,
I need to know ahead of time if there are *any* 'Ls' in that particular
group ( f1 + f2 + f3 )
so somehow I need to loop through the distinct groups containing *all*
fields and divide them by distinct on just the first *3* fields,
test that group for the existence of "2L" some how like
Select Count() ... Where F5 = "2L" .Field(0).value 0
Select Distinct F1, F2, F3 yields:(for example)
A:1:1(shown above)
A:1:2
A:1:3 etc
Loop through each of those and set bHaveL variable for each
Then get
Distinct F1,F2,F3,F4,F5 and do what's required based on the boolean switch
for each group
somehow I need to figure out how to use the Count(*) function to find out
for each Distinct 3Field Group of f1,f2,f3 if any of them have an L before
going back through the Distinct 5Field Set to decide what to do with the R's
at least that's what it seems to me i need to do.
Thanks again for all your help
Mark
Oye Vey!
I just replied to your post on another limb of this burdgeoning thread!
:-)
"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@127.0.0. 1...
>
I found it useful, as well, because it made clear where the line is
crossed into becoming easier with a recordset. In your case, you
don't need it, it seems to me, but it's a close call, and it's only
the fact that you've got the repeating data that makes the DISTINCT
recordset -row-by-row SQL UPDATE possible. In cases where you had
to evaluate based on groups of records, it could tip things in favor
of walking the recordset.
In that other reply I showed that I *almost* can walk through the Distinct
5Field set but *not quite*
(good thing I don't like ceegars!!!)<g>
I need to collect a Distinct 3field grouping first, determine if that 3Field
group has *any* 'Lefts' before going back through the 5Field set and doing
my updates (or so I think)
Shouldn't be too hard but I'm trying to figure out how to use the Count()
function now to work that wrinkle out.
:-0
whew!
I'm gonna need a tall cool one after all this is over!
:-)
Thanks again,
Mark
"MP" <no****@Thanks.comwrote in
news:n7******************@tornado.rdc-kc.rr.com:
I need to collect a Distinct 3field grouping first, determine if
that 3Field group has *any* 'Lefts' before going back through the
5Field set and doing my updates (or so I think)
Shouldn't be too hard but I'm trying to figure out how to use the
Count() function now to work that wrinkle out.
I don't understand why. You could just do the DISTINCT on all 5
columns, sort it in order from top to bottom, and retain the value
of the previous record for the 5th column and compare it to the next
row. That should tell you all you need to know, right? The reason is
because you *know* that each row is unique, so that you have new
values for one or more of the fields, and your incrementing the
counter except for R after L. So, you increment the counter except
when R is not immediately preceeded by L.
Or is there a bunch of logic you've either left out or that I zoned
out on?
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
"MP" <no****@Thanks.comwrote in
news:p0******************@tornado.rdc-kc.rr.com:
I fear i'm on the verge of wearing out my welcome here :-)
I don't see my first reply to this post, maybe it's taking a while
getting to the ng.
but here's a followup reply as well
"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@127.0.0. 1...
>"MP" <no****@Thanks.comwrote in
news:Qu*******************@tornado.rdc-kc.rr.com:
David,
Thanks for the clarifications.
>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.
thats one wrinkle i'm working on now...they don't necessarily come
in pairs...(see examples below)
The treatment of F5="3R" varies according to whether or not I have
any F5="2L" anywhere in the group in that particular family of
F1:F2:F3 If I get to "2L" before i get to "3R" it's easy...if I
get to "3R" first I don't know what to do yet
So somehow I need to read the entire group, set a boolean bHaveL
and then re-traverse the group accordingly
Yes, i like the distinct idea on all 5 fields
so if my distinct gives me a return similar to:
F1 : F2 : F3 : F4 : F5 : F6
A: 1: 1: 36: 1NA: 6:
A: 1: 1: 36: 2L: 7:
A: 1: 1: 36: 3R: 7X:
A: 1: 1: 36: 4B: 8:
A: 1: 1: 24: 1NA: 9:
A: 1: 1: 24: 2L: 10:
A: 1: 1: 24: 3R: 10X:
A: 1: 1: 24: 4B: 11:
If you did a DISTINCT on this, it would be sorted, thus:
F1 : F2 : F3 : F4 : F5 : F6
A: 1: 1: 24: 1NA: 6:
A: 1: 1: 24: 2L: 7:
A: 1: 1: 24: 3R: 7X:
A: 1: 1: 24: 4B: 8:
A: 1: 1: 36: 1NA: 9:
A: 1: 1: 36: 2L: 10:
A: 1: 1: 36: 3R: 10X:
A: 1: 1: 36: 4B: 11:
You'll need to do an ORDER BY to sort the 3rd column in descending
order (assuming that's what you want).
it's a simple case and i can step through them in order and assign
field 6 because:
in that case when i get to record 3
(A: 1: 1: 36: 3R: 7X: )
I know how to treat F5="3R" because record 2 had F5="2L"
however in a case where my distinct would yield the
following(shown before assigning F6)
A: 1: 1: 36: 1NA:
A: 1: 1: 36: 3R:
A: 1: 1: 36: 4B:
A: 1: 1: 24: 1NA:
A: 1: 1: 24: 2L: <<<< this L means the previous R is treated
accordingly but i don't know that yet till i get here
A: 1: 1: 24: 3R:
A: 1: 1: 24: 4B:
for this grouping i get to R (record2) before getting to L(record
5)
What do records 2 and 5 have to do with each other? How do you
*know* that 2L goes with 3R?
so I
don't know at this point what to do
so I can't just step through one record at a time and make the
decision, I need to know ahead of time if there are *any* 'Ls' in
that particular group ( f1 + f2 + f3 )
It seems to me that you've got an inconsistency in that column 4 is
sometimes important and sometimes it's not. If that can't be
resolved, yes, then you'll have to walk the recordset. I have just
been trying to figure out some way to parse the data sequentially,
but it doesn't work.
Are you sure you haven't mis-typed your example data?
so somehow I need to loop through the distinct groups containing
*all* fields and divide them by distinct on just the first *3*
fields, test that group for the existence of "2L" some how like
Select Count() ... Where F5 = "2L" .Field(0).value 0
Select Distinct F1, F2, F3 yields:(for example)
A:1:1(shown above)
A:1:2
A:1:3 etc
Loop through each of those and set bHaveL variable for each
Then get
Distinct F1,F2,F3,F4,F5 and do what's required based on the
boolean switch for each group
Yes, opening a second DISTINCT recordset limited to the DISTINCT row
in the first recordset.
somehow I need to figure out how to use the Count(*) function to
find out for each Distinct 3Field Group of f1,f2,f3 if any of them
have an L before going back through the Distinct 5Field Set to
decide what to do with the R's
at least that's what it seems to me i need to do.
I can't answer your question unless you actually typed incorrect
sample data. If it's correct, I can't make heads or tails of why the
L and R go together at all.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@127.0.0. 1...
"MP" <no****@Thanks.comwrote in
news:p0******************@tornado.rdc-kc.rr.com:
If you did a DISTINCT on this, it would be sorted, thus:
F1 : F2 : F3 : F4 : F5 : F6
A: 1: 1: 24: 1NA: 6:
A: 1: 1: 24: 2L: 7:
A: 1: 1: 24: 3R: 7X:
A: 1: 1: 24: 4B: 8:
A: 1: 1: 36: 1NA: 9:
A: 1: 1: 36: 2L: 10:
A: 1: 1: 36: 3R: 10X:
A: 1: 1: 36: 4B: 11:
You'll need to do an ORDER BY to sort the 3rd column in descending
order (assuming that's what you want).
yes that's what i need and what i'm doing thus my order as posted
the order by includes F4 DESC (all others ASC)
the posted example was a cut and paste from the log file my program produces
to test my results so it was an actual case.
however in a case where my distinct would yield the
following(shown before assigning F6)
A: 1: 1: 36: 1NA:
A: 1: 1: 36: 3R:
A: 1: 1: 36: 4B:
A: 1: 1: 24: 1NA:
A: 1: 1: 24: 2L: <<<< this L means the previous R is treated
accordingly but i don't know that yet till i get here
A: 1: 1: 24: 3R:
A: 1: 1: 24: 4B:
for this grouping i get to R (record2) before getting to L(record
5)
What do records 2 and 5 have to do with each other? How do you
*know* that 2L goes with 3R?
Because they are both in group A:1:1
that's why i think i need two distinct groupings
A:1:1 and all subsequent records(the small sample shown)
A:1:2 and all subsequent records
A:1:3 and all subsequent records
A:2:0 and all subsequent records
etc, etc, etc...hundreds of possible combinations
now for each of those groups
Get the 5 field grouping sorted as I posted
A: 1: 1: 36: 1NA:
A: 1: 1: 36: 3R:
A: 1: 1: 36: 4B:
A: 1: 1: 24: 1NA:
A: 1: 1: 24: 2L: <<<< this one L in the entire group means any Rs get
treated a certain way(but i don't get there first in a sequential stepping
motion)
A: 1: 1: 24: 3R:
A: 1: 1: 24: 4B:
if the grouping happened to be thus the R's gets treated differently(no Ls
were found in entire group)
A: 1: 1: 36: 1NA:
A: 1: 1: 36: 3R:
A: 1: 1: 36: 4B:
A: 1: 1: 24: 1NA:
A: 1: 1: 24: 3R:
A: 1: 1: 24: 4B:
that's why i said
I can't just step through one record at a time and make the
decision, I need to know ahead of time if there are *any* 'Ls' in
that particular group ( f1 + f2 + f3 )(A:1:1 in the sample group shown)
It seems to me that you've got an inconsistency in that column 4 is
sometimes important and sometimes it's not.
no, column 4 is *always* important to establish the sorting order(desc)
within each 3 field group
..... these are all related by group F1F2F3
...... these are related also by F4
A: 1: 1: 36: 1NA:
A: 1: 1: 36: 3R:
A: 1: 1: 36: 4B:
........these are related also by F4
A: 1: 1: 24: 1NA:
A: 1: 1: 24: 3R:
A: 1: 1: 24: 4B:
........these are related also by F4
A: 1: 1: 18: 1NA:
A: 1: 1: 18: 3R:
A: 1: 1: 18: 4B:
etc, etc
Are you sure you haven't mis-typed your example data?
yes Im sure, it's a cut and paste from actual program run(log file)
Yes, opening a second DISTINCT recordset limited to the DISTINCT row
in the first recordset.
Ok, Ok, OK how do i do that?!!!???
somehow I need to figure out how to use the Count(*) function to
find out for each Distinct 3Field Group of f1,f2,f3 if any of them
have an L before going back through the Distinct 5Field Set to
decide what to do with the R's
I've been tearing my hair out trying to get the count(*) function to work to
no avail(posted in separate thread)
I can't answer your question unless you actually typed incorrect
sample data. If it's correct, I can't make heads or tails of why the
L and R go together at all.
because they both live in the A11 group (F1,F2,F3)
here are two examples with F6 filled out correctly
one has a left somewhere within the group(f1,f2,f3)
the other has no left anywhere within the group
group A11 with one L
A: 1: 1: 36: 1NA: 1
A: 1: 1: 36: 3R: 2x
A: 1: 1: 36: 4B: 3
A: 1: 1: 24: 1NA: 4
A: 1: 1: 24: 2L: 5
A: 1: 1: 24: 3R: 5x
A: 1: 1: 24: 4B: 6
group A11 with no L
A: 1: 1: 36: 1NA: 1
A: 1: 1: 36: 3R: 2
A: 1: 1: 36: 4B: 3
A: 1: 1: 24: 1NA: 4
A: 1: 1: 24: 3R: 5
A: 1: 1: 24: 4B: 6
I don't know if an explanation would help
NA = not applicable L = Left R = Right B = Both
(i only added the preceeding digit "1Na" "2L" "3R" "4B" so they would sort
correctly)
-
If I have a Left handed object, any similar (same group, same length) Right
handed object is opposite hand of that leftie(same number with X)
-
If I have *Any* "lefties" in a given group (F1,F2,F3) then *any* Rights
whether or not they have an "equal length(F4) Left counterpart become
"opposite hand" of whatever length(F4) they might be.(some number with X)
-
If I have no Left handed objects, then all Right handed objects stand on
their own and get their own number and are not opposite to anything (so no
X).
-
don't know if that helps make it more understandable or not?
-
Thanks again for all the time and effort you've put into all this, sorry i'm
making it so difficult.
:-)
Mark
"MP" <no****@Thanks.comwrote in
news:j2******************@tornado.rdc-kc.rr.com:
David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@127.0.0. 1...
>"MP" <no****@Thanks.comwrote in
news:p0******************@tornado.rdc-kc.rr.com:
If you did a DISTINCT on this, it would be sorted, thus:
F1 : F2 : F3 : F4 : F5 : F6
A: 1: 1: 24: 1NA: 6:
A: 1: 1: 24: 2L: 7:
A: 1: 1: 24: 3R: 7X:
A: 1: 1: 24: 4B: 8:
A: 1: 1: 36: 1NA: 9:
A: 1: 1: 36: 2L: 10:
A: 1: 1: 36: 3R: 10X:
A: 1: 1: 36: 4B: 11:
You'll need to do an ORDER BY to sort the 3rd column in
descending order (assuming that's what you want).
yes that's what i need and what i'm doing thus my order as posted
the order by includes F4 DESC (all others ASC)
the posted example was a cut and paste from the log file my
program produces to test my results so it was an actual case.
however in a case where my distinct would yield the
following(shown before assigning F6)
A: 1: 1: 36: 1NA:
A: 1: 1: 36: 3R:
A: 1: 1: 36: 4B:
A: 1: 1: 24: 1NA:
A: 1: 1: 24: 2L: <<<< this L means the previous R is treated
accordingly but i don't know that yet till i get here
A: 1: 1: 24: 3R:
A: 1: 1: 24: 4B:
for this grouping i get to R (record2) before getting to
L(record 5)
What do records 2 and 5 have to do with each other? How do you
*know* that 2L goes with 3R?
Because they are both in group A:1:1
that's why i think i need two distinct groupings
A:1:1 and all subsequent records(the small sample shown)
A:1:2 and all subsequent records
A:1:3 and all subsequent records
A:2:0 and all subsequent records
I think a self-join is going to be required. What you need is all
the L's and an outer join on the first three fields to the same
table, but limited to the R's.
Another way to do it would be to do the join, but filter on f5=L or
f5=R and <>[f5 in other table]. That would give you the R for an L
and an L for the R, and would tell you when you need to increment.
If you come to a new R and it has no value in the self-join table,
increment. If not, grab the number from the self-join table.
It would be something like this:
SELECT f1, f2, f3, f4, f5, Other.f5, Other.f6
FROM table1 [outer join syntax] table1 As Other
This will allow you to grab the increment value for L if you get to
the R long after your counter has passed the L that goes with it.
etc, etc, etc...hundreds of possible combinations
now for each of those groups
Get the 5 field grouping sorted as I posted
A: 1: 1: 36: 1NA:
A: 1: 1: 36: 3R:
A: 1: 1: 36: 4B:
A: 1: 1: 24: 1NA:
A: 1: 1: 24: 2L: <<<< this one L in the entire group means any
Rs get
treated a certain way(but i don't get there first in a sequential
stepping motion)
A: 1: 1: 24: 3R:
A: 1: 1: 24: 4B:
I assume that within any A: 1: 1: that there can be only one L and
one R?
if the grouping happened to be thus the R's gets treated
differently(no Ls were found in entire group)
A: 1: 1: 36: 1NA:
A: 1: 1: 36: 3R:
A: 1: 1: 36: 4B:
A: 1: 1: 24: 1NA:
A: 1: 1: 24: 3R:
A: 1: 1: 24: 4B:
that's why i said
I can't just step through one record at a time and make the
decision, I need to know ahead of time if there are *any* 'Ls'
in that particular group ( f1 + f2 + f3 )(A:1:1 in the sample
group shown)
Perhaps the self-join will give you some ideas?
[]
>Yes, opening a second DISTINCT recordset limited to the DISTINCT
row in the first recordset.
Ok, Ok, OK how do i do that?!!!???
Simple. First, open your recordset DISTINCT on the first three
columns.
For each record, construct dynamic SQL to open a recordset DISTINCT
on the first 5 columns and limited to the rows that are in that
group.
That would allow you to handle every row individually, with the
counter logic in the loop through this DISTINCT recordset.
A further comment: rather than doing the self-join, it might be
faster to use a SQL lookup for the L values when you hit an R. I'm
not sure how fast this will be. I assume the columns are all
indexed. If they are, opening a persistent table-type recordset and
using SEEK might be faster. Oh, oops. That's DAO, not ADO. This is a
case where DAO would be substantially faster than ADO, because it
can access the Jet indexes directly, whereas ADO can't. And I
believe ADO's FIND is slower than DAO's FindFirst. But you'd want to
compare to a filtered recordset returning the single matching
record.
somehow I need to figure out how to use the Count(*) function
to find out for each Distinct 3Field Group of f1,f2,f3 if any
of them have an L before going back through the Distinct 5Field
Set to decide what to do with the R's
I've been tearing my hair out trying to get the count(*) function
to work to no avail(posted in separate thread)
Try the self-join and see if you can get it to work.
>I can't answer your question unless you actually typed incorrect
sample data. If it's correct, I can't make heads or tails of why
the L and R go together at all.
because they both live in the A11 group (F1,F2,F3)
here are two examples with F6 filled out correctly
one has a left somewhere within the group(f1,f2,f3)
the other has no left anywhere within the group
group A11 with one L
A: 1: 1: 36: 1NA: 1
A: 1: 1: 36: 3R: 2x
A: 1: 1: 36: 4B: 3
A: 1: 1: 24: 1NA: 4
A: 1: 1: 24: 2L: 5
A: 1: 1: 24: 3R: 5x
A: 1: 1: 24: 4B: 6
group A11 with no L
A: 1: 1: 36: 1NA: 1
A: 1: 1: 36: 3R: 2
A: 1: 1: 36: 4B: 3
A: 1: 1: 24: 1NA: 4
A: 1: 1: 24: 3R: 5
A: 1: 1: 24: 4B: 6
I don't know if an explanation would help
NA = not applicable L = Left R = Right B = Both
(i only added the preceeding digit "1Na" "2L" "3R" "4B" so they
would sort correctly) - If I have a Left handed object, any
similar (same group, same length) Right handed object is opposite
hand of that leftie(same number with X) - If I have *Any*
"lefties" in a given group (F1,F2,F3) then *any* Rights whether or
not they have an "equal length(F4) Left counterpart become
"opposite hand" of whatever length(F4) they might be.(some number
with X) - If I have no Left handed objects, then all Right handed
objects stand on their own and get their own number and are not
opposite to anything (so no X). - don't know if that helps make
it more understandable or not?
Sort of. Makes it sound to me like the self-join is the way to go.
Since all L's will be incremented, you can change what I said above
to just have the join show the L's for the R's. You'd need criteria
like:
SELECT f1, f2, f3, f4, f5, Other.f5, Other.f6
FROM table1 [outer join syntax] table1 As Other
WHERE (Other.f5<>table1.f5 Or Other.f5 Is Null) And table1.f5="R"
Actually, you'd probably need to encapsulate the self-join table as
a subquery or as a virtual table. It could be done like this:
SELECT f1, f2, f3, f4, f5, Other.f5, Other.f6
FROM table1 [outer join syntax] [SELECT table1.f5, table1.f6 FROM
table1 WHERE table1.f5="L"]. As Other WHERE Other.f5<>table1.f5 Or
Other.f5 Is Null
The "[]. As alias" syntax is something many people don't know about.
It's like () in other dialects of SQL, where between the brackets
(square or round) you can have a SQL statement that returns rows.
This allows you to filter it without affecting the number of rows
returned in the main SELECT statement.
It's possible you'll need a DISTINCT on the virtual table, but that
depends on whether a group can have more than one L.
Thanks again for all the time and effort you've put into all this,
sorry i'm making it so difficult.
It's a fascinating problem, seems to me. And I'm convinced there's a
way to do it relying primarily on SQL for the updates.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
I guess you get the infinite patience award!!!
:-)
"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@127.0.0. 1...
snip
>
I assume that within any A: 1: 1: that there can be only one L and
one R?
no i was just trying to show a small sample
they could all have Ls , all have Rs, none have Ls , none have Rs or any
combination.
a broader sample
'group with no Ls
A: 1: 1: 36: 1NA:
A: 1: 1: 36: 3R:
A: 1: 1: 36: 4B:
A: 1: 1: 24: 1NA:
A: 1: 1: 24: 3R:
A: 1: 1: 24: 4B:
'group with all lengths having both L and R
A: 1: 2: 36: 1NA:
A: 1: 2: 36: 2L:
A: 1: 2: 36: 3R:
A: 1: 2: 36: 4B:
A: 1: 2: 24: 1NA:
A: 1: 2: 24: 2L:
A: 1: 2: 24: 3R:
A: 1: 2: 24: 4B:
'group with some ls
A: 1: 3: 36: 1NA:
A: 1: 3: 36: 2L:
A: 1: 3: 36: 3R:
A: 1: 3: 36: 4B:
A: 1: 3: 24: 1NA:
A: 1: 3: 24: 3R:
A: 1: 3: 24: 4B:
'group with no rs
A: 1: 4: 36: 1NA:
A: 1: 4: 36: 2L:
A: 1: 4: 36: 4B:
A: 1: 4: 24: 1NA:
A: 1: 4: 24: 2L:
A: 1: 4: 24: 4B:
'etc etc etc
Perhaps the self-join will give you some ideas?
I will look into that...
ironically that's what i meant in my original post by inner join i was
thinking self join but said it wrong....
>
[]
Yes, opening a second DISTINCT recordset limited to the DISTINCT
row in the first recordset.
Ok, Ok, OK how do i do that?!!!???
Simple. First, open your recordset DISTINCT on the first three
columns.
For each record, construct dynamic SQL to open a recordset DISTINCT
on the first 5 columns and limited to the rows that are in that
group.
yeah that's what i'm trying to do now...but was trying to count if there
were Ls
some reason the count() funciton doesn't work in the middle of a loop (using
same connection)
I may need two connections to do that???
I postted that problem in another thread "Select Count() problem"
>
That would allow you to handle every row individually, with the
counter logic in the loop through this DISTINCT recordset.
A further comment: rather than doing the self-join, it might be
faster to use a SQL lookup for the L values when you hit an R. I'm
not sure how fast this will be. I assume the columns are all
indexed. If they are, opening a persistent table-type recordset and
using SEEK might be faster. Oh, oops. That's DAO, not ADO. This is a
case where DAO would be substantially faster than ADO, because it
can access the Jet indexes directly, whereas ADO can't. And I
believe ADO's FIND is slower than DAO's FindFirst. But you'd want to
compare to a filtered recordset returning the single matching
record.
snip my whining <g>
Try the self-join and see if you can get it to work.
I'll play with that and see if i can figure it out.
snip
>
Sort of. Makes it sound to me like the self-join is the way to go.
Since all L's will be incremented, you can change what I said above
to just have the join show the L's for the R's. You'd need criteria
like:
SELECT f1, f2, f3, f4, f5, Other.f5, Other.f6
FROM table1 [outer join syntax] table1 As Other
WHERE (Other.f5<>table1.f5 Or Other.f5 Is Null) And table1.f5="R"
Actually, you'd probably need to encapsulate the self-join table as
a subquery or as a virtual table. It could be done like this:
SELECT f1, f2, f3, f4, f5, Other.f5, Other.f6
FROM table1 [outer join syntax] [SELECT table1.f5, table1.f6 FROM
table1 WHERE table1.f5="L"]. As Other WHERE Other.f5<>table1.f5 Or
Other.f5 Is Null
The "[]. As alias" syntax is something many people don't know about.
It's like () in other dialects of SQL, where between the brackets
(square or round) you can have a SQL statement that returns rows.
This allows you to filter it without affecting the number of rows
returned in the main SELECT statement.
It's possible you'll need a DISTINCT on the virtual table, but that
depends on whether a group can have more than one L.
Thanks again for all the time and effort you've put into all this,
sorry i'm making it so difficult.
It's a fascinating problem, seems to me. And I'm convinced there's a
way to do it relying primarily on SQL for the updates.
this is exciting stuff
too bad i'm such a slow learner :-(
Thanks again for all your help and patience on this
Mark
I've been following this thread with interest. I don't have anything to
add to David's advice, but I have built up a huge curiosity as to where
this data is coming from and what the resulting calculated value is used
for. What can you tell us about it?
--
Bri
"Bri" <no*@here.comwrote in message
news:0R50h.192861$5R2.101265@pd7urf3no...
I've been following this thread with interest. I don't have anything to
add to David's advice, but I have built up a huge curiosity as to where
this data is coming from and what the resulting calculated value is used
for. What can you tell us about it?
the data is descriptions of characteristics of objects in an external
program (autocad)
each field is one characteristic
each object must be "grouped" "sorted" "named" "labeled" according to it's
list of characteristcs
the naming process groups the objects into groups based on their common
characteristics.
once the sorting and assigning of the "name" is completed (via the database
sql hopefully)
the object gets identified in the external program with that "name"
hth
Not what I thought at all. I was thinking you were collecting data from
some sort of remote sensing equipment. I did a project like that years
ago, before there was a PC on every desk. Thanks for satisfying my
curiosity. :)
--
Bri
"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@127.0.0. 1...
I think a self-join is going to be required. What you need is all
the L's and an outer join on the first three fields to the same
table, but limited to the R's.
Actually, you'd probably need to encapsulate the self-join table as
a subquery or as a virtual table. It could be done like this:
SELECT f1, f2, f3, f4, f5, Other.f5, Other.f6
FROM table1 [outer join syntax] [SELECT table1.f5, table1.f6 FROM
table1 WHERE table1.f5="L"]. As Other WHERE Other.f5<>table1.f5 Or
Other.f5 Is Null
Hi David,
If you're still watching....
i've been trying to understand the join method but am not getting it.
as a test i'm trying to get all records where t1.fldLength = t2.fldLength
and t1.fldCondLbr = 'L' and t2.fldCondLbr = 'R'
"SELECT fldTypeName, fldCondName, fldCondVar, fldCondLbr, fldLength " & _
"FROM tbltypecond AS T1 " & _
"OUTER JOIN [SELECT T1.fldCondLbr, T1.fldLength, FROM T1 WHERE
T1.fldCondLbr = '2L']. AS T2 " & _
"ON T1.fldLength = T2.fldLength" & _
"WHERE t2.fldCondLbr = '3R' ORDER BY T1.fldLength"
ErrState: -2147217908 <Command text was not set for the command object.>
I've been reading about joins till my head hurts and just not groking it...
:-(
MP wrote:
"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@127.0.0. 1...
>>I think a self-join is going to be required. What you need is all
the L's and an outer join on the first three fields to the same
table, but limited to the R's.
>>Actually, you'd probably need to encapsulate the self-join table as
a subquery or as a virtual table. It could be done like this:
SELECT f1, f2, f3, f4, f5, Other.f5, Other.f6
FROM table1 [outer join syntax] [SELECT table1.f5, table1.f6 FROM
table1 WHERE table1.f5="L"]. As Other WHERE Other.f5<>table1.f5 Or
Other.f5 Is Null
Hi David,
If you're still watching....
i've been trying to understand the join method but am not getting it.
as a test i'm trying to get all records where t1.fldLength = t2.fldLength
and t1.fldCondLbr = 'L' and t2.fldCondLbr = 'R'
"SELECT fldTypeName, fldCondName, fldCondVar, fldCondLbr, fldLength " & _
"FROM tbltypecond AS T1 " & _
"OUTER JOIN [SELECT T1.fldCondLbr, T1.fldLength, FROM T1 WHERE
T1.fldCondLbr = '2L']. AS T2 " & _
"ON T1.fldLength = T2.fldLength" & _
"WHERE t2.fldCondLbr = '3R' ORDER BY T1.fldLength"
ErrState: -2147217908 <Command text was not set for the command object.>
I've been reading about joins till my head hurts and just not groking it...
:-(
Jet does not support OUTER JOIN, you need to use either LEFT JOIN or
RIGHT JOIN.
--
Bri
"Bri" <no*@here.comwrote in message
news:zNM1h.231231$5R2.52872@pd7urf3no...
>
"SELECT fldTypeName, fldCondName, fldCondVar, fldCondLbr, fldLength " &
_
"FROM tbltypecond AS T1 " & _
"OUTER JOIN [SELECT T1.fldCondLbr, T1.fldLength, FROM T1
WHERE
T1.fldCondLbr = '2L']. AS T2 " & _
"ON T1.fldLength = T2.fldLength" & _
"WHERE t2.fldCondLbr = '3R' ORDER BY T1.fldLength"
ErrState: -2147217908 <Command text was not set for the command object.>
I've been reading about joins till my head hurts and just not groking
it...
:-(
Jet does not support OUTER JOIN, you need to use either LEFT JOIN or
RIGHT JOIN.
--
Bri
Thanks I'll try that...other than that the syntax looked right???(highly
doubtful <g>)
"MP" <no****@Thanks.comwrote in
news:3v*******************@tornado.rdc-kc.rr.com:
"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@127.0.0. 1...
>I think a self-join is going to be required. What you need is all
the L's and an outer join on the first three fields to the same
table, but limited to the R's.
>Actually, you'd probably need to encapsulate the self-join table
as a subquery or as a virtual table. It could be done like this:
SELECT f1, f2, f3, f4, f5, Other.f5, Other.f6
FROM table1 [outer join syntax] [SELECT table1.f5, table1.f6 FROM
table1 WHERE table1.f5="L"]. As Other WHERE Other.f5<>table1.f5
Or Other.f5 Is Null
If you're still watching....
I've changed my mind on this. I think you'd be better off with a
crosstab (the SQL with the TRANSFORM in it). This would give you one
row for each 3-column combination, and then as many columns as it
takes to list the values. I don't know if you'd want to use full
values for the columns, or just Mid([field],2) (to skip the numeric
part). But I think that will give you a top-level recordset that
tells you what you need to know about whether or not there are Rs
and Ls in the resultset.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@127.0.0. 1...
"MP" <no****@Thanks.comwrote in
news:3v*******************@tornado.rdc-kc.rr.com:
"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@127.0.0. 1...
I think a self-join is going to be required. What you need is all
the L's and an outer join on the first three fields to the same
table, but limited to the R's.
Actually, you'd probably need to encapsulate the self-join table
as a subquery or as a virtual table. It could be done like this:
SELECT f1, f2, f3, f4, f5, Other.f5, Other.f6
FROM table1 [outer join syntax] [SELECT table1.f5, table1.f6 FROM
table1 WHERE table1.f5="L"]. As Other WHERE Other.f5<>table1.f5
Or Other.f5 Is Null
If you're still watching....
I've changed my mind on this. I think you'd be better off with a
crosstab (the SQL with the TRANSFORM in it). This would give you one
row for each 3-column combination, and then as many columns as it
takes to list the values. I don't know if you'd want to use full
values for the columns, or just Mid([field],2) (to skip the numeric
part). But I think that will give you a top-level recordset that
tells you what you need to know about whether or not there are Rs
and Ls in the resultset.
I'll try and see if i can figure that one out...
not doing so well on the joins yet...:-(
:-)
think i need to join a few more brain cells between my ears
<g>
Since you don't have the benefit of the Access Help file (A97), here is
what it has to say about the Left/Right Joins. This should help you to
understand how they work which will help you determine if it is what you
need.
--
Bri
Syntax
FROM table1 [ LEFT | RIGHT ] JOIN table2
ON table1.field1 compopr table2.field2
The LEFT JOIN and RIGHT JOIN operations have these parts:
Part Description
table1, table2 The names of the tables from which records are combined.
field1, field2 The names of the fields that are joined. The fields must
be of the same data type and contain the same kind of data, but they
don't need to have the same name.
compopr Any relational comparison operator: "=," "<," ">," "<=," ">=,"
or "<>."
Remarks
Use a LEFT JOIN operation to create a left outer join. Left outer joins
include all of the records from the first (left) of two tables, even if
there are no matching values for records in the second (right) table.
Use a RIGHT JOIN operation to create a right outer join. Right outer
joins include all of the records from the second (right) of two tables,
even if there are no matching values for records in the first (left) table.
For example, you could use LEFT JOIN with the Departments (left) and
Employees (right) tables to select all departments, including those that
have no employees assigned to them. To select all employees, including
those who aren't assigned to a department, you would use RIGHT JOIN.
The following example shows how you could join the Categories and
Products tables on the CategoryID field. The query produces a list of
all categories, including those that contain no products:
SELECT CategoryName,
ProductName
FROM Categories LEFT JOIN Products
ON Categories.CategoryID = Products.CategoryID;
In this example, CategoryID is the joined field, but it isn't included
in the query results because it isn't included in the SELECT statement.
To include the joined field, enter the field name in the SELECT
statement — in this case, Categories.CategoryID.
Notes
· To create a query that includes only records in which the data in the
joined fields is the same, use an INNER JOIN operation.
· A LEFT JOIN or a RIGHT JOIN can be nested inside an INNER JOIN, but an
INNER JOIN cannot be nested inside a LEFT JOIN or a RIGHT JOIN. See the
discussion of nesting in the INNER JOIN topic to see how to nest joins
within other joins.
· You can link multiple ON clauses. See the discussion of clause linking
in the INNER JOIN topic to see how this is done.
· If you try to join fields containing Memo or OLE Object data, an error
occurs.
"Bri" <no*@here.comwrote in message
news:i952h.236401$5R2.106907@pd7urf3no...
Since you don't have the benefit of the Access Help file (A97), here is
what it has to say about the Left/Right Joins. This should help you to
understand how they work which will help you determine if it is what you
need.
--
Bri
Syntax
FROM table1 [ LEFT | RIGHT ] JOIN table2
ON table1.field1 compopr table2.field2
The LEFT JOIN and RIGHT JOIN operations have these parts:
Thanks for that Bri,
I wouldn't mind seeing the version form inner join mentioned in
that...:-)....if you were so inclined...
I *sortof kindof almost* understand the 'principle' of the join syntax <g>
I can follow the simple samples in that description as well as a dozen other
samples/tutorials I've been studying
(though that one was pretty clear, thanks)
What I'm having trouble understanding is how to apply it to my problem
All the examples show working with more than one table
In my case I have only one table so need to create a "Select xxx As Alias"
somehow to use as "table2"
That's one stumbling block in my understanding is figuring out what select
to use for 'table2" to arrive at the results I need (per my previous posts
ad nauseum <g>...sorry Lyle...)
I've been trying to follow Davids' hints about what structures might work. -
joins - transforms - etc
Just not getting it real quick.
I'll keep studying though, meanwhile still refining the code stepping
through recordsets with complex select case statements to do the "grouping"
i need....almost there...
too bad my "real job" keeps getting in the way!
:-)
thanks for any pointers anyone may have.
Mark
MP wrote:
Thanks for that Bri,
I wouldn't mind seeing the version form inner join mentioned in
that...:-)....if you were so inclined...
I'll attach that to this post. I'll do a separate post to respond to the
rest of your post later if I get the time. But for now, here is the A97
Help on Inner Join:
Combines records from two tables whenever there are matching values in a
common field.
Syntax
FROM table1 INNER JOIN table2 ON table1.field1 compopr table2.field2
The INNER JOIN operation has these parts:
Part Description
table1, table2 The names of the tables from which records are combined.
field1, field2 The names of the fields that are joined. If they aren't
numeric, the fields must be of the same data type and contain the same
kind of data, but they don't have to have the same name.
compopr Any relational comparison operator: "=," "<," ">," "<=," ">=,"
or "<>."
Remarks:
You can use an INNER JOIN operation in any FROM clause. This is the most
common type of join. Inner joins combine records from two tables
whenever there are matching values in a field common to both tables.
You can use INNER JOIN with the Departments and Employees tables to
select all the employees in each department. In contrast, to select all
departments (even if some have no employees assigned to them) or all
employees (even if some aren't assigned to a department), you can use a
LEFT JOIN or RIGHT JOIN operation to create an outer join.
If you try to join fields containing Memo or OLE Object data, an error
occurs.
You can join any two numeric fields of like types. For example, you can
join on AutoNumber and Long fields because they are like types. However,
you cannot join Single and Double types of fields.
The following example shows how you could join the Categories and
Products tables on the CategoryID field:
SELECT CategoryName, ProductName
FROM Categories INNER JOIN Products
ON Categories.CategoryID = Products.CategoryID;
In the preceding example, CategoryID is the joined field, but it isn't
included in the query output because it isn't included in the SELECT
statement. To include the joined field, include the field name in the
SELECT statement ¾ in this case, Categories.CategoryID.
You can also link several ON clauses in a JOIN statement, using the
following syntax:
SELECT fields
FROM table1 INNER JOIN table2
ON table1.field1 compopr table2.field1 AND
ON table1.field2 compopr table2.field2) OR
ON table1.field3 compopr table2.field3)];
You can also nest JOIN statements using the following syntax:
SELECT fields
FROM table1 INNER JOIN
(table2 INNER JOIN [( ]table3
[INNER JOIN [( ]tablex [INNER JOIN ...)]
ON table3.field3 compopr tablex.fieldx)]
ON table2.field2 compopr table3.field3)
ON table1.field1 compopr table2.field2;
A LEFT JOIN or a RIGHT JOIN may be nested inside an INNER JOIN, but an
INNER JOIN may not be nested inside a LEFT JOIN or a RIGHT JOIN.
--
Bri This thread has been closed and replies have been disabled. Please start a new discussion. |
