gpt4 book ai didi

sql - 用于根据日期重置总和值的 MS-Access 2003 SQL 修改

转载 作者:行者123 更新时间:2023-12-02 02:08:14 26 4
gpt4 key购买 nike

我有一个查询可以完美地提取数据,但有一个异常(exception),我希望数据总和在周年纪念日重新开始。我当前的查询是;

"qry_Vac2"
UserID Category Gain Used Left

查询SQL;

SELECT 
SchedulingLog.UserID,
SchedulingLog.Category,
Sum(IIf([CatDetail] Like 'Ann*',[Value],0))
AS Gain, Sum(IIf([CatDetail] Like '*Used*',[Value],0))+Sum(IIf([CatDetail] Like 'Adj*',[Value],0))
AS Used, [Gain]+[Used] AS [Left]
FROM
SchedulingLog
GROUP BY SchedulingLog.UserID, SchedulingLog.Category
HAVING (((SchedulingLog.Category) Like "Vac*"));

我对修改以在周年纪念事件后重置总和的想法;

   SELECT Roster.UserID, Roster.[WM DOH], Roster.Schedule, Month([WM DOH]) & "/" & Day([WM DOH]) & "/" & Year(Date()) AS [Anniversary Date], SchedulingLog.Category, Sum(IIf([CatDetail] Like 'Ann*',[Value],0)) AS Gain, Sum(IIf([CatDetail] Like '*Used*',[Value],0))+Sum(IIf([CatDetail] Like 'Adj*',[Value],0)) AS Used, [Gain]+[Used] AS [Left]
FROM SchedulingLog INNER JOIN Roster ON SchedulingLog.UserID = Roster.UserID
WHERE (((SchedulingLog.EventDate)>[Anniversary Date]))
GROUP BY Roster.UserID, Roster.[WM DOH], Roster.Schedule, Month([WM DOH]) & "/" & Day([WM DOH]) & "/" & Year(Date()), SchedulingLog.Category;

修改后的查询不返回任何数据。想法?

最佳答案

周年纪念日期不作为您可以在 where 语句中引用的字段存在。要决定做什么,您需要发布 SchedulingLog 日期的格式。

schedulinglog.eventdate  > Month([WM DOH]) & "/" & Day([WM DOH]) & "/" & Year(Date())

可能有用。

或者

schedulinglog.eventdate  > DateSerial(Year(Date(),Month([WM DOH]),Day([WM DOH]))

或者

schedulinglog.eventdate  > DateAdd("yyyy",1,[WM DOH])

关于sql - 用于根据日期重置总和值的 MS-Access 2003 SQL 修改,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/13952164/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com