October 25, 2008 at 4:25 am
Hi all,
Working with SQL Server 2008 Express, I am trying to match user answers to correct answers and tally up the score. Tables are as follows;
Answers
GameNo
Ans1
Ans2
Ans2... etc.
PlayerAnswers
GameNo
Player
Ans1
Ans2
Ans3... etc.
Score(total correct)
First of all, will this simple design work? Should each game have it's own table? Should each Player's game have it's own? Second, how do I compare and match correct answers to player answers, and calculate the total in "score" column? Iteration advice for multiple player records also welcome!
I've Googled till my fingers bled (well, almost), I think I'm not searching correct terms. I've found plenty on matching records(JOIN, MERGE, etc.), but can't find for individual data within records, or I am just not comprehending the correlation.
The "quiz" itself is in a VB 2008 Windows Form Application, with DataGridViews bound to these tables to show results. I've been trying on that end also, and I've managed to match answers and change formatting based on correct answers, just can't get the count. I'm assuming it is possible comparing datatables, but hit the same brick wall. If anyone has any hints in that direction they would also be appreciated.
Many thanks in advance,
Mike
October 25, 2008 at 10:44 am
See forum best practice
http://extras.sqlservercentral.com/prettifier/prettifier.aspx
I would suggest start with Sample Data/Table scripts
I don't quite understand the question, and it also sounds like an assignment/homework? 😛
Oops, I meant to post the Best Practice link (not the Prettifier), thanks for adding it guys
October 25, 2008 at 11:16 am
Jerry Hung (10/25/2008)
http://extras.sqlservercentral.com/prettifier/prettifier.aspx%5B/quote%5D
Heck... make it as pretty as you want... that's only part of the "best practice". What is a "best practice", though, is actually posting table CREATE statements and data in a readily consumable format so we can spend the time working on the problem instead of setting up test data.
See the link in my signature below for how to do all of that pretty easily.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 25, 2008 at 11:18 am
1mwalsh1 (10/25/2008)
Hi all,Working with SQL Server 2008 Express, I am trying to match user answers to correct answers and tally up the score. Tables are as follows;
Answers
GameNo
Ans1
Ans2
Ans2... etc.
PlayerAnswers
GameNo
Player
Ans1
Ans2
Ans3... etc.
Score(total correct)
First of all, will this simple design work? Should each game have it's own table? Should each Player's game have it's own? Second, how do I compare and match correct answers to player answers, and calculate the total in "score" column? Iteration advice for multiple player records also welcome!
I've Googled till my fingers bled (well, almost), I think I'm not searching correct terms. I've found plenty on matching records(JOIN, MERGE, etc.), but can't find for individual data within records, or I am just not comprehending the correlation.
The "quiz" itself is in a VB 2008 Windows Form Application, with DataGridViews bound to these tables to show results. I've been trying on that end also, and I've managed to match answers and change formatting based on correct answers, just can't get the count. I'm assuming it is possible comparing datatables, but hit the same brick wall. If anyone has any hints in that direction they would also be appreciated.
Many thanks in advance,
Mike
Mike, take a look at the link in my signature... it'll help you get a better anwser quicker. Sounds like you already have tables for this venture, so shouldn't be a problem...
--Jeff Moden
Change is inevitable... Change for the better is not.
October 25, 2008 at 11:31 am
1mwalsh1 (10/25/2008)
First of all, will this simple design work?
Will it work? Yes. Is it the best design? Hard to say with so little information but probably not...
Should each game have it's own table? Should each Player's game have it's own?
No and no.
However, it seems likely that each answer should have its own row, instead of its own column. Like so:Create table Games(
GameNo int)
Create Table GameAnswers(
GameNo int,
AnswerNo int,
Question varchar(255),
Answer varchar(255),
etc...)
Create Table PlayerAnswers(
GameNo int,
PlayerNo int,
AnswerNo int,
AnswerScore int)
Second, how do I compare and match correct answers to player answers, and calculate the total in "score" column? Iteration advice for multiple player records also welcome!
Well, with your layout, you just have to add them across one at a time:total = column1 + column2 + ... columnNwhich can be a problem if there are different numbers of columns/answers per game.
If you use my method, then you can do it pretty simply:Select GameNo, PlayerNo,
SUM(AnswerScore) as GameScore
From PlayerAnswers
Group By GameNo, PlayerNo
I've Googled till my fingers bled (well, almost), I think I'm not searching correct terms. I've found plenty on matching records(JOIN, MERGE, etc.), but can't find for individual data within records, or I am just not comprehending the correlation.
Right. thats because in relational design, you generally do not want a whole bunch of similar things to be in separate columns on one row, you want them to be in separate rows in one column. Then you can use row-wise "Aggregate" functions like SUM(), above.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 25, 2008 at 5:20 pm
Thank you all for your responses so far..
To try and clarify:
Is this a homework assignment? If only it were, I'd be much younger! No, I must confess, it's not a "Quiz" either, I fibbed. I just got started teaching myself VB and SQL not very long ago, and as a first learning exercise I decided to build an application to administrate a football pool for a friend of mine. As this is technically illegal in my state, I thought I'd use the analogy of a quiz, as I figured the mechanics would be about the same. I was hoping for a quick point in the right direction on this one query.
As far as making it "pretty", I will try, only I'm not sure where to start as most of the data is sent by parameters from VB to stored procedures to tables I created manually. I will attempt to "Create" the CREATE statements;
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..Pick','U') IS NOT NULL
DROP TABLE Pick
--===== Create the test table with
CREATE Table Pick(
Season BigInt,
Week BigInt,
Wins BigInt NULL, 'This is where I was attempting to sum # of matches.
Player VarChar (50),
Game1 VarChar (50) NULL, 'I am allowing NULLs on these fields, for when choosing player from
Game2 VarChar (50) NULL, 'combobox on form, it first checks whether picks have already
Game3 VarChar (50) NULL, 'been made, if not, a sp inserts a new record with
Game4 VarChar (50) NULL, '"Season", "Week", and "Player", and then picks may be added.
Game5 VarChar (50) NULL, ' If picks already exist, an UPDATE sp is then called if picks
Game6 VarChar (50) NULL, 'are to be changed. Pretty much the same for "Winners" table
Game7 VarChar (50) NULL,
Game8 VarChar (50) NULL,
Game8 VarChar (50) NULL,
Game9 VarChar (50) NULL,
Game10 VarChar (50) NULL,
Game11 VarChar (50) NULL,
Game12 VarChar (50) NULL,
Game13 VarChar (50) NULL,
Game14 VarChar (50) NULL,
Game15 VarChar (50) NULL,
Points BigInt NULL)
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT Pick ON
--===== Insert the test data into the test table
INSERT INTO Pick
(Season, Week, Wins, Player, Game1, Game2, Game3, Game4, Game5, Game6, Game7, Game8,
Game9, Game10, Game11, Game12, Game13, Game14, Game15, Game16, Points)
SELECT '2008','1','0','Elway','NYG','ATL','BAL','BUF','NYJ','NE','NO','PHI','PIT','TEN','DAL','CAR','ARI','CHI','GB',
'DEN','43' UNION ALL
SELECT '2008','1','0','Robb Gunn','NYG','DET','BAL','SEA','NYJ','KC','NO','STL','PIT','TEN','CLE','CAR','SF','CHI',
'MIN','DEN','33'
--===== Set the identity insert back to normal
SET IDENTITY_INSERT Pick ON
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..Winner','U') IS NOT NULL
DROP TABLE Winner
CREATE Table Winner(
Season BigInt,
Week BigInt,
Game1 VarChar (50) NULL,
Game2 VarChar (50) NULL,
Game3 VarChar (50) NULL,
Game4 VarChar (50) NULL,
Game5 VarChar (50) NULL,
Game6 VarChar (50) NULL,
Game7 VarChar (50) NULL,
Game8 VarChar (50) NULL,
Game8 VarChar (50) NULL,
Game9 VarChar (50) NULL,
Game10 VarChar (50) NULL,
Game11 VarChar (50) NULL,
Game12 VarChar (50) NULL,
Game13 VarChar (50) NULL,
Game14 VarChar (50) NULL,
Game15 VarChar (50) NULL,
Points BigInt NULL)
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT Winner ON
--===== Insert the test data into the test table
INSERT INTO Winner
(Season, Week, Game1, Game2, Game3, Game4, Game5, Game6, Game7, Game8,
Game9, Game10, Game11, Game12, Game13, Game14, Game15, Game16, Points)
SELECT '2008','1','NYG','ATL','BAL','BUF','NYJ','NE','NO','PHI','PIT','TEN','DAL','CAR','ARI','CHI','GB','DEN','43'
--===== Set the identity insert back to normal
SET IDENTITY_INSERT Winner ON
I've haven't dealt with SET IDENTITY_INSERT or the DROP statement yet, I copied from your example, changed "#myTable", sorry if wrong. Thanks for introducing me to QUOTENAME, nifty.
If more info needed, please ask before giving up on me, I'll try to make it pretty for you!
Thanks for your patience and direction, eagerly awaiting direction. If nothing else, I've already learned from you, so thanks again!
Mike
October 25, 2008 at 5:32 pm
P.S. All the code I've tried so far I have deleted in frustration. If you would care to see the VB code that I managed to match the datagridviews with, I'd be more than happy to post it, wasn't sure if it would be of value for this discussion.
Meanwhile, I found something this morning I'm going to give a try, as well as follow the leads and suggestions from your posts.
Mike
October 25, 2008 at 5:44 pm
To be honest, I've kind of lost track of where we are on this: What are you looking for next from us?
As for the VB code: go ahead and post it as an attachment. Your existing stored procedures would be helpful also.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 25, 2008 at 5:53 pm
1mwalsh1 (10/25/2008)
I just got started teaching myself VB and SQL not very long ago, and as a first learning exercise ...
If you would care to see the VB code that I managed to match the datagridviews with, ...
You just got started with VB and you're using DataGridViews? Well, that's certainly jumping in the deep end. Good for you!
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 25, 2008 at 8:43 pm
1mwalsh1 (10/25/2008)
I am trying to match user answers to correct answers and tally up the score.
OK, I guess this is where you are.
Try this:
SELECT
w.Season
, w.Week
, p.Player
, Case When w.Game1 = p.Game1 Then 1 Else 0 End
+ Case When w.Game2 = p.Game2 Then 1 Else 0 End
+ Case When w.Game3 = p.Game3 Then 1 Else 0 End
+ Case When w.Game4 = p.Game4 Then 1 Else 0 End
+ Case When w.Game5 = p.Game5 Then 1 Else 0 End
+ Case When w.Game6 = p.Game6 Then 1 Else 0 End
+ Case When w.Game7 = p.Game7 Then 1 Else 0 End
+ Case When w.Game8 = p.Game8 Then 1 Else 0 End
+ Case When w.Game9 = p.Game9 Then 1 Else 0 End
+ Case When w.Game10 = p.Game10 Then 1 Else 0 End
+ Case When w.Game11 = p.Game11 Then 1 Else 0 End
+ Case When w.Game12 = p.Game12 Then 1 Else 0 End
+ Case When w.Game13 = p.Game13 Then 1 Else 0 End
+ Case When w.Game14 = p.Game14 Then 1 Else 0 End
+ Case When w.Game15 = p.Game15 Then 1 Else 0 End
+ Case When w.Game16 = p.Game16 Then 1 Else 0 End
As TotalScore
From Winner w
Join Pick p ON w.Season = p.Season And w.Week = p.Week
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 26, 2008 at 3:48 am
Hey rbarryyoung,
I believe that last statement hit it! I'll figure out how to fit it into my scenario.
Speaking of which, any comments/advice on the structure of the two tables you've seen? As you commented, I've little experience here. I've played around with VB off and on for sometime, making spreadsheets in Excel, but just recently (month or two) decided to try and figure out what was really possible. I downloaded the Express versions of VB 2008 and SQL Server, I read a few books, have done a lot of searching online, but I think my education is proceeding in a such a hit and miss manner, solving problems when I come to them, that I sometimes am missing the big picture. But, it keeps me out of the bars!
Any advice on reading material or sites to visit would be greatly appreciated.
Thank you all again, if I have further problems with this issue, I will post again and attach the whole mess. I'm not sure my fragile ego could stand the laughter if I showed you now!
Mike
October 26, 2008 at 11:10 am
My one suggestion would be the same as before: try a more relational approach to this.
Here is an example of how you might do that:
--====== Relational Tables
Create Table Players(
PlayerID SmallInt Identity,
PlayerName Varchar(50))
Create Table Teams(
TeamID TinyInt Identity,
TeamName Varchar(50))
Create Table Games(
Season TinyInt,
Week TinyInt,
GameNo TinyInt,
WinningTeamID TinyInt NULL)
CREATE Table GamePicks(
Season TinyInt,
Week TinyInt,
PlayerID SmallInt,
GameNo TinyInt,
TeamID TinyInt NULL)
--====== Total Query w/ Relational Tables
SELECT
g.Season
, g.Week
, pl.PlayerName
, SUM(Case When g.WinningTeamID = gp.TeamID Then 1 Else 0 End)
As PlayersWeekScore
From Games g
Join GamePicks gp
ON g.Season = gp.Season
And g.Week = gp.Week
And g.GameNo = gp.GameNo
Join Players pl ON gp.PlayerID = pl.PlayerID
Group By g.Season, g.Week, pl.PlayerName
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 26, 2008 at 2:55 pm
Hi again rbarryyoung,
Are you psychic? I was logging in to see if any replies on SQL books, while in the back of my mind the question to your answer was lurking!
I will give that a try a.s.a.p. I've played with relationships, but didn't really get where I was going to go with them. As a first project, I considered it simple enough to get by without them, while getting my feet wet in the SQL shallow end. I've a good feel for VB now, can mostly figure things out given enough time, so I'm going to concentrate more on SQL.
By the way, I checked out your site this a.m., though far above my head now, hopefully someday I'll be able to enjoy it!
Thanks,
Mike
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply