Solved Multiple Append/Update Query (1 Viewer)

murray83

Games Collector
Local time
Today, 13:14
Joined
Mar 31, 2017
Messages
617
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

Code:
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
 

Attachments

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:14
Joined
May 21, 2018
Messages
3,383
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

Games Collector
Local time
Today, 13:14
Joined
Mar 31, 2017
Messages
617
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
 

Attachments

June7

AWF VIP
Local time
Today, 04:14
Joined
Mar 9, 2014
Messages
3,292
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?
 
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:14
Joined
May 21, 2018
Messages
3,383
I think this would have been a lot easier with a normalized structure

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

murray83

Games Collector
Local time
Today, 13:14
Joined
Mar 31, 2017
Messages
617
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
 

Attachments

June7

AWF VIP
Local time
Today, 04:14
Joined
Mar 9, 2014
Messages
3,292
Oh yeah, and Arizona, Indiana, Louisiana, Minnesota, Montana, Nevada, Oklahoma, Virginia.
 

murray83

Games Collector
Local time
Today, 13:14
Joined
Mar 31, 2017
Messages
617
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
 
Last edited:

June7

AWF VIP
Local time
Today, 04:14
Joined
Mar 9, 2014
Messages
3,292
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.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom