SQL query/join syntax

This is actually a MS Access/SQL, but used in conjunction with VB.net

This is a "simple" multitable join question. Some skeleton data to work
with is provided. Consider a database of collected classic television
episodes.

Table: Networks
NetworkID Network
1 NBC
2 CBS
3 ABC
4 PBS
5 FOX

Table: Series
SeriesID SeriesName NetworkID
1 Roseanne 3
2 Cheers 1
3 House 5

Table: Episodes
EpisodeID EpisodeName SeriesID
1 A New Becky 1
2 Roseanna Sings 1
3 Norm's Beer 2
4 Sammy Goes Long 2

Table: CollectedEpisodes
CollectedEpisodeID EpisodeID Format
1 1 VHS
2 1 DVD
3 3 TiVo
4 4 TiVo


In text, the tables define respectively, TV networks, Series Names, Episode
Names, and finally which episodes a person has collected. Going down the
list each table entry has a 1 to many mapping (ie many series per network,
many episodes per series, many possible collected episodes (via different
media) per episode.)

I would like a query that shows, for the entire collection (entries in
CollectedEpisodes), how many of the episodes are from each series...from each
network.

This for the skeleton data given above. The query would return.

Roseanne 2
Cheers 2
House 0

and

ABC 2
NBC 2

How would I accomplish that? Simple joins I understand...this one is beyond
me.

I would expect the answer to fall into the category of "here is the Join
syntax to return all records from 'NBC'" and then do a count, but I cannot
work out the syntax.


Also secondly, is one query possible that says

"For each series that has at least one Collected Episode, here are the
Episodes needed to complete the respective series."


Thanks in advance

For your first question (identifying number of series and networks), you
need to build two queries and save them:


qryDistinctNetworks

