MS-Access 2003 SQL修改,用于基于日期重置总和值 [英] MS-Access 2003 SQL modification for resetting sum values based on a date
本文介绍了MS-Access 2003 SQL修改,用于基于日期重置总和值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个查询可以完美地提取数据,但有一个例外,我希望数据总和从周年纪念日开始.我当前的查询是;
I have a query that is pulling data perfectly with one exception, I want the data sums to start over at an anniversary date. My current query is;
"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*"));
我的修改建议,以在周年纪念日之后重新设置总金额;
My ideas for a modification to reset the sum after anniversary event;
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;
修改后的查询不返回任何数据.有想法吗?
The modified query returns no data. Thoughts?
推荐答案
周年纪念日不存在,您可以在where语句中引用该字段.要决定要做什么,您需要发布SchedulingLog日期的格式.
Anniversary date does not exists as a field that you can reference in a where statement. To decide what to do, you need to post the format of the SchedulingLog date.
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])
这篇关于MS-Access 2003 SQL修改,用于基于日期重置总和值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文