Re: Ranking, subqueries and update query problem

Thanks for promoting me to MVP <bg>, but I am not Steve Schapel. I am a
different Steve, Steve Sanford. I wish I *was* at the level of Steve Schapel
and the other MVP's.

(big sigh) Maybe in my next lifetime I will be a MVP.... one can always
dream...

--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


"Carlos" wrote:

> Steve,
>
> >Glad it is helping. But is it faster??
>
> Really fast, from couple of minutes using my orig design down to < 5
> seconds. Indeed a big help!!!
>
> >'change this line
> > !E = var_E * (!Variance <= 0.155) * -1 '*****
> > .Update
>
> First !E gives a value of 2 if >15.5 so I add one more line similar to !B if
> I am not wrong.
> var_E = var_E + (!Variance > 0.155) '*******
>
> Nice explanation about that evaluation. I guess I need to go back to my
> basic algebra.
>
> As a token of appreciation I include in the new code:
>
> Private Sub RankRecord()
> ‘Courtesy of Steve Schapel, Microsoft Access MVP
> ‘(and to his high school math teacher Mr. Ferris)
> ‘In: microsoft.public.access.queries
> ‘Re: Ranking, subqueries and update query problem
> ‘7/17/2005 9:21 PM PST
>
> Again thanks a lot and God bless.
>
> Carlos
>
>
>
> "SteveS" wrote:
>
> > Carlos,
> >
> > Glad it is helping. But is it faster?? See below for modified code for B
> > and E.
> >
> > > equations for this condition but not successful. Please excuse me for being
> > > so dull interpreting your equations especially the use of * and -1. I wonder
> > > you don’t use any If functions at all.
> > >
> >
> > A lot of people have a problem the first time they see this. I had a really,
> > really good math teacher in high school (thanks Mr. Ferris) who liked to give
> > us mind benders, like proving algebraically that 2 = 1.
> >
> >
> > If you know X * 0 = 0 and X * 1 = X then you evaluate the formula
> > !B = var_B * ((!Variance <= 0.155) * -1) like this:
> >
> > IF var_b = 10 and !Variance = .100 then
> >
> > !B = var_B * ((!Variance <= 0.155) * -1)
> > !B = 10 * ((.100 <= 0.155) * -1)
> > !B = 10 * ((TRUE) * -1)
> > !B = 10 * ((-1) * -1)
> > !B = 10 * (1)
> > !B = 10
> >
> >
> > IF var_b = 10 and !Variance = .300 then we have
> >
> > !B = var_B * ((!Variance <= 0.155) * -1)
> > !B = 10 * ((.300 <= 0.155) * -1)
> > !B = 10 * ((FALSE) * -1)
> > !B = 10 * ((0) * -1)
> > !B = 10 * (0)
> > !B = 0
> >
> >
> > This works you want the number *OR* a zero.
> >
> >
> > You can also use the same idea to change a string to a number, like "JAN" to
> > a 1, etc. Instead of nested IF()s or a SELECT CASE(), you could use:
> >
> > str = "FEB"
> >
> > MTH = 1*(str = "JAN") + 2*(str = "FEB") + 3*(str = "MAR") + ... + 12*(str =
> > "DEC")
> > MTH = MTH * -1 //change to postitve number
> >
> > Since only one condition can be true, you get
> >
> > MTH = 0 + -2 + 0 + 0 + 0 + ... + 0
> > MTH = -2 * -1
> > MTH = 2 //"FEB"
> >
> >
> > OK, enough of that! Change 2 lines and add one line.
> > Here is the modified code for B & E:
> >
> > '*** begin modified code snippet ***
> >
> > With rst
> > 'first record
> > .MoveFirst
> > .Edit
> > !DateGap = Null
> > 'first record is always 1? not for B & E
> >
> > 'change this line
> > !B = var_B * ((!Variance <= 0.155) * -1) '******
> > 'add this line
> > var_B = var_B + (!Variance > 0.155) '*******
> >
> > !C = var_C
> > !D = var_D
> >
> > 'change this line
> > !E = var_E * (!Variance <= 0.155) * -1 '*****
> > .Update
> >
> > '***end code snippet ***
> >
> > HTH
> > --
> > Steve S
> > --------------------------------
> > "Veni, Vidi, Velcro"
> > (I came; I saw; I stuck around.)
> >
> >
> > "Carlos" wrote:
> >
> > > SteveS,
> > >
> > > Really, really great…. Thanks for that brilliant approach.
> > >
> > > Your quote:
> > > > ‘Original rank
> > > > .Edit
> > > > !Record = Me!frmEditSub.Form. CurrentRecord
> > > > .Update
> > >
> > > >I wasn't sure what you were doing here so I didn't code it.
> > >
> > > This is updating the field !Record after refreshing the qryEdit which is
> > > basically sort by date plus few other fields. I managed to update !Record
> > > using your technique.
> > >
> > > Your quote:
> > > > 'first record is always 1?
> > >
> > > Yes for C and D, but not for B and E. B and E should be 0 if Variance is
> > > over 15.5% even if they are in the first row and should only start to 1 for
> > > the first record with Variance less 15.5%. I’m trying to manipulate your
> > > equations for this condition but not successful. Please excuse me for being
> > > so dull interpreting your equations especially the use of * and -1. I wonder
> > > you don’t use any If functions at all.
> > >
> > > Like I said this is excellent. I would start to adopt this asap.
> > >
> > > Thanks and best regards.
> > >
> > > Carlos
> > >
> > >
> > > "SteveS" wrote:
> > >
> > > > "Carlos" wrote:
> > > >
> > > > > John / Steve,
> > > > >
> > > > > First of all thanks for your advices. Now it is clear to me that such
> > > > > subqueries won't work to update the records.
> > > > >
> > > > > You have one common suggestion, i.e., to use codes and this query as a
> > > > > recordset in the VBA. In fact I'm still using this approach with the original
> > > > > design of my database and trying to redesign it with this sort of subqueries
> > > > > to speed up processing, but unfortunately seems impossible.
> > > > >
> > > > > Allow me to show the procedure I use to update records and perhaps you can
> > > > > suggest improvement. I use so far DMax and DLookup functions:
> > > > >
> > > > > Private Sub RankRecord()
> > > > >
> > > > > Dim rst As Object
> > > > > Set rst = Me!frmEditSub.Form.Recordset 'subform source is qryEdit
> > > > >
> > > > > With rst
> > > > >
> > > > > .MoveFirst ' for full update or I can use .FindFirst ....criteria here
> > > > >
> > > > > Do
> > > > >
> > > > > ‘Original rank
> > > > > .Edit
> > > > > !Record = Me!frmEditSub.Form. CurrentRecord
> > > > > .Update
> > > > >
> > > > > ‘Days gap between current and previous dates
> > > > > .Edit
> > > > > !DateGap = !DateCast - DLookup ("DateCast", "qryEdit", "Record = " &
> > > > > !Record - 1)
> > > > > .Update
> > > > >
> > > > > ‘Rank to ignore Variance over 15.5%
> > > > > .Edit
> > > > > If !Variance <= 0.155 Then
> > > > > !B = DMax("B", "qryEdit", "Record < " & !Record) + 1
> > > > > Else
> > > > > !B = 0
> > > > > .Update
> > > > >
> > > > > ‘Group rank
> > > > > .Edit
> > > > > If !DateGap > 14 Then
> > > > > !C = DLookup("C", "qryEdit", "Record = " & !Record - 1) + 1
> > > > > Else
> > > > > !C = DLookup("C", "qryEdit", "Record = " & !Record - 1)
> > > > > .Update
> > > > >
> > > > > ‘etc. for D, E & F
> > > > >
> > > > > .MoveNext
> > > > > Loop Until .EOF
> > > > >
> > > > > End Sub
> > > > >
> > > > > As I mentioned earlier, updating is very slow but results are perfect. A set
> > > > > of around 1000 records would take more than a minute to process from BOF to
> > > > > EOF. I would have even more records to come very soon.
> > > > >
> > > > > Any more advice will be much appreciated.
> > > > >
> > > > > Thanks
> > > > >
> > > > > Carlos
> > > > >
> > > >
> > > >
> > > > Carlos,
> > > >
> > > > I don't know your table structure of what "qryEdit" looks like, but given
> > > > the data you posted, I set up a table and a query. Then I tried to do what
> > > > you were doing but without using COUNT, LOOKUP, DMAX,...
> > > >
> > > > > ‘Original rank
> > > > > .Edit
> > > > > !Record = Me!frmEditSub.Form. CurrentRecord
> > > > > .Update
> > > >
> > > > I wasn't sure what you were doing here so I didn't code it.
> > > >
> > > > In the query "qryEdit", I set the sorting to the field "RECORD" asc.
> > > >
> > > > Maybe this will help you....
> > > >
> > > > The code below matched the data you posted...as far as I can tell.
> > > >
> > > > '*** begin code*****
> > > > Private Sub RankRecord()
> > > > Dim rst As dao.Recordset
> > > > Dim varDC As Date
> > > > Dim var_B As Integer
> > > > Dim var_C As Integer
> > > > Dim Last_C As Integer
> > > > Dim var_D As Integer
> > > > Dim var_E As Integer
> > > >
> > > > Set rst = CurrentDb.OpenRecordset("qryEdit")
> > > >
> > > > var_B = 1
> > > > var_C = 1
> > > > var_D = 1
> > > > var_E = 1
> > > > Last_C = 1
> > > >
> > > > With rst
> > > > 'first record
> > > > .MoveFirst
> > > > .Edit
> > > > !DateGap = Null
> > > > 'first record is always 1?
> > > > !B = var_B
> > > > !C = var_C
> > > > !D = var_D
> > > > !E = var_E
> > > > .Update
> > > > varDC = !DateCast
> > > > .MoveNext
> > > > 'loop thru the rest of the records
> > > > Do
> > > > .Edit
> > > >
> > > > 'calc days gap
> > > > 'Days gap between current and previous dates
> > > > !DateGap = !DateCast - varDC
> > > > 'calc B
> > > > var_B = var_B + (!Variance <= 0.155) * -1
> > > > !B = var_B * ((!Variance <= 0.155) * -1)
> > > > 'calc C
> > > > var_C = var_C + ((!DateGap.Value > 14) * -1)
> > > > !C = var_C
> > > > 'calc D
> > > > var_D = var_D * (var_C = Last_C) * -1 + 1
.

Relevant Pages

  • Re: Ranking, subqueries and update query problem
    ... ‘Courtesy of Steve Schapel, Microsoft Access MVP ... Carlos ... > Glad it is helping. ... >>> Dim rst As dao.Recordset ...
    (microsoft.public.access.queries)
  • Re: [opensuse] (OT) Charter Communications - refusal to correct a bill
    ... to the VP Steve ... I wonder what this has to do with Linux or openSUSE? ... Carlos E. R. ...
    (SuSE)
  • Re: [SLE] user and group problem [was:NIS database data too long]
    ... On Monday 26 January 2004 02:02, Carlos E. R. wrote: ... OK but I can't fit 160 users into the eg the uucp group. ... are steve and invitado now members of the uucp group? ... Check the headers for your unsubscription address For additional commands send e-mail to suse-linux-e-help@suse.com Also check the archives at http://lists.suse.com Please read the FAQs: suse-linux-e-faq@suse.com ...
    (SuSE)
  • Re: Mirroring Server
    ... > Excuse my ignorance but what do you mean with SA? ... >>> Carlos ...
    (microsoft.public.windows.server.sbs)
  • Re: Krutchless Kelly SOL
    ... Dave, Dave, Dave. ... Say high to Steve for me and my friend. ... gonna see and hear more from my old friend Martin Fierro than I will ... Now if you'll EXCUSE me, The Love Boat Christmas Marathon is in the DVD ...
    (rec.music.gdead)
Quantcast