Sum of Most Recent to Most Recent-12 months

[Guest]

I have been searching around for a week now and I still can't wrap my head
around this problem, so any help is greatly appreciated!

I have an attendence database, where employees get points when they aren't
at work. I am trying to make a query/report that will find the most recent
occurance of a point, and then sum the points for the employee from that most
recent occurance to 12 months prior.

My table looks something like:
employee pointdate pointvalue


Example Data Set:
John 12/05/05 1
John 10/18/05 1
John 08/20/04 1

Desired Result: John's current points = 2

Can anything give me a starting point on how I should approach this? Thanks
in advance!

 

[Guest]

I'm not sure, but from your example try this

Select EmployeeFieldName , Sum(PointsFieldName) As SumOfPoints
From TableName
Where DateFieldName <> DMax("DateFieldName","TableName","EmployeeFieldName =
'" & [EmployeeFieldName] & "'")
Group By EmployeeFieldName

 

[Guest]

Thank you for the quick reply Ofer.

From what I can tell, that code will give me the most recent point for each
employee? Do you know of the next step I should take that would then compare
the other points on record to see if they happened within a year before that
most recent point?

Thanks

I'm not sure, but from your example try this

Select EmployeeFieldName , Sum(PointsFieldName) As SumOfPoints
From TableName
Where DateFieldName <> DMax("DateFieldName","TableName","EmployeeFieldName =
'" & [EmployeeFieldName] & "'")
Group By EmployeeFieldName

--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck

I have been searching around for a week now and I still can't wrap my head
around this problem, so any help is greatly appreciated!

I have an attendence database, where employees get points when they aren't
at work. I am trying to make a query/report that will find the most recent
occurance of a point, and then sum the points for the employee from that most
recent occurance to 12 months prior.

My table looks something like:
employee pointdate pointvalue


Example Data Set:
John 12/05/05 1
John 10/18/05 1
John 08/20/04 1

Desired Result: John's current points = 2

Can anything give me a starting point on how I should approach this? Thanks
in advance!

 

[Guest]

This example will add up all the points without the last entry, if you want
the records from the last 12 month's, try this

Select EmployeeFieldName , Sum(PointsFieldName) As SumOfPoints
From TableName
Where DateFieldName <> DMax("DateFieldName","TableName","EmployeeFieldName =
'" & [EmployeeFieldName] & "'") And DateFieldName > DateAdd("m",-12,Date())
Group By EmployeeFieldName


--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck

Thank you for the quick reply Ofer.

From what I can tell, that code will give me the most recent point for each
employee? Do you know of the next step I should take that would then compare
the other points on record to see if they happened within a year before that
most recent point?

Thanks

I'm not sure, but from your example try this

Select EmployeeFieldName , Sum(PointsFieldName) As SumOfPoints
From TableName
Where DateFieldName <> DMax("DateFieldName","TableName","EmployeeFieldName =
'" & [EmployeeFieldName] & "'")
Group By EmployeeFieldName

--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck

I have been searching around for a week now and I still can't wrap my head
around this problem, so any help is greatly appreciated!

I have an attendence database, where employees get points when they aren't
at work. I am trying to make a query/report that will find the most recent
occurance of a point, and then sum the points for the employee from that most
recent occurance to 12 months prior.

My table looks something like:
employee pointdate pointvalue


Example Data Set:
John 12/05/05 1
John 10/18/05 1
John 08/20/04 1

Desired Result: John's current points = 2

Can anything give me a starting point on how I should approach this? Thanks
in advance!

 

[Per Larsen]

How about this:

SELECT T1.employee, Sum(T1.pointvalue) AS SumOfPointValue
FROM MyTable AS T1
WHERE T1.pointdate <= (SELECT Max(pointdate) FROM MyTable T2 WHERE T1.employee = T2.employee)
AND DateAdd("yyyy", 1, T1.pointdate) >= (SELECT Max(pointdate) FROM MyTable T3 WHERE T1.employee = T3.employee)
GROUP BY T1.employee

hth
PerL

 

[Guest]

That seems to have done the trick! Thank you very much!