Hello,
I am creating a fantasy football website. I have two tables the Player table which stores the player ID , their week1 to 13 scores and total score. I have a form which allows scores to be added each week for a particular player and a sum calculation which totals the score and updates the total score.
I also created a Query with the query builder which totals week1 score for each user in the database. From the recordset i created with the query builder I drag the new week one score to the page and add a repeat region to display all records and an update to update the users table with their week one score.
The problem is this need score calculated from the query builder is not updating week1 score in the users table.
Below is a copy of the query:
SELECT users.*, manager.wkone AS wkone_15, manager.ID AS ID_15, Player_13.ID AS ID_14, Player_13.wkone AS wkone_14, Player_12.ID AS ID_13, Player_12.wkone AS wkone_13, Player_11.ID AS ID_12, Player_11.wkone AS wkone_12, Player_10.ID AS ID_11, Player_10.wkone AS wkone_11, Player_9.ID AS ID_10, Player_9.wkone AS wkone_10, Player_8.ID AS ID_9, Player_8.wkone AS wkone_9, Player_7.ID AS ID_8, Player_7.wkone AS wkone_8, Player_6.ID AS ID_7, Player_6.wkone AS wkone_7, Player_5.ID AS ID_6, Player_5.wkone AS wkone_6, Player_4.ID AS ID_5, Player_4.wkone AS wkone_5, Player_3.ID AS ID_4, Player_3.wkone AS wkone_4, Player_2.ID AS ID_3, Player_2.wkone AS wkone_3, Player_1.ID AS ID_2, Player_1.wkone AS wkone_2, Player_0.ID AS ID_1, Player_0.wkone AS wkone_1, Player.ID, Player.wkone, SUM(manager.wkone+Player.wkone+Player_0.wkone+Player_1.wkone+Player_2.wkone+Player_3.wkone+Player_4.wkone+Player_5.wkone+Player_6.wkone+Player_7.wkone+Player_8.wkone+Player_9.wkone+Player_10.wkone+Player_11.wkone+Player_12.wkone+Player_13.wkone) AS WeekOneScore
FROM ((((((((((((((((users LEFT JOIN Player AS Player_0 ON Player_0.ID=users.player2) LEFT JOIN Player AS Player_1 ON Player_1.ID=users.player3) LEFT JOIN Player AS Player_2 ON Player_2.ID=users.player4) LEFT JOIN Player AS Player_3 ON Player_3.ID=users.player5) LEFT JOIN Player AS Player_4 ON Player_4.ID=users.player6) LEFT JOIN Player AS Player_5 ON Player_5.ID=users.player7) LEFT JOIN Player AS Player_6 ON Player_6.ID=users.player8) LEFT JOIN Player AS Player_7 ON Player_7.ID=users.player9) LEFT JOIN Player AS Player_8 ON Player_8.ID=users.player10) LEFT JOIN Player AS Player_9 ON Player_9.ID=users.player11) LEFT JOIN Player AS Player_10 ON Player_10.ID=users.player12) LEFT JOIN Player AS Player_11 ON Player_11.ID=users.player13) LEFT JOIN Player AS Player_12 ON Player_12.ID=users.player14) LEFT JOIN Player AS Player_13 ON Player_13.ID=users.player15) LEFT JOIN Player ON Player.ID=users.player1) LEFT JOIN manager ON manager.ID=users.manager)
WHERE users.player1 = Player.ID
GROUP BY users.id
Regards,
Donna
