Football

[gil_wilkes]

I have a table called DATA with fields of DATE, HOME, AWAY, REFEREE. Referees
have multiple games and i need to find if referees have had the same team
(HOME or AWAY) in a certain time period. I've done the query with DATE &
HOME, and DATE & AWAY but I need to do to join them together to serch all
games.

 

[Michel Walsh]

Make a first query (in SQL view):

SELECT referee, home AS team
FROM data
WHERE date BETWEEN startingDate AND endingDate

UNION ALL

SELECT referee, away
FROM data
WHERE date BETWEEN startingDate AND endingDate



Call it qu1.


Next, make the query:

SELECT referee, team
FROM qu1
GROUP BY referee, team
HAVING COUNT(*) > 1


will return the referee-team which appears more than once in the given date
interval.



Vanderghast, Access MVP

 

[Ken Sheridan]

If you merely want the names of the referees and teams you can do as the
other respondent describes and use a UNION ALL operation to produce a result
table which you can then restrict by means of a HAVING clause. If you want
the full data, including the dates of the matches in question and which are
the away and home teams, then use the following:

PARAMETERS
[Enter start date:] DATETIME,
[Enter end date:] DATETIME;
SELECT *
FROM Data AS D1
WHERE D1.[Date] BETWEEN
[Enter start date:] AND [Enter end date:]
AND EXISTS
(SELECT *
FROM Data AS D2
WHERE D2.Referee = D1.Referee
AND (D2.Home = D1.Home
OR D2.Away = D1.Away
OR D2.Home = D1.Away
OR D2.Away = D1.Home)
AND D2.[Date] <> D1.[Date]
AND D2.[Date] BETWEEN
[Enter start date:] AND [Enter end date:])
ORDER BY D1.Referee,D1.[Date];

I'd recommend against naming a column 'Date', however. It’s the name of a
built in function (returning the current date) so is best avoided. use
something more specific such as MatchDate. if you do use 'Date' as a column
name be sure to enclose it in brackets, [Date],when referencing it.

Note that it’s a good idea to declare parameters of date/time data type.
This avoids false results arising from ambiguity of regional date formats, or
from a parameter value entered in short date format being interpreted as an
arithmetical expression rather than a date.

Ken Sheridan
Stafford, England

 

[gil_wilkes]

Thanks for your help, it works just as I want it to.

 

[gil_wilkes]

Thanks for your help, it works fine as I want it

If you merely want the names of the referees and teams you can do as the
other respondent describes and use a UNION ALL operation to produce a result
table which you can then restrict by means of a HAVING clause. If you want
the full data, including the dates of the matches in question and which are
the away and home teams, then use the following:

PARAMETERS
[Enter start date:] DATETIME,
[Enter end date:] DATETIME;
SELECT *
FROM Data AS D1
WHERE D1.[Date] BETWEEN
[Enter start date:] AND [Enter end date:]
AND EXISTS
(SELECT *
FROM Data AS D2
WHERE D2.Referee = D1.Referee
AND (D2.Home = D1.Home
OR D2.Away = D1.Away
OR D2.Home = D1.Away
OR D2.Away = D1.Home)
AND D2.[Date] <> D1.[Date]
AND D2.[Date] BETWEEN
[Enter start date:] AND [Enter end date:])
ORDER BY D1.Referee,D1.[Date];

I'd recommend against naming a column 'Date', however. It’s the name of a
built in function (returning the current date) so is best avoided. use
something more specific such as MatchDate. if you do use 'Date' as a column
name be sure to enclose it in brackets, [Date],when referencing it.

Note that it’s a good idea to declare parameters of date/time data type.
This avoids false results arising from ambiguity of regional date formats, or
from a parameter value entered in short date format being interpreted as an
arithmetical expression rather than a date.

Ken Sheridan
Stafford, England

I have a table called DATA with fields of DATE, HOME, AWAY, REFEREE. Referees
have multiple games and i need to find if referees have had the same team
(HOME or AWAY) in a certain time period. I've done the query with DATE &
HOME, and DATE & AWAY but I need to do to join them together to serch all
games.