Forum Discussion
Access freezes when creating this specific table
I'm trying to run the following query below. However, whenever I try to run it, Access freezes.
It's not likely a system performance issue as my CPU and memory are running well under capacity the whole time.
Individual tables contain roughly 1000 entries each for reference.
EDIT: After experimentation, the query will run if I remove 4 or more left joins. Is there a way I could make the SQL code more efficient to run completely?
SELECT [2022-points-s-pitching].PID, [2022-points-s-pitching].Name, [2022-points-s-pitching].PPG AS PPG22, [2022-standard-pitching].FIP AS FIP22, [2022-advanced-pitching].HardH AS HardH22, [2021-points-s-pitching].PPG AS PPG21, [2021-standard-pitching].FIP AS FIP21, [2021-advanced-pitching].HardH AS HardH21, [2020-points-s-pitching].PPG AS PPG20, [2020-standard-pitching].FIP AS FIP20, [2020-advanced-pitching].HardH AS HardH20, [2019-points-s-pitching].PPG AS PPG19, [2019-standard-pitching].FIP AS FIP19, [2019-advanced-pitching].HardH AS HardH19, [2018-points-s-pitching].PPG AS PPG18, [2018-standard-pitching].FIP AS FIP18, [2018-advanced-pitching].HardH AS HardH18, [2017-points-s-pitching].PPG AS PPG17, [2017-standard-pitching].FIP AS FIP17, [2017-advanced-pitching].HardH AS HardH17, [2016-points-s-pitching].PPG AS PPG16, [2016-standard-pitching].FIP AS FIP16, [2016-advanced-pitching].HardH AS HardH16, [2015-points-s-pitching].PPG AS PPG15, [2015-standard-pitching].FIP AS FIP15, [2015-advanced-pitching].HardH AS HardH15, [2022-points-s-pitching].G, [2022-points-s-pitching].GS, [2022-points-s-pitching].GR INTO StartingPitcher
FROM (((((((((((((((((((((([2022-points-s-pitching]
LEFT JOIN [2022-standard-pitching] ON ([2022-points-s-pitching].PID = [2022-standard-pitching].PID AND [2022-standard-pitching].[GS]>=5))
LEFT JOIN [2022-advanced-pitching] ON ([2022-points-s-pitching].PID = [2022-advanced-pitching].PID AND [2022-advanced-pitching].[GS]>=5))
LEFT JOIN [2021-points-s-pitching] ON ([2022-points-s-pitching].PID = [2021-points-s-pitching].PID AND [2021-points-s-pitching].[GS]>=5))
LEFT JOIN [2021-standard-pitching] ON ([2022-points-s-pitching].PID = [2021-standard-pitching].PID AND [2021-standard-pitching].[GS]>=5))
LEFT JOIN [2021-advanced-pitching] ON ([2022-points-s-pitching].PID = [2021-advanced-pitching].PID AND [2021-advanced-pitching].[GS]>=5))
LEFT JOIN [2020-points-s-pitching] ON ([2022-points-s-pitching].PID = [2020-points-s-pitching].PID AND [2020-points-s-pitching].[GS]>=5))
LEFT JOIN [2020-standard-pitching] ON ([2022-points-s-pitching].PID = [2020-standard-pitching].PID AND [2020-standard-pitching].[GS]>=5))
LEFT JOIN [2020-advanced-pitching] ON ([2022-points-s-pitching].PID = [2020-advanced-pitching].PID AND [2020-advanced-pitching].[GS]>=5))
LEFT JOIN [2019-points-s-pitching] ON ([2022-points-s-pitching].PID = [2019-points-s-pitching].PID AND [2019-points-s-pitching].[GS]>=5))
LEFT JOIN [2019-standard-pitching] ON ([2022-points-s-pitching].PID = [2019-standard-pitching].PID AND [2019-standard-pitching].[GS]>=5))
LEFT JOIN [2019-advanced-pitching] ON ([2022-points-s-pitching].PID = [2019-advanced-pitching].PID AND [2019-advanced-pitching].[GS]>=5))
LEFT JOIN [2018-points-s-pitching] ON ([2022-points-s-pitching].PID = [2018-points-s-pitching].PID AND [2018-points-s-pitching].[GS]>=5))
LEFT JOIN [2018-standard-pitching] ON ([2022-points-s-pitching].PID = [2018-standard-pitching].PID AND [2018-standard-pitching].[GS]>=5))
LEFT JOIN [2018-advanced-pitching] ON ([2022-points-s-pitching].PID = [2018-advanced-pitching].PID AND [2018-advanced-pitching].[GS]>=5))
LEFT JOIN [2017-points-s-pitching] ON ([2022-points-s-pitching].PID = [2017-points-s-pitching].PID AND [2017-points-s-pitching].[GS]>=5))
LEFT JOIN [2017-standard-pitching] ON ([2022-points-s-pitching].PID = [2017-standard-pitching].PID AND [2017-standard-pitching].[GS]>=5))
LEFT JOIN [2017-advanced-pitching] ON ([2022-points-s-pitching].PID = [2017-advanced-pitching].PID AND [2017-advanced-pitching].[GS]>=5))
LEFT JOIN [2016-points-s-pitching] ON ([2022-points-s-pitching].PID = [2016-points-s-pitching].PID AND [2016-points-s-pitching].[GS]>=5))
LEFT JOIN [2016-standard-pitching] ON ([2022-points-s-pitching].PID = [2016-standard-pitching].PID AND [2016-standard-pitching].[GS]>=5))
LEFT JOIN [2016-advanced-pitching] ON ([2022-points-s-pitching].PID = [2016-advanced-pitching].PID AND [2016-advanced-pitching].[GS]>=5))
LEFT JOIN [2015-points-s-pitching] ON ([2022-points-s-pitching].PID = [2015-points-s-pitching].PID AND [2015-points-s-pitching].[GS]>=5))
LEFT JOIN [2015-standard-pitching] ON ([2022-points-s-pitching].PID = [2015-standard-pitching].PID AND [2015-standard-pitching].[GS]>=5))
LEFT JOIN [2015-advanced-pitching] ON ([2022-points-s-pitching].PID = [2015-advanced-pitching].PID AND [2015-advanced-pitching].[GS]>=5);
- Harun24HRBronze ContributorUNION query may simplify your current query.
- TheLuggageMonkeyCopper Contributor
Thanks for your reply.
I don't want to union any data together into the same column though. What I'm trying to do is create a new table with columns drawn from multiple sources, none of these columns will merge data, they will be a separate column in a new table.
However, I want this new column to include data where ID's match the 2022 ID and other conditions.Here's a simplified version of what I'm trying to achieve.
This is what I have:
2022 Table
PlayerID
GamesPlayedPoints
2021 Table
PlayerID
GamesPlayedPoints
2020 Table
PlayerID
GamesPlayedPoints
I want to create a new table as such:
Combined Table
PlayerID
2022Points (Where 2022GamesPlayed>=5)
2021Points (WHERE 2021PlayerID = 2022PlayerID AND 2022GamesPlayed>=5 AND 2021GamesPlayed>=5)
2020Points (WHERE 2020PlayerID = 2022PlayerID AND 2022GamesPlayed>=5 AND 2020GamesPlayed>=5)
In other words I want one row for each player with historical data based on the number of games they've played in the current season and the season that data is being pulled from. They don't need to have played 5 games in every season, only the current season and historical season in question.
I'm not very experienced with SQL and there may be another way to do this that I'm not seeing.
Cheers