Attached is a list of 5 records created from a view of Order Renewals. They have a field, ORank, with values from 2-6. The values begin at 2 because they a subsequent to the initial order.
What I want to do is create a new table where I add a record assigning an ORank value of 1 for the initial record and an 7th record for the EndOrdID, subsititued for the OrderID, having an Orank of 1.
The attached file has Before and After sections showing the expected results of the query I want to write.