Running totals in a query

[Guest]

I have a query that sums the number of attendance points an employee has
based on the interval week and this figure need to be added to the YTD total
of points by week. The YTD total then needs to be used in a calculation.

Example:
Weekly YTD
1 1
.5 1.5
1.5 3
0 3
Monthly totals then would be 3 and YTD totals would be 3. I don't need the
monthly total just the weekly and the YTD.

I was looking at some of the other posts and answers. I thought a Dsum
expression would do it but have not been able to get it working yet.

Any help on this would be appreciated!!

 

[Michael Gramelspacher]

I have a query that sums the number of attendance points an employee has
based on the interval week and this figure need to be added to the YTD total
of points by week. The YTD total then needs to be used in a calculation.

Example:
Weekly YTD
1 1
.5 1.5
1.5 3
0 3
Monthly totals then would be 3 and YTD totals would be 3. I don't need the
monthly total just the weekly and the YTD.

I was looking at some of the other posts and answers. I thought a Dsum
expression would do it but have not been able to get it working yet.

Any help on this would be appreciated!!

Given this table, this seems to work. (watch line wrapping)

CREATE TABLE Attendance
(employee_id VARCHAR (10) NOT NULL,
award_date DATETIME NOT NULL,
attendance_points DECIMAL (10,2) NULL,
PRIMARY KEY (employee_id, award_date));

PARAMETERS [Enter Date:] DateTime;
SELECT attendance.employee_id,
[enter date:] - DATEPART("w",[enter date:]) + 1 AS [week of],
SUM(IIF(DATEDIFF("ww",[enter date:] - DATEPART("w",[enter date:])
+ 1,
attendance.award_date) =
0,attendance.attendance_points,
0)) AS [weekly points],
SUM(IIF(DATEDIFF("yyyy",DATEADD("yyyy",DATEDIFF("yyyy",0,[enter
date:]),0),
attendance.award_date) =
0,attendance.attendance_points,
0)) AS [ytd points]
FROM attendance
WHERE attendance.attendance_points IS NOT NULL
AND attendance.award_date <= [enter date:]
GROUP BY attendance.employee_id;

 

[Guest]

I thank you for the assistance but your answer 'does not compute' in my mind.
Anyway, I thank you for your assistance. I have solved the problem by using
the report that is currently being run but have started another problem
(always happens). Here is the problem for today:

The report has a field that has a running sum for the attendance points.
The Detail line shows the current number of points earned in the reporting
week and then shows the running total next to it. I added a textbox to the
detail area that takes the textbox with the running total and does some
calculations on it then displays the results which is EXACTLY what was
desired at the start. However, on the monthly totals, I want to do an
average. It does not want me to do an average on percentages. There are a
total of 3 percents that I add and then average but because I am calculating
one of the percents now, if I try to include the textbox in the average, it
asks for the value in a popup box. Ideas???