Exclude first two records from Sum in query 1

[stupiet]

To anyone who can help,

I have a query that I need to create.
In a table I got a list of records that shows the employee along with the points that they earned for a specific task.

Here is my problem. Only the points earned in the last 12 months are valid. So I created a query that lists all records for the last 12 months only.

Then I have to create a sum for each employee on how many total points they earned over the last 12 months. The problem is I have to disregard the points they received for the first two tasks.

Here is a sample scenario. Assume today's date is 9/8/08.
Below are records for employee #21

EMPL POINTS DATE
21 1.5 9/1/2007
21 0.5 10/5/2007
21 1 3/29/2008
21 1.5 5/1/2008
21 2 7/14/2008
21 1 8/8/2008

So record 1 is not counted in the total since the date was more than 12 months ago.
The total sum then is 0.5+1+1.5+2+1 = 6
However, the first two records are not counted, so I'm looking to only have the following sum: 1.5+2+1 = 4.5

For each employee these dates vary so I can't just set a date rule. Also, the points vary as well, so I can't just subtract a fixed number.

Please let me know if you have a solution to this problem. I just can't seem to figure it out.

Thanks for the help!

 

[Hap007]

Hi,

Sometimes, it is just make sense to write some VBA code to do what you want.

This seems to be a good example where VBA code makes good sense.

Just write a small VBA function to open the dataset and loop through all the records.

This way, you can skip the records you want.

Either let the function return to you a total, or write the desired records to a temporary table.

Hope This Helps,
Hap...


Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Accounting Solutions - with free source code
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121

 

[fneily]

After you do your filtering, you can use some VBA to skip over the first two records. Here's some code that you might use:

Private Sub Command2_Click()
Dim tot As Single
Dim RS As DAO.Recordset
Set db = CurrentDb()
Set RS = db.OpenRecordset("Extract_Two_Records_Table", dbOpenDynaset)
RS.MoveFirst
RS.MoveNext
RS.MoveNext
Do Until RS.EOF
tot = tot + RS![Time]
RS.MoveNext
Loop
Me![Text0].Value = tot
End Sub

 

[stupiet]

Initially I wanted to avoid using VB, but after using your code it works like a charm. Thanks for the tip!

 

[Hap007]

Huh?



Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Accounting Solutions - with free source code
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121