MAX with JOINS

[Cliff]

This undoubtedly is very simple, but for the life of me I can't get
the query to work. I tried a search of the archives, but didn't come
up with anything I could understand.

Two tables:
Pitchers_work with one field: playerID (unique values)
Fielding with two fields: playerID, yearID (playerID not unique)

I want a listing MAX(yearID) for each playerID in Pitchers_work. In
other words, with this data (I grouped and separated the records to
make it easier to read):

playerID yearID
----------------------
aberal01 1956
aberal01 1957
aberal01 1957
....
abercda01 1871
....
abernbi01 1952
....
abernbr01 2001
abernbr01 2002

I need this output:

PlayerID lastyr
----------------------
aberal01 1957
abercda01 1871
abernbi01 1952
abernbr01 2002

I usually get around these problems by transfering my data to Excel
because I understand that program, but I have too many records in this
case to fit on one worksheet. I could do that piecemeal, but I'd
rather try to get some grasp on SQL.

Thanks,
Cliff

[LoopyNZ]

Probably the key to what you want is to use 'Totals' when you build your
query.

1) To turn this on, click the 'Totals' button on the toolbar (looks like
a zig-zag E) while you're in query design mode. You'll see a 'Total'
row become available in the design grid (between 'Table' and 'Sort').
2) Add your tables to the query (joined on PlayerID).
3) Add your PlayerID and YearID fields to the query.
4) Leave the 'Total' field set to 'Group By' for PlayerID.
5) Change the 'Total' field for YearID to 'Max'.

Hope this helps, let us know how it goes!

PS This is one of those things that is MUCH easier in Access than
Excel... once you know how to do it, of course!

-------
LoopyNZ
-------

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!