I think I need a subquery

[nick1408]

I have had some help building the following query:

SELECT [RICT Weekly Baseline].[Week Ending], [Open NPS Tasks].[31 date], Sum([Open NPS Tasks].[Total Effort]) AS [SumOfTotal Effort]
FROM [Open NPS Tasks], [RICT Weekly Baseline]
GROUP BY [RICT Weekly Baseline].[Week Ending], [Open NPS Tasks].[31 date];

I would like to add another column to help the data sum correctly but am not quite sure how to do it.

What I am after is the syntax to say if [Open NPS Tasks].[31date] is inside the last seven days of [RICT Weekly Baseline].[Week Ending] sum all the [Open NPS Tasks].[Total Effort] for those records together.

I'm not sure if I can do this on a Totals Row Query or not or if it is something as easy as adding a criteria to [31 date] (which I did try unsuccessfully).

I apologise in advance if I haven't explained myself well.

 

[Ranman256]

Add the 7 day limit as criteria,
DateDiff("d",[31date],[weekEnding]) <8