Find records matching 2 criteria in one field

[baseball]

I have a database of baseball with a table (Batting) of statistics by playerID, year, team, league and I want to find all players who played in both leagues so I can see if players do better or worse when they change leagues. It may be simple but I cannot figure out how to generate that list. All I need is a list of playerIDs for all players who logged time in the AL and the NL. I can make a table from that to pull the stats. I'm not an SQL person; I write my queries in Access (but I can transform the general SQL query form to match my database). Thanks.

Cliff

 

[Joe4]

There are a few ways. Here is one:

1. Create a query usng criteria to only pull the AL members.
2. Create a second query using criteria to pull only NL members.
3. Perform a matched query on the first two queries created above. Those are the people you want.

 

[baseball]

There are a few ways. Here is one:
...
3. Perform a matched query on the first two queries created above. Those are the people you want.

This did cross my mind but I was wondering if I was missing a simpler solution. Thank you for the help as this will do what I need.

 

[Joe4]

Well, you can do it all in one "nested" query if you write it directly in SQL code instead of trying to use the Query Builder to do it.

If the key elements are:
Table Name: tblPlayer
Player ID Field Name: PlayerID
League Field Name: League

Then the code would look like this:

SELECT Z.PlayerID, Count(Z.League)
FROM
   (SELECT tblPlayer.PlayerID, tblPlayer.League
   FROM tblPlayer
   GROUP BY tblPlayer.PlayerID, tblPlayer.League) as Z
GROUP BY Z.PlayerID
HAVING Count(Z.League)>1;

 

[baseball]

That works. Thank you. I really need to find if my local community college has a course in SQL.