SELECT DISTINCT Networks.NetworkID, Networks.Network
FROM ((CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.EpisodeID)
INNER JOIN Network ON Network.NetworkID = Series.NetworkID;




qryDistinctSeries

SELECT DISTINCT Series.SeriesID, Series.SeriesName
FROM (CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.EpisodeID;


Then, these two queries should give you the desired counts:

SELECT (SELECT Count(Q.NetworkID) FROM qryDistinctNetworks Q
WHERE Q.NetworkID = qryDistinctNetworks.NetworkID) AS CountOfNetwork,
Network, NetworkID
FROM qryDistinctNetworks;



SELECT (SELECT Count(Q.SeriesID) FROM qryDistinctSeries Q
WHERE Q.SeriesID = qryDistinctSeries.SeriesID) AS CountOfSeries,
SeriesName, SeriesID
FROM qryDistinctSeries;



For the sdecond question (which episodes haven't been collected yet), use
this query:

SELECT Episodes.EpisodeID, Episodes.EpisodeName
FROM Episodes LEFT JOIN CollectedEpisodes
ON Episodes.EpisodeID = CollectedEpisodeID
WHERE CollectedEpisodeID IS NULL;


--

Ken Snell
MS ACCESS MVP


"Spektre" wrote in message
...
This is actually a MS Access/SQL, but used in conjunction with VB.net

This is a "simple" multitable join question. Some skeleton data to work
with is provided. Consider a database of collected classic television
episodes.

Table: Networks
NetworkID Network
1 NBC
2 CBS
3 ABC
4 PBS
5 FOX

Table: Series
SeriesID SeriesName NetworkID
1 Roseanne 3
2 Cheers 1
3 House 5

Table: Episodes
EpisodeID EpisodeName SeriesID
1 A New Becky 1
2 Roseanna Sings 1
3 Norm's Beer 2
4 Sammy Goes Long 2

Table: CollectedEpisodes
CollectedEpisodeID EpisodeID Format
1 1 VHS
2 1 DVD
3 3 TiVo
4 4 TiVo


In text, the tables define respectively, TV networks, Series Names,
Episode
Names, and finally which episodes a person has collected. Going down the
list each table entry has a 1 to many mapping (ie many series per network,
many episodes per series, many possible collected episodes (via different
media) per episode.)

I would like a query that shows, for the entire collection (entries in
CollectedEpisodes), how many of the episodes are from each series...from
each
network.

This for the skeleton data given above. The query would return.

Roseanne 2
Cheers 2
House 0

and

ABC 2
NBC 2

How would I accomplish that? Simple joins I understand...this one is
beyond
me.

I would expect the answer to fall into the category of "here is the Join
syntax to return all records from 'NBC'" and then do a count, but I
cannot
work out the syntax.


Also secondly, is one query possible that says

"For each series that has at least one Collected Episode, here are the
Episodes needed to complete the respective series."


Thanks in advance

Thanks for the help Ken,

My two follow up questions a

1. How do you "save a query" in Visual BASIC such that you can query on a
query? I generally perform a query and then read the results with a
DataReader. I suppose this method will not work well for this.

2. The second query, from what I can see, does not do what was hoped for.
It gives back ALL episodes that are not collected whehter you have an episode
fomr a given series or not. The desired behaviour is to only give the
uncollected episodes for series that have at least one collected episode.

(Off to find out how to query a query in Visual Basic.)

"Ken Snell (MVP)" wrote:

For your first question (identifying number of series and networks), you
need to build two queries and save them:


qryDistinctNetworks

SELECT DISTINCT Networks.NetworkID, Networks.Network
FROM ((CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.EpisodeID)
INNER JOIN Network ON Network.NetworkID = Series.NetworkID;




qryDistinctSeries

SELECT DISTINCT Series.SeriesID, Series.SeriesName
FROM (CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.EpisodeID;


Then, these two queries should give you the desired counts:

SELECT (SELECT Count(Q.NetworkID) FROM qryDistinctNetworks Q
WHERE Q.NetworkID = qryDistinctNetworks.NetworkID) AS CountOfNetwork,
Network, NetworkID
FROM qryDistinctNetworks;



SELECT (SELECT Count(Q.SeriesID) FROM qryDistinctSeries Q
WHERE Q.SeriesID = qryDistinctSeries.SeriesID) AS CountOfSeries,
SeriesName, SeriesID
FROM qryDistinctSeries;



For the sdecond question (which episodes haven't been collected yet), use
this query:

SELECT Episodes.EpisodeID, Episodes.EpisodeName
FROM Episodes LEFT JOIN CollectedEpisodes
ON Episodes.EpisodeID = CollectedEpisodeID
WHERE CollectedEpisodeID IS NULL;


--

Ken Snell
MS ACCESS MVP


"Spektre" wrote in message
...
This is actually a MS Access/SQL, but used in conjunction with VB.net

This is a "simple" multitable join question. Some skeleton data to work
with is provided. Consider a database of collected classic television
episodes.

Table: Networks
NetworkID Network
1 NBC
2 CBS
3 ABC
4 PBS
5 FOX

Table: Series
SeriesID SeriesName NetworkID
1 Roseanne 3
2 Cheers 1
3 House 5

Table: Episodes
EpisodeID EpisodeName SeriesID
1 A New Becky 1
2 Roseanna Sings 1
3 Norm's Beer 2
4 Sammy Goes Long 2

Table: CollectedEpisodes
CollectedEpisodeID EpisodeID Format
1 1 VHS
2 1 DVD
3 3 TiVo
4 4 TiVo


In text, the tables define respectively, TV networks, Series Names,
Episode
Names, and finally which episodes a person has collected. Going down the
list each table entry has a 1 to many mapping (ie many series per network,
many episodes per series, many possible collected episodes (via different
media) per episode.)

I would like a query that shows, for the entire collection (entries in
CollectedEpisodes), how many of the episodes are from each series...from
each
network.

This for the skeleton data given above. The query would return.

Roseanne 2
Cheers 2
House 0

and

ABC 2
NBC 2

How would I accomplish that? Simple joins I understand...this one is
beyond
me.

I would expect the answer to fall into the category of "here is the Join
syntax to return all records from 'NBC'" and then do a count, but I
cannot
work out the syntax.


Also secondly, is one query possible that says

"For each series that has at least one Collected Episode, here are the
Episodes needed to complete the respective series."


Thanks in advance

Instead of saving the two queries that I outlined, you can use these two
queries for the first two results that you seek:

SELECT (SELECT Count(Q.NetworkID) FROM
(SELECT DISTINCT Series.SeriesID, Series.SeriesName
FROM (CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.EpisodeID) Q
WHERE Q.NetworkID = qryDistinctNetworks.NetworkID) AS CountOfNetwork,
qryDistinctNetworks.Network, qryDistinctNetworks.NetworkID
FROM (SELECT DISTINCT Series.SeriesID, Series.SeriesName
FROM (CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.EpisodeID)
qryDistinctNetworks;



SELECT (SELECT Count(Q.SeriesID) FROM
(SELECT DISTINCT Series.SeriesID, Series.SeriesName
FROM (CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.EpisodeID) Q
WHERE Q.SeriesID = qryDistinctSeries.SeriesID) AS CountOfSeries,
qryDistinctSeries.SeriesName, qryDistinctSeries.SeriesID
FROM (SELECT DISTINCT Series.SeriesID, Series.SeriesName
FROM (CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.EpisodeID)
qryDistinctSeries;




For the second question (which episodes haven't been collected yet), use
this query (I did indeed not filter out for noncollected episodes)

SELECT Episodes.EpisodeID, Episodes.EpisodeName
FROM Episodes LEFT JOIN CollectedEpisodes
ON Episodes.EpisodeID = CollectedEpisodeID
WHERE CollectedEpisodeID IS NULL AND
Episodes.SeriesID IN (SELECT DISTINCT Episode.SeriesID
FROM Episodes INNER JOIN CollectedEpisodes
ON Episodes.EpisodeID = CollectedEpisodes.EpisodeID);

--

Ken Snell
MS ACCESS MVP


"Spektre" wrote in message
...
Thanks for the help Ken,

My two follow up questions a

1. How do you "save a query" in Visual BASIC such that you can query on a
query? I generally perform a query and then read the results with a
DataReader. I suppose this method will not work well for this.

2. The second query, from what I can see, does not do what was hoped
for.
It gives back ALL episodes that are not collected whehter you have an
episode
fomr a given series or not. The desired behaviour is to only give the
uncollected episodes for series that have at least one collected episode.

(Off to find out how to query a query in Visual Basic.)

"Ken Snell (MVP)" wrote:

For your first question (identifying number of series and networks), you
need to build two queries and save them:


qryDistinctNetworks

SELECT DISTINCT Networks.NetworkID, Networks.Network
FROM ((CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.EpisodeID)
INNER JOIN Network ON Network.NetworkID = Series.NetworkID;




qryDistinctSeries

SELECT DISTINCT Series.SeriesID, Series.SeriesName
FROM (CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.EpisodeID;


Then, these two queries should give you the desired counts:

SELECT (SELECT Count(Q.NetworkID) FROM qryDistinctNetworks Q
WHERE Q.NetworkID = qryDistinctNetworks.NetworkID) AS CountOfNetwork,
Network, NetworkID
FROM qryDistinctNetworks;



SELECT (SELECT Count(Q.SeriesID) FROM qryDistinctSeries Q
WHERE Q.SeriesID = qryDistinctSeries.SeriesID) AS CountOfSeries,
SeriesName, SeriesID
FROM qryDistinctSeries;



For the sdecond question (which episodes haven't been collected yet), use
this query:

SELECT Episodes.EpisodeID, Episodes.EpisodeName
FROM Episodes LEFT JOIN CollectedEpisodes
ON Episodes.EpisodeID = CollectedEpisodeID
WHERE CollectedEpisodeID IS NULL;


--

Ken Snell
MS ACCESS MVP


"Spektre" wrote in message
...
This is actually a MS Access/SQL, but used in conjunction with VB.net

This is a "simple" multitable join question. Some skeleton data to
work
with is provided. Consider a database of collected classic television
episodes.

Table: Networks
NetworkID Network
1 NBC
2 CBS
3 ABC
4 PBS
5 FOX

Table: Series
SeriesID SeriesName NetworkID
1 Roseanne 3
2 Cheers 1
3 House 5

Table: Episodes
EpisodeID EpisodeName SeriesID
1 A New Becky 1
2 Roseanna Sings 1
3 Norm's Beer 2
4 Sammy Goes Long 2

Table: CollectedEpisodes
CollectedEpisodeID EpisodeID Format
1 1 VHS
2 1 DVD
3 3 TiVo
4 4 TiVo


In text, the tables define respectively, TV networks, Series Names,
Episode
Names, and finally which episodes a person has collected. Going down
the
list each table entry has a 1 to many mapping (ie many series per
network,
many episodes per series, many possible collected episodes (via
different
media) per episode.)

I would like a query that shows, for the entire collection (entries in
CollectedEpisodes), how many of the episodes are from each
series...from
each
network.

This for the skeleton data given above. The query would return.

Roseanne 2
Cheers 2
House 0

and

ABC 2
NBC 2

How would I accomplish that? Simple joins I understand...this one is
beyond
me.

I would expect the answer to fall into the category of "here is the
Join
syntax to return all records from 'NBC'" and then do a count, but I
cannot
work out the syntax.


Also secondly, is one query possible that says

"For each series that has at least one Collected Episode, here are the
Episodes needed to complete the respective series."


Thanks in advance

Thanks again Ken,

i am trying to pick apart the SELECT statements you provided to understand
how they will work. Some of the clauses just seem nonsensical to me.

For example in the 1st query:

"...INNER JOIN Series ON Series.SeriesID = Episodes.EpisodeID..."

This seems to be comparing apples to oranges. Series.SeriesID refers to a
key identifying a unique series. Episodes.EpisodeID refers to a key
identifying a unque episode. There will be cases of equivalence, but you are
comparing disparate item types. Did you mean Episodes.SeriesID?

The rest of it is going to take me days to deconstruct lol. I am not
familar with the AS keyword nor the use of "renaming" as you have done with Q
and qryDistinctNetworks.

"Ken Snell (MVP)" wrote:

Instead of saving the two queries that I outlined, you can use these two
queries for the first two results that you seek:

SELECT (SELECT Count(Q.NetworkID) FROM
(SELECT DISTINCT Series.SeriesID, Series.SeriesName
FROM (CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.EpisodeID) Q
WHERE Q.NetworkID = qryDistinctNetworks.NetworkID) AS CountOfNetwork,
qryDistinctNetworks.Network, qryDistinctNetworks.NetworkID
FROM (SELECT DISTINCT Series.SeriesID, Series.SeriesName
FROM (CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.EpisodeID)
qryDistinctNetworks;



SELECT (SELECT Count(Q.SeriesID) FROM
(SELECT DISTINCT Series.SeriesID, Series.SeriesName
FROM (CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.EpisodeID) Q
WHERE Q.SeriesID = qryDistinctSeries.SeriesID) AS CountOfSeries,
qryDistinctSeries.SeriesName, qryDistinctSeries.SeriesID
FROM (SELECT DISTINCT Series.SeriesID, Series.SeriesName
FROM (CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.EpisodeID)
qryDistinctSeries;




For the second question (which episodes haven't been collected yet), use
this query (I did indeed not filter out for noncollected episodes)

SELECT Episodes.EpisodeID, Episodes.EpisodeName
FROM Episodes LEFT JOIN CollectedEpisodes
ON Episodes.EpisodeID = CollectedEpisodeID
WHERE CollectedEpisodeID IS NULL AND
Episodes.SeriesID IN (SELECT DISTINCT Episode.SeriesID
FROM Episodes INNER JOIN CollectedEpisodes
ON Episodes.EpisodeID = CollectedEpisodes.EpisodeID);

--

Ken Snell
MS ACCESS MVP


"Spektre" wrote in message
...
Thanks for the help Ken,

My two follow up questions a

1. How do you "save a query" in Visual BASIC such that you can query on a
query? I generally perform a query and then read the results with a
DataReader. I suppose this method will not work well for this.

2. The second query, from what I can see, does not do what was hoped
for.
It gives back ALL episodes that are not collected whehter you have an
episode
fomr a given series or not. The desired behaviour is to only give the
uncollected episodes for series that have at least one collected episode.

(Off to find out how to query a query in Visual Basic.)

"Ken Snell (MVP)" wrote:

For your first question (identifying number of series and networks), you
need to build two queries and save them:


qryDistinctNetworks

SELECT DISTINCT Networks.NetworkID, Networks.Network
FROM ((CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.EpisodeID)
INNER JOIN Network ON Network.NetworkID = Series.NetworkID;




qryDistinctSeries

SELECT DISTINCT Series.SeriesID, Series.SeriesName
FROM (CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.EpisodeID;


Then, these two queries should give you the desired counts:

SELECT (SELECT Count(Q.NetworkID) FROM qryDistinctNetworks Q
WHERE Q.NetworkID = qryDistinctNetworks.NetworkID) AS CountOfNetwork,
Network, NetworkID
FROM qryDistinctNetworks;



SELECT (SELECT Count(Q.SeriesID) FROM qryDistinctSeries Q
WHERE Q.SeriesID = qryDistinctSeries.SeriesID) AS CountOfSeries,
SeriesName, SeriesID
FROM qryDistinctSeries;



For the sdecond question (which episodes haven't been collected yet), use
this query:

SELECT Episodes.EpisodeID, Episodes.EpisodeName
FROM Episodes LEFT JOIN CollectedEpisodes
ON Episodes.EpisodeID = CollectedEpisodeID
WHERE CollectedEpisodeID IS NULL;


--

Ken Snell
MS ACCESS MVP


"Spektre" wrote in message
...
This is actually a MS Access/SQL, but used in conjunction with VB.net

This is a "simple" multitable join question. Some skeleton data to
work
with is provided. Consider a database of collected classic television
episodes.

Table: Networks
NetworkID Network
1 NBC
2 CBS
3 ABC
4 PBS
5 FOX

Table: Series
SeriesID SeriesName NetworkID
1 Roseanne 3
2 Cheers 1
3 House 5

Table: Episodes
EpisodeID EpisodeName SeriesID
1 A New Becky 1
2 Roseanna Sings 1
3 Norm's Beer 2
4 Sammy Goes Long 2

Table: CollectedEpisodes
CollectedEpisodeID EpisodeID Format
1 1 VHS
2 1 DVD
3 3 TiVo
4 4 TiVo


In text, the tables define respectively, TV networks, Series Names,
Episode
Names, and finally which episodes a person has collected. Going down
the
list each table entry has a 1 to many mapping (ie many series per
network,
many episodes per series, many possible collected episodes (via
different
media) per episode.)

I would like a query that shows, for the entire collection (entries in
CollectedEpisodes), how many of the episodes are from each
series...from
each
network.

This for the skeleton data given above. The query would return.

Roseanne 2
Cheers 2
House 0

and

ABC 2
NBC 2

How would I accomplish that? Simple joins I understand...this one is
beyond
me.

I would expect the answer to fall into the category of "here is the
Join
syntax to return all records from 'NBC'" and then do a count, but I
cannot
work out the syntax.


Also secondly, is one query possible that says

"For each series that has at least one Collected Episode, here are the
Episodes needed to complete the respective series."


Thanks in advance

My apologies; typos....

SELECT (SELECT Count(Q.NetworkID) FROM
(SELECT DISTINCT Series.SeriesID, Series.SeriesName
FROM (CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.SeriesID) Q
WHERE Q.NetworkID = qryDistinctNetworks.NetworkID) AS CountOfNetwork,
qryDistinctNetworks.Network, qryDistinctNetworks.NetworkID
FROM (SELECT DISTINCT Series.SeriesID, Series.SeriesName
FROM (CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.SeriesID)
qryDistinctNetworks;



SELECT (SELECT Count(Q.SeriesID) FROM
(SELECT DISTINCT Series.SeriesID, Series.SeriesName
FROM (CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.SeriesID) Q
WHERE Q.SeriesID = qryDistinctSeries.SeriesID) AS CountOfSeries,
qryDistinctSeries.SeriesName, qryDistinctSeries.SeriesID
FROM (SELECT DISTINCT Series.SeriesID, Series.SeriesName
FROM (CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.SeriesID)
qryDistinctSeries;


The "renaming" is using an alias for the query SQL statement. In SQL, one
can use AS or just leave it out when identifying an alias for a query:

SELECT (SELECT Count(Q.SeriesID) FROM
(SELECT DISTINCT Series.SeriesID, Series.SeriesName
FROM (CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.SeriesID) Q
WHERE Q.SeriesID = qryDistinctSeries.SeriesID) AS CountOfSeries,
qryDistinctSeries.SeriesName, qryDistinctSeries.SeriesID
FROM (SELECT DISTINCT Series.SeriesID, Series.SeriesName
FROM (CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.SeriesID)
qryDistinctSeries;

is the same as

SELECT (SELECT Count(Q.SeriesID) FROM
(SELECT DISTINCT Series.SeriesID, Series.SeriesName
FROM (CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.SeriesID) AS Q
WHERE Q.SeriesID = qryDistinctSeries.SeriesID) AS CountOfSeries,
qryDistinctSeries.SeriesName, qryDistinctSeries.SeriesID
FROM (SELECT DISTINCT Series.SeriesID, Series.SeriesName
FROM (CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.SeriesID)
AS qryDistinctSeries;


Put all together this way, the query is definitely not easy to quickly
understand. That is why I'd initially recommended saving the two original
queries. All I did to create these new queries was to replace the saved
query names with the full SQL statements in the "final" queries. It's still
possible that the queries will need some debugging (I didn't test them,
unfortunately), as I wrote them "off the top of my head". Let me know if you
find problems.
--

Ken Snell
MS ACCESS MVP




"Spektre" wrote in message
...
Thanks again Ken,

i am trying to pick apart the SELECT statements you provided to understand
how they will work. Some of the clauses just seem nonsensical to me.

For example in the 1st query:

"...INNER JOIN Series ON Series.SeriesID = Episodes.EpisodeID..."

This seems to be comparing apples to oranges. Series.SeriesID refers to a
key identifying a unique series. Episodes.EpisodeID refers to a key
identifying a unque episode. There will be cases of equivalence, but you
are
comparing disparate item types. Did you mean Episodes.SeriesID?

The rest of it is going to take me days to deconstruct lol. I am not
familar with the AS keyword nor the use of "renaming" as you have done
with Q
and qryDistinctNetworks.

"Ken Snell (MVP)" wrote:

Instead of saving the two queries that I outlined, you can use these two
queries for the first two results that you seek:

SELECT (SELECT Count(Q.NetworkID) FROM
(SELECT DISTINCT Series.SeriesID, Series.SeriesName
FROM (CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.EpisodeID) Q
WHERE Q.NetworkID = qryDistinctNetworks.NetworkID) AS CountOfNetwork,
qryDistinctNetworks.Network, qryDistinctNetworks.NetworkID
FROM (SELECT DISTINCT Series.SeriesID, Series.SeriesName
FROM (CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.EpisodeID)
qryDistinctNetworks;



SELECT (SELECT Count(Q.SeriesID) FROM
(SELECT DISTINCT Series.SeriesID, Series.SeriesName
FROM (CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.EpisodeID) Q
WHERE Q.SeriesID = qryDistinctSeries.SeriesID) AS CountOfSeries,
qryDistinctSeries.SeriesName, qryDistinctSeries.SeriesID
FROM (SELECT DISTINCT Series.SeriesID, Series.SeriesName
FROM (CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.EpisodeID)
qryDistinctSeries;




For the second question (which episodes haven't been collected yet), use
this query (I did indeed not filter out for noncollected episodes)

SELECT Episodes.EpisodeID, Episodes.EpisodeName
FROM Episodes LEFT JOIN CollectedEpisodes
ON Episodes.EpisodeID = CollectedEpisodeID
WHERE CollectedEpisodeID IS NULL AND
Episodes.SeriesID IN (SELECT DISTINCT Episode.SeriesID
FROM Episodes INNER JOIN CollectedEpisodes
ON Episodes.EpisodeID = CollectedEpisodes.EpisodeID);

--

Ken Snell
MS ACCESS MVP


"Spektre" wrote in message
...
Thanks for the help Ken,

My two follow up questions a

1. How do you "save a query" in Visual BASIC such that you can query
on a
query? I generally perform a query and then read the results with a
DataReader. I suppose this method will not work well for this.

2. The second query, from what I can see, does not do what was hoped
for.
It gives back ALL episodes that are not collected whehter you have an
episode
fomr a given series or not. The desired behaviour is to only give the
uncollected episodes for series that have at least one collected
episode.

(Off to find out how to query a query in Visual Basic.)

"Ken Snell (MVP)" wrote:

For your first question (identifying number of series and networks),
you
need to build two queries and save them:


qryDistinctNetworks

SELECT DISTINCT Networks.NetworkID, Networks.Network
FROM ((CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.EpisodeID)
INNER JOIN Network ON Network.NetworkID = Series.NetworkID;




qryDistinctSeries

SELECT DISTINCT Series.SeriesID, Series.SeriesName
FROM (CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.EpisodeID;


Then, these two queries should give you the desired counts:

SELECT (SELECT Count(Q.NetworkID) FROM qryDistinctNetworks Q
WHERE Q.NetworkID = qryDistinctNetworks.NetworkID) AS CountOfNetwork,
Network, NetworkID
FROM qryDistinctNetworks;



SELECT (SELECT Count(Q.SeriesID) FROM qryDistinctSeries Q
WHERE Q.SeriesID = qryDistinctSeries.SeriesID) AS CountOfSeries,
SeriesName, SeriesID
FROM qryDistinctSeries;



For the sdecond question (which episodes haven't been collected yet),
use
this query:

SELECT Episodes.EpisodeID, Episodes.EpisodeName
FROM Episodes LEFT JOIN CollectedEpisodes
ON Episodes.EpisodeID = CollectedEpisodeID
WHERE CollectedEpisodeID IS NULL;


--

Ken Snell
MS ACCESS MVP


"Spektre" wrote in message
...
This is actually a MS Access/SQL, but used in conjunction with
VB.net

This is a "simple" multitable join question. Some skeleton data to
work
with is provided. Consider a database of collected classic
television
episodes.

Table: Networks
NetworkID Network
1 NBC
2 CBS
3 ABC
4 PBS
5 FOX

Table: Series
SeriesID SeriesName NetworkID
1 Roseanne 3
2 Cheers 1
3 House 5

Table: Episodes
EpisodeID EpisodeName SeriesID
1 A New Becky 1
2 Roseanna Sings 1
3 Norm's Beer 2
4 Sammy Goes Long 2

Table: CollectedEpisodes
CollectedEpisodeID EpisodeID Format
1 1 VHS
2 1 DVD
3 3 TiVo
4 4 TiVo


In text, the tables define respectively, TV networks, Series Names,
Episode
Names, and finally which episodes a person has collected. Going
down
the
list each table entry has a 1 to many mapping (ie many series per
network,
many episodes per series, many possible collected episodes (via
different
media) per episode.)

I would like a query that shows, for the entire collection (entries
in
CollectedEpisodes), how many of the episodes are from each
series...from
each
network.

This for the skeleton data given above. The query would return.

Roseanne 2
Cheers 2
House 0

and

ABC 2
NBC 2

How would I accomplish that? Simple joins I understand...this one
is
beyond
me.

I would expect the answer to fall into the category of "here is the
Join
syntax to return all records from 'NBC'" and then do a count, but I
cannot
work out the syntax.


Also secondly, is one query possible that says

"For each series that has at least one Collected Episode, here are
the
Episodes needed to complete the respective series."


Thanks in advance

No apologies necessary Ken, thanks for taking the time.

Another follow up question...

Dissecting the query it looks like "Q" and "qryDistinctNetworks" refer to
the same thing right?

Namely:

(SELECT DISTINCT Series.SeriesID, Series.SeriesName FROM (CollectedEpisodes
INNER JOIN Episodes ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.SeriesID)

right?




INNER JOIN Series ON Series.SeriesID = Episodes.Series

"Ken Snell (MVP)" wrote:

My apologies; typos....

SELECT (SELECT Count(Q.NetworkID) FROM
(SELECT DISTINCT Series.SeriesID, Series.SeriesName
FROM (CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.SeriesID) Q
WHERE Q.NetworkID = qryDistinctNetworks.NetworkID) AS CountOfNetwork,
qryDistinctNetworks.Network, qryDistinctNetworks.NetworkID
FROM (SELECT DISTINCT Series.SeriesID, Series.SeriesName
FROM (CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.SeriesID)
qryDistinctNetworks;



SELECT (SELECT Count(Q.SeriesID) FROM
(SELECT DISTINCT Series.SeriesID, Series.SeriesName
FROM (CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.SeriesID) Q
WHERE Q.SeriesID = qryDistinctSeries.SeriesID) AS CountOfSeries,
qryDistinctSeries.SeriesName, qryDistinctSeries.SeriesID
FROM (SELECT DISTINCT Series.SeriesID, Series.SeriesName
FROM (CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.SeriesID)
qryDistinctSeries;


The "renaming" is using an alias for the query SQL statement. In SQL, one
can use AS or just leave it out when identifying an alias for a query:

SELECT (SELECT Count(Q.SeriesID) FROM
(SELECT DISTINCT Series.SeriesID, Series.SeriesName
FROM (CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.SeriesID) Q
WHERE Q.SeriesID = qryDistinctSeries.SeriesID) AS CountOfSeries,
qryDistinctSeries.SeriesName, qryDistinctSeries.SeriesID
FROM (SELECT DISTINCT Series.SeriesID, Series.SeriesName
FROM (CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.SeriesID)
qryDistinctSeries;

is the same as

SELECT (SELECT Count(Q.SeriesID) FROM
(SELECT DISTINCT Series.SeriesID, Series.SeriesName
FROM (CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.SeriesID) AS Q
WHERE Q.SeriesID = qryDistinctSeries.SeriesID) AS CountOfSeries,
qryDistinctSeries.SeriesName, qryDistinctSeries.SeriesID
FROM (SELECT DISTINCT Series.SeriesID, Series.SeriesName
FROM (CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.SeriesID)
AS qryDistinctSeries;


Put all together this way, the query is definitely not easy to quickly
understand. That is why I'd initially recommended saving the two original
queries. All I did to create these new queries was to replace the saved
query names with the full SQL statements in the "final" queries. It's still
possible that the queries will need some debugging (I didn't test them,
unfortunately), as I wrote them "off the top of my head". Let me know if you
find problems.
--

Ken Snell
MS ACCESS MVP




"Spektre" wrote in message
...
Thanks again Ken,

i am trying to pick apart the SELECT statements you provided to understand
how they will work. Some of the clauses just seem nonsensical to me.

For example in the 1st query:

"...INNER JOIN Series ON Series.SeriesID = Episodes.EpisodeID..."

This seems to be comparing apples to oranges. Series.SeriesID refers to a
key identifying a unique series. Episodes.EpisodeID refers to a key
identifying a unque episode. There will be cases of equivalence, but you
are
comparing disparate item types. Did you mean Episodes.SeriesID?

The rest of it is going to take me days to deconstruct lol. I am not
familar with the AS keyword nor the use of "renaming" as you have done
with Q
and qryDistinctNetworks.

"Ken Snell (MVP)" wrote:

Instead of saving the two queries that I outlined, you can use these two
queries for the first two results that you seek:

SELECT (SELECT Count(Q.NetworkID) FROM
(SELECT DISTINCT Series.SeriesID, Series.SeriesName
FROM (CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.EpisodeID) Q
WHERE Q.NetworkID = qryDistinctNetworks.NetworkID) AS CountOfNetwork,
qryDistinctNetworks.Network, qryDistinctNetworks.NetworkID
FROM (SELECT DISTINCT Series.SeriesID, Series.SeriesName
FROM (CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.EpisodeID)
qryDistinctNetworks;



SELECT (SELECT Count(Q.SeriesID) FROM
(SELECT DISTINCT Series.SeriesID, Series.SeriesName
FROM (CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.EpisodeID) Q
WHERE Q.SeriesID = qryDistinctSeries.SeriesID) AS CountOfSeries,
qryDistinctSeries.SeriesName, qryDistinctSeries.SeriesID
FROM (SELECT DISTINCT Series.SeriesID, Series.SeriesName
FROM (CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.EpisodeID)
qryDistinctSeries;




For the second question (which episodes haven't been collected yet), use
this query (I did indeed not filter out for noncollected episodes)

SELECT Episodes.EpisodeID, Episodes.EpisodeName
FROM Episodes LEFT JOIN CollectedEpisodes
ON Episodes.EpisodeID = CollectedEpisodeID
WHERE CollectedEpisodeID IS NULL AND
Episodes.SeriesID IN (SELECT DISTINCT Episode.SeriesID
FROM Episodes INNER JOIN CollectedEpisodes
ON Episodes.EpisodeID = CollectedEpisodes.EpisodeID);

--

Ken Snell
MS ACCESS MVP


"Spektre" wrote in message
...
Thanks for the help Ken,

My two follow up questions a

1. How do you "save a query" in Visual BASIC such that you can query
on a
query? I generally perform a query and then read the results with a
DataReader. I suppose this method will not work well for this.

2. The second query, from what I can see, does not do what was hoped
for.
It gives back ALL episodes that are not collected whehter you have an
episode
fomr a given series or not. The desired behaviour is to only give the
uncollected episodes for series that have at least one collected
episode.

(Off to find out how to query a query in Visual Basic.)

"Ken Snell (MVP)" wrote:

For your first question (identifying number of series and networks),
you
need to build two queries and save them:


qryDistinctNetworks

SELECT DISTINCT Networks.NetworkID, Networks.Network
FROM ((CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.EpisodeID)
INNER JOIN Network ON Network.NetworkID = Series.NetworkID;




qryDistinctSeries

SELECT DISTINCT Series.SeriesID, Series.SeriesName
FROM (CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.EpisodeID;


Then, these two queries should give you the desired counts:

SELECT (SELECT Count(Q.NetworkID) FROM qryDistinctNetworks Q
WHERE Q.NetworkID = qryDistinctNetworks.NetworkID) AS CountOfNetwork,
Network, NetworkID
FROM qryDistinctNetworks;



SELECT (SELECT Count(Q.SeriesID) FROM qryDistinctSeries Q
WHERE Q.SeriesID = qryDistinctSeries.SeriesID) AS CountOfSeries,
SeriesName, SeriesID
FROM qryDistinctSeries;



For the sdecond question (which episodes haven't been collected yet),
use
this query:

SELECT Episodes.EpisodeID, Episodes.EpisodeName
FROM Episodes LEFT JOIN CollectedEpisodes
ON Episodes.EpisodeID = CollectedEpisodeID
WHERE CollectedEpisodeID IS NULL;


--

Ken Snell
MS ACCESS MVP


"Spektre" wrote in message
...
This is actually a MS Access/SQL, but used in conjunction with
VB.net

This is a "simple" multitable join question. Some skeleton data to
work
with is provided. Consider a database of collected classic
television
episodes.

Table: Networks
NetworkID Network
1 NBC
2 CBS
3 ABC
4 PBS
5 FOX

Table: Series
SeriesID SeriesName NetworkID
1 Roseanne 3
2 Cheers 1
3 House 5

Table: Episodes
EpisodeID EpisodeName SeriesID
1 A New Becky 1
2 Roseanna Sings 1
3 Norm's Beer 2
4 Sammy Goes Long 2

Table: CollectedEpisodes
CollectedEpisodeID EpisodeID Format
1 1 VHS
2 1 DVD
3 3 TiVo
4 4 TiVo


In text, the tables define respectively, TV networks, Series Names,
Episode
Names, and finally which episodes a person has collected. Going
down
the
list each table entry has a 1 to many mapping (ie many series per
network,
many episodes per series, many possible collected episodes (via
different
media) per episode.)

I would like a query that shows, for the entire collection (entries
in
CollectedEpisodes), how many of the episodes are from each
series...from
each
network.

This for the skeleton data given above. The query would return.

Roseanne 2
Cheers 2
House 0

and

ABC 2
NBC 2

Yes, that is correct. Those two aliases refer to the same subquery.

--

Ken Snell
MS ACCESS MVP


"Spektre" wrote in message
...
No apologies necessary Ken, thanks for taking the time.

Another follow up question...

Dissecting the query it looks like "Q" and "qryDistinctNetworks" refer to
the same thing right?

Namely:

(SELECT DISTINCT Series.SeriesID, Series.SeriesName FROM
(CollectedEpisodes
INNER JOIN Episodes ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.SeriesID)

right?




INNER JOIN Series ON Series.SeriesID = Episodes.Series

"Ken Snell (MVP)" wrote:

My apologies; typos....

SELECT (SELECT Count(Q.NetworkID) FROM
(SELECT DISTINCT Series.SeriesID, Series.SeriesName
FROM (CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.SeriesID) Q
WHERE Q.NetworkID = qryDistinctNetworks.NetworkID) AS CountOfNetwork,
qryDistinctNetworks.Network, qryDistinctNetworks.NetworkID
FROM (SELECT DISTINCT Series.SeriesID, Series.SeriesName
FROM (CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.SeriesID)
qryDistinctNetworks;



SELECT (SELECT Count(Q.SeriesID) FROM
(SELECT DISTINCT Series.SeriesID, Series.SeriesName
FROM (CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.SeriesID) Q
WHERE Q.SeriesID = qryDistinctSeries.SeriesID) AS CountOfSeries,
qryDistinctSeries.SeriesName, qryDistinctSeries.SeriesID
FROM (SELECT DISTINCT Series.SeriesID, Series.SeriesName
FROM (CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.SeriesID)
qryDistinctSeries;


The "renaming" is using an alias for the query SQL statement. In SQL, one
can use AS or just leave it out when identifying an alias for a query:

SELECT (SELECT Count(Q.SeriesID) FROM
(SELECT DISTINCT Series.SeriesID, Series.SeriesName
FROM (CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.SeriesID) Q
WHERE Q.SeriesID = qryDistinctSeries.SeriesID) AS CountOfSeries,
qryDistinctSeries.SeriesName, qryDistinctSeries.SeriesID
FROM (SELECT DISTINCT Series.SeriesID, Series.SeriesName
FROM (CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.SeriesID)
qryDistinctSeries;

is the same as

SELECT (SELECT Count(Q.SeriesID) FROM
(SELECT DISTINCT Series.SeriesID, Series.SeriesName
FROM (CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.SeriesID) AS Q
WHERE Q.SeriesID = qryDistinctSeries.SeriesID) AS CountOfSeries,
qryDistinctSeries.SeriesName, qryDistinctSeries.SeriesID
FROM (SELECT DISTINCT Series.SeriesID, Series.SeriesName
FROM (CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.SeriesID)
AS qryDistinctSeries;


Put all together this way, the query is definitely not easy to quickly
understand. That is why I'd initially recommended saving the two original
queries. All I did to create these new queries was to replace the saved
query names with the full SQL statements in the "final" queries. It's
still
possible that the queries will need some debugging (I didn't test them,
unfortunately), as I wrote them "off the top of my head". Let me know if
you
find problems.
--

Ken Snell
MS ACCESS MVP




"Spektre" wrote in message
...
Thanks again Ken,

i am trying to pick apart the SELECT statements you provided to
understand
how they will work. Some of the clauses just seem nonsensical to me.

For example in the 1st query:

"...INNER JOIN Series ON Series.SeriesID = Episodes.EpisodeID..."

This seems to be comparing apples to oranges. Series.SeriesID refers
to a
key identifying a unique series. Episodes.EpisodeID refers to a key
identifying a unque episode. There will be cases of equivalence, but
you
are
comparing disparate item types. Did you mean Episodes.SeriesID?

The rest of it is going to take me days to deconstruct lol. I am not
familar with the AS keyword nor the use of "renaming" as you have done
with Q
and qryDistinctNetworks.

"Ken Snell (MVP)" wrote:

Instead of saving the two queries that I outlined, you can use these
two
queries for the first two results that you seek:

SELECT (SELECT Count(Q.NetworkID) FROM
(SELECT DISTINCT Series.SeriesID, Series.SeriesName
FROM (CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.EpisodeID) Q
WHERE Q.NetworkID = qryDistinctNetworks.NetworkID) AS CountOfNetwork,
qryDistinctNetworks.Network, qryDistinctNetworks.NetworkID
FROM (SELECT DISTINCT Series.SeriesID, Series.SeriesName
FROM (CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.EpisodeID)
qryDistinctNetworks;



SELECT (SELECT Count(Q.SeriesID) FROM
(SELECT DISTINCT Series.SeriesID, Series.SeriesName
FROM (CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.EpisodeID) Q
WHERE Q.SeriesID = qryDistinctSeries.SeriesID) AS CountOfSeries,
qryDistinctSeries.SeriesName, qryDistinctSeries.SeriesID
FROM (SELECT DISTINCT Series.SeriesID, Series.SeriesName
FROM (CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.EpisodeID)
qryDistinctSeries;




For the second question (which episodes haven't been collected yet),
use
this query (I did indeed not filter out for noncollected episodes)

SELECT Episodes.EpisodeID, Episodes.EpisodeName
FROM Episodes LEFT JOIN CollectedEpisodes
ON Episodes.EpisodeID = CollectedEpisodeID
WHERE CollectedEpisodeID IS NULL AND
Episodes.SeriesID IN (SELECT DISTINCT Episode.SeriesID
FROM Episodes INNER JOIN CollectedEpisodes
ON Episodes.EpisodeID = CollectedEpisodes.EpisodeID);

--

Ken Snell
MS ACCESS MVP


"Spektre" wrote in message
...
Thanks for the help Ken,

My two follow up questions a

1. How do you "save a query" in Visual BASIC such that you can
query
on a
query? I generally perform a query and then read the results with a
DataReader. I suppose this method will not work well for this.

2. The second query, from what I can see, does not do what was
hoped
for.
It gives back ALL episodes that are not collected whehter you have
an
episode
fomr a given series or not. The desired behaviour is to only give
the
uncollected episodes for series that have at least one collected
episode.

(Off to find out how to query a query in Visual Basic.)

"Ken Snell (MVP)" wrote:

For your first question (identifying number of series and
networks),
you
need to build two queries and save them:


qryDistinctNetworks

SELECT DISTINCT Networks.NetworkID, Networks.Network
FROM ((CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.EpisodeID)
INNER JOIN Network ON Network.NetworkID = Series.NetworkID;




qryDistinctSeries

SELECT DISTINCT Series.SeriesID, Series.SeriesName
FROM (CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.EpisodeID;


Then, these two queries should give you the desired counts:

SELECT (SELECT Count(Q.NetworkID) FROM qryDistinctNetworks Q
WHERE Q.NetworkID = qryDistinctNetworks.NetworkID) AS
CountOfNetwork,
Network, NetworkID
FROM qryDistinctNetworks;



SELECT (SELECT Count(Q.SeriesID) FROM qryDistinctSeries Q
WHERE Q.SeriesID = qryDistinctSeries.SeriesID) AS CountOfSeries,
SeriesName, SeriesID
FROM qryDistinctSeries;



For the sdecond question (which episodes haven't been collected
yet),
use
this query:

SELECT Episodes.EpisodeID, Episodes.EpisodeName
FROM Episodes LEFT JOIN CollectedEpisodes
ON Episodes.EpisodeID = CollectedEpisodeID
WHERE CollectedEpisodeID IS NULL;


--

Ken Snell
MS ACCESS MVP


"Spektre" wrote in message
...
This is actually a MS Access/SQL, but used in conjunction with
VB.net

This is a "simple" multitable join question. Some skeleton data
to
work
with is provided. Consider a database of collected classic
television
episodes.

Table: Networks
NetworkID Network
1 NBC
2 CBS
3 ABC
4 PBS
5 FOX

Table: Series
SeriesID SeriesName NetworkID
1 Roseanne 3
2 Cheers 1
3 House 5

Table: Episodes
EpisodeID EpisodeName SeriesID
1 A New Becky 1
2 Roseanna Sings 1
3 Norm's Beer 2
4 Sammy Goes Long 2

Table: CollectedEpisodes
CollectedEpisodeID EpisodeID Format
1 1 VHS
2 1 DVD
3 3 TiVo
4 4 TiVo


In text, the tables define respectively, TV networks, Series
Names,
Episode
Names, and finally which episodes a person has collected. Going
down
the
list each table entry has a 1 to many mapping (ie many series per
network,
many episodes per series, many possible collected episodes (via
different
media) per episode.)

I would like a query that shows, for the entire collection
(entries
in
CollectedEpisodes), how many of the episodes are from each
series...from
each
network.

This for the skeleton data given above. The query would return.

Roseanne 2
Cheers 2
House 0

and

ABC 2
NBC 2

At the risk of being a pest...

In the first "superquery" we get the network name from

qryDistinctNetworks.Network

However the alias qryDistinctNetworks is made up of joins from the tables
Series,Episodes, and CollectedEpisodes. The table Networks is not included
in the join so we cannot access Networks.Network can we?

Fred

"Ken Snell (MVP)" wrote:

Yes, that is correct. Those two aliases refer to the same subquery.

--

Ken Snell
MS ACCESS MVP


"Spektre" wrote in message
...
No apologies necessary Ken, thanks for taking the time.

Another follow up question...

Dissecting the query it looks like "Q" and "qryDistinctNetworks" refer to
the same thing right?

Namely:

(SELECT DISTINCT Series.SeriesID, Series.SeriesName FROM
(CollectedEpisodes
INNER JOIN Episodes ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.SeriesID)

right?




INNER JOIN Series ON Series.SeriesID = Episodes.Series

"Ken Snell (MVP)" wrote:

My apologies; typos....

SELECT (SELECT Count(Q.NetworkID) FROM
(SELECT DISTINCT Series.SeriesID, Series.SeriesName
FROM (CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.SeriesID) Q
WHERE Q.NetworkID = qryDistinctNetworks.NetworkID) AS CountOfNetwork,
qryDistinctNetworks.Network, qryDistinctNetworks.NetworkID
FROM (SELECT DISTINCT Series.SeriesID, Series.SeriesName
FROM (CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.SeriesID)
qryDistinctNetworks;



SELECT (SELECT Count(Q.SeriesID) FROM
(SELECT DISTINCT Series.SeriesID, Series.SeriesName
FROM (CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.SeriesID) Q
WHERE Q.SeriesID = qryDistinctSeries.SeriesID) AS CountOfSeries,
qryDistinctSeries.SeriesName, qryDistinctSeries.SeriesID
FROM (SELECT DISTINCT Series.SeriesID, Series.SeriesName
FROM (CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.SeriesID)
qryDistinctSeries;


The "renaming" is using an alias for the query SQL statement. In SQL, one
can use AS or just leave it out when identifying an alias for a query:

SELECT (SELECT Count(Q.SeriesID) FROM
(SELECT DISTINCT Series.SeriesID, Series.SeriesName
FROM (CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.SeriesID) Q
WHERE Q.SeriesID = qryDistinctSeries.SeriesID) AS CountOfSeries,
qryDistinctSeries.SeriesName, qryDistinctSeries.SeriesID
FROM (SELECT DISTINCT Series.SeriesID, Series.SeriesName
FROM (CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.SeriesID)
qryDistinctSeries;

is the same as

SELECT (SELECT Count(Q.SeriesID) FROM
(SELECT DISTINCT Series.SeriesID, Series.SeriesName
FROM (CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.SeriesID) AS Q
WHERE Q.SeriesID = qryDistinctSeries.SeriesID) AS CountOfSeries,
qryDistinctSeries.SeriesName, qryDistinctSeries.SeriesID
FROM (SELECT DISTINCT Series.SeriesID, Series.SeriesName
FROM (CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.SeriesID)
AS qryDistinctSeries;


Put all together this way, the query is definitely not easy to quickly
understand. That is why I'd initially recommended saving the two original
queries. All I did to create these new queries was to replace the saved
query names with the full SQL statements in the "final" queries. It's
still
possible that the queries will need some debugging (I didn't test them,
unfortunately), as I wrote them "off the top of my head". Let me know if
you
find problems.
--

Ken Snell
MS ACCESS MVP




"Spektre" wrote in message
...
Thanks again Ken,

i am trying to pick apart the SELECT statements you provided to
understand
how they will work. Some of the clauses just seem nonsensical to me.

For example in the 1st query:

"...INNER JOIN Series ON Series.SeriesID = Episodes.EpisodeID..."

This seems to be comparing apples to oranges. Series.SeriesID refers
to a
key identifying a unique series. Episodes.EpisodeID refers to a key
identifying a unque episode. There will be cases of equivalence, but
you
are
comparing disparate item types. Did you mean Episodes.SeriesID?

The rest of it is going to take me days to deconstruct lol. I am not
familar with the AS keyword nor the use of "renaming" as you have done
with Q
and qryDistinctNetworks.

"Ken Snell (MVP)" wrote:

Instead of saving the two queries that I outlined, you can use these
two
queries for the first two results that you seek:

SELECT (SELECT Count(Q.NetworkID) FROM
(SELECT DISTINCT Series.SeriesID, Series.SeriesName
FROM (CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.EpisodeID) Q
WHERE Q.NetworkID = qryDistinctNetworks.NetworkID) AS CountOfNetwork,
qryDistinctNetworks.Network, qryDistinctNetworks.NetworkID
FROM (SELECT DISTINCT Series.SeriesID, Series.SeriesName
FROM (CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.EpisodeID)
qryDistinctNetworks;



SELECT (SELECT Count(Q.SeriesID) FROM
(SELECT DISTINCT Series.SeriesID, Series.SeriesName
FROM (CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.EpisodeID) Q
WHERE Q.SeriesID = qryDistinctSeries.SeriesID) AS CountOfSeries,
qryDistinctSeries.SeriesName, qryDistinctSeries.SeriesID
FROM (SELECT DISTINCT Series.SeriesID, Series.SeriesName
FROM (CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.EpisodeID)
qryDistinctSeries;




For the second question (which episodes haven't been collected yet),
use
this query (I did indeed not filter out for noncollected episodes)

SELECT Episodes.EpisodeID, Episodes.EpisodeName
FROM Episodes LEFT JOIN CollectedEpisodes
ON Episodes.EpisodeID = CollectedEpisodeID
WHERE CollectedEpisodeID IS NULL AND
Episodes.SeriesID IN (SELECT DISTINCT Episode.SeriesID
FROM Episodes INNER JOIN CollectedEpisodes
ON Episodes.EpisodeID = CollectedEpisodes.EpisodeID);

--

Ken Snell
MS ACCESS MVP


"Spektre" wrote in message
...
Thanks for the help Ken,

My two follow up questions a

1. How do you "save a query" in Visual BASIC such that you can
query
on a
query? I generally perform a query and then read the results with a
DataReader. I suppose this method will not work well for this.

2. The second query, from what I can see, does not do what was
hoped
for.
It gives back ALL episodes that are not collected whehter you have
an
episode
fomr a given series or not. The desired behaviour is to only give
the
uncollected episodes for series that have at least one collected
episode.

(Off to find out how to query a query in Visual Basic.)

"Ken Snell (MVP)" wrote:

For your first question (identifying number of series and
networks),
you
need to build two queries and save them:


qryDistinctNetworks

SELECT DISTINCT Networks.NetworkID, Networks.Network
FROM ((CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.EpisodeID)
INNER JOIN Network ON Network.NetworkID = Series.NetworkID;




qryDistinctSeries

SELECT DISTINCT Series.SeriesID, Series.SeriesName
FROM (CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.EpisodeID;


Then, these two queries should give you the desired counts:

SELECT (SELECT Count(Q.NetworkID) FROM qryDistinctNetworks Q
WHERE Q.NetworkID = qryDistinctNetworks.NetworkID) AS
CountOfNetwork,
Network, NetworkID
FROM qryDistinctNetworks;



SELECT (SELECT Count(Q.SeriesID) FROM qryDistinctSeries Q
WHERE Q.SeriesID = qryDistinctSeries.SeriesID) AS CountOfSeries,
SeriesName, SeriesID
FROM qryDistinctSeries;



For the sdecond question (which episodes haven't been collected
yet),
use
this query:

SELECT Episodes.EpisodeID, Episodes.EpisodeName
FROM Episodes LEFT JOIN CollectedEpisodes
ON Episodes.EpisodeID = CollectedEpisodeID
WHERE CollectedEpisodeID IS NULL;


--

Ken Snell
MS ACCESS MVP


"Spektre" wrote in message
...
This is actually a MS Access/SQL, but used in conjunction with
VB.net

This is a "simple" multitable join question. Some skeleton data
to
work
with is provided. Consider a database of collected classic
television
episodes.

Table: Networks
NetworkID Network
1 NBC
2 CBS
3 ABC
4 PBS
5 FOX

Table: Series

I think I see what happened. When you copied the original stored queries
into the 2nd queries, you copied the same stored query to each "superquery".
The network subquery is different.

"Spektre" wrote:

At the risk of being a pest...

In the first "superquery" we get the network name from

qryDistinctNetworks.Network

However the alias qryDistinctNetworks is made up of joins from the tables
Series,Episodes, and CollectedEpisodes. The table Networks is not included
in the join so we cannot access Networks.Network can we?

Fred

"Ken Snell (MVP)" wrote:

Yes, that is correct. Those two aliases refer to the same subquery.

--

Ken Snell
MS ACCESS MVP


"Spektre" wrote in message
...
No apologies necessary Ken, thanks for taking the time.

Another follow up question...

Dissecting the query it looks like "Q" and "qryDistinctNetworks" refer to
the same thing right?

Namely:

(SELECT DISTINCT Series.SeriesID, Series.SeriesName FROM
(CollectedEpisodes
INNER JOIN Episodes ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.SeriesID)

right?




INNER JOIN Series ON Series.SeriesID = Episodes.Series

"Ken Snell (MVP)" wrote:

My apologies; typos....

SELECT (SELECT Count(Q.NetworkID) FROM
(SELECT DISTINCT Series.SeriesID, Series.SeriesName
FROM (CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.SeriesID) Q
WHERE Q.NetworkID = qryDistinctNetworks.NetworkID) AS CountOfNetwork,
qryDistinctNetworks.Network, qryDistinctNetworks.NetworkID
FROM (SELECT DISTINCT Series.SeriesID, Series.SeriesName
FROM (CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.SeriesID)
qryDistinctNetworks;



SELECT (SELECT Count(Q.SeriesID) FROM
(SELECT DISTINCT Series.SeriesID, Series.SeriesName
FROM (CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.SeriesID) Q
WHERE Q.SeriesID = qryDistinctSeries.SeriesID) AS CountOfSeries,
qryDistinctSeries.SeriesName, qryDistinctSeries.SeriesID
FROM (SELECT DISTINCT Series.SeriesID, Series.SeriesName
FROM (CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.SeriesID)
qryDistinctSeries;


The "renaming" is using an alias for the query SQL statement. In SQL, one
can use AS or just leave it out when identifying an alias for a query:

SELECT (SELECT Count(Q.SeriesID) FROM
(SELECT DISTINCT Series.SeriesID, Series.SeriesName
FROM (CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.SeriesID) Q
WHERE Q.SeriesID = qryDistinctSeries.SeriesID) AS CountOfSeries,
qryDistinctSeries.SeriesName, qryDistinctSeries.SeriesID
FROM (SELECT DISTINCT Series.SeriesID, Series.SeriesName
FROM (CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.SeriesID)
qryDistinctSeries;

is the same as

SELECT (SELECT Count(Q.SeriesID) FROM
(SELECT DISTINCT Series.SeriesID, Series.SeriesName
FROM (CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.SeriesID) AS Q
WHERE Q.SeriesID = qryDistinctSeries.SeriesID) AS CountOfSeries,
qryDistinctSeries.SeriesName, qryDistinctSeries.SeriesID
FROM (SELECT DISTINCT Series.SeriesID, Series.SeriesName
FROM (CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.SeriesID)
AS qryDistinctSeries;


Put all together this way, the query is definitely not easy to quickly
understand. That is why I'd initially recommended saving the two original
queries. All I did to create these new queries was to replace the saved
query names with the full SQL statements in the "final" queries. It's
still
possible that the queries will need some debugging (I didn't test them,
unfortunately), as I wrote them "off the top of my head". Let me know if
you
find problems.
--

Ken Snell
MS ACCESS MVP




"Spektre" wrote in message
...
Thanks again Ken,

i am trying to pick apart the SELECT statements you provided to
understand
how they will work. Some of the clauses just seem nonsensical to me.

For example in the 1st query:

"...INNER JOIN Series ON Series.SeriesID = Episodes.EpisodeID..."

This seems to be comparing apples to oranges. Series.SeriesID refers
to a
key identifying a unique series. Episodes.EpisodeID refers to a key
identifying a unque episode. There will be cases of equivalence, but
you
are
comparing disparate item types. Did you mean Episodes.SeriesID?

The rest of it is going to take me days to deconstruct lol. I am not
familar with the AS keyword nor the use of "renaming" as you have done
with Q
and qryDistinctNetworks.

"Ken Snell (MVP)" wrote:

Instead of saving the two queries that I outlined, you can use these
two
queries for the first two results that you seek:

SELECT (SELECT Count(Q.NetworkID) FROM
(SELECT DISTINCT Series.SeriesID, Series.SeriesName
FROM (CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.EpisodeID) Q
WHERE Q.NetworkID = qryDistinctNetworks.NetworkID) AS CountOfNetwork,
qryDistinctNetworks.Network, qryDistinctNetworks.NetworkID
FROM (SELECT DISTINCT Series.SeriesID, Series.SeriesName
FROM (CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.EpisodeID)
qryDistinctNetworks;



SELECT (SELECT Count(Q.SeriesID) FROM
(SELECT DISTINCT Series.SeriesID, Series.SeriesName
FROM (CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.EpisodeID) Q
WHERE Q.SeriesID = qryDistinctSeries.SeriesID) AS CountOfSeries,
qryDistinctSeries.SeriesName, qryDistinctSeries.SeriesID
FROM (SELECT DISTINCT Series.SeriesID, Series.SeriesName
FROM (CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.EpisodeID)
qryDistinctSeries;




For the second question (which episodes haven't been collected yet),
use
this query (I did indeed not filter out for noncollected episodes)

SELECT Episodes.EpisodeID, Episodes.EpisodeName
FROM Episodes LEFT JOIN CollectedEpisodes
ON Episodes.EpisodeID = CollectedEpisodeID
WHERE CollectedEpisodeID IS NULL AND
Episodes.SeriesID IN (SELECT DISTINCT Episode.SeriesID
FROM Episodes INNER JOIN CollectedEpisodes
ON Episodes.EpisodeID = CollectedEpisodes.EpisodeID);

--

Ken Snell
MS ACCESS MVP


"Spektre" wrote in message
...
Thanks for the help Ken,

My two follow up questions a

1. How do you "save a query" in Visual BASIC such that you can
query
on a
query? I generally perform a query and then read the results with a
DataReader. I suppose this method will not work well for this.

2. The second query, from what I can see, does not do what was
hoped
for.
It gives back ALL episodes that are not collected whehter you have
an
episode
fomr a given series or not. The desired behaviour is to only give
the
uncollected episodes for series that have at least one collected
episode.

(Off to find out how to query a query in Visual Basic.)

"Ken Snell (MVP)" wrote:

For your first question (identifying number of series and
networks),
you
need to build two queries and save them:


qryDistinctNetworks

SELECT DISTINCT Networks.NetworkID, Networks.Network
FROM ((CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.EpisodeID)
INNER JOIN Network ON Network.NetworkID = Series.NetworkID;




qryDistinctSeries

SELECT DISTINCT Series.SeriesID, Series.SeriesName
FROM (CollectedEpisodes INNER JOIN Episodes
ON CollectedEpisodes.EpisodeID = Episodes.EpisodeID)
INNER JOIN Series ON Series.SeriesID = Episodes.EpisodeID;


Then, these two queries should give you the desired counts:

SELECT (SELECT Count(Q.NetworkID) FROM qryDistinctNetworks Q
WHERE Q.NetworkID = qryDistinctNetworks.NetworkID) AS
CountOfNetwork,
Network, NetworkID
FROM qryDistinctNetworks;



SELECT (SELECT Count(Q.SeriesID) FROM qryDistinctSeries Q
WHERE Q.SeriesID = qryDistinctSeries.SeriesID) AS CountOfSeries,
SeriesName, SeriesID
FROM qryDistinctSeries;



For the sdecond question (which episodes haven't been collected
yet),
use
this query:

SELECT Episodes.EpisodeID, Episodes.EpisodeName
FROM Episodes LEFT JOIN CollectedEpisodes
ON Episodes.EpisodeID = CollectedEpisodeID
WHERE CollectedEpisodeID IS NULL;


--

Ken Snell
MS ACCESS MVP


"Spektre" wrote in message
...
This is actually a MS Access/SQL, but used in conjunction with
VB.net

This is a "simple" multitable join question. Some skeleton data
to