Loading ...
Sorry, an error occurred while loading the content.

Re: [baseball-databank] Database -- not an error

Expand Messages
  • tom tom
    Create an Access query. This will display the table as a separate column for each position, with the number of games for each position. Let me know if you
    Message 1 of 4 , Mar 28, 2002
    • 0 Attachment
      Create an Access query.

      This will display the table as a separate column for
      each position, with the number of games for each
      position. Let me know if you need to have the "Pos"
      field like the past instead. (I only listed 3
      positions so that you can see it.)

      SELECT Fielding.LahmanID, Fielding.Year,
      Fielding.Team, Fielding.Lg, Sum(IIf([Pos]="C",[G],0))
      AS Pos_C, Sum(IIf([Pos]="LF",[G],0)) AS Pos_LF,
      Sum(IIf([Pos]="2B",[G],0)) AS Pos_2B
      FROM Batting LEFT JOIN Fielding ON (Batting.Lg =
      Fielding.Lg) AND (Batting.Team = Fielding.Team) AND
      (Batting.Year = Fielding.Year) AND (Batting.LahmanID =
      Fielding.LahmanID)
      GROUP BY Fielding.LahmanID, Fielding.Year,
      Fielding.Team, Fielding.Lg;


      __________________________________________________
      Do You Yahoo!?
      Yahoo! Movies - coverage of the 74th Academy Awards´┐Ż
      http://movies.yahoo.com/
    • mrmiller3
      Tom, Yes, I need like in the past instead! Thanks, Rob
      Message 2 of 4 , Mar 28, 2002
      • 0 Attachment
        Tom,
        Yes, I need "like in the past" instead!
        Thanks,
        Rob

        --- In baseball-databank@y..., tom tom <tmasc@y...> wrote:
        > Create an Access query.
        >
        > This will display the table as a separate column for
        > each position, with the number of games for each
        > position. Let me know if you need to have the "Pos"
        > field like the past instead. (I only listed 3
        > positions so that you can see it.)
        >
        > SELECT Fielding.LahmanID, Fielding.Year,
        > Fielding.Team, Fielding.Lg, Sum(IIf([Pos]="C",[G],0))
        > AS Pos_C, Sum(IIf([Pos]="LF",[G],0)) AS Pos_LF,
        > Sum(IIf([Pos]="2B",[G],0)) AS Pos_2B
        > FROM Batting LEFT JOIN Fielding ON (Batting.Lg =
        > Fielding.Lg) AND (Batting.Team = Fielding.Team) AND
        > (Batting.Year = Fielding.Year) AND (Batting.LahmanID =
        > Fielding.LahmanID)
        > GROUP BY Fielding.LahmanID, Fielding.Year,
        > Fielding.Team, Fielding.Lg;
        >
        >
        > __________________________________________________
        > Do You Yahoo!?
        > Yahoo! Movies - coverage of the 74th Academy Awards®
        > http://movies.yahoo.com/
      • tmasc
        This won t get you the correct order , but it will get you all of them in the same field Running this SELECT Fielding.LahmanID, Fielding.Year, Fielding.Team,
        Message 3 of 4 , Mar 29, 2002
        • 0 Attachment
          This won't get you the correct "order", but it will get you all of
          them in the same field

          Running this
          SELECT Fielding.LahmanID, Fielding.Year, Fielding.Team, Fielding.Lg,
          [Pos_1B_ind]+[Pos_2B_ind]+[Pos_ss_ind] AS Pos_Tot, Sum(IIf([Fielding].
          [Pos]="1B",[Fielding].[G],0)) AS Pos_1B, Sum(IIf([Fielding].[Pos]
          ="2B",[Fielding].[G],0)) AS Pos_2B, Sum(IIf([Fielding].[Pos]="SS",
          [Fielding].[G],0)) AS Pos_SS, IIf([Pos_1B]>0,"3","") AS Pos_1B_ind,
          IIf([Pos_2B]>0,"4","") AS Pos_2B_ind, IIf([Pos_SS]>0,"6","") AS
          Pos_SS_ind
          FROM Fielding AS Fielding_1 RIGHT JOIN Fielding ON (Fielding_1.Lg =
          Fielding.Lg) AND (Fielding_1.Team = Fielding.Team) AND
          (Fielding_1.Year = Fielding.Year) AND (Fielding_1.LahmanID =
          Fielding.LahmanID)
          GROUP BY Fielding.LahmanID, Fielding.Year, Fielding.Team, Fielding.Lg
          HAVING (((Fielding.LahmanID)="oquenjo01"));

          Will get you this
          LahmanID Year Team Lg Pos_Tot Pos_1B Pos_2B Pos_SS
          Pos_1B_ind Pos_2B_ind Pos_SS_ind
          oquenjo01 1983 nyn NL 6 0 0 116
          6
          oquenjo01 1984 nyn NL 6 0 0 67
          6
          oquenjo01 1986 sln NL 46 0 84 116
          4 6
          oquenjo01 1987 sln NL 346 18 192 138
          3 4 6
          oquenjo01 1988 sln NL 346 112 483 119
          3 4 6
          oquenjo01 1989 sln NL 346 3 468 21
          3 4 6
          oquenjo01 1990 sln NL 46 0 300 8
          4 6
          oquenjo01 1991 sln NL 346 12 472 88
          3 4 6
          oquenjo01 1992 sln NL 46 0 18 10
          4 6
          oquenjo01 1993 sln NL 46 0 32 44
          4 6
          oquenjo01 1994 sln NL 46 0 32 56
          4 6
          oquenjo01 1995 sln NL 46 0 248 96
          4 6
        Your message has been successfully submitted and would be delivered to recipients shortly.