Dynamic Pivot Header in SQL Query (1 Viewer)

Treylok

New member
Local time
Today, 10:53
Joined
Nov 24, 2008
Messages
1
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:
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);
Here is the output I get, which is exactly how I want it.
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
Here's what I have attempted to use, but I get an error message in access:

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'));
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
 
Last edited:

DCrake

Remembered
Local time
Today, 15:53
Joined
Jun 8, 2005
Messages
8,632
You are nearly there.

What you need to do is to create a string similar to

strHeading = "'Jan 2008','Feb 2008','etc 2008'"

Or

strHeading = "'01/2008','02/2008','03/2008','etc 2008'"


Then just prior to running the query you need to change the sql of the query dynamically.

Dim qry as QueryDef
set qry = "YourQueryName"

qry.sql = Replace(qry.sql,";", "In(" & strHeading & ");"

Aircode

Summary
You create a full set of month years for the current year or last 12 months, whatever as a string

Then grab the sql statement of the known query and replace the closing semicolon with the In() statement which includes the headings.

An altogether easier way is to go the query in design mode and click on the properties of the column field and where it asks for column headings type them in there manully. This will cause the query to display the periods even if there is no data for that period and it will also be in the order you type in the headings.

David
 

Users who are viewing this thread

Top Bottom