Averaging similar columns from multiple tables

[thexxvi]

Hello,

I have three tables that include projected football statistics from multiple sources that I want to average out. The following is an example of the columns in use:

Player Name


Team Name
Position
Passing Yards
Rushing Yards
Passing Touchdowns
Rushing Touchdowns

So again, the column names are the same in each table for consistency. I essentially want to run a query showing the player name, team name, and position once, but averages the actual statistics (Passing Yards, Rushing Yards, Passing Touchdowns, Rushing Touchdowns) from the three tables. In other words, if Table 1 shows 4,000 Passing Yards, Table 2 shows 2,500 Passing Yards, and Table 3 shows 3,000 Passing Yards for a specific player, the output would show 3,166.66 Passing Yards.

Is this doable and if so, how can I accomplish this?

Thank you in advance.

[cbende2]

In short, yes this is doable.

However, you have repeated data and your database is not normalized.

You should have 5 tables:

PlayerTable---
with fields:
1)PKPlayerID(autonumber)
2)FirstName(text)
3)LastName(text)
4)TeamFK(number)
5)PositionFK(number)

TeamTable---
with fields:
1)PKTeamID(autonumber)
2)TeamName(text)

PositionTable---
with fields:
1)PKPositionID(autonumber)
2)PositionName(text)

StatsTable---
with fields:
1)PKStatID(autonumber)
2)StatTypeFK(number)
3)PlayerFK(number)
4)Amount(number)

StatTypeTable---
with fields:
1)PKTypeID(autonumber)
2)Type(text)---(Passing Yards, Rushing Yards, Passing Touchdowns, Rushing Touchdowns)


You will then easily be able to find the averages of different stats if you use this DB design.

[thexxvi]

Thank you for your help on this and on yesterdays question as well. I'm new to access to I'm still trying to get a grasp on this, but I'm picking it up quickly...especially with tips that you've provided. I'll give this a try tomorrow and will follow-up.

[cbende2]

You're welcome!

Post back if you need help with the relationships!

[June7]

Combining the tables to do aggregate calcs would require a UNION query. The UNION would emulate single table should have to begin with. As cbende2 suggests, optimize data structure

[thexxvi]

After some thought about this, is there any other way to do this? The reason why I ask is because, lets just say I have Player A and I pull his projected stats up this week from 4 different sources, Source A, B, C and D. It sounds like I would have to start with Source A and find the player, then I would have to go to Source B, find the player there, and get the information from that line. Same with Sources C and D. I would have to do that for over 500 players, and these projected stats change on a weekly basis. It seems like a massive headache to do it that way, unless I'm misinterpreting what you're saying.

[thexxvi]

Ideally I would like to have a table for each of the sources, so that when the data changes weekly, I can dump the new information into their respective tables and run a query that finds the player in each table and averages the stats out. Does that make sense?

[June7]

Separate tables does not really make sense.

Options:

1. restructure database - build queries that average the data for all players - build reports that do calcs and sorting and filtering

2. but if you must keep separate tables, use UNION query as described in post 5 and use the UNION like a table to do everything described in option 1

[cbende2]

Trust us on it thexxvi, it may not seem like the right option in your head right now, but once you start learning normalized data structure it will all start clicking. I've had several "OOOooohhh" moments throughout my time dealing with databases and learning to optimize and normalize the data structure. It will make sense eventually, just trust us for now