Re: Ranking, subqueries and update query problem
- From: Carlos <Carlos@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Sat, 16 Jul 2005 10:28:01 -0700
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
"John Spencer (MVP)" wrote:
> Since you are using the COUNT function in the subqueries you can't use UPDATE.
>
> You can Append this information to a temp table and then use that temp table to
> update your main table.
>
> OR
>
> You can use VBA and this query as a recordset in the VBA and update the
> individual records using the values of the fields returned in the recordset
>
> OR
>
> You can just append this to a temp table and join the temp table to the main
> table/query as needed.
>
> That said- unless your data is very stable you are running the risk of having
> incorrect information if you fail to update this information when you need it.
> You may be better off living with the slowness of this particular process. That
> is, of course, a decision you need to make based on the volatility of your data.
>
> Carlos wrote:
> >
> > Karl,
> >
> > Thanks for the advice. I have tried this already for one field (the easiest
> > is DateGap) but the same error.
> >
> > "KARL DEWEY" wrote:
> >
> > > Try running a separate update query for each field instead of trying to do it
> > > all at once.
> > > Build a little - test a little.
> > >
> > > "Carlos" wrote:
> > >
> > > > Hi,
> > > >
> > > > It's me again. I posted similar question for quite sometime last year about
> > > > ranking and this time with few more other ranking problems (sub-sub-sub
> > > > query) plus to update the records base on this ranking. I could do simple
> > > > update query but got stuck with this one.
> > > >
> > > > Here it goes: a set of records from query “qryEditâ€* is arranged below having
> > > > few fields exposed (Record, Variance, DateCast, DateGap, B, C, D & E).
> > > >
> > > > My goal is to update set of records in the table for fields DateGap, B, C, D
> > > > and E base on the 3 fields to the left. The set of records base on correlated
> > > > subqueries would give the ranking values as shown below: (I hope the table
> > > > below is readable)
> > > >
> > > > Record Variance DateCast DateGap B C D E
> > > > 1 5.86% 28/08/03 1 1 1 1
> > > > 2 0.00% 16/09/03 19 2 2 1 1
> > > > 3 4.55% 14/01/04 120 3 3 1 1
> > > > 4 4.38% 16/01/04 2 4 3 2 2
> > > > 5 4.44% 04/11/04 293 5 4 1 1
> > > > 6 1.74% 04/11/04 0 6 4 2 2
> > > > 7 6.33% 08/11/04 4 7 4 3 3
> > > > 8 2.96% 10/11/04 2 8 4 4 4
> > > > 9 4.83% 10/11/04 0 9 4 5 5
> > > > 10 2.08% 10/11/04 0 10 4 6 6
> > > > 11 10.53% 10/11/04 0 11 4 7 7
> > > > 12 1.98% 10/11/04 0 12 4 8 8
> > > > 13 3.70% 10/11/04 0 13 4 9 9
> > > > 14 1.55% 12/01/05 63 14 5 1 1
> > > > 15 6.70% 17/01/05 5 15 5 2 2
> > > > 16 15.96% 17/01/05 0 0 5 3 0
> > > > 17 1.92% 17/01/05 0 16 5 4 3
> > > > 18 7.41% 17/01/05 0 17 5 5 4
> > > > 19 5.71% 17/01/05 0 18 5 6 5
> > > > 20 2.82% 17/01/05 0 19 5 7 6
> > > > 21 3.95% 26/01/05 9 20 5 8 7
> > > > 22 3.56% 26/01/05 0 21 5 9 8
> > > > 23 2.86% 26/01/05 0 22 5 10 9
> > > > 24 1.53% 26/01/05 0 23 5 11 10
> > > > 25 4.58% 01/02/05 6 24 5 12 11
> > > > 26 1.48% 01/02/05 0 25 5 13 12
> > > > 27 6.64% 01/02/05 0 26 5 14 13
> > > > 28 4.48% 01/02/05 0 27 5 15 14
> > > > 29 12.60% 02/02/05 1 28 5 16 15
> > > > 30 4.33% 02/02/05 0 29 5 17 16
> > > > 31 2.37% 02/02/05 0 30 5 18 17
> > > > 32 0.00% 02/02/05 0 31 5 19 18
> > > > 33 2.37% 02/02/05 0 32 5 20 19
> > > > 34 0.85% 07/02/05 5 33 5 21 20
> > > > 35 4.29% 07/02/05 0 34 5 22 21
> > > > 36 8.55% 10/05/05 92 35 6 1 1
> > > >
> > > > Subqueries:
> > > >
> > > > DateGap is counting the lapsed day between the current and the previous date.
> > > > DateGap: [DateCast]-(SELECT (DateCast) FROM qryEdit AS X WHERE X.Record =
> > > > qryEdit.Record - 1)
> > > >
> > > > B is ranking record ignoring Variance over 15.5%
> > > > B: IIf([Variance]>0.155,0,(SELECT Count (*)+1 FROM qryEdit AS X WHERE
> > > > (X.Record < qryEdit.Record And X.Variance <=0.155)))
> > > >
> > > > C is group ranking for each lapsed day over 14 days
> > > > C: (SELECT Count (*)+1 FROM qryEdit AS X WHERE X.Record <= qryEdit.Record
> > > > And ([x.DateCast]-(SELECT (DateCast) FROM qryEdit AS Y WHERE Y.Record =
> > > > x.Record - 1))>14)
> > > >
> > > > D is a new ranking base on the group ranking. Please note that field Grp
> > > > below is equivalent to C above which I couldn’t visualize to insert the whole
> > > > subquery of C below. Grp values are the existing group numbers which I enter
> > > > manually in the table.
> > > > D: (SELECT Count (*) FROM qryEdit AS X WHERE X.Grp = qryEdit.Grp And
> > > > x.Record <= qryEdit.Record)
> > > >
> > > > E is a new ranking base on the group ranking and ignoring Variance over 15%
> > > > E: IIf([Variance]>0.155,0,(SELECT Count (*)+1 FROM qryEdit AS X WHERE
> > > > (X.Record < qryEdit.Record And X.Variance <=0.155 And X.Grp = qryEdit.Grp)))
> > > >
> > > > The above subqueries give me the exact ranking I want. I observed that
> > > > updating these resulted values in the table rather than calculated fields in
> > > > the query as source of report/form could speed-up my application.
> > > >
> > > > I view the query and it works very, very fast but running it gives me this
> > > > Error: “Operation must use an updateable queryâ€*. Here is the full query:
> > > >
> > > > UPDATE DISTINCTROW qryEdit SET qryEdit.DateGap = [DateCast]-(SELECT
> > > > (DateCast) FROM qryEdit AS X WHERE X.Record = qryEdit.Record - 1),
> > > > qryEdit.RecordValid = IIf([Variance]>0.155,0,(SELECT Count (*)+1 FROM qryEdit
> > > > AS X WHERE (X.Record < qryEdit.Record And X.Variance <=0.155))), qryEdit.Grp
> > > > = (SELECT Count (*)+1 FROM qryEdit AS X WHERE X.Record <= qryEdit.Record And
> > > > ([x.DateCast]-(SELECT (DateCast) FROM qryEdit AS Y WHERE Y.Record = x.Record
> > > > - 1))>14), qryEdit.GrpRecord = (SELECT Count (*) FROM qryEdit AS X WHERE
> > > > X.Grp = qryEdit.Grp And x.Record <= qryEdit.Record), qryEdit.GrpRecordValid =
> > > > IIf([Variance]>0.155,0,(SELECT Count (*)+1 FROM qryEdit AS X WHERE (X.Record
> > > > < qryEdit.Record And X.Variance <=0.155 And X.Grp = qryEdit.Grp)));
> > > >
> > > > What is the cause of error with this kind of subqueries. Could someone
> > > > please advise? Or any other idea how to accomplish this kind of ranking. I
> > > > hope this won’t be too annoying to the readers of this long message.
> > > >
> > > > Thanks in advance.
> > > >
> > > > Carlos
> > > >
> > > >
> > > >
>
.
- Follow-Ups:
- Re: Ranking, subqueries and update query problem
- From: SteveS
- Re: Ranking, subqueries and update query problem
- References:
- Ranking, subqueries and update query problem
- From: Carlos
- RE: Ranking, subqueries and update query problem
- From: KARL DEWEY
- RE: Ranking, subqueries and update query problem
- From: Carlos
- Re: Ranking, subqueries and update query problem
- From: John Spencer (MVP)
- Ranking, subqueries and update query problem
- Prev by Date: Putting multiple fields into one field
- Next by Date: Re: query only returns values if criteria cut-and-paste
- Previous by thread: Re: Ranking, subqueries and update query problem
- Next by thread: Re: Ranking, subqueries and update query problem
- Index(es):
Relevant Pages
|
