query help with conditional summing

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Cliff
    #1

    query help with conditional summing

    I have a database with a table named Fielding that contains (among
    others) the following records and fields:

    playerID = unique with records for each change in POS, year, team

    POS = P, C, 1B, 2B, 3B, SS, LF, CF, RF, OF, DH

    Games = number of games played at each POS

    For example, using this data:

    playerID yearID stint teamID POS Games
    -----------------------------------------------------
    lakeed01 1943 1 BOS SS 63
    lakeed01 1944 1 BOS 2B 3
    lakeed01 1944 1 BOS 3B 1
    lakeed01 1944 1 BOS P 6
    lakeed01 1944 1 BOS SS 41
    lakeed01 1945 1 BOS 2B 1
    lakeed01 1945 1 BOS SS 130
    lakeed01 1946 1 DET SS 155
    lakeed01 1947 1 DET SS 158
    lakeed01 1948 1 DET 2B 45

    I need an output like this for each playerID:

    playerID =P <>P
    ---------------------------
    lakeed01 6 597

    Can this be done straightforward ly? Thanks,
    Cliff
    Tags: None
    • MacDermott
      #2
      Re: query help with conditional summing

      You can use a crosstab query like this:

      TRANSFORM Sum(Fielding.Ga mes) AS SumOfGames
      SELECT Fielding.Player ID
      FROM Fielding
      GROUP BY Fielding.Player ID
      PIVOT [POS]="P";

      HTH
      - Turtle


      "Cliff" <baseball318@ho tmail.com> wrote in message
      news:a53cd421.0 408240617.6cc72 491@posting.goo gle.com...[color=blue]
      > I have a database with a table named Fielding that contains (among
      > others) the following records and fields:
      >
      > playerID = unique with records for each change in POS, year, team
      >
      > POS = P, C, 1B, 2B, 3B, SS, LF, CF, RF, OF, DH
      >
      > Games = number of games played at each POS
      >
      > For example, using this data:
      >
      > playerID yearID stint teamID POS Games
      > -----------------------------------------------------
      > lakeed01 1943 1 BOS SS 63
      > lakeed01 1944 1 BOS 2B 3
      > lakeed01 1944 1 BOS 3B 1
      > lakeed01 1944 1 BOS P 6
      > lakeed01 1944 1 BOS SS 41
      > lakeed01 1945 1 BOS 2B 1
      > lakeed01 1945 1 BOS SS 130
      > lakeed01 1946 1 DET SS 155
      > lakeed01 1947 1 DET SS 158
      > lakeed01 1948 1 DET 2B 45
      >
      > I need an output like this for each playerID:
      >
      > playerID =P <>P
      > ---------------------------
      > lakeed01 6 597
      >
      > Can this be done straightforward ly? Thanks,
      > Cliff[/color]


        Comment

        • Cliff
          #3
          Re: query help with conditional summing

          "MacDermott " <macdermott@nos pam.com> wrote in message news:<gSPWc.110 98$2L3.923@news read3.news.atl. earthlink.net>. ..[color=blue]
          > You can use a crosstab query like this:
          >
          > TRANSFORM Sum(Fielding.Ga mes) AS SumOfGames
          > SELECT Fielding.Player ID
          > FROM Fielding
          > GROUP BY Fielding.Player ID
          > PIVOT [POS]="P";[/color]

          Thanks, Turtle. That got me on track. Didn't know about pivot tables and Access.

          Cliff

            Comment

            Previous template Next
            Working...