Last of Previous record

[leonhuynh2006]

Dear all bro and pro,

Let say I have:
OrderID Date
1 1/1/2010
2 15/1/2010
3 25/1/2010
4 30/1/2010

Now, I would like to add a new row called previous date
OrderID Date P.Date


1 1/1/2010
2 15/1/2010 1/1/2010
3 25/1/2010 15/1/2010
4 30/1/2010 30/1/2010

Sorry for my english as I can not describe by english without illustration

I am very appreciated for your helping

Thanks & Regards

[hertfordkc]

I don't know if your problem can be done with a query or SQL, but I've done this sort of thing using VBA.
Assuming "mytable" is sorted in proper order:

Dim rs as recordset
dim temp as text
set rs = currentdb().openrecordset("mytable", dbOpenDynaset)
With rs
.MoveFirst
temp = !Datefld
Do while not .EOF
.MoveNext
.Edit
!LastRecDate = temp
temp = !Datefld
.Update
Loop
rs.Close

Caution: I typed this without doing any error checking, you are only getting 90% of the story.

[alansidman]

Here is a non VBA solution

http://support.microsoft.com/kb/210504T

BTW: The term Date is a reserved Access name. You should change the field name to something else. If you keep it, at some point it will confuse Access and give you problems.

Alan

[leonhuynh2006]

Thanks for quick reply. However, my records are not followed by sequential order. I would like to do it by using query.

Please helps. Thanks!!!

[alansidman]

My apologies, I posted the wrong link for you.

Here is a link that does what you want in a non VBA manner and in a query

http://support.microsoft.com/kb/210504

Alan