using only partial data for stats

  • Thread starter Thread starter pat67
  • Start date Start date
P

pat67

In my Db I have player stats that I am tracking. I want to run a query
where I use only the stats from the first 50 games of each players
total stats. Can someone assist?

Example:

John has 52 wins 28 losses for the year. But in his first 50 games he
had a record of 31 wins and 19 losses. I want to be able to run a
query to show that.
 
In my Db I have player stats that I am tracking. I want to run a query
where I use only the stats from the first 50 games of each players
total stats. Can someone assist?

Example:

John has 52 wins 28 losses for the year. But in his first 50 games he
had a record of 31 wins and 19 losses. I want to be able to run a
query to show that.
Click to expand...

Does your table have a field which indicates the game number?
As criteria on the game number field, write:
<=50
 
You need to supply more details to get a good answer. Check out the following
URL for an explanation on how to get the TOP N records per group using a subquery.

http://allenbrowne.com/subquery-01.html#TopN

If you post back with some details on your table structure perhaps someone can
give you more detailed instructions.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
You need to supply more details to get a good answer.  Check out the following
URL for an explanation on how to get the TOP N records per group using a subquery.

http://allenbrowne.com/subquery-01.html#TopN

If you post back with some details on your table structure perhaps someone can
give you more detailed instructions.

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




- Show quoted text -
Click to expand...

There are no game number fields. I have a table with the results and
several queries to get to the overall stats. I am not that experienced
so i do in a couple of queries what an expert could probably do in
one. But I will show my query for the records.

