0

I have a query that pulls vacation summary information. I would like to limit the results to greater than current anniversary date. When I riun the query with only the anniversary expression the anniversary expression works fine. When I put in the Where clause it prompts for Anniversary. With out the Where clause is performs the exp[ression and presents the Anniversary date. 

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], Month([WM DOH]) & "/" & Day([WM DOH]) & "/" & Year(Date()) AS Anniversary
FROM SchedulingLog INNER JOIN Roster ON SchedulingLog.UserID = Roster.UserID
WHERE (((SchedulingLog.EventDate)>=[Anniversary]))
GROUP BY SchedulingLog.UserID, SchedulingLog.Category, Month([WM DOH]) & "/" & Day([WM DOH]) & "/" & Year(Date())
HAVING (((SchedulingLog.Category) Like "Vac*"));

What I am looking for is a way to limit the summed values to after the Expression Anniversary, for the Event Date.

Based on Remous patience with my SQL inexperience and guidance. I am begining to see the light. So when I take 

DateAdd("yyyy",1,[WM DOH])
it returns the individuals date of Hire and the year advances by 1.
ex. employee DOH 12-25-2003
The expression returns 12-25-2004

I am adjusting the dateAdd to something like this

DateAdd("yyyy",Year(Date())-1,[WM DOH])
This returns something completely wrong. but illustrates what I am trying to accomplish
desired result ex. 12-25-2011

Thoughts?

Desert Spider
  • 744
  • 1
  • 13
  • 31

1 Answers1

2

Same story. Anniversary is not a field that you can refer to in the where clause, it is an alias.

WHERE SchedulingLog.EventDate>= 
    DateSerial(Year(Date())-1,Month([WM DOH]) ,Day([WM DOH]))
Fionnuala
  • 90,370
  • 7
  • 114
  • 152
  • Remou, I greatly appreciate the light you are shedding on my limited SQL abilities and as ususal you are guiding my down the right path but my results are not quite there. I have modified the Original Post to show the next steps. – Desert Spider Dec 20 '12 at 15:36
  • As per http://stackoverflow.com/questions/13952164/ms-access-2003-sql-modification-for-resetting-sum-values-based-on-a-date – Fionnuala Dec 20 '12 at 15:41
  • Remou, my most sincere apologies! Thank you! – Desert Spider Dec 20 '12 at 15:50