Query Where Two Fields from a Table Are Linked

[racefan91]

I have a couple of queries that are getting too complicated for what I know how query so I'm hoping someone will be able to help me.
This is my Table for entering Tournament Information


What I'm wondering is if there is a way to link my winner column to the corresponding Team column they are in. I want to be able to query stuff like the rank or seed of a team when they are either a winner or loser where one time they might be in Team1 and the next time they might be in Team2. Should I create a fields for the Winner and Loser that say rank and seed?

I also want to be able to do stuff like Filter by the a certain Rank, only show the times that team has one, and produce a count for the team that can be sorted.

I have this table set up as a template so certain criteria already entered to save time so it would be nice not to have to drastically change the design of my table to be able to do such queries.

If anybody has any input I would greatly appreciate it.

[June7]

If you want to query for a team regardless of which field they are in, one way is with UNION query that normalizes the data. There is no wizard or designer for UNION, must type in the SQL view of query builder.

SELECT Tournament, Round, Winner AS TeamName, "Winner" AS Result FROM tablename
UNION SELECT Tournament, Round, Loser, "Loser" FROM tablename;

Teams should have a name or ID that is unique across all regions. If each region has a TeamA and this is the value in stats table, queries will be more complicated, maybe impossible.

[Dal Jeanis]

That's why you don't put that all in one table. Break it down to component facts. I don't know how often your ranks or seeds will be recalculated, so I can't tell you exactly how to break down your tables.

I'll assume that within any tournament, the Ranks and Seeds will be set initially and stay the same.


tblTeam
teamID
TeamName

tblTeamRank
Round
Region
TeamID
Rank
Seed

In Round 1, East Region, Team A is Rank 1, Seed 1,
In Round 1, East Region, Team B is Rank 64, Seed 16

tblGames
Gamenumber
Round
Region

In Round 1, East Region, there is a Game X1,
In Round 1, East Region, there is a Game X2,
In Round 2, East Region, there is a Game Y1,



tblPlays
GameNumber
TeamID
WinOrLoss

Team A Plays Game X1, Wins
Team B Plays Game X1, Loses
Team C Plays Game X2, Loses
Team D Plays Game X2, Wins
Team A Plays Game Y1, Loses
Team D Plays Game Y1, Wins


You can join these tables to produce your same "View" above, but you can get much more useful information out of them, rather than duplicating your seed and rank information in a highly redundant fashion.

[racefan91]

I have things somewhat working. Using 5 queries to get the result seems like overkill but if it works I'm ok with it. I'm using parameters in certain fields I want to filter by and then using ([Tbl_Tournaments].[Winner]) in the Team criteria. I have one for team1 and one for team2. Then I have two queries off these where there is just a Team field and a winner field that has a count. Now in the last query I am trying to add the count of both queries. The issue I'm having is if a Team is only in one query they don't show up in the final query, only teams that are both in team1 and team2. Is there a way code in sql if one of the names doesn't exist in a query to just use 0 to add with?

[June7]

No, VBA won't help you with that, unless you want to write records to another table.

What you need is a master dataset that has all the teams. Join the two queries to that master. If you don't have a master table of teams, the master can be created by a query, again use a UNION.

[racefan91]

Ok, I do have a master dataset that has all the teams. When I created this query I have team from that table set with a relationship to the team from each of the other queries. That's where I don't understand if they are linked why they all don't show up in the query.

[June7]

What is the join type? Should be like "Show all records from Teams table ..."

[racefan91]

I ended up getting this to work and without the multiple queries. I had my team name linked to team1 and when I switched it be linked to winner everything worked exactly as I wanted it to.