Results 1 to 1 of 1
  1. #1
    Join Date
    Jun 2004
    Posts
    9

    Unanswered: Tournament App - Union not updateable. Is there a workaround?

    Tournament App - Union fails. Is there a workaround?

    Hello.

    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.

    Thanks.
    Last edited by kaanuki; 06-25-04 at 16:15.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •