先把前两个汇总后再运行这个测评汇总
Sub 民主测评()
Set cnn = CreateObject("ADODB.Connection")
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & ThisWorkbook.FullName
With Sheets("民主评议汇总")
.UsedRange.Offset(1).ClearContents
sqlx = "select 序号,学籍号,性别,班级,姓名,avg(学业水平)*0.1 as 学业水平,avg(身心健康)*0.1 as 身心健康,avg(艺术素养)*0.1 as 艺术素养,avg(社会实践)*0.1 as 社会实践 from [自评$a1:i] where 学籍号 <> NULL group by 序号,学籍号,性别,班级,姓名"
sqlh = "select 序号,学籍号,性别,班级,姓名,avg(学业水平)*0.5 as 学业水平,avg(身心健康)*0.5 as 身心健康,avg(艺术素养)*0.5 as 艺术素养,avg(社会实践)*0.5 as 社会实践 from [互评$a1:i] where 学籍号 <> NULL group by 序号,学籍号,性别,班级,姓名"
sqlj = "select 序号,学籍号,性别,班级,姓名,avg(学业水平)*0.4 as 学业水平,avg(身心健康)*0.4 as 身心健康,avg(艺术素养)*0.4 as 艺术素养,avg(社会实践)*0.4 as 社会实践 from [教师评$a1:i] where 学籍号 <> NULL group by 序号,学籍号,性别,班级,姓名"
Sql = "select a.序号,a.学籍号,a.性别,a.班级,a.姓名,a.学业水平+b.学业水平 as 学业,a.身心健康+b.身心健康 as 身心,a.艺术素养+b.艺术素养 as 艺术,a.社会实践+b.社会实践 as 社会 from (" & sqlx & ") a left join (" & sqlh & ") b on a.学籍号=b.学籍号"
Sql = "select c.序号,c.学籍号,c.性别,c.班级,c.姓名,c.学业水平+d.学业,c.身心健康+d.身心,c.艺术素养+d.艺术,c.社会实践+d.社会 from (" & sqlj & ") c left join (" & Sql & ") d on c.学籍号=d.学籍号"
.Range("a2").CopyFromRecordset cnn.Execute(Sql)
End With
cnn.Close
Set cnn = Nothing
End Sub |