Correct dates inserted into incorrect fields in table.

[Tcurtis]

I have a table that has serial numbers with dates that show when a job was entered, the date it was closed and the date it was installed. The Date of the installation is on the incorrect line and I would like to have it put onto the correct line. How can I do this in Access without going thru every line manually? Below is an example of one serial number with multiple entries.

BarCode	Date_Ent	Date_Cls	Date_Warranty
A-320618	6/2/2017​	6/13/2017
A-320618	3/1/2018​	4/25/2018	5/11/2018
A-320618	5/16/2018​	6/1/2018	8/28/2018
A-320618	9/6/2018​	10/3/2018

I would like to have them lined up like this. I have over 73000 entries to go thru. The Date Warranty needs to be greater than the previous Date Cls but less than the next Date Ent.

BarCode	Date_Ent	Date_Cls	Date_Warranty
A-320618	6/2/2017​	6/13/2017
A-320618	3/1/2018​	4/25/2018
A-320618	5/16/2018​	6/1/2018	5/11/2018
A-320618	9/6/2018​	10/3/2018	8/28/2018

 

[JonXL]

Given the criteria you've provided, 8/28/2018 would fit in both bottom rows.

How are you deciding which value to use when more than one will work?

 

[Tcurtis]

JonXL. The criteria is that the Date Warranty is greater than the Date Cls but less than the next Date Ent. 8/28/2018 has a greater date than the previous three lines but is only less that the last lines Date_Ent, 9/6/2018. By this criteria it will only fit in the last line.

Thanks

 

[xenou]

9/6/2018 happens to be not just the "next" Date_Ent but also the "last" Date_Ent.

I think we need a definition of previous and next. For instance, in this case it looks like you have "previous" looking at other rows but "next" looking at the same row.