change to UPDATE query

[Guest]

I have the following query:

SELECT Tbl_archive.[Loan Acct #], Tbl_archive.status AS thurs_status,
DLookUp("[status]","tbl_archive","[PopEnterDt] = #" & Date()-2 & "# AND [loan
acct #] = " & [loan acct #]) AS wed_status, IIf([wed_status]<>"chargeoff" And
[thurs_status]="chargeoff" And [CHG OFF DATE] Is Not
Null,"CHARGEOFF",IIf([WED_STATUS]<>"BK" And [THURS_STATUS]="BK" And [ACTIVE
INACTIVE FLAG]="A","BK",IIf([WED_STATUS]<>"PAIDOFF" And
[THURS_STATUS]="PAIDOFF" And [PAID OFF?]=1 And [PAYOFF DATE] Is Not
Null,"PAIDOFF",IIf([WED_STATUS]<>"REPO" And [THURS_STATUS]="REPO" And
[INVENTORY STATUS] In ("IN","SO"),"REPO",IIf([WED_STATUS] Like "PASTDUE*" And
[THURS_STATUS]="CURRENT" And ([AMENDED THIS MO?]=1 Or [EXTENDED THIS MO?]=1)
And [PAID OFF?]=0 And [ACTIVE INACTIVE FLAG]="I","CURRENT BKAMD
DEF",IIf([WED_STATUS] Like "PASTDUE*" And [THURS_STATUS]="CURRENT" And
([AMENDED THIS MO?]=0 Or [EXTENDED THIS MO?]=0) And [PAID OFF?]=0,"CURRENT
PAID",IIf([WED_STATUS] Like "PASTDUE*" And [THURS_STATUS]="CURRENT" And
([AMENDED THIS MO?]=1 Or [EXTENDED THIS MO?]=1) And [PAID OFF?]=0,"CURRENT
AMD DEFR",""))))))) AS possible_pasted, Tbl_archive.[chg off date],
Tbl_archive.[active bk flag], Tbl_archive.[active inactive flag],
Tbl_archive.[paid off?], Tbl_archive.[payoff date], Tbl_archive.[inventory
status], Tbl_archive.[amended this mo?], Tbl_archive.[extended this mo?]
FROM Tbl_archive
WHERE (((Tbl_archive.PopEnterDt)=Date()-1));

I need to change it to an UPDATE query, in which the [pasted] value for each
record is updated by the value of [possible_pasted] for each row. ALL in
tbl_archive.

thanks in advance,
geebee

 

[Michel Walsh]

Hi,


I assume that query is itself updateable, is it not, right now? so, if you
save it as myQuery then


UPDATE myQuery SET pasted=possible_pasted


should do the job.



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

trying it now. the update query works really fast up till halfway through,
then takes more than an hour. any recommendations? also, the myQuery is a
query returning records from a table, so will the UPDATE query actually
change values for the column of the myQuery? doesnt seem right.

geebee

Hi,


I assume that query is itself updateable, is it not, right now? so, if you
save it as myQuery then


UPDATE myQuery SET pasted=possible_pasted


should do the job.



Hoping it may help,
Vanderghast, Access MVP

I have the following query:

SELECT Tbl_archive.[Loan Acct #], Tbl_archive.status AS thurs_status,
DLookUp("[status]","tbl_archive","[PopEnterDt] = #" & Date()-2 & "# AND
[loan
acct #] = " & [loan acct #]) AS wed_status, IIf([wed_status]<>"chargeoff"
And
[thurs_status]="chargeoff" And [CHG OFF DATE] Is Not
Null,"CHARGEOFF",IIf([WED_STATUS]<>"BK" And [THURS_STATUS]="BK" And
[ACTIVE
INACTIVE FLAG]="A","BK",IIf([WED_STATUS]<>"PAIDOFF" And
[THURS_STATUS]="PAIDOFF" And [PAID OFF?]=1 And [PAYOFF DATE] Is Not
Null,"PAIDOFF",IIf([WED_STATUS]<>"REPO" And [THURS_STATUS]="REPO" And
[INVENTORY STATUS] In ("IN","SO"),"REPO",IIf([WED_STATUS] Like "PASTDUE*"
And
[THURS_STATUS]="CURRENT" And ([AMENDED THIS MO?]=1 Or [EXTENDED THIS
MO?]=1)
And [PAID OFF?]=0 And [ACTIVE INACTIVE FLAG]="I","CURRENT BKAMD
DEF",IIf([WED_STATUS] Like "PASTDUE*" And [THURS_STATUS]="CURRENT" And
([AMENDED THIS MO?]=0 Or [EXTENDED THIS MO?]=0) And [PAID OFF?]=0,"CURRENT
PAID",IIf([WED_STATUS] Like "PASTDUE*" And [THURS_STATUS]="CURRENT" And
([AMENDED THIS MO?]=1 Or [EXTENDED THIS MO?]=1) And [PAID OFF?]=0,"CURRENT
AMD DEFR",""))))))) AS possible_pasted, Tbl_archive.[chg off date],
Tbl_archive.[active bk flag], Tbl_archive.[active inactive flag],
Tbl_archive.[paid off?], Tbl_archive.[payoff date], Tbl_archive.[inventory
status], Tbl_archive.[amended this mo?], Tbl_archive.[extended this mo?]
FROM Tbl_archive
WHERE (((Tbl_archive.PopEnterDt)=Date()-1));

I need to change it to an UPDATE query, in which the [pasted] value for
each
record is updated by the value of [possible_pasted] for each row. ALL in
tbl_archive.

thanks in advance,
geebee

 

[Michel Walsh]

Hi,


An update is always performed to the supporting table(s), the view has no
physical 'storage', as the data is concerned.

A possible way to accelerate the execution, if it takes time, is to remove
the computed fields, and DLookup, and other not involved field (I assumed
the DLookup are not involved in the update). Sure, if you ALSO need the
original query elsewhere, you can make a second copy of the initial query,
stripping all the irrelevant fields from the copy, and then, make the update
on this shortest query.


Hoping it may help,
Vanderghast, Access MVP

trying it now. the update query works really fast up till halfway
through,
then takes more than an hour. any recommendations? also, the myQuery is
a
query returning records from a table, so will the UPDATE query actually
change values for the column of the myQuery? doesnt seem right.

geebee

Hi,


I assume that query is itself updateable, is it not, right now? so, if
you
save it as myQuery then


UPDATE myQuery SET pasted=possible_pasted


should do the job.



Hoping it may help,
Vanderghast, Access MVP

I have the following query:

SELECT Tbl_archive.[Loan Acct #], Tbl_archive.status AS thurs_status,
DLookUp("[status]","tbl_archive","[PopEnterDt] = #" & Date()-2 & "# AND
[loan
acct #] = " & [loan acct #]) AS wed_status,
IIf([wed_status]<>"chargeoff"
And
[thurs_status]="chargeoff" And [CHG OFF DATE] Is Not
Null,"CHARGEOFF",IIf([WED_STATUS]<>"BK" And [THURS_STATUS]="BK" And
[ACTIVE
INACTIVE FLAG]="A","BK",IIf([WED_STATUS]<>"PAIDOFF" And
[THURS_STATUS]="PAIDOFF" And [PAID OFF?]=1 And [PAYOFF DATE] Is Not
Null,"PAIDOFF",IIf([WED_STATUS]<>"REPO" And [THURS_STATUS]="REPO" And
[INVENTORY STATUS] In ("IN","SO"),"REPO",IIf([WED_STATUS] Like
"PASTDUE*"
And
[THURS_STATUS]="CURRENT" And ([AMENDED THIS MO?]=1 Or [EXTENDED THIS
MO?]=1)
And [PAID OFF?]=0 And [ACTIVE INACTIVE FLAG]="I","CURRENT BKAMD
DEF",IIf([WED_STATUS] Like "PASTDUE*" And [THURS_STATUS]="CURRENT" And
([AMENDED THIS MO?]=0 Or [EXTENDED THIS MO?]=0) And [PAID
OFF?]=0,"CURRENT
PAID",IIf([WED_STATUS] Like "PASTDUE*" And [THURS_STATUS]="CURRENT" And
([AMENDED THIS MO?]=1 Or [EXTENDED THIS MO?]=1) And [PAID
OFF?]=0,"CURRENT
AMD DEFR",""))))))) AS possible_pasted, Tbl_archive.[chg off date],
Tbl_archive.[active bk flag], Tbl_archive.[active inactive flag],
Tbl_archive.[paid off?], Tbl_archive.[payoff date],
Tbl_archive.[inventory
status], Tbl_archive.[amended this mo?], Tbl_archive.[extended this
mo?]
FROM Tbl_archive
WHERE (((Tbl_archive.PopEnterDt)=Date()-1));

I need to change it to an UPDATE query, in which the [pasted] value for
each
record is updated by the value of [possible_pasted] for each row. ALL
in
tbl_archive.

thanks in advance,
geebee