SELECT DISTINCT, pulling an entire record of my choice

[shteev]

Hi. I have an Access database of cards for a trading card game, some of which have identical names, although their other fields may not be identical.

Example

NAME - COST - CARD TEXT - YEAR PRINTED

Fog - G - No creatures deal damage in combat this turn - 1995
Fog - G - Creatures deal no combat damage this turn - 1997

I want to write a query which, for each card which has multiple printings, all but the most recent. i.e, in this case:

Fog - G - Creatures deal no combat damage this turn - 1997

I want the query to return all the fields of my records, and so I'm having difficulty seeing how I can do this with functions like DISTINCT or GROUP BY.

 

[lespaul]

maybe something like:

SELECT Name, Cost, CardText, Max([Year Printed] FROM TableName
GROUP BY Name, Cost, CardText

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[shteev]

That returns both records.

 

[lespaul]

what about

SELECT [Name], Cost, CardText, [Year Printed] FROM tableName A
INNER JOIN (SELECT [NAME], Max([Year Printed] As MaxYear FROM TableName GROUP BY [Name]) B ON A.[Name] = B.[Name] AND A.[Year Printed] = B.MaxYear

 

[traingamer]

I always used 'edition' to track my Magic: the Gathering cards, rather than year. You might also check out Magic suitcase which I'd probably use instead if I was still playing.

(But Leslie's query should have worked given the criteria you supplied).

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill