C
Carlos
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
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