G
Guest
Hello, can anyone tell me where I am going wrong with the following query? I
want to produce a pivot table of the number of people in a database who saw
the film The Aviator listed by year in which they became a subscriber, but
ignoring the number of times they saw the film (the database is a practice
tool):
Below is the result I am trying to achieve..
Year Total
2000 3
2001 2
2002 1
2003 2
2004 2
...using the following query:
SELECT DISTINCT DatePart("yyyy",[Subscribers].[Year]) AS [Year],
Count(Subscribers.Subscriber_ID) AS Total
FROM Subscribers INNER JOIN Movieviewings ON Subscribers.Subscriber_ID =
Movieviewings.Subscriber_ID
WHERE (((Movieviewings.Movie_ID)=1))
GROUP BY DatePart("yyyy",[Subscribers].[Year]), Subscribers.Subscriber_ID;
The following is the result I get with the above query:
Year Total
2000 1
2001 1
2002 1
2003 1
2003 3
2004 1
Table: Movies
Movie_ID Movie_Name
1 Aviator
Table: Subscribers
Subscriber_ID Subscriber_Name Year
1 Bill 01 March 2000
2 Susanna 03 April 2000
3 Joanna 12 May 2000
4 John 02 October 2001
5 Steve 13 August 2001
6 Pete 07 July 2002
7 Harry 03 September 2003
8 Heidi 11 June 2003
9 Marilyn 14 April 2004
10 Chloe 15 February 2004
Table: Movieviewings
Movieviewing_ID Movie Subscriber
1 Aviator Bill
2 Aviator Susanna
3 Aviator Joanna
4 Aviator John
5 Aviator Steve
6 Aviator Pete
7 Aviator Harry
8 Aviator Heidi
9 Aviator Heidi
10 Aviator Heidi
11 Aviator Marilyn
12 Aviator Chloe
Any help would be much appreciated.
Many thanks in advance
Tim Long
want to produce a pivot table of the number of people in a database who saw
the film The Aviator listed by year in which they became a subscriber, but
ignoring the number of times they saw the film (the database is a practice
tool):
Below is the result I am trying to achieve..
Year Total
2000 3
2001 2
2002 1
2003 2
2004 2
...using the following query:
SELECT DISTINCT DatePart("yyyy",[Subscribers].[Year]) AS [Year],
Count(Subscribers.Subscriber_ID) AS Total
FROM Subscribers INNER JOIN Movieviewings ON Subscribers.Subscriber_ID =
Movieviewings.Subscriber_ID
WHERE (((Movieviewings.Movie_ID)=1))
GROUP BY DatePart("yyyy",[Subscribers].[Year]), Subscribers.Subscriber_ID;
The following is the result I get with the above query:
Year Total
2000 1
2001 1
2002 1
2003 1
2003 3
2004 1
Table: Movies
Movie_ID Movie_Name
1 Aviator
Table: Subscribers
Subscriber_ID Subscriber_Name Year
1 Bill 01 March 2000
2 Susanna 03 April 2000
3 Joanna 12 May 2000
4 John 02 October 2001
5 Steve 13 August 2001
6 Pete 07 July 2002
7 Harry 03 September 2003
8 Heidi 11 June 2003
9 Marilyn 14 April 2004
10 Chloe 15 February 2004
Table: Movieviewings
Movieviewing_ID Movie Subscriber
1 Aviator Bill
2 Aviator Susanna
3 Aviator Joanna
4 Aviator John
5 Aviator Steve
6 Aviator Pete
7 Aviator Harry
8 Aviator Heidi
9 Aviator Heidi
10 Aviator Heidi
11 Aviator Marilyn
12 Aviator Chloe
Any help would be much appreciated.
Many thanks in advance
Tim Long