How to write event procedure for table updating?

[hkgary33 via AccessMonster.com]

Dear all, (it's a bit long....sorry)
In my database for monitoring the movement of the files, I've built a table
named "F_Move" to store relevant information, it contains 3 fields
(FILE_NO;MOVEMENT;DATE&TIME). I've also connect this table to a form so that
once a staff handle a file, he/she will type his/her name in the MOVEMENT
field (store staff names), and then the DATE&TIME field will be auto-
generated as current date; also, the FILE_NO will be auto-stored (becuz the
staff already chose which file no. he want to handle in a combo box in the
form), an example of F_Move table is shown as follows:

FILE_NO MOVEMENT DATE&TIME
1 John 2006-07-01; 14:00
2 Tom 2006-07-02; 13:00
1 David 2006-07-02; 15:05
2 John 2006-07-03; 11:00
2 Calvin 2006-07-03; 11:30

However, I now want to add another table F_LastMove so that it will only
store the name of the last movement staff of each file no., the question is
that how can i do in the form event programming (may be in the AfterUpdate
event handling procedure) such that once a staff add his movement record in
the F_Move table, then the F_LastMove will also be auto-updated. For example,
if initially, for file no. 1, the last movement is John. Then after sometime,
David handle file no. 1 and his add his record in F_Move, so that last
movement will be changed to David?

The expected F_LastMove table is shown as follows:
FILE_NO LAST_MOVEMENT
1 David
2 Calvin


Thanks you all a lot!!!!!!

 

[Guest]

Why do you need to store this in a seperate table? Since you are already
storing the Date/Time, you could get at the last movement by using a query.

Barry

 

[hkgary33 via AccessMonster.com]

Dear Barry,
but how? becuz if i use a "total" query and then choose "Max" to filter the
table....then surely i can get the last movement field...but if I also want
to show "File_No" and "Movement" field, then what can i choose the "total"
combo box? becuz i've chose "Group By" in that combo box....but it don't
works........

can u tell me how to build such a query detailly?
Thanks a lot!!!!

Gary

Why do you need to store this in a seperate table? Since you are already
storing the Date/Time, you could get at the last movement by using a query.

Barry

Dear all, (it's a bit long....sorry)
In my database for monitoring the movement of the files, I've built a table

[quoted text clipped - 28 lines]

Thanks you all a lot!!!!!!

 

[Guest]

Don't use group by's. Instead, put the following in the criteria field of the
Date&Time column:

SELECT Max([Date&Time]) FROM F_MOVE FM WHERE FM.FILE_NO = [FILENO]

This is called a correlated subquery. It says, find the maximum datetime
value in the table for this file number and use it as the criteria for the
main query.

Barry

Dear Barry,
but how? becuz if i use a "total" query and then choose "Max" to filter the
table....then surely i can get the last movement field...but if I also want
to show "File_No" and "Movement" field, then what can i choose the "total"
combo box? becuz i've chose "Group By" in that combo box....but it don't
works........

can u tell me how to build such a query detailly?
Thanks a lot!!!!

Gary

Why do you need to store this in a seperate table? Since you are already
storing the Date/Time, you could get at the last movement by using a query.

Barry

Dear all, (it's a bit long....sorry)
In my database for monitoring the movement of the files, I've built a table

[quoted text clipped - 28 lines]

Thanks you all a lot!!!!!!

 

[hkgary33 via AccessMonster.com]

Thanks a lot Barry!

Dear Barry,
but how? becuz if i use a "total" query and then choose "Max" to filter the
table....then surely i can get the last movement field...but if I also want
to show "File_No" and "Movement" field, then what can i choose the "total"
combo box? becuz i've chose "Group By" in that combo box....but it don't
works........

can u tell me how to build such a query detailly?
Thanks a lot!!!!

Gary

Why do you need to store this in a seperate table? Since you are already
storing the Date/Time, you could get at the last movement by using a query.

[quoted text clipped - 6 lines]