Solved Multiple Append/Update Query

[murray83]

OK so have a situation like so.

end of first of questions it displays the scores and then a query selects the team with highest score as shown in the attached image, but know i have an idea on how to move on and alter teams based on score, let me know if this would work or is daft, this is all back of the post it note ideas by the way

so

if txtTeamLeaving.Value ="Team 1" then 
DoCmd.OpenQuery "UpdateTeam2" 'moves team 2 from team 2 table to team 1 table 
DoCmd.OpenQuery "UpdateTeam3" 'moves team 3 from team 3 table to team 2 table 
DoCmd.OpenQuery "UpdateTeam4" 'moves team 4 from team 4 table to team 3 table 
else
end if

and then the same thing for when Team2, 3 or 4 are the high scoreres

or owuld it best to do just do it in sql with an update query

 

[MajP]

This seems overly complicated and not normalized. Why is there not a single Teams table? If a team is a team it all goes in one table.

 

[murray83]

im in the process of making ( trying to make ) an access version of the UK Gameshow pointless, where if the team has the highest score then they leave.

but i think i have nailed it, see attached

 

[June7]

Misspelled Doctor as Docotor in question. Suggest setting TabStop to No on textboxes that are locked and don't need focus, such as player names. Not listing Alaska, Florida, Georgia as an answer for states ending in "a" (any more?). Misspelled "North Decota".

How do you determine score for each answer?

 

[MajP]

I think this would have been a lot easier with a normalized structure

tblTeams
-- Team Number
-- Player Number
-- Player Score

 

[murray83]

Misspelled Doctor as Docotor in question. Suggest setting TabStop to No on textboxes that are locked and don't need focus, such as player names. Forgot Alaska as an answer for states ending in "a". Misspelled "North Decota".

How do you determine score for each answer?

Hugh Grant was almost 9th Doctor but he turned it down and Eccleston got it.

i saw i missed Alaska after posted it and tried it myself and said that was wrong, i was like WTF ( wednesday, thursday, friday )

and how i work them out, well at mo making it up but have two options, use excel attached and change the formula, this is what i sue for my quiz-a-rama percentage ask the audience or just 10 or so people and then use that as a base as

 

[June7]

I edited my previous post after you read it.

 

[June7]

Oh yeah, and Arizona, Indiana, Louisiana, Minnesota, Montana, Nevada, Oklahoma, Virginia.

 

[murray83]

Oh yeah, and Arizona, Indiana, Louisiana, Minnesota, Montana, Nevada, Oklahoma, Virginia.

cheers but was just holding for proper data

but what do you think of the idea/desgin/how its implemented

 

[June7]

Seem to remember reviewing this same db in another thread some months ago. I would say it functions nicely. May be good reason for separate team tables but might be worth another look at normalization.