Sum info from one table into another

[knpaddac]

I am doing basketball stats. I have one table that has each players information (age, school, season scoring total, etc.) for a season titled 'PlayersbySeason' and another table 'Performances' that has a record for each players involvement in each game. I want the "Seasonpointtotal" field in the Playersbyseason table to be a sum of all linked records in the Performances table, specifically from a field titled "Points". How would I go about doing this? I assumed some sort of update query, but I do not know how to do it.

[JoeM]

You don't put it in a table - calculate it in a query. That way it is dynamic, and you never need to worry about running another Update Query every time you have new data.
The general rule of thumb in Access is never store any calculated amount at the table level that can easily be run/calculated on demand.

[knpaddac]

Okay. So, how would I create such a query to run those totals?

[JoeM]

It all depends on how you data is structured. If it is normalized, it should be a pretty easy task.
Can you post a small sample/example of data from each table?

[knpaddac]

Here is an image of the relationship between the two tables. I don't know if that is exactly what you would need.

[JoeM]

Post a few data records from each table to show us what your data looks like, and show us what your expected results should look like.

[knpaddac]

I hope this will help in understanding what I am doing. While I am pretty good with numbers and statistics, I am very new to managing a database and subsequently the terminology of it all.

This is an always growing dataset as I research and add information about basketball games played (in current time and past). The first image is of the player information for each season, and the second is each game within a season. A player might have had as many as 30 games in a given season, and may have played as many as four seasons (eventually I plan to have a table of career information, unless I find out that there is a better way to organize).
I am wanting to be able to get the players' season scoring total based on the information in the performance table. My original plan was to have that sum entered into a field in the 'playersbyseason' table, but I am hearing that that might be an inefficient plan. As you can see some players in that table already have totals, but that is because the information was uploaded from a previous source.
So, maybe as a starting point let's say I want to get a season total for Bertsch (2609 in the playersbyseason table)?

Post a few data records from each table to show us what your data looks like, and show us what your expected results should look like.

[JoeM]

I am wanting to be able to get the players' season scoring total based on the information in the performance table. My original plan was to have that sum entered into a field in the 'playersbyseason' table, but I am hearing that that might be an inefficient plan.

Yes, I would not recommend doing that. The general rule of thumb is to never store calculations which can easily be performed at run-time.

It sounds like you want is an Aggregate (Totals) Query.
You will join the two tables in a query, based on your table relationship.
Then, you select all the fields you want from each table.
Then, click on the Totals button in the Query Builder (looks like a sigma). This will add a Totals row in your Query with the words "Group By" under each field.
Under the Points field, change the value from "Group By" to "Sum". This will group all like records (based on those other fields), and return the total number of Points for each grouping.

[knpaddac]

Great. Thanks. One other question....
Because I enter those 'performance' records from the "Games" table they already have the gameID field populated, but I currently have to go back to look up the playerID and enter it on each 'performance' record. Do you happen to know of a way to enter the same value in multiple records at the same time?

[ssanfu]

Do you happen to know of a way to enter the same value in multiple records at the same time?

I would use a main form/sub form setup. The problem in that your tables are not normalized.

It looks like a spreadsheet design. From the image in Post #5, I see around 6 tables.

[knpaddac]

There are a total of 5 tables: Schools, Seasons, Playersbyseason, Games, and Performances.

Quick breakdown of those:
Schools is basic information (location, years in existence, etc.) about all schools around the state.
Seasons is each season that each school played basketball (record, tournament result, coach, etc.).
Playersbyseason is player info for each season they played (grade, height, weight, position)
Games is the info about each game played (date, winning team, losing team, location, etc.)
Performances includes any stats recordable for an individual player in a game.

This is what the source of my info typically looks like:
Spellings are often inconsistent which often creates troubles.

I am always looking for ways to simplify the process of entering data, so I appreciate any suggestions. I have worked a little bit with forms but not much, so I am unfamiliar with creating one that will let me quickly and easily link the individual stats to the player and the game tables.