Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
MrRay
Starting Member
28 Posts |
 Posted - 2004-12-02 : 18:31:21
|
| I'm looking for some help with query/datagrid design. See the table samples below. The basics of the project are that a user will visit a page with a test in it. That user taking that test results in a session being created. The session data is in the "SESSIONS" table.During each test session, the user will answer multiple questions. Each question will result in about 12 pieces of data, so I have created a separate table for "RESULTS", where each question will create its own record. As you can see, each Result is associated with a Session.And each 'session' is then associated with a particular 'user' and 'test', both of which have more data in a separate USERS and TESTS tables. (That query is already working.)I have one problem: My report (simple datagrid created in ASP.NET|VB) needs to contain all information from the Sessions table and some summary information from the Results table. So in the datagrid I need to show: - All columns from SESSIONS for each SessionID (name, session, test, totalscore, etc) - Some columns from Results table where the SessionID exists (Q1 Answer, Q2 Answer, Q3 Answer, etc), where each Q is in its own Result record.How do you recommend going about this?======= SESSIONS TABLE ======= SESSIONID | USERID | TESTID | STARTTIME | STATUS 100001 . . . . . user1 . . . test1 . . . 11:30:02 . . . Done 100002 . . . . . user1 . . . test2 . . . 12:29:55 . . . Done 100003 . . . . . user2 . . . test1 . . . 15:43:45 . . . Done 100004 . . . . . user2 . . . test2 . . . 16:08:21 . . . Done ======= RESULTS TABLE ======= RESULTID | SESSIONID | QUESTION# | CORRECT | ACTUAL (total 12 cols) 500021 . . . . 100001 . . . . . . 1 . . . . . . . . c . . . . . . . etc. 500022 . . . . 100001 . . . . . . 2 . . . . . . . . b . . . . . . . etc. 500023 . . . . 100002 . . . . . . 1 . . . . . . . . a . . . . . . . etc. 500024 . . . . 100001 . . . . . . 3 . . . . . . . . b . . . . . . . etc. 500025 . . . . 100002 . . . . . . 2 . . . . . . . . d . . . . . . . etc. 500026 . . . . 100002 . . . . . . 3 . . . . . . . . b . . . . . . . etc. 500027 . . . . 100004 . . . . . . 1 . . . . . . . . a . . . . . . . etc. 500028 . . . . 100003 . . . . . . 1 . . . . . . . . d . . . . . . . etc. 500029 . . . . 100003 . . . . . . 2 . . . . . . . . c . . . . . . . etc. |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2004-12-02 : 19:49:26
|
| You'll need to add two datatables to your dataset, and then create a data relation between the two. Then Databind() your grid to the Dataset and it should work.Take a look at this link:http://www.wimdows.net/articles/article.aspx?aid=19Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
 |
|
|
|
|
|
|
|
Database & Query Design