Creating a New Column End Date in a query

[renaccess1]

Hello I would like to create the following is this doable in access?

Table #1
PROC_NM	TBL_KEY	TBL_LAST_DT
R_BOND_STA	251,826,400,HELI	11/15/2016 12:48:53
R_BOND_STA	251,826,400,HELI	11/22/2016 11:07:25
R_BOND_STA	251,826,400,HELI	2/25/2016 14:33:46
Results Table
PROC_NM	TBL_KEY	Begin Date	End Date
R_BOND_STA	251,826,400,HELI	2/25/2016 14:33:46	11/15/2016 12:48:52
R_BOND_STA	251,826,400,HELI	11/15/2016 12:48:53	11/22/2016 11:07:25
R_BOND_STA	251,826,400,HELI	11/22/2016 11:07:26	Current Ddate
would like to create new column with an end range

[RuralGuy]

I'm pretty sure this can be accomplished. What are the calculations needed to create the value?

[renaccess1]

What I am really trying to create is a query that shows the dates when a status has changed and trying to figure out the best way to do this... thats my challenge I a trying to do this but not ending the result table

PROC_NM	TBL_KEY	ORIG_VAL	NEW_VAL	TBL_LAST_DT
R_BOND_STA	251,826,400,HELI		AA	11/15/2016 12:48:53
R_BOND_STA	251,826,400,HELI	AA	WW	11/22/2016 11:07:25
R_BOND_STA	251,826,400,HELI			2/25/2016 14:33:46
Result table
PROC_NM	TBL_KEY	ORIG_VAL	NEW_VAL	TBL_LAST_DT	End Date
R_BOND_STA	251,826,400,HELI		AA	11/15/2016 12:48:53	11/22/2016 11:07:24
R_BOND_STA	251,826,400,HELI	AA	WW	11/22/2016 11:07:25	2/25/2016 14:33:45
R_BOND_STA	251,826,400,HELI			2/25/2016 14:33:46	Current date and time

[RuralGuy]

Okay, now I understand. Maybe a "Get the value in another record" SubQuery will work: http://allenbrowne.com/subquery-01.html

[renaccess1]

Will see .. any other options?

[davegri]

Not very clear what you want.
Why do some records in table have and end date and others not?
When do you want to update the table end date? Whenever it is viewed in a form? Or run an update query once a day to check for incomplete records?
We don't know enough about your data flow and operational processes to help much here...

SELECT tblInformation.*, Date() AS NewDate
FROM tblInformation;

To simply add a new current date field to every row in a query do above.

[RuralGuy]

You could brute force it with a RecordSet and come looping code.

[RuralGuy]

Why do some records in table have and end date and others not?

I could be wrong but I believe the OP is faced with a Next record date issue and when there are no additional records in this series, insert the current date.

[RuralGuy]

The more I think about it, the more convinced I am that the SubQuery would be the way to go. You would be looking for the TBL_LAST_DT in a record with a TBL_LAST_DT > that the record you're on. If no records exist (your on the last record of the sequense) then I would expect a Null to be returned I think.