Unusual Query Problem

[Peter Hibbs]

Hi

I have a table (tblTeams) which has (amongst others) a text field
called TeamID and there are 14 records in the table. I need to create
a list of fixtures where every team plays each other team once.

I have created a 'Cartesian Product' query which has two copies of
tblTeams (with no joins) and a criteria which eliminates the case
where a team plays itself, as follows :-

SELECT tblTeams_1.TeamID AS HomeTeam, tblTeams.TeamID AS AwayTeam, ""
AS WeekNo
FROM tblTeams, tblTeams AS tblTeams_1
WHERE (((tblTeams_1.TeamID)<>[tblTeams].[TeamID]));

This produces a list where every team plays every other team *twice*.
What I am trying to do is modify the query so that the case where a
team plays another team only appears once. For example, say the team
names are TeamA, TeamB, etc. The query returns -

HomeTeam AwayTeam
TeamA TeamB
TeamB TeamA
etc (although not in this order).

I want to remove one of these records for each pair of teams (which
would then return half the number of records it does now). The WeekNo
field is just a blank field which will be filled in with a week number
when I get this bit working.

Any ideas on how to do this.

Thank you

Peter Hibbs.

 

[Baz]

Instead of (tblTeams_1.TeamID)<>[tblTeams].[TeamID]), try
(tblTeams_1.TeamID)>[tblTeams].[TeamID])

 

[Peter Hibbs]

Baz

Brilliant. It works fine (not sure why, I'll have to think about it).

Peter Hibbs

Instead of (tblTeams_1.TeamID)<>[tblTeams].[TeamID]), try
(tblTeams_1.TeamID)>[tblTeams].[TeamID])

Hi

I have a table (tblTeams) which has (amongst others) a text field
called TeamID and there are 14 records in the table. I need to create
a list of fixtures where every team plays each other team once.

I have created a 'Cartesian Product' query which has two copies of
tblTeams (with no joins) and a criteria which eliminates the case
where a team plays itself, as follows :-

SELECT tblTeams_1.TeamID AS HomeTeam, tblTeams.TeamID AS AwayTeam, ""
AS WeekNo
FROM tblTeams, tblTeams AS tblTeams_1
WHERE (((tblTeams_1.TeamID)<>[tblTeams].[TeamID]));

This produces a list where every team plays every other team *twice*.
What I am trying to do is modify the query so that the case where a
team plays another team only appears once. For example, say the team
names are TeamA, TeamB, etc. The query returns -

HomeTeam AwayTeam
TeamA TeamB
TeamB TeamA
etc (although not in this order).

I want to remove one of these records for each pair of teams (which
would then return half the number of records it does now). The WeekNo
field is just a blank field which will be filled in with a week number
when I get this bit working.

Any ideas on how to do this.

Thank you

Peter Hibbs.

 

[John Vinson]

Baz

Brilliant. It works fine (not sure why, I'll have to think about it).

It'll be pretty obvious when you do. If team 1 plays team 3, then you
can bet that team 3 is also playing team 1... <g>

John W. Vinson[MVP]