I would like to add records to the "DrgGrp" table based on records in the "Drg" table (please see attached files for screenshots of sample data and field names from both tables).
I want to add a record to the DrgGrp table for every time a a record exists in the Drg table with the text "CAP" anywhere in the FORM field and the records being added will all have the same value for DrgGrp.GroupID (lets say for the example of the query to make it 199). Care must be taken to update the DrgGrp.Seq field properly. If several records exist with the same DrgGrp.DrgID the DrgGrp.Seq must be incremented to the next higher number. For example if there are already two records with the same DrgGrp.DrgID then the 3rd record that gets added would need to have a DrgGrp.Seq value of 3.