英文:
SQL help needed to shorten the query to iterate through columns
问题
我有一个大约有200列的表,主键是ID,其中大约有180列是y/n类型的。但是我需要通过计算表中每个ID的所有y/n列中的“y”总数来计算一个名为“百分比”的列。
任何帮助将不胜感激。
我必须使用Access,我正在寻找一个在SQL中完成这个任务的简短查询。我可以使用列名来编写查询,但那将需要很多时间。
英文:
I have a table of about 200 columns having primary key as the ID and almost 180 of them are y/n fed. But i have to calculate one column “percentage” through calculating total “y” present from overall y/n columns for each ID in the table.
Any help will be appreciated.
I’m bound to use access and I’m searching for a short query to do it in SQL. I can use column names to write the query but that will take lots of time.
答案1
得分: 2
使用 VBA 和 DAO 来循环表格的代码如下:
Public Function CheckPercent()
Const Sql As String = "Select * From YourTable"
Dim Records As DAO.Recordset
Dim Field As DAO.Field
Dim Total As Integer
Dim Selected As Integer
Set Records = CurrentDb.OpenRecordset(Sql)
While Not Records.EOF
' Calculate.
Total = 0
Selected = 0
For Each Field In Records.Fields
If Field.Type = dbBoolean Then
Total = Total + 1
Selected = Selected + Abs(Field.Value)
End If
Next
' Print result.
Debug.Print "Id " & Records(0).Value, "Total " & Total, "Selected " & Selected, Format(Selected / Total, "Percent")
' Save the percentage for this record in field PercentSelected
' having the data type Currency.
Records.Edit
Records!PercentSelected.Value = Selected / Total
Records.Update
' Move on.
Records.MoveNext
Wend
Records.Close
End Function
输出示例:
Id 1002 Total 8 Selected 5 62,50%
Id 1003 Total 8 Selected 6 75,00%
英文:
Use VBA and DAO to loop the table:
Public Function CheckPercent()
Const Sql As String = "Select * From YourTable"
Dim Records As DAO.Recordset
Dim Field As DAO.Field
Dim Total As Integer
Dim Selected As Integer
Set Records = CurrentDb.OpenRecordset(Sql)
While Not Records.EOF
' Calculate.
Total = 0
Selected = 0
For Each Field In Records.Fields
If Field.Type = dbBoolean Then
Total = Total + 1
Selected = Selected + Abs(Field.Value)
End If
Next
' Print result.
Debug.Print "Id " & Records(0).Value, "Total " & Total, "Selected " & Selected, Format(Selected / Total, "Percent")
' Save the percentage for this record in field PercentSelected
' having the data type Currency.
Records.Edit
Records!PercentSelected.Value = Selected / Total
Records.Update
' Move on.
Records.MoveNext
Wend
Records.Close
End Function
Output example:
Id 1002 Total 8 Selected 5 62,50%
Id 1003 Total 8 Selected 6 75,00%
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论