Month to Date Year to Date Sums for Current and Prior Year

[herkiefan]

Hi,

I have a table with two columns: GamingDate and PassCnt.

I am trying to write a query that will give me the PassCnt, MTD PassCnt, and YTD PassCnt for a date entered by the user. I have the following figured out so far:

SELECT tbl_PASS_CNT.GamingDATE, tbl_PASS_CNT.PASSCnt, Sum(tbl_PASS_CNT.PASSCnt) AS [MTD PASS CT], Sum(tbl_PASS_CNT.PASSCnt) AS [YTD PASS CT]
FROM tbl_PASS_CNT
GROUP BY tbl_PASS_CNT.GamingDATE, tbl_PASS_CNT.PASSCnt
HAVING (((tbl_PASS_CNT.GamingDATE)=[Enter Date] Or (tbl_PASS_CNT.GamingDATE)=DateAdd("yyyy",-1,[Enter Date])));

However, and probably obvious to yall is that the MTD and YTD numbers are just replications of the current PassCnt.

How do I modify my query to add up Month to Date and Year to Date numbers?

As always I look forward to your help.

Mike

“Only two things are infinite, the universe and human stupidity, and I'm not sure about the former.”-Albert Einstein

 

[SkipVought]

How about this...

, Sum(iif(Format(GamingDATE,"yyyymm")=Format([Enter Date],'yyyymm"),PASSCnt,0)) AS [MTD PASS CT]
, Sum(iif(Format(GamingDATE,"yyyy")=Format([Enter Date],'yyyy"),PASSCnt,0))  AS [YTD PASS CT]

Skip,

_[red][/red]

 

[AlexCuse]

I think that this query will do what you need (not tested)

Basically, you want to join to queries that contain all the data you need for the current month (b) and current year (c), making sure that the date is <= the GamingDate in your table (a). Then, by summing b.PassCnt and c.PassCnt, you have a YTD or MTD sum up to the GamingDate on the left side of the join. Post back if you have any questions.

SELECT a.GamingDATE
, a.PASSCnt
, Sum(b.PASSCnt) AS [MTD PASS CT]
, Sum(c.PASSCnt) AS [YTD PASS CT]
FROM tbl_PASS_CNT a
left join 
(
select GamingDate, PassCnt
from tbl_PASS_CNT 
where month(GamingDate) = month([Enter Date])
) b
on a.GamingDate >= b.GamingDate
left join
(
select GamingDate, PassCnt
from tbl_PASS_CNT 
where year(GamingDate) = year([Enter Date])
) c
on a.GamingDate >= c.GamingDate
GROUP BY tbl_PASS_CNT.GamingDATE, tbl_PASS_CNT.PASSCnt
HAVING a.GamingDATE=[Enter Date] Or a.GamingDATE=DateAdd("yyyy",-1,[Enter Date]);

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[herkiefan]

Skip,

Where exactly would I put your code?

“Only two things are infinite, the universe and human stupidity, and I'm not sure about the former.”-Albert Einstein

 

[herkiefan]

Skip,

This is how I have incorporated your suggestion:

SELECT tbl_PASS_CNT.GamingDATE, tbl_PASS_CNT.PASSCnt, 
Sum(iif(Format(GamingDATE,"yyyymm")=Format([Enter Date],'yyyymm"),PASSCnt,0)) AS [MTD PASS CT], 
Sum(iif(Format(GamingDATE,"yyyy")=Format([Enter Date],'yyyy"),PASSCnt,0))  AS [YTD PASS CT]
GROUP BY tbl_PASS_CNT.GamingDATE, tbl_PASS_CNT.PASSCnt
HAVING (((tbl_PASS_CNT.GamingDATE)=[Enter Date] Or (tbl_PASS_CNT.GamingDATE)=DateAdd("yyyy",-1,[Enter Date])));

I get a syntax error.

What have I done incorrectly?

Mike

“Only two things are infinite, the universe and human stupidity, and I'm not sure about the former.”-Albert Einstein

 

[AlexCuse]

Your query is missing the FROM portion ;-)

Ignorance of certain subjects is a great part of wisdom

 

[herkiefan]

AlexCuse,

Forgive my newbie-ness. Here is the code I am using.

SELECT tbl_PASS_CNT.GamingDATE, tbl_PASS_CNT.PASSCnt, Sum(iif(Format(GamingDATE,"yyyymm")=Format([Enter Date],'yyyymm"),PASSCnt,0)) AS [MTD PASS CT], Sum(iif(Format(GamingDATE,"yyyy")=Format([Enter Date],'yyyy"),PASSCnt,0))  AS [YTD PASS CT]
FROM tbl_PASS_CNT
GROUP BY tbl_PASS_CNT.GamingDATE, tbl_PASS_CNT.PASSCnt,
HAVING (((tbl_PASS_CNT.GamingDATE)=[Enter Date] Or (tbl_PASS_CNT.GamingDATE)=DateAdd("yyyy",-1,[Enter Date])));

The error I am getting is:
"Syntax error (missing operator) in query expression 'Sum(iif(Format(GamingDATE,"yyyymm")=Format...GROUP BY tbl_PASS_CNT.GamingDATE, tbl_PASS_CNT.'.

I looked at the help function for this and it doesn't really give me much advice.

What would you suggest?

Mike

“Only two things are infinite, the universe and human stupidity, and I'm not sure about the former.”-Albert Einstein

 

[AlexCuse]

You know, after looking more closely I am not sure if those sums are going to work while you are grouping on PassCnt. If you group by something, and also sum it, you are really only going to get a sum for each distinct row (in your case, each different PassCnt).

The only other problem I could think of is that you are entering the date in some fashion that access does not like (mm/dd/yy usually works). I think you might be better served to try the query that I suggested above, replacing this line

GROUP BY tbl_PASS_CNT.GamingDATE, tbl_PASS_CNT.PASSCnt

with this:

GROUP BY a.GamingDATE, a.PASSCnt

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom