Help Disambiguate my Join

[Commish]

Hello Helpful People. I have an ambiguous join - I understand why it
is ambiguous, I just cannot figure out a way around it to get the
correct set of results.

I have 2 tables, AllPlayers and PlayerHistory. The key fields are
playerID and YearID - where a player can have 0 or 1 records in a year
in either table. A player record can exist in multiple years - 1 or
more - in either table.

So, I do an outer join on playerId to get the set of all playerIDs. I
want to use a query to add information from PlayerHistory to the
AllPlayer information. So, I need to join on playerID and yearID, but
if I add in yearID in the join, it becomes ambiguous.

Doing the first join, saving the query, and making a second query
using the first query and PlayerHistory, gets me the same ambiguity
problem. So, what little technique am I missing?

 

[Bob Barrows]

Hello Helpful People. I have an ambiguous join - I understand why it
is ambiguous, I just cannot figure out a way around it to get the
correct set of results.

I have 2 tables, AllPlayers and PlayerHistory. The key fields are
playerID and YearID - where a player can have 0 or 1 records in a year
in either table. A player record can exist in multiple years - 1 or
more - in either table.

Huh? A table called "AllPlayers" might not contain a record for a player
entered in "PlayerHistory"???
Why call it "AllPlayers" if it does not contain _all_ the players??

So, I do an outer join on playerId to get the set of all playerIDs. I
want to use a query to add information from PlayerHistory to the
AllPlayer information.

You wish to store the same data in two places? Not really recommended ...
If you wish to create an update query that updates data in one table based
on data that exists in another table, an outer join is probably not the type
of join you want to use in any case. What is the point of getting data from
PlayerHistory that does not exist in AllPlayers for the purpose of updating
related records in AllPlayers? If the Player ID exists in PlayerHistory but
not in AllPlayers, there won't be an AllPlayer record to update will there?
An Inner Join seems to me to be the type of join you should be using.

So, I need to join on playerID and yearID, but
if I add in yearID in the join, it becomes ambiguous.

Does AllPlayers have a YearID field in it as well? This design is making
less sense as your description goes on. I would prefer if you actually
showed us a few sample rows of data from each table in addition to all this
narrative. It might help us (me) make more sense of things.

Doing the first join, saving the query, and making a second query
using the first query and PlayerHistory, gets me the same ambiguity
problem. So, what little technique am I missing?

I'm frankly lost now. Please show us the SQL statement you are using, As
well as the sample data showing both what it looks like before the query is
run and what you want it to look like after the query is run.

 

[John Spencer]

I think your structure is incorrect.

The AllPlayers table should have one record for each player.
Player history can have multiple records for a player (and perhaps multiple
records for the same player and year?).

If you need to record data about a player such as years played then you should
do that in a separate table.

With your current set up, you MIGHT be able to use a UNION query to generate a
comprehensive list of players and then use that to join to your two tables.

SELECT PlayerID
FROM AllPlayers
UNION
SELECT PlayerID
FROM PlayerHistory

That should give you a unique list of playerIds.

With that query saved, you can join to the other two tables to get data from both.

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