问题描述
[Access 2003 mdb]
[Access 2003 mdb]
TRANSFORM Sum（[Count]）
SELECT [State]，[County]，[Municipality]， [案例类型]
FROM [县 - 案例类型趋势]
WHERE [日期]> = DateAdd（'ww'， - 8，Date（））
GROUP BY [州]，[县]，[市政]，[案件类型]
PIVOT WeeksAgo（[Date]）In（"Current"，"1 Week" ;，"2周"，"3周"，"4周"，"5周"，"6周"，"7周"，"8周";
TRANSFORM Sum([Count])
SELECT [State], [County], [Municipality], [Case Type]
FROM [Counties - Case Type Trend]
WHERE [Date]>=DateAdd('ww', -8, Date())
GROUP BY [State], [County], [Municipality], [Case Type]
PIVOT WeeksAgo([Date]) In("Current","1 Week","2 Weeks","3 Weeks","4 Weeks","5 Weeks","6 Weeks","7 Weeks","8 Weeks");
上面的SQL生成13个字段，Select子句中的4个字符+ Pivot子句中函数WeeksAgo（）返回的字段。对于此查询输出，我想添加一个列，该列给出最后8个Pivot子句字段的平均值（除
[Current]之外的所有字段）。有没有简单的方法来实现这个目标？
The SQL above produces 13 fields, the 4 in the Select clause + the ones returned by the function, WeeksAgo(), in the Pivot clause. To this query output, I would like to add a column that gives an average of the last 8 Pivot clause fields (all fields except [Current]). Is there any easy way to achieve this goal?
顺便提一下，有没有更有效的方法来编码WeeksAgo函数<见下文>？我对日历周感兴趣，而不仅仅是随机的7天周。
By the way, is there a more efficient way to code the WeeksAgo function <see below>? I am interested in calendar weeks and not just random 7-day weeks.
功能WeeksAgo $（DateInWeek为日期）
Dim WeekDiff％
Dim PreDate As Date
PreDate =日期+ 1
Do Before PreDate = DateInWeek
PreDate = PreDate - 1年
如果工作日（PreDate）= 7则为
WeekDiff = WeekDiff + 1
结束如果是
循环
选择案例WeekDiff
案例0
WeeksAgo =" Current"
案例1&
WeeksAgo = WeekDiff& "周"&
案例是< 9
WeeksAgo = WeekDiff& "周数< b $ b 案例等等
停止
WeeksAgo = WeekDiff& "周数< b $ b 结束选择
结束功能
Function WeeksAgo$(DateInWeek As Date)
Dim WeekDiff%
Dim PreDate As Date
PreDate = Date + 1
Do Until PreDate = DateInWeek
PreDate = PreDate - 1
If Weekday(PreDate) = 7 Then
WeekDiff = WeekDiff + 1
End If
Loop
Select Case WeekDiff
Case 0
WeeksAgo = "Current"
Case 1
WeeksAgo = WeekDiff & " Week"
Case Is < 9
WeeksAgo = WeekDiff & " Weeks"
Case Else
Stop
WeeksAgo = WeekDiff & " Weeks"
End Select
End Function
推荐答案
Function WeeksAgo(DateInWeek As Date) As String
Dim d1 As Date
Dim d2 As Date
Dim WeekDiff As Long
' Sunday on or before DateInWeek
d1 = DateInWeek - Weekday(DateInWeek, vbMonday) Mod 7
' Sunday in current week
d2 = Date - Weekday(Date, vbMonday) Mod 7
' Number of weeks in between
WeekDiff = (d2 - d1) / 7
Select Case WeekDiff
Case 0
WeeksAgo = "Current"
Case 1
WeeksAgo = WeekDiff & " Week"
Case Else
WeeksAgo = WeekDiff & " Weeks"
End Select
End Function
这篇关于如何在交叉表查询中平均枢轴列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！