Query to SUM values of most recent 4 weeks only

[bradical987]

As I am trying to re-learn Access after many years of not using it, I've decided to put together a database of football statistics (I'm a fantasy football addict). So far, I have been able to figure out most of the simple queries I've wanted to run, but the one I am struggling with is summing the statistics for the most recent 4 weeks (the period of time of a real-world scouting report). Here are the tables I am working with:

tbl_Offensive_Stats


+ID, Week, PlayerKey, Opp, Bye, OFPTS

tbl_Player_Info
+FirstName, LastName, PlayerPos, PlayerTeam, PlayerKey

tbl_Schedule
+ID, Team, Opp, Week, Day, Date, Time

I have also setup joins between the following tables/columns:
tbl_Offensive_Stats.PlayerKey <=> tbl_Player_Info.PlayerKey
tbl_Player_Info.PlayerTeam <=> tbl_Schedule.Team

The PlayerKey contains player information in the format: Drew Brees QB | NO
-This is how the data is exported into a csv file I am able to download from the web.

The query I am trying to run will have the following fields that will not be manipulated:
tbl_Player_Info.LastName, tbl_Player_Info.FirstName, tbl_Player_Info.PlayerPos, tbl_Player_Info.PlayerTeam

I want to be able to sum many fields (not listed) from tbl_Offensive_Stats, but to keep this post simple, I will just be using the OFPTS field. As for this field, I want to sum the OFPTS value for each player (the number of fantasy points scored each week) from only the most recent 4 weeks. I would like to have this query automatically calculate what the most recent 4 weeks are, but if I need to somehow manually enter week numbers or date ranges using BETWEEN (or something I'm not aware of), that would suffice to get this working. The tbl_Offensive_Stats table contains all the player data from each week, as I just import the weekly data into the one table. The Week field contains the week number (i.e. 1, 2, 3, etc) and not in date format.

What I have tried is variation of the following code:

SELECT tbl_Player_Info.LastName, tbl_Player_Info.FirstName, tbl_Player_Info.PlayerPos AS Pos, tbl_Player_Info.PlayerTeam AS Team, Sum(tbl_Offensive_Stats.OFPTS) AS FPTSFROM (tbl_Player_Info INNER JOIN tbl_Offensive_Stats ON tbl_Player_Info.PlayerKey = tbl_Offensive_Stats.PlayerKey) INNER JOIN tbl_Schedule ON tbl_Player_Info.PlayerTeam = tbl_Schedule.Team
GROUP BY tbl_Player_Info.LastName, tbl_Player_Info.FirstName, tbl_Player_Info.PlayerPos, tbl_Player_Info.PlayerTeam
HAVING (((Sum(tbl_Offensive_Stats.OFPTS)) Between [Enter Start Date] And [Enter End Date]));

And:

SELECT tbl_Player_Info.LastName, tbl_Player_Info.FirstName, tbl_Player_Info.PlayerPos AS Pos, tbl_Player_Info.PlayerTeam AS Team, Sum(tbl_Offensive_Stats.OFPTS) AS FPTS
FROM (tbl_Player_Info INNER JOIN tbl_Offensive_Stats ON tbl_Player_Info.PlayerKey = tbl_Offensive_Stats.PlayerKey) INNER JOIN tbl_Schedule ON tbl_Player_Info.PlayerTeam = tbl_Schedule.Team
GROUP BY tbl_Player_Info.LastName, tbl_Player_Info.FirstName, tbl_Player_Info.PlayerPos, tbl_Player_Info.PlayerTeam, tbl_Schedule.Date
HAVING (((Sum(tbl_Offensive_Stats.OFPTS)) Between [Enter Start Date] And [Enter End Date]));

All I get with these is a blank datasheet when I run the query. After countless hours of searching and trying to find posts or blogs with similar questions that have been answered, I haven't been able to come up with anything that closely enough resembles what I am trying to do. If I need to provide any further information about what I am working with, please let me know. Any help, as always, is greatly appreciated!

[June7]

Your filter criteria makes no sense. Why would the Sum of OFPTS be compared to date values?

[bradical987]

It isn't that I'm comparing them to date values, it is that I only want the sum of the OFPTS value for the most recent 4 weeks per player, instead of all weeks per player (as I have in a separate query for YTD stats). I have the date involved, right now, because a different solution I found that was kind of sort of like what I am trying to do used a date range (which didn't work for me the way they did it).

[June7]

But you ARE comparing the sum to date values in the HAVING clause. This makes no sense. Should be a date field where the Sum() expression is and this would be a WHERE clause.

BTW, Date is a reserved word. Should not use reserved words as names for anything.

[bradical987]

Ok so I have changed Date to be GameDate as to avoid using a reserved word.

As I mentioned in my initial post, I am trying to learn access (and extremely new to SQL), so I'm not entirely sure what you mean, but this is what I have come up with:

SELECT tbl_Player_Info.LastName, tbl_Player_Info.FirstName, tbl_Player_Info.PlayerPos AS Pos, tbl_Player_Info.PlayerTeam AS Team
FROM (tbl_Player_Info INNER JOIN tbl_Offensive_Stats ON tbl_Player_Info.PlayerKey = tbl_Offensive_Stats.PlayerKey) INNER JOIN tbl_Schedule ON tbl_Player_Info.PlayerTeam = tbl_Schedule.Team
WHERE (((tbl_Schedule.GameDate)=Sum([tbl_Offensive_Stats].[OFPTS])))
GROUP BY tbl_Player_Info.LastName, tbl_Player_Info.FirstName, tbl_Player_Info.PlayerPos, tbl_Player_Info.PlayerTeam;

As I'm sure you are able to tell, it doesn't work because of the error message "Cannot have aggregate function in WHERE clause..."

Also, if there is a better way of setting this query up, in your eyes, how would you do it? The way it is setup now is not concrete, it was just the best I could come up with based on my limited knowledge.

[June7]

WHERE [GameDate] BETWEEN [Enter Start Date] AND [Enter End Date]

Are you using the Access query designer to aid in constructing SQL?

[bradical987]

Yes, I am using the query designer.

SELECT tbl_Player_Info.LastName, tbl_Player_Info.FirstName, tbl_Player_Info.PlayerPos AS Pos, tbl_Player_Info.PlayerTeam AS Team, Sum(tbl_Offensive_Stats.OFPTS) AS SumOfOFPTS
FROM (tbl_Player_Info INNER JOIN tbl_Offensive_Stats ON tbl_Player_Info.PlayerKey = tbl_Offensive_Stats.PlayerKey) INNER JOIN tbl_Schedule ON tbl_Player_Info.PlayerTeam = tbl_Schedule.Team
WHERE (([GameDate] Between [Enter Start Date] And [Enter End Date]))
GROUP BY tbl_Player_Info.LastName, tbl_Player_Info.FirstName, tbl_Player_Info.PlayerPos, tbl_Player_Info.PlayerTeam;

I am getting the error message This expression is typed incorrectly, or it is too complex to be evaluated. ...

I tried the WHERE clause as it is above (created using the query designer) and I tried it exactly as you typed it into the SQL view (without the double parenthesis around it).

[June7]

Build a simpler query first, just tbl_Offensive_Stats - no calcs and no grouping and no other tables for starters - and see if the filtering works.

If you want to provide db for analysis and testing, follow instructions at bottom of my post.

[bradical987]

Thanks for all your help. I was finally able to get it working using the following code:

SELECT tbl_Player_Info.LastName, tbl_Player_Info.FirstName, tbl_Player_Info.PlayerPos AS Pos, tbl_Player_Info.PlayerTeam AS Team, Sum(tbl_Offensive_Stats.PaAtt) AS PaAtt, Sum(tbl_Offensive_Stats.PaCmp) AS PaCmp, Sum(tbl_Offensive_Stats.PaYd) AS PaYd, Sum(tbl_Offensive_Stats.PaTD) AS PaTD, Sum(tbl_Offensive_Stats.PaInt) AS PaInt, Sum(tbl_Offensive_Stats.RuAtt) AS RuAtt, Sum(tbl_Offensive_Stats.RuYd) AS RuYd, Avg(tbl_Offensive_Stats.RuAvg) AS RuAvg, Sum(tbl_Offensive_Stats.RuTD) AS RuTD, Sum(tbl_Offensive_Stats.ReTar) AS ReTar, Sum(tbl_Offensive_Stats.Recpt) AS Recpt, Sum(tbl_Offensive_Stats.ReYd) AS ReYd, Avg(tbl_Offensive_Stats.ReAvg) AS ReAvg, Sum(tbl_Offensive_Stats.ReTD) AS ReTD, Sum(tbl_Offensive_Stats.FL) AS FL, Sum(tbl_Offensive_Stats.OFPTS) AS FPTS
FROM tbl_Player_Info INNER JOIN tbl_Offensive_Stats ON tbl_Player_Info.PlayerKey = tbl_Offensive_Stats.PlayerKey
WHERE (((tbl_Offensive_Stats.Week) Between [Start Week] And [End Week]))
GROUP BY tbl_Player_Info.LastName, tbl_Player_Info.FirstName, tbl_Player_Info.PlayerPos, tbl_Player_Info.PlayerTeam
ORDER BY Sum(tbl_Offensive_Stats.OFPTS) DESC;