Combine by date and ID

[cfobare79]

I am working on an Access DB that has a query that gives me an assortment of baseball batting stats. Some players have played for multiple teams in a single year and their stats are broken up by these "stints". Some of these stats need to be added together and some averaged. Below is some of the base fields I'm using.

playerid (player name)
stint (# associated with each team played for that yearid)
yearid (year of stats)


index (# of years since debut)
AB (At bats, will be a sum function)
BA (Batting Average, average of stats)
Complete (The query name that holds all the info)

[JoeM]

You will want to do an Aggregate Query, grouping by the PlayerID and YearID (and possibly index?).
But you will want to leave the stint field out of this query, so it combines the records you want (for players who played for multiple teams in the same year).
Fields like AB will be aggregated (SUM).

To get an accurate batting average for people who played for multiple teams, you will have to calculate it (SUM of HITS divided by SUM of AB), because you cannot just average your different batting averages, since they are not weighted equally (unless they had the exact same number of AB for each team they played for, which would be pretty rare).

[cfobare79]

I will try that as soon as I get home tonight. Seems like such an easy fit. Not sure how I looked past it. Thank you. I will let you know how it works out.

[cfobare79]

The aggregate query was able to combine the "stint". The problem I am having now is that either my next query is too large or my query below is too large. If you have a fix for me let me know. Thanks

SELECT IIf([Master]![debut_yr]<1962,([Batting1]![yearID]-1962),([Batting1]![yearID]-[Master]![debut_yr])) AS [Index], Join.master.playerid, Join.birthyear, Join.birthcountry, Join.height, Join.bats, Join.throws, Join.batting1.playerid AS Expr2, Join.yearid, Join.Age, Join.G_batting, Join.debut_yr, Join.AB, Join.R, Join.H, Join.[2B], Join.[3B], Join.HR, Join.RBI, Join.SB, Join.CS, Join.BB, Join.SO, Join.IBB, Join.HBP, Join.SH, Join.SF, Join.GIDP, Join.ABperHR, Join.BA, Join.BABIP, Join.Walk_to_SO_Ratio, Join.TB, Join.SLG, Join.ISO, Join.OBP, Join.OPS, Join.RC, Join.TA, Join.TOB, Join.XBH
FROM [Join];

[JoeM]

You have references to multiple tables in your SELECT clause (Master, Batting1, and Join), but you only have the Join table in your FROM cause. You need to show all three there, if you are selecting fields form all three, and you would normally have the joins between the tables defined in the query.

Also note. Typically when you have a new question that isn't dependent on the previous one, you usually want to post that new question in a new thread so it shows up as a new unanswered question. That way other people will be more likely to see it (not just me!).