英文:
Microsoft Access copy data from field in next record to current record
问题
我正在尝试规范化大约600个Excel文件中的数据，这些数据将被导入Access中的临时表中。这只是一次性过程。
我想我可以在Excel中做这个，但要么创建一个巨大的单一表格，要么重复这个过程600次 
在每个文件中，它们都有一个原始的行条目。如果电子邮件或电话号码发生变化，它们会将其放在其下的行中。
我导入了带有自动编号ID字段的文件，所以我知道数据的顺序。
我将数据分开，所以我有一个查询结果，显示了原始行和更新行的ID号码。（见GIF图）。
我的问题是如何从记录ID 2复制电子邮件和电话号码字段到记录ID 1，Email_2，Phone_Number_2？完成后，我将删除所有没有Student_Name的记录。然后我可以附加工作表。
有什么想法吗？
英文:
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??
I haven't done, besides trying to find the answer, unsuccessfully, a solution.
答案1
得分: 0
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.
英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论