Ranking, subqueries and update query problem

G

Guest

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
 
G

Guest

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 said:
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
Click to expand...
 
G

Guest

Karl,

Thanks for the advice. I have tried this already for one field (the easiest
is DateGap) but the same error.

KARL DEWEY said:
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 said:
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
Click to expand...
Click to expand...
 
S

Steve Schapel

Carlos,

I am sorry, at this stage I can't suggest a specific solution to achieve
the results you want. But it would appear that your qryEdit query is
non-updateable. There are a number of factors that determine whether a
query is updateable or not. For example, the use of Domain functions,
joins on a non-unique field, one-to-many-to-many relationship
configurations, are often implicated in a query being non-updateable.
There is some good information about this in Access Help. If you can't
find a way to make an updateable query that you can use, you may need to
consider opening a recordset in code, and updating the data field by
field and record by record.

--
Steve Schapel, Microsoft Access MVP

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
Click to expand...
 
J

John Spencer (MVP)

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.
Karl,

Thanks for the advice. I have tried this already for one field (the easiest
is DateGap) but the same error.

KARL DEWEY said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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) said:
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.
Karl,

Thanks for the advice. I have tried this already for one field (the easiest
is DateGap) but the same error.

KARL DEWEY said:
Try running a separate update query for each field instead of trying to do it
all at once.
Build a little - test a little.

:

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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Carlos said:
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
Click to expand...


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
Click to expand...

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
!D = var_D
'calcE
var_E = var_E * (var_C = Last_C) * -1 + 1 * (!Variance <= 0.155)
* -1
!E = var_E * (!Variance <= 0.155) * -1

.Update

'update variables
Last_C = var_C
varDC = !DateCast

.MoveNext
Loop Until .EOF
End With

'clean up
rst.Close
Set rst = Nothing

Me.Refresh 'debugging

'MsgBox "done"

End Sub
'**** end code ****

HTH
 
G

Guest

SteveS,

Really, really great…. Thanks for that brilliant approach.

Your quote:
‘Original rank
.Edit
!Record = Me!frmEditSub.Form. CurrentRecord
.Update
Click to expand...
I wasn't sure what you were doing here so I didn't code it.
Click to expand...

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?
Click to expand...

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
 
G

Guest

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.
Click to expand...

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
 
G

Guest

Steve,
Glad it is helping. But is it faster??
Click to expand...

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
Click to expand...

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
 
G

Guest

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...
 
G

Guest

My apology I jumbled your names. I wish to see your name in the future with a
big bold MVP attached.

Cheers.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Access Running Balance in Access 1
Running Balance (Debit and Credit) in Access 2007 4
Advanced problem using auto-number and datediff to find readmissio 1
Query update table 2
Help with a troublesome Query - Think it needs embeded SubQueries 5
The right person for the job 9
Employee Availblility 3
Looping through data with calculations 1

Top