SELECT tblRosters.[Player Name], tblRosters.[Team Name], Sum(IIf(IsNull
([Wins]),0,[Wins]))+Sum(IIf(IsNull([Losses]),0,[Losses])) AS Games, Sum
(IIf(IsNull([Wins]),0,[Wins])) AS Win, Sum(IIf(IsNull([Losses]),0,
[Losses])) AS Loss, Sum(IIf(IsNull([RO's]),0,[RO's])) AS Runouts, IIf
([Games]=0,0,Sum(IIf(IsNull([Wins]),0,[Wins]))/[Games]) AS Pct
FROM ZqryLosses RIGHT JOIN (ZqryWins RIGHT JOIN tblRosters ON
ZqryWins.Winner = tblRosters.[Player Name]) ON ZqryLosses.Loser =
tblRosters.[Player Name]
GROUP BY tblRosters.[Player Name], tblRosters.[Team Name]
ORDER BY Sum(IIf(IsNull([Wins]),0,[Wins])) DESC;


Basically I take the results table and run a query for total wins and
another query for total losses. Then I combine those 2 queries with
the rosters table to ensure I get every player whether he played or
not. That's this query. What I need is to stop a player's count at 50
games played.
 
HOW do you determine the first fifty games played? Is there a date field in
there somewhere?

IF there is a date field then you might have a query like
SELECT A.*
FROM GamesPlayed as A
WHERE A.[GameDate] in
(SELECT TOP 50 [GameDate]
FROM GamesPlayed as B
WHERE B.[Player Name] = A.[Player Name]
ORDER BY B.[GameDate] ASC)

That will give you the 50 earliest GamesPlayed for each player.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
You need to supply more details to get a good answer. Check out the following
URL for an explanation on how to get the TOP N records per group using a subquery.

http://allenbrowne.com/subquery-01.html#TopN

If you post back with some details on your table structure perhaps someone can
give you more detailed instructions.

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



- Show quoted text -
Click to expand...

There are no game number fields. I have a table with the results and
several queries to get to the overall stats. I am not that experienced
so i do in a couple of queries what an expert could probably do in
one. But I will show my query for the records.

SELECT tblRosters.[Player Name], tblRosters.[Team Name], Sum(IIf(IsNull
([Wins]),0,[Wins]))+Sum(IIf(IsNull([Losses]),0,[Losses])) AS Games, Sum
(IIf(IsNull([Wins]),0,[Wins])) AS Win, Sum(IIf(IsNull([Losses]),0,
[Losses])) AS Loss, Sum(IIf(IsNull([RO's]),0,[RO's])) AS Runouts, IIf
([Games]=0,0,Sum(IIf(IsNull([Wins]),0,[Wins]))/[Games]) AS Pct
FROM ZqryLosses RIGHT JOIN (ZqryWins RIGHT JOIN tblRosters ON
ZqryWins.Winner = tblRosters.[Player Name]) ON ZqryLosses.Loser =
tblRosters.[Player Name]
GROUP BY tblRosters.[Player Name], tblRosters.[Team Name]
ORDER BY Sum(IIf(IsNull([Wins]),0,[Wins])) DESC;


Basically I take the results table and run a query for total wins and
another query for total losses. Then I combine those 2 queries with
the rosters table to ensure I get every player whether he played or
not. That's this query. What I need is to stop a player's count at 50
games played.
Click to expand...
 
HOW do you determine the first fifty games played?  Is there a date field in
there somewhere?

IF there is a date field then you might have a query like
SELECT A.*
FROM GamesPlayed as A
WHERE A.[GameDate] in
   (SELECT TOP 50 [GameDate]
    FROM GamesPlayed as B
    WHERE B.[Player Name] = A.[Player Name]
    ORDER BY B.[GameDate] ASC)

That will give you the 50 earliest GamesPlayed for each player.

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


There are no game number fields. I have a table with the results and
several queries to get to the overall stats. I am not that experienced
so i do in a couple of queries what an expert could probably do in
one. But I will show my query for the records.
Click to expand...
SELECT tblRosters.[Player Name], tblRosters.[Team Name], Sum(IIf(IsNull
([Wins]),0,[Wins]))+Sum(IIf(IsNull([Losses]),0,[Losses])) AS Games, Sum
(IIf(IsNull([Wins]),0,[Wins])) AS Win, Sum(IIf(IsNull([Losses]),0,
[Losses])) AS Loss, Sum(IIf(IsNull([RO's]),0,[RO's])) AS Runouts, IIf
([Games]=0,0,Sum(IIf(IsNull([Wins]),0,[Wins]))/[Games]) AS Pct
FROM ZqryLosses RIGHT JOIN (ZqryWins RIGHT JOIN tblRosters ON
ZqryWins.Winner = tblRosters.[Player Name]) ON ZqryLosses.Loser =
tblRosters.[Player Name]
GROUP BY tblRosters.[Player Name], tblRosters.[Team Name]
ORDER BY Sum(IIf(IsNull([Wins]),0,[Wins])) DESC;
Click to expand...
Basically I take the results table and run a query for total wins and
another query for total losses. Then I combine those 2 queries with
the rosters table to ensure I get every player whether he played or
not. That's this query. What I need is to stop a player's count at 50
games played.- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

There is a date field in the results table. But I am unsure how to use
your query
 
I would guess that you could use it as the starting point since it would
return the first 50 games for each player.

With that you should be able to get the wins in the first 50 games for
each individual and the losses in the first 50 games for each individual.



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

HOW do you determine the first fifty games played? Is there a date field in
there somewhere?

IF there is a date field then you might have a query like
SELECT A.*
FROM GamesPlayed as A
WHERE A.[GameDate] in
(SELECT TOP 50 [GameDate]
FROM GamesPlayed as B
WHERE B.[Player Name] = A.[Player Name]
ORDER BY B.[GameDate] ASC)

That will give you the 50 earliest GamesPlayed for each player.

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


You need to supply more details to get a good answer. Check out the following
URL for an explanation on how to get the TOP N records per group using a subquery.
http://allenbrowne.com/subquery-01.html#TopN
If you post back with some details on your table structure perhaps someone can
give you more detailed instructions.
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
pat67 wrote:
In my Db I have player stats that I am tracking. I want to run a query
where I use only the stats from the first 50 games of each players
total stats. Can someone assist?
Example:
John has 52 wins 28 losses for the year. But in his first 50 games he
had a record of 31 wins and 19 losses. I want to be able to run a
query to show that.- Hide quoted text -
- Show quoted text -
There are no game number fields. I have a table with the results and
several queries to get to the overall stats. I am not that experienced
so i do in a couple of queries what an expert could probably do in
one. But I will show my query for the records.
SELECT tblRosters.[Player Name], tblRosters.[Team Name], Sum(IIf(IsNull
([Wins]),0,[Wins]))+Sum(IIf(IsNull([Losses]),0,[Losses])) AS Games, Sum
(IIf(IsNull([Wins]),0,[Wins])) AS Win, Sum(IIf(IsNull([Losses]),0,
[Losses])) AS Loss, Sum(IIf(IsNull([RO's]),0,[RO's])) AS Runouts, IIf
([Games]=0,0,Sum(IIf(IsNull([Wins]),0,[Wins]))/[Games]) AS Pct
FROM ZqryLosses RIGHT JOIN (ZqryWins RIGHT JOIN tblRosters ON
ZqryWins.Winner = tblRosters.[Player Name]) ON ZqryLosses.Loser =
tblRosters.[Player Name]
GROUP BY tblRosters.[Player Name], tblRosters.[Team Name]
ORDER BY Sum(IIf(IsNull([Wins]),0,[Wins])) DESC;
Basically I take the results table and run a query for total wins and
another query for total losses. Then I combine those 2 queries with
the rosters table to ensure I get every player whether he played or
not. That's this query. What I need is to stop a player's count at 50
games played.- Hide quoted text -
Click to expand...
- Show quoted text -
Click to expand...

There is a date field in the results table. But I am unsure how to use
your query
Click to expand...
 
I would guess that you could use it as the starting point since it would
return the first 50 games for each player.

With that you should be able to get the wins in the first 50 games for
each individual and the losses in the first 50 games for each individual.

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


HOW do you determine the first fifty games played?  Is there a date field in
there somewhere?
IF there is a date field then you might have a query like
SELECT A.*
FROM GamesPlayed as A
WHERE A.[GameDate] in
   (SELECT TOP 50 [GameDate]
    FROM GamesPlayed as B
    WHERE B.[Player Name] = A.[Player Name]
    ORDER BY B.[GameDate] ASC)
That will give you the 50 earliest GamesPlayed for each player.
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
pat67 wrote:
You need to supply more details to get a good answer.  Check out the following
URL for an explanation on how to get the TOP N records per group using a subquery.
http://allenbrowne.com/subquery-01.html#TopN
If you post back with some details on your table structure perhaps someone can
give you more detailed instructions.
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
pat67 wrote:
In my Db I have player stats that I am tracking. I want to run a query
where I use only the stats from the first 50 games of each players
total stats. Can someone assist?
Example:
John has 52 wins 28 losses for the year. But in his first 50 games he
had a record of 31 wins and 19 losses. I want to be able to run a
query to show that.- Hide quoted text -
- Show quoted text -
There are no game number fields. I have a table with the results and
several queries to get to the overall stats. I am not that experienced
so i do in a couple of queries what an expert could probably do in
one. But I will show my query for the records.
SELECT tblRosters.[Player Name], tblRosters.[Team Name], Sum(IIf(IsNull
([Wins]),0,[Wins]))+Sum(IIf(IsNull([Losses]),0,[Losses])) AS Games, Sum
(IIf(IsNull([Wins]),0,[Wins])) AS Win, Sum(IIf(IsNull([Losses]),0,
[Losses])) AS Loss, Sum(IIf(IsNull([RO's]),0,[RO's])) AS Runouts, IIf
([Games]=0,0,Sum(IIf(IsNull([Wins]),0,[Wins]))/[Games]) AS Pct
FROM ZqryLosses RIGHT JOIN (ZqryWins RIGHT JOIN tblRosters ON
ZqryWins.Winner = tblRosters.[Player Name]) ON ZqryLosses.Loser =
tblRosters.[Player Name]
GROUP BY tblRosters.[Player Name], tblRosters.[Team Name]
ORDER BY Sum(IIf(IsNull([Wins]),0,[Wins])) DESC;
Basically I take the results table and run a query for total wins and
another query for total losses. Then I combine those 2 queries with
the rosters table to ensure I get every player whether he played or
not. That's this query. What I need is to stop a player's count at 50
games played.- Hide quoted text -
- Show quoted text -
Click to expand...
Click to expand...
There is a date field in the results table. But I am unsure how to use
your query- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

What I don't understand from your query, is this

SELECT A.*
FROM GamesPlayed as A
WHERE A.[GameDate] in
(SELECT TOP 50 [GameDate]
FROM GamesPlayed as B
WHERE B.[Player Name] = A.[Player Name]
ORDER BY B.[GameDate] ASC)

What does A.* correspond to? And what does B correspond to?
 
A and B are alias names for your table which I called GamesPlayed.

They allow you to use multiple instances of the same table.

A.* means return ALL fields from the A instance of the table.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
A and B are alias names for your table which I called GamesPlayed.

They allow you to use multiple instances of the same table.

A.* means return ALL fields from the A instance of the table.

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




- Show quoted text -
Click to expand...

ok. when i go in and substitute my actual table for "GamesPlayed", i
have other problems. my table doesn't have a player name field so when
i run it, i get a pop up that asks for a parameter value. this is
where i am now. my table looks like this

Date - Round - Winner - Team - Points - Loser - Team1 - Points 1


I hope that helps
 
ok. when i go in and substitute my actual table for "GamesPlayed", i
have other problems. my table doesn't have a player name field so when
i run it, i get a pop up that asks for a parameter value. this is
where i am now. my table looks like this

Date - Round - Winner - Team - Points - Loser - Team1 - Points 1

I hope that helps- Hide quoted text -

- Show quoted text -
Click to expand...

Here is what I can do. I have an GameID Field. I can get the query to
return top "n" Wins or Losses. My idea was to combine these 2 queries
into 1 and then run your query on that. I think I confuse myself
 
No, it does not help. That structure makes life more difficult since you now
have to look in two places to get the name of the player (Winner or Loser)

PERHAPS the following will work to get the top 50 games, but I don't think it
will.

SELECT A.*
FROM [GamesPlayed] as A
WHERE A.[GameDate] in
(SELECT TOP 50 [GameDate]
FROM GamesPlayed as B
WHERE B.[Winner] = A.[Winner]
OR B.[Loser] = A.[Loser]
ORDER BY B.[GameDate] ASC)

You now in all probability need to use a union query to normalize the data
into a good form.

SELECT [Date] as GameDate, Round, Winner as Player, Team, Points, "Won" as Result
FROM [YourTable]
UNION ALL
SELECT [Date] as GameDate, Round, Loser as Player, Team1, Points, "Lost" as Result
FROM [YourTable]

Now with that saved query you can hopefully determine the first 50 games for
an individual

SELECT A.*
FROM qUnionQuery as A
WHERE A.[GameDate] in
(SELECT TOP 50 [GameDate]
FROM qUnionQuery as B
WHERE B.[Player] = A.[Player]
ORDER BY B.[GameDate] ASC)

At this point you should have the first 50 games for each player.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
No, it does not help.  That structure makes life more difficult since you now
have to look in two places to get the name of the player (Winner or Loser)

PERHAPS the following will work to get the top 50 games, but I don't think it
will.

SELECT A.*
FROM [GamesPlayed] as A
WHERE A.[GameDate] in
   (SELECT TOP 50 [GameDate]
    FROM GamesPlayed as B
    WHERE B.[Winner] = A.[Winner]
    OR B.[Loser] = A.[Loser]
    ORDER BY B.[GameDate] ASC)

You now in all probability need to use a union query to normalize the data
into a good form.

SELECT [Date] as GameDate, Round, Winner as Player, Team, Points, "Won" as Result
FROM [YourTable]
UNION ALL
SELECT [Date] as GameDate, Round, Loser as Player, Team1, Points, "Lost" as Result
FROM [YourTable]

Now with that saved query you can hopefully determine the first 50 games for
an individual

SELECT A.*
FROM qUnionQuery as A
WHERE A.[GameDate] in
   (SELECT TOP 50 [GameDate]
    FROM qUnionQuery as B
    WHERE B.[Player] = A.[Player]
    ORDER BY B.[GameDate] ASC)

At this point you should have the first 50 games for each player.

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



- Show quoted text -
Click to expand...

That looks like it is giving me the first 50 games listed by date. I
now just need to create a query to add all of the stats together. I
assume that if I used in another table that had Game IDs I could use
that instead of date?

Thanks
 
No, it does not help.  That structure makes life more difficult sinceyou now
have to look in two places to get the name of the player (Winner or Loser)
Click to expand...
PERHAPS the following will work to get the top 50 games, but I don't think it
will.
Click to expand...
SELECT A.*
FROM [GamesPlayed] as A
WHERE A.[GameDate] in
   (SELECT TOP 50 [GameDate]
    FROM GamesPlayed as B
    WHERE B.[Winner] = A.[Winner]
    OR B.[Loser] = A.[Loser]
    ORDER BY B.[GameDate] ASC)
Click to expand...
You now in all probability need to use a union query to normalize the data
into a good form.
Click to expand...
SELECT [Date] as GameDate, Round, Winner as Player, Team, Points, "Won"as Result
FROM [YourTable]
UNION ALL
SELECT [Date] as GameDate, Round, Loser as Player, Team1, Points, "Lost" as Result
FROM [YourTable]
Click to expand...
Now with that saved query you can hopefully determine the first 50 games for
an individual
Click to expand...
SELECT A.*
FROM qUnionQuery as A
WHERE A.[GameDate] in
   (SELECT TOP 50 [GameDate]
    FROM qUnionQuery as B
    WHERE B.[Player] = A.[Player]
    ORDER BY B.[GameDate] ASC)
Click to expand...
At this point you should have the first 50 games for each player.
Click to expand...
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Click to expand...
- Show quoted text -
Click to expand...

That looks like it is giving me the first 50 games listed by date. I
now just need to create a query to add all of the stats together. I
assume that if I used in another table that had Game IDs I could use
that instead of date?

Thanks- Hide quoted text -

- Show quoted text -
Click to expand...

I cannot tell you how much you have helped. Not only for the Top 50
thing but in general. I had never used a union query at all and for
all of the stats I was creating 3 or 4 queries to do what I can now do
in 2. Once again, thank you very much.
 
Back
Top