Unanswered: Tournament App - Union not updateable. Is there a workaround?
Tournament App - Union fails. Is there a workaround?
I'm creating an 32 team tournament bracket application online, using Access as my db, because of low number of users. I will convert immediately to MS SQL if my problem can be remedied, but from what I have read MS SQL may not work either...
I'd like it to automatically select the winners from each round after I enter the scores. However, after round 2 (going into round three), I am running into problems because access says that "my recordset is not updateable".
Here's what I do.
Round 1: I manually enter the 2 player matches into 16 seperate game tables (ie, R1GAME1 - R1GAME16)
Round 2 (where automation begins): Round 2 Table selects the winner from from each game table (ex. - SELECT *
FROM R1G1 WHERE Score1=(SELECT MAX(Score1) FROM R1G1); /////// This gives me the 16 winners' Tables from round 1 (which I call R2TEAM1 - R2TEAM16), inwhich I am able to enter the round 2 scores into. However, these tables are 1 record tables, and I need to create "matchups" like I did in round 1, to compare and select the highest round 2 scores.
Round 3 (here's where I run into problems): I entered the round 2 scores in each of the respective R2TEAMx tables, and then created 8 UNION tables, to compare the scores: The UNION table, called R2TEAM1_vs_R2TEAM2, uses this: SELECT * FROM R2TEAM1 UNION SELECT * FROM R2TEAM2.
I then create another query (called R3Team1) to select the MAX score from this table: SELECT *
FROM R2TEAM1_vs_R2TEAM2 WHERE Score2=(SELECT MAX(Score2) FROM R2TEAM1_vs_R2TEAM2);
Now, It grabs the winner perfectly, and I should be ready to enter the round 3 scores into this new table, but I get the error message : "This recordset is not updateable". It is because of the UNION table, but I cant figure out a way around using the UNION statement to create matchups for subsequently selecting the highest scorer.
I would appreciate any help, either an Access solution or MS SQL solution.