Quiz database - Brainfreeze (1 Viewer)

PhilC

New member
Local time
Today, 10:51
Joined
Nov 6, 2019
Messages
24
I am creating a scoring system to keep track of team scores during a quiz night.

I have three tables tblTeam, tblRound and tblScore.

A quiz consists of say 10 rounds
During a quiz a team cn play its Joker which doubles the Team score for that round only. A team can only do that for one round.

tblTeam has fields TeamID(Number PK), TeamName(ShortText), JokerRound (Number)
tblRound has fields RoundID(Number PK), RoundName(ShortText)
tblScore has fields ScorID(Number PK), TeamID(Number), RoundID(Number), Score(Number), Joker(T/F)

All Number Fields are Long Integer

Before the quiz starts each team has to register with a team name and the round they wish to play their Joker. I have created a continuous form to enter this detail. (frmTeamEntry). This works fine. This form's record source is tblTeam.

This is where I have got stuck with brainfreeze!

I want to write VBA event procedure for a button on frmTeamEntry to populate tblScore fields. I am familiar with using VBA and planned to use recordsets to achieve the population. But I just cannot fathom how.

All suggestions very, very welcome.

Many thanks
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:51
Joined
May 7, 2009
Messages
19,175
you can use Insert Query.
how many teams will play for the whole game?
 

PhilC

New member
Local time
Today, 10:51
Joined
Nov 6, 2019
Messages
24
Thanks arnelgp. There could be between 12 and 20 teams depending on how many folk turn up on the night.

I had considered using an append query but thought using recordsets might be a swift answer. I'll give that a try.

many thanks
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:51
Joined
May 7, 2009
Messages
19,175
make sure to add Index to tblScore table:

TeamID + RoundID (Unique)

the Query:

Insert into tblScore (TeamID, RoundID)
Select tblTeam.TeamID, tblRound.RoundID
From tblTeam, tblRound;
 

PhilC

New member
Local time
Today, 10:51
Joined
Nov 6, 2019
Messages
24
Thanks again arnelgp.

I have created the initial insert query which matches yours identically. Good to know two minds think alike! But to get the Joker flag to match the chosen round number in tblteam I created an update query which adds the true flag for the appropriate Team/Round combination, i.e.

UPDATE (tblRound INNER JOIN tblScore ON tblRound.RoundID = tblScore.RoundID) INNER JOIN tblTeam ON (tblTeam.TeamID = tblScore.TeamID) AND (tblRound.RoundID = tblTeam.JokerRound) SET tblScore.Joker = True
WHERE (((tblScore.TeamID)=[tblTeam].[TeamID]) AND ((tblScore.RoundID)=[tblRound].[RoundID]) AND ((tblTeam.JokerRound)=[tblScore].[RoundID]));

It works brilliantly.

Many thanks for your help.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:51
Joined
May 7, 2009
Messages
19,175
you can also create your form (for scoring).
the demo has only 2 teams.
i am sure you can figure it out on the other teams.
 

Attachments

  • showTime.accdb
    692 KB · Views: 454

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:51
Joined
Feb 19, 2002
Messages
42,981
Can multiple teams get a score for any question? If not then the Score table should not be pre-populated with empty rows. It should be:

tblScore has fields ScorID(Number PK), TeamID(Number), RoundID(Number), QuestionNum(Number)Score(Number)

Then a teamps score for the round is the sum of its scores in tblScore and the flag in the team table tells you whether to double it.
 

PhilC

New member
Local time
Today, 10:51
Joined
Nov 6, 2019
Messages
24
Yes each team will gain a score for each of the ten rounds. A team can score 2 points for each correct answer, but if they have played their joker on that round they can score a maximum of 40 points. So over ten rounds they could score 9 x 20 points and 1 x 40 points. Every team is independent of the others so every team could, not very likely, score 220 points over the ten rounds. With arnelgp's help it all works a treat now, including entering the scores.

Thanks all.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:51
Joined
Feb 19, 2002
Messages
42,981
I didn't ask about the round, I asked about multiple teams getting a score for the same question. Even if multiple teams can get a score for a single question, I would not pre-create the empty rows. But if you insist,

Create a query that selects the TeamID from tblTeams. Add tblRounds. There will be no join because this query will be a Cartesian Product - all rows in tbl1 * all rows in tbl2. Change the query type to Append and choose tblScore. Select the TeamID from tblTeams and the RoundID from tblRound. Save the query. Run it. The query will append one row to tblScore for each team for each round.
 

PhilC

New member
Local time
Today, 10:51
Joined
Nov 6, 2019
Messages
24
Pat, I think we're singing from the same hymn sheet. Your suggestion is exactly what I have done to create the tblScore table. My apologies if I have inadvertently misled you.

I have 3 tables: tblTeam, tblRound and tblScore. As you advised, I created tblscore from a query containing tblTeam.TeamID and tblRound.RoundID which were not joined. The other two fields in tblScore are Score and Joker.

I have 3 forms. One to enter the teamNames and the teams chosen Joker round. One to enter the score for each team after each round. The third form is created from a crosstab query which shows the scoreboard with teams and total team score as row headings and Rounds as column heading and round score as value. I have tested with dummy data and everything seems to work as expected.

I am immensely grateful to all contributors. Your advice has enabled me to see clearly where I was going wrong.

Thank you
 

Users who are viewing this thread

Top Bottom