Top 5 Query Problem

  • Thread starter Thread starter Paul
  • Start date Start date
P

Paul

I'm trying to make a query of the Top 5 that will eventually be displayed on
a form. What I have is a db tracking the company softball team. I would
like this query to count the number of homers a player has hit and give me
the total, which I have seem to be able to do. All the counts are correct.
The problem I'm having is I'd like it to show the Top 5, for each year. I'm
getting data back that shows 5 records, but it shows them as one record from
this year and only four from last. How do I get the query to the Top 5 of
each season?

Thanks in advance

Paul
 
Here is what Access generated for the SQL.

SELECT TOP 5 [Season Table].[Season ID], [Batting Table].Batter,
Count([Batting

Table].Batter) AS CountOfBatter

FROM ([Season Table] INNER JOIN [Games Table] ON [Season Table].[Season ID]
= [Games Table].[Season

ID]) INNER JOIN [Batting Table] ON [Games Table].[Game ID] = [Batting
Table].[Game ID] GROUP BY [Season Table].[Season ID], [Batting
Table].Batter, [Games Table].[Season ID], [Games Table].Playoff, [Batting
Table].[1 2 3 H] HAVING ((([Games Table].[Season ID])=[Season Table.Season
ID]) AND (([Games Table].Playoff)=False) AND (([Batting Table].[1 2 3
H])="4")) ORDER BY [Season Table].[Season ID] DESC , Count([Batting
Table].Batter) DESC;
 
The feature of your requirement is that you want the top 5
rows from the set of data from one season added with the
top 5 rows from the set of data for another season and so
on. I think that there are two ways of approaching this.
If the number of seasons is quite small, then you can build
the result with a Union query
e.g.

SELECT TOP 5 etc
etc
WHERE [Season Table].[Season Id] = 2003
UNION
SELECT TOP 5 etc
etc
WHERE [Season Table].[Season Id] = 2004

Alternatively you could create a new table to hold these
results. Presumably there comes a time when the season's
averages can no longer be changed so you could populate the
new table with the past seasons data then delete the 5 rows
and re-add the 5 rows for the current season. Then you
would just have to select all from the new table to get the
results set you require.

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
Here is what Access generated for the SQL.

SELECT TOP 5 [Season Table].[Season ID], [Batting Table].Batter,
Count([Batting

Table].Batter) AS CountOfBatter

FROM ([Season Table] INNER JOIN [Games Table] ON [Season Table].[Season ID]
= [Games Table].[Season

ID]) INNER JOIN [Batting Table] ON [Games Table].[Game ID] = [Batting
Table].[Game ID] GROUP BY [Season Table].[Season ID], [Batting
Table].Batter, [Games Table].[Season ID], [Games Table].Playoff, [Batting
Table].[1 2 3 H] HAVING ((([Games Table].[Season ID])=[Season Table.Season
ID]) AND (([Games Table].Playoff)=False) AND (([Batting Table].[1 2 3
H])="4")) ORDER BY [Season Table].[Season ID] DESC , Count([Batting
Table].Batter) DESC;

Gerald Stanley said:
Can you post the SQL that gives the correct counts

Gerald Stanley MCSD
Click to expand...


.
Click to expand...
 
Thanks, it helped a lot.

Gerald Stanley said:
The feature of your requirement is that you want the top 5
rows from the set of data from one season added with the
top 5 rows from the set of data for another season and so
on. I think that there are two ways of approaching this.
If the number of seasons is quite small, then you can build
the result with a Union query
e.g.

SELECT TOP 5 etc
etc
WHERE [Season Table].[Season Id] = 2003
UNION
SELECT TOP 5 etc
etc
WHERE [Season Table].[Season Id] = 2004

Alternatively you could create a new table to hold these
results. Presumably there comes a time when the season's
averages can no longer be changed so you could populate the
new table with the past seasons data then delete the 5 rows
and re-add the 5 rows for the current season. Then you
would just have to select all from the new table to get the
results set you require.

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
Here is what Access generated for the SQL.

SELECT TOP 5 [Season Table].[Season ID], [Batting Table].Batter,
Count([Batting

Table].Batter) AS CountOfBatter

FROM ([Season Table] INNER JOIN [Games Table] ON [Season Table].[Season ID]
= [Games Table].[Season

ID]) INNER JOIN [Batting Table] ON [Games Table].[Game ID] = [Batting
Table].[Game ID] GROUP BY [Season Table].[Season ID], [Batting
Table].Batter, [Games Table].[Season ID], [Games Table].Playoff, [Batting
Table].[1 2 3 H] HAVING ((([Games Table].[Season ID])=[Season Table.Season
ID]) AND (([Games Table].Playoff)=False) AND (([Batting Table].[1 2 3
H])="4")) ORDER BY [Season Table].[Season ID] DESC , Count([Batting
Table].Batter) DESC;

Gerald Stanley said:
Can you post the SQL that gives the correct counts

Gerald Stanley MCSD
-----Original Message-----
I'm trying to make a query of the Top 5 that will
eventually be displayed on
a form. What I have is a db tracking the company softball
team. I would
like this query to count the number of homers a player has
hit and give me
the total, which I have seem to be able to do. All the
counts are correct.
The problem I'm having is I'd like it to show the Top 5,
for each year. I'm
getting data back that shows 5 records, but it shows them
as one record from
this year and only four from last. How do I get the query
to the Top 5 of
each season?

Thanks in advance

Paul


.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top