Update Table Statement - Time Out Problem

[dev]

I am creating a simple bug tracker (using Access) and i created
a history table to log the bug history.

The history table contains details like ProblemRecordNo (PRN),
HistoryDate, RecordStatus, Assignee, Reporter,etc. The PK for this table is
ID.
The history table contains multiple entries for a Record.

I had inserted a lot of records into History table and now I introduced two
new fields into History table. They are PreviousStatus and PreviousAssignee.
I wanted to update the previous values of the Status and Assignee for each
record.

Say my History table contains values like

ID PRN Status Assignee
1 10 Report UserA
2 10 InProcess UserA
3 10 Esclated UserB


Now after introducing the Previous fields, the History table should
look like


ID PRN Status Assignee PreviousStatus
PreviousAssignee
1 10 Report UserA
2 10 InProcess UserA Report UserA
3 10 Esclated UserB InProcess UserA

In the first record the PreviousStatus and PreviousAssignee are empty
bse there is no previous values for those two items. the next two
records contain the previous values of the status and assignee.


I used the following sql statement to update the table, but I get a
Script Timeout error


UPDATE (
SELECT H.PRN, H.ID, H.PREVIOUSSTATUS AS OLDSTATUS, N.STATUS AS
NEWSTATUS,
H.PREVIOUSASSIGNEE AS OLDASSIGNEE, N.ASSIGNEE AS NEWASSIGNEE FROM
HISTORY H,
(SELECT N.ID, N.PRN, N.STATUS, N.ASSIGNEE FROM HISTORY N ) N
WHERE H.PRN = N.PRN AND N.ID < H.ID AND N.ID IN
(SELECT MAX(M.ID) FROM HISTORY M WHERE M.ID < H.ID AND H.PRN = M.PRN) )
SET OLDSTATUS = NEWSTATUS,
OLDASSIGNEE = NEWASSIGNEE

I know the problem is I use History table thrice in the join and the most
affected part is the sub query to get the MAX(ID) from History table..
I dont know any other way to get the next maximum ID of the current ID
for that record.


No. of Rows is > 50,000

any way to optimize my query ?

thnx

 

[Jerry Whittle]

Looks like you are trying to commit Excel. Reconsider adding the new columns.
What you actually need is another table. The PRN table would have
information about the trouble. Then you would link it to a Status table where
all the information about the status would be kept. You would use something
like a Date/Time field where the default is Now() so that you could tell
which is the most current status plus rack and stack all the old status
entries. This would have the benefit of keeping all the past status and not
just the most recent.

 

[dev]

HI Jerry,

Yes, that makes sense..

but...I do keep track of all the status changes..
like each row in History table will have the previous values of status for
that PRN (and history table has many rows for one record, it logs the status
change, assignee change, etc...)..so by this way I keep track of all the
status changes

Also, I was able to acheive this in SQL Server and Oracle databases, but I
got stuck with Access.

thnx
dev