Hi
trying to begin to learn database using vb6, ado/adox, mdb format, sql
(not using access...just mdb format via ado)
i need to group the values of multiple fields
- get their possible variations(comb ination of fields),
- then act on each group in some way ...eg ProcessRs (oRs as RecordSet)...
the following query will get me the distinct groups
strSQL = "SELECT DISTINCT Field0, Field1, Field2, Field3 From " & TABLE_NAME
& _
" ORDER BY Field0, Field1, Field2, Field3"
Set oRs1 = moConn.Execute( strSQL)
Do While Not oRs1.EOF
Debug.Print "Group " & i
j = 0
For Each oFld In oRs1t.Fields
Debug.Print "Field" & j & vbTab & "-Value: " & oFld.Value
j = j + 1
Next oFld
oRs1.MoveNext
i = i + 1
Loop
....results in ....
Group 0
Field0-Value:A;Field1-Value:1;Field2-Value:0;Field3-Value:1NA;
Group 1
Field0-Value:A;Field1-Value:1;Field2-Value:1;Field3-Value:1NA;
Group 2
Field0-Value:A;Field1-Value:1;Field2-Value:1;Field3-Value:2L;
Group 3
Field0-Value:A;Field1-Value:1;Field2-Value:1;Field3-Value:3R;
Group 4
Field0-Value:A;Field1-Value:1;Field2-Value:1;Field3-Value:4B;
Group 5
Field0-Value:A;Field1-Value:1;Field2-Value:2;Field3-Value:1NA;
Group 6
Field0-Value:A;Field1-Value:1;Field2-Value:3;Field3-Value:1NA;
Group 7
Field0-Value:A;Field1-Value:1;Field2-Value:4;Field3-Value:1NA;
so I have 7 groups to process
do i need to loop back through the database 7 times to get all records for
each group?
Process Group0
Select * From Table_Name Where Field0 = A and Field1 = 1 and Field2 = 0 and
Field3 = 1Na
Process Group1
Select * From Table_Name Where Field0 = A and Field1 = 1 and Field2 = 1 and
Field3 = 1Na
Process Group2
Select * From Table_Name Where Field0 = A and Field1 = 1 and Field2 = 1 and
Field3 = 2L
Process Group3
Select * From Table_Name Where Field0 = A and Field1 = 1 and Field2 = 1 and
Field3 = 3R
Process Group4
Select * From Table_Name Where Field0 = A and Field1 = 1 and Field2 = 1 and
Field3 = 4B
....etc....
or is there some way to combine the grouping, and subsequent processing of
each distinct recordset group, all in one sql statement?
is there a one-step way to divide the recordset in that way?...some how
using aliases and joins when there's only one table???
something like this seems to work for the multiple loop way
Dim oRs2 As Recordset
Dim oFld2 As Field
'get the distinct group list
strSQL = "SELECT distinct fldTypeName, fldCondName, fldCondVar, fldCondLbr
From " & TABLE_NAME & _
" ORDER BY fldTypeName, fldCondName, fldcondvar, fldCondLbr"
Set oRs1 = moConn.Execute( strSQL)
If Not oRs1 Is Nothing Then
Do While Not oRs1.EOF
j = 0
strSQL = "Select * From " & TABLE_NAME & " WHERE "
For Each oFld In oRs1.Fields
strSQL = strSQL & oFld.Name & " = '" & oFld.Value & "' AND "
j = j + 1
Next oFld
'REMOVE LAST AND
strSQL = Left$(strSQL, Len(strSQL) - 4)
Set oRs2 = moConn.Execute( strSQL)
Do While Not oRs2.EOF
ProcessInnerGro up oRs2
oRs2.MoveNext
Loop
oRs1.MoveNext
i = i + 1
Loop
Thanks for any pointers.
Mark
Oct 17 '06
52  6348 
"David W. Fenton" <XX*******@dfen ton.com.invalid wrote in message
news:Xn******** *************** **********@127. 0.0.1...
"MP" <no****@Thanks. comwrote in
news:40******** **********@torn ado.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******** ***********@tor nado.rdc-kc.rr.com:
"David W. Fenton" <XX*******@dfen ton.com.invalid wrote in message
news:Xn******** *************** **********@127. 0.0.1...
>"MP" <no****@Thanks. comwrote in
news:40******* ***********@tor nado.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*******@dfen ton.com.invalid wrote in message
news:Xn******** *************** ***********@127 .0.0.1...
"MP" <no****@Thanks. comwrote in
news:Qu******** ***********@tor nado.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*******@dfen ton.com.invalid wrote 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******** **********@torn ado.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******** **********@torn ado.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*******@dfen ton.com.invalid wrote in message
news:Xn******** *************** ***********@127 .0.0.1...
>"MP" <no****@Thanks. comwrote in
news:Qu******* ************@to rnado.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*******@dfen ton.com.invalid wrote in message
news:Xn******** *************** ***********@127 .0.0.1...
"MP" <no****@Thanks. comwrote in
news:p0******** **********@torn ado.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******** **********@torn ado.rdc-kc.rr.com:
David W. Fenton" <XX*******@dfen ton.com.invalid wrote in message
news:Xn******** *************** ***********@127 .0.0.1...
>"MP" <no****@Thanks. comwrote in
news:p0******* ***********@tor nado.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<>tabl e1.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<>table 1.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*******@dfen ton.com.invalid wrote 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<>tabl e1.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<>table 1.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
This thread has been closed and replies have been disabled. Please start a new discussion. |
