Re: Top 5 Query Problem

From: Paul (P_Acker_at_NO.Hotmail.SPAM.com)
Date: 04/08/04 

Date: Thu, 8 Apr 2004 14:06:14 -0400

Thanks, it helped a lot.

"Gerald Stanley" <gcstanley@dsl.pipex.com> wrote in message
news:157d101c41d72$9cfbee50$a001280a@phx.gbl...
> 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" <gcstanley@dsl.pipex.com> wrote in message
> >news:1997401c41ccb$0f23a6b0$a301280a@phx.gbl...
> >> 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
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >

Relevant Pages

  • Re: Top 5 Query Problem
    ... The feature of your requirement is that you want the top 5 ... and re-add the 5 rows for the current season. ... >Here is what Access generated for the SQL. ... >> Gerald Stanley MCSD ...
    (microsoft.public.access.queries)
  • Re: Simulating A Full Outer Join in Access from 3 Tables
    ... task unless you have advanced knowledge of VBA and or Jset SQL DDL. ... you can use the union query you have to load the dates. ... and using the date feild as the basis to align ... What I'm seeking to do is consolidate the files into one so ...
    (microsoft.public.access.queries)
  • Re: Submit Form Elements to Insert Multiple Records into a Database
    ... > response...wouldn't your solution just append each set of values to ... > each other causing the insert statement to feed in one long string of ... The derived_table is any SQL statement that returns a dataset that matches ... UNION query portion of the INSERT statement into Query Analyser. ...
    (microsoft.public.inetserver.asp.general)
  • Re: Union fields in the order they appear in the table
    ... with SQL, and do not know where I am going wrong. ... "John W. Vinson" wrote: ... populate the table based on the UNION query; be sure that there is a nonunique ...
    (microsoft.public.access.queries)
  • Re: Changing Query Layouts
    ... Whenever you post something like "with no success", ... Is it easier to do it as a Union query rather ... I have very limited SQL knowledge, ... >>At least one other reply in another news group. ...
    (microsoft.public.access.queries)