Microsoft Access copy data from field in next record to current record

Question

I'm trying to normalized data in about 600 excel files that will be imported into a temporary table in Access. This is a one-time process.

I guess I could do this in Excel, but would either have to create a gigantic single table or repeat the process 600 times :)

In each file, they have an original row entry. Should the email or telephone number change, they placed it in the row under it.

I imported the file with an autonumber ID field, so I know what order the data is listed.

I segregated the data so I have a query result that shows ID numbers for the original and the updated rows. (see gif).

My question is how to I copy from record ID 2 the Email and Phone_Number fields into record ID 1, Email_2, Phone_Number_2? When accomplished, I will delete all records without Student_Name. I then can append the working table.

Ideas??

Example of Query Results

I haven't done, besides trying to find the answer, unsuccessfully, a solution.

Answer 1

Build and save a query object that retrieves records where Student_Name IS NULL

Q1:
SELECT * FROM Data WHERE Student_Name IS NULL;

Build UPDATE query that joins Q1 to table:

UPDATE Data INNER JOIN Q1 ON Data.Certification_No = Q1.Certification_No 
SET Data.Phone_Number_2 = [Q1].[Phone_Number], Data.Email_2 = [Q1].[Email]
WHERE ((Not (Data.Student_Name) Is Null));

I tried embedding Q1 SQL within UPDATE SQL and it fails.

Alternative is to have two tables. One table of unique student records and a related dependent table where each phone and email pair is a record.