League

[gil_wilkes]

I have a table called Data, with fields Home, Away, Referee. What I want to
do is find out which teams (Home or Away) a referee hasn't had, by entering a
referee's name.

 

[Bob Barrows [MVP]]

I have a table called Data, with fields Home, Away, Referee. What I
want to do is find out which teams (Home or Away) a referee hasn't
had, by entering a referee's name.

To start off, I'd be surprised if "Data" isn't a reserved keyword. You
should consider renaming the table to something a little more descriptive,
like GameData, which I can guarantee is not a reserved keyword.

This is not a trivial problem. You will need to use some advanced sql
techniques to get your answer. In fact, if you are not familiar with using
the SQL View of the Query Builder, now is the time to start.

With your database open in Access, click into the Queries tab and create a
new query in Design view. Cancel the Choose Tables dialog without selecting
a table. Use the View menu, or the toolbar button, or the right-click
context menu to switch to SQL View. This is where we will be working.

Now, it's simple to determine which Home teams the referee has not refereed.
Paste this into the sql window:

SELECT DISTINCT [Home] FROM [Data]
WHERE [Referee] <> [Enter Referee Name]

Click the Execute button in the toolbar to run it. Swithc back to Design
View to see what it looks like in the grid.

It is just as easy to figure out which Away teams have not been refereed.
Just change [Home] to [Away].

So, you see the problem: the teams exist in two columns. The solution is to
get them into a single column. How to do that? Well, here is where the first
advanced sql technique I was talking about comes into play. It's called a
"union" query. A union query is used to combine the data from two separate
queries into a single resultset. What we want to do is select the distinct
home teams and put them into the same column as the set of distinct away
teams. Here is how it works. Paste the following into the sql window:

SELECT DISTINCT [Home] As Team,[Referee] FROM [Data]
UNION
SELECT DISTINCT [Away],[Referee] FROM [Data]

Note that we are now in a sql-only world: Access will not allow you to
switch this query to Design View.
Note also: there is an optional

Run the query to see that we have now accomplished our first objective: we
have a list of teams that each referee has done. And that list is in a
single column.
Note also: there is an optional keyword that will affect the results of a
union query: "ALL". I did not use that keyword, so the query engine
eliminated duplicate records from the resultset. Put the keyword in to see
the difference:
SELECT DISTINCT [Home] As Team,[Referee] FROM [Data]
UNION ALL
SELECT DISTINCT [Away],[Referee] FROM [Data]

See how you now have multiple records with the same team and referee? Not
the intended result so let's take that keyword out of there. Note that this
particular situation is one where the keyword should not be used. In other
situations, for example, when you are sure that no duplicate results will be
obtained, you should use the "ALL" keyword, because performance will be
improved without the query engine having to perform the extra task of
removing duplicates.

Now, we need to use the next advanced sql technique: the subquery. Ideally,
that list of referees and teams would be in a table which could easily be
queried using the first piece of sql I provided above. The hurdle is to
realize that it IS a set of data that can be queried. A sql statement's FROM
clause is not limited to the names of tables or saved queries: it can also
contain an expression (a subquery) that results in a set of data. Here is
how it works:

SELECT DIstinct [Team] FROM
(
SELECT DISTINCT [Home] As Team,[Referee] FROM [Data]
UNION
SELECT DISTINCT [Away],[Referee] FROM [Data]
) As q
WHERE [Referee] <> [Enter Referee Name]

And that's the solution.
HTH,
Bob Barrows

 

[gil_wilkes]

Thank for you reply. I got the query working fine, but it lists all the teams
in the league. What I want is for it to list all teams that a referee has not
had. In data are the matches a referee has had, ie - home - away - referee.

I have a table called Data, with fields Home, Away, Referee. What I
want to do is find out which teams (Home or Away) a referee hasn't
had, by entering a referee's name.

To start off, I'd be surprised if "Data" isn't a reserved keyword. You
should consider renaming the table to something a little more descriptive,
like GameData, which I can guarantee is not a reserved keyword.

This is not a trivial problem. You will need to use some advanced sql
techniques to get your answer. In fact, if you are not familiar with using
the SQL View of the Query Builder, now is the time to start.

With your database open in Access, click into the Queries tab and create a
new query in Design view. Cancel the Choose Tables dialog without selecting
a table. Use the View menu, or the toolbar button, or the right-click
context menu to switch to SQL View. This is where we will be working.

Now, it's simple to determine which Home teams the referee has not refereed.
Paste this into the sql window:

SELECT DISTINCT [Home] FROM [Data]
WHERE [Referee] <> [Enter Referee Name]

Click the Execute button in the toolbar to run it. Swithc back to Design
View to see what it looks like in the grid.

It is just as easy to figure out which Away teams have not been refereed.
Just change [Home] to [Away].

So, you see the problem: the teams exist in two columns. The solution is to
get them into a single column. How to do that? Well, here is where the first
advanced sql technique I was talking about comes into play. It's called a
"union" query. A union query is used to combine the data from two separate
queries into a single resultset. What we want to do is select the distinct
home teams and put them into the same column as the set of distinct away
teams. Here is how it works. Paste the following into the sql window:

SELECT DISTINCT [Home] As Team,[Referee] FROM [Data]
UNION
SELECT DISTINCT [Away],[Referee] FROM [Data]

Note that we are now in a sql-only world: Access will not allow you to
switch this query to Design View.
Note also: there is an optional

Run the query to see that we have now accomplished our first objective: we
have a list of teams that each referee has done. And that list is in a
single column.
Note also: there is an optional keyword that will affect the results of a
union query: "ALL". I did not use that keyword, so the query engine
eliminated duplicate records from the resultset. Put the keyword in to see
the difference:
SELECT DISTINCT [Home] As Team,[Referee] FROM [Data]
UNION ALL
SELECT DISTINCT [Away],[Referee] FROM [Data]

See how you now have multiple records with the same team and referee? Not
the intended result so let's take that keyword out of there. Note that this
particular situation is one where the keyword should not be used. In other
situations, for example, when you are sure that no duplicate results will be
obtained, you should use the "ALL" keyword, because performance will be
improved without the query engine having to perform the extra task of
removing duplicates.

Now, we need to use the next advanced sql technique: the subquery. Ideally,
that list of referees and teams would be in a table which could easily be
queried using the first piece of sql I provided above. The hurdle is to
realize that it IS a set of data that can be queried. A sql statement's FROM
clause is not limited to the names of tables or saved queries: it can also
contain an expression (a subquery) that results in a set of data. Here is
how it works:

SELECT DIstinct [Team] FROM
(
SELECT DISTINCT [Home] As Team,[Referee] FROM [Data]
UNION
SELECT DISTINCT [Away],[Referee] FROM [Data]
) As q
WHERE [Referee] <> [Enter Referee Name]

And that's the solution.
HTH,
Bob Barrows

--
Microsoft MVP - ASP/ASP.NET - 2002-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

 

[Bob Barrows [MVP]]

Hmm, I did make a mistake in that answer, but now I see that even if I
correct the mistake, the query will still give the wrong results (no results
at all). Perhaps I was attempting to answer the wrong question. It always
helps to show a few rows of sample data followed by the results you want to
get from that data. Is it the matchups that are important?

Thank for you reply. I got the query working fine, but it lists all
the teams in the league. What I want is for it to list all teams that
a referee has not had. In data are the matches a referee has had, ie
- home - away - referee.

I have a table called Data, with fields Home, Away, Referee. What I
want to do is find out which teams (Home or Away) a referee hasn't
had, by entering a referee's name.

To start off, I'd be surprised if "Data" isn't a reserved keyword.
You should consider renaming the table to something a little more
descriptive, like GameData, which I can guarantee is not a reserved
keyword.

This is not a trivial problem. You will need to use some advanced sql
techniques to get your answer. In fact, if you are not familiar with
using the SQL View of the Query Builder, now is the time to start.

With your database open in Access, click into the Queries tab and
create a new query in Design view. Cancel the Choose Tables dialog
without selecting a table. Use the View menu, or the toolbar button,
or the right-click context menu to switch to SQL View. This is where
we will be working.

Now, it's simple to determine which Home teams the referee has not
refereed. Paste this into the sql window:

SELECT DISTINCT [Home] FROM [Data]
WHERE [Referee] <> [Enter Referee Name]

Click the Execute button in the toolbar to run it. Swithc back to
Design View to see what it looks like in the grid.

It is just as easy to figure out which Away teams have not been
refereed. Just change [Home] to [Away].

So, you see the problem: the teams exist in two columns. The
solution is to get them into a single column. How to do that? Well,
here is where the first advanced sql technique I was talking about
comes into play. It's called a "union" query. A union query is used
to combine the data from two separate queries into a single
resultset. What we want to do is select the distinct home teams and
put them into the same column as the set of distinct away teams.
Here is how it works. Paste the following into the sql window:

SELECT DISTINCT [Home] As Team,[Referee] FROM [Data]
UNION
SELECT DISTINCT [Away],[Referee] FROM [Data]

Note that we are now in a sql-only world: Access will not allow you
to switch this query to Design View.
Note also: there is an optional

Run the query to see that we have now accomplished our first
objective: we have a list of teams that each referee has done. And
that list is in a single column.
Note also: there is an optional keyword that will affect the results
of a union query: "ALL". I did not use that keyword, so the query
engine eliminated duplicate records from the resultset. Put the
keyword in to see the difference:
SELECT DISTINCT [Home] As Team,[Referee] FROM [Data]
UNION ALL
SELECT DISTINCT [Away],[Referee] FROM [Data]

See how you now have multiple records with the same team and
referee? Not the intended result so let's take that keyword out of
there. Note that this particular situation is one where the keyword
should not be used. In other situations, for example, when you are
sure that no duplicate results will be obtained, you should use the
"ALL" keyword, because performance will be improved without the
query engine having to perform the extra task of removing duplicates.

Now, we need to use the next advanced sql technique: the subquery.
Ideally, that list of referees and teams would be in a table which
could easily be queried using the first piece of sql I provided
above. The hurdle is to realize that it IS a set of data that can be
queried. A sql statement's FROM clause is not limited to the names
of tables or saved queries: it can also contain an expression (a
subquery) that results in a set of data. Here is how it works:

SELECT DIstinct [Team] FROM
(
SELECT DISTINCT [Home] As Team,[Referee] FROM [Data]
UNION
SELECT DISTINCT [Away],[Referee] FROM [Data]
) As q
WHERE [Referee] <> [Enter Referee Name]

And that's the solution.
HTH,
Bob Barrows

--
Microsoft MVP - ASP/ASP.NET - 2002-2007
Please reply to the newsgroup. This email account is my spam trap so
I don't check it very often. If you must reply off-line, then remove
the "NO SPAM"

 

[John Spencer]

You really need a table of referees and a table of teams. If you don't
have that you can construct queries to get the list.

qRefereeList - unique list of all referees
SELECT DISTINCT Referee
FROM Data

qTeamList - unique list of all teams
SELECT Home as TeamName
FROM DATA
UNION
SELECT Away
FROM Data

qPossibles - a list of all possible combinations of Referee and Teams
SELECT Referee, TeamName
FROM qRefereeList, qTeamList

qRefereedGames - a list of referees and teams they have refereed.
Another UNION query should give you that
SELECT Referee, Home as TeamName
FROM DATA
UNION
SELECT Referee, Away
FROM DATA

FINALLY, you can combine the last two queries in another query
SELECT qPossibles.*
FROM qPossibles LEFT JOIN qRefereedGames
ON qPossibles.Referee = qRefereedGames.Referee
AND qPossibles.Team = qRefereedGames.TeamName
WHERE qRefereedGames.Referee is Null

In theory, you could write one complex query that would do all that at
once.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Bob Barrows [MVP]]

You really need a table of referees and a table of teams. If you
don't have that you can construct queries to get the list.

Yes, that's what I was leaning towards once he clarified his intentions.

 

[gil_wilkes]

Thanks for your help it works fine but 'qPossibles' lists all referees and
all possible matches they havn't had, what I want is to search the list for a
referee's name, I've tried to figure it out but I can't get it to work.

 

[John Spencer]

qPossibles is designed to list the combination of all referees and all teams.

So if there are 40 teams and 10 referees you end up with 400 records. You
need to use that query in the last query in the posting.

The last query should list all the combinations of referree and team that have
not happened.

If that is not what you want, then please try to explain in more detail
exactly what results you are looking for. Perhaps a specific example would
help --- a small sample of existing data and a small sample of the expected
result.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[gil_wilkes]

Yes that is what happens, I have a list of over 700 combinations of teams and
referees. What I would like to do with that list is to search for a
particular referee by typing his name.

 

[gil_wilkes]

I have now figured it out with:-
SELECT qNotRefereed.Referee, qNotRefereed.TeamName, Teams.DIV
FROM qNotRefereed INNER JOIN Teams ON qNotRefereed.TeamName = Teams.TEAMS
WHERE (((qNotRefereed.Referee)=[referee name]))
ORDER BY qNotRefereed.TeamName;
Thanks for all your help, it now does what I want it to do with your help.

gil_wilkes