Prev: SQL totalling items report
Next: Duplicate fields in Access Database
From: pat67 on 16 Mar 2010 08:51
I have a union query showing the results from pool matches

GameID Player Opponent Result
1 Joe Bob Won
2 Jim Bill Won
1 Bob Joe Lost
2 Bill Jim Lost

Obviousy a lot more games but that is the premise. What i am looking
for is to show the last 10,20 whatever results for each player. Total
record is easy, just count won and lost. But I am not sure how do get
the last 20 games for each player. Can someone help?

Thanks
From: ghetto_banjo on 16 Mar 2010 09:13
Do you store the date/time of these matches? If not, it looks like
the only way to grab the "last" 20 matches would be via the GameID.

I am not sure how your table is setup exactly, but this should point
you in the right direction.

SELECT TOP 20 GameID, Player, Opponent, Result
FROM tblMatches
WHERE Player = 'Joe'
ORDER BY GameID DESC;
From: pat67 on 16 Mar 2010 09:46
On Mar 16, 9:13 am, ghetto_banjo <adam.v...(a)gmail.com> wrote:
> Do you store the date/time of these matches?  If not, it looks like
> the only way to grab the "last" 20 matches would be via the GameID.
>
> I am not sure how your table is setup exactly, but this should point
> you in the right direction.
>
> SELECT TOP 20 GameID, Player, Opponent, Result
> FROM tblMatches
> WHERE Player = 'Joe'
> ORDER BY GameID DESC;

I want to do it for every player. i do have a date, but need to use
the ID since a player normally plays 3 or 4 games a night.
From: pat67 on 16 Mar 2010 10:08
On Mar 16, 9:46 am, pat67 <pbus...(a)comcast.net> wrote:
> On Mar 16, 9:13 am, ghetto_banjo <adam.v...(a)gmail.com> wrote:
>
> > Do you store the date/time of these matches?  If not, it looks like
> > the only way to grab the "last" 20 matches would be via the GameID.
>
> > I am not sure how your table is setup exactly, but this should point
> > you in the right direction.
>
> > SELECT TOP 20 GameID, Player, Opponent, Result
> > FROM tblMatches
> > WHERE Player = 'Joe'
> > ORDER BY GameID DESC;
>
> I want to do it for every player. i do have a date, but need to use
> the ID since a player normally plays 3 or 4 games a night.

To be more specific, i need to show the GameID for each player in
descending order.
From: ghetto_banjo on 16 Mar 2010 10:54
Try this out. You need to use a subquery to get Top results per
Player.

SELECT tblMatches.Player, tblMatches.Opponent, tblMatches.Result,
tblMatches.GameID
FROM tblMatches
WHERE tblMatches.GameID IN (SELECT TOP 2 GameID From tblMatches AS
Dupe WHERE Dupe.Player = tblMatches.Player ORDER BY Dupe.GameID DESC)
ORDER By tblMatches.Player, tblMatches.GameID DESC;



 |  Next  |  Last
Pages: 1 2
Prev: SQL totalling items report
Next: Duplicate fields in Access Database