Pivot table using query

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
 
M

MGFoster

GROUP BY should be:

GROUP BY DatePart("yyyy",[Subscribers].[Year])

No Subscriber_ID.
 
G

Guest

Hello, many thanks for your reply. I've amended the query as advised and get
the following result:

Year Total
2000 3
2001 2
2002 1
2003 4
2004 2

I think the problem may be in the Select line, but that's what I can't
figure out...

MGFoster said:
GROUP BY should be:

GROUP BY DatePart("yyyy",[Subscribers].[Year])

No Subscriber_ID.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

Tim said:
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
Click to expand...
Click to expand...
 
M

MGFoster

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I believe you'll need to change your query so that, first you get the
subscribers who saw the movie (as subquery) & then do the count of those
subscribers per joined year (main query).

JET SQL syntax (untested):

SELECT DatePart("YYYY", S.[Year]) As [Year],
Count(A.Subscriber_ID) As Total

FROM Subscribers AS S INNER JOIN

[SELECT DISTINCT S.Subscriber_ID
FROM Subscribers As S INNER JOIN MovieViewings As V
ON S.Subscriber_ID = V.Subscriber_ID
WHERE V.Movie_ID = 1]. AS A

ON S.Subscriber_ID = A.Subscriber_ID

GROUP BY DatePart("YYYY", S.[Year]) As [Year]

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQgQPtYechKqOuFEgEQJa0ACgqJqrdaEBJSy6Hez1RLKhcsQjWHYAoJ3M
ZpNZhARNzpL5i96wxKPxHWXj
=B2xo
-----END PGP SIGNATURE-----


Tim said:
Hello, many thanks for your reply. I've amended the query as advised and get
the following result:

Year Total
2000 3
2001 2
2002 1
2003 4
2004 2

I think the problem may be in the Select line, but that's what I can't
figure out...

:

GROUP BY should be:

GROUP BY DatePart("yyyy",[Subscribers].[Year])

No Subscriber_ID.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

Tim said:
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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Select Column runtime in query 1
Populating a query result based on a range 1
Access pivot table question 2
Query Calculation Doesn't Get Put Into Table 3
How to extract a character from a string then VLOOKUP a table? 7
Pivot table from CSV data, problem with percentage of total. 1
comparing a single entry to field in other rows? 7
Pivot table from CSV data, problem getting a percentage from the total 2

Top