I am hoping someone can help me with a query I am trying to create.
I have one Table with 2 columns: MovieName and MovieDate
I am trying to create a query that will show the number of hits on each movie grouped by month, but only from the previous 12 months from the current date.
I have tried numerous methods, but the solution that seems the best involves a pivot table. I can get it to work just fine if I manually enter the column names of the pivot table, but I would really like to have the pivot table dynamically update itself based on the current month and year.
Here is my current SQL statement:
Here is the output I get, which is exactly how I want it.
Here's what I have attempted to use, but I get an error message in access:
I have only included the current month and the previous 3 months to test it. Here is the error message that I get:
Missing ), ], or Item in query expression 'Format([MovieDate],'mm/yyyy') In
(Format(DateSerial(Year(now()'.
Hopefully someone can help.
Thanks
I have one Table with 2 columns: MovieName and MovieDate
I am trying to create a query that will show the number of hits on each movie grouped by month, but only from the previous 12 months from the current date.
I have tried numerous methods, but the solution that seems the best involves a pivot table. I can get it to work just fine if I manually enter the column names of the pivot table, but I would really like to have the pivot table dynamically update itself based on the current month and year.
Here is my current SQL statement:
Code:
TRANSFORM Count(MovieName) AS CountOfID
SELECT MovieName, Count(MovieName) as Total
FROM CB
GROUP BY MovieName
PIVOT Format([MovieDate],'mm/yyyy') In
(11/2007, 12/2007,1/2008,2/2008,3/2008,4/2008,5/2008,6/2008,7/2008.8/2008,9/2008,10/2008,11/2008);
Code:
MovieName | Total | 11/2007 | 12/2007 | 1/2008 | .... | 11/2008 |
Movie 1 12 3 2 2 1
Movie 2 5 1 2
Movie 3 22 5 7 9 4
Code:
TRANSFORM Count(MovieName) AS CountOfID
SELECT MovieName, Count(MovieName) as Total
FROM CB
GROUP BY MovieName
PIVOT Format([MovieDate],'mm/yyyy') In
(Format(DateSerial(Year(now()),month(now())+1,0),'mm/yyyy'),Format(DateSerial(Year(now()),month(now())-1,1),'mm/yyyy'),Format(DateSerial(Year(now()),month(now())-2,1),'mm/yyyy'),Format(DateSerial(Year(now()),month(now())-3,1),'mm/yyyy'));
Missing ), ], or Item in query expression 'Format([MovieDate],'mm/yyyy') In
(Format(DateSerial(Year(now()'.
Hopefully someone can help.
Thanks
Last edited: