locked
Querying From Multiple Tables - VB.Net/MS Access 2013 RRS feed

  • Question

  • How do I query from multiple tables? I am trying to print a user's answer sheet to show in a datagridview. However, I need to pull the needed data from three different tables: tblQuestions, tblQuizAnswers, and tblQuizResults.

    Here are the columns I need from each table:

    tblQuizAnswers:
    -StudentID
    -FirstName
    -LastName
    -pCourse
    -CourseSubject
    -QuizID1
    -QuizTitle
    -QuizNumber
    -Answer
    -DateTaken

    tblQuizResults:
    -QuizScore
    -QPercentage

    tblQuestions:
    -ActualQuestion
    -CorrectAnswer
    -AnswerExplanation

    Here's what's common between the tables:

    tblQuestions & tblQuizAnswers:
    -QuizID/QuizID1
    -QuizTitle/QuizTitle
    -QuestionNumber/QuizNumber
    -CourseSubject/CourseSubject
    -CorrectAnswer/CorrectAnswer

    tblQuestions & tblQuizResults:
    -QuizID/QuizID1
    -QuizTitle/QuizTitle
    -CourseSubject/CourseSubject

    tblQuizAnswers & tblQuizResults:
    -StudentID/StudentID
    -FirstName/FirstName
    -LastName/LastName
    -pCourse/pCourse
    -CourseSubject/CourseSubject
    =QuizID1/QuizID
    -QuizTitle/QuizTitle

    all three tables:
    -QuizID
    -QuizTitle
    -CourseSubject

    I also need to show the corresponding data for the user's answer. For example, the user's answer for question number one for a particular quiz is letter A (and that's the only data that's being saved in the db), I need to show in my gridview what was option A.
    Monday, August 7, 2017 7:55 AM

All replies

  • There are many examples that document how to join multiple tables into a single query based upon common fields. See the below link for an example:

    https://stackoverflow.com/questions/19367565/access-sql-inner-join-with-multiple-tables


    Paul ~~~~ Microsoft MVP (Visual Basic)


    Monday, August 7, 2017 12:41 PM
  • Hi astrid22,

    According to your description, you can try to the code below.

    Dim dt As New DataTable()
            Dim conn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Database\Date.accdb; Persist Security Info=False;")
            Dim selectSql = "select  ta.StudentID,ta.FirstName,ta.LastName,ta.pCourse,ta.CourseSubject,ta.QuizID1,ta.QuizTitle,ta.QuizNumber,
    ta.Answer,ta.DateTaken,tq.ActualQuestion,tq.CorrectAnswer,tq.AnswerExplanation,tqr.QuizScore,tqr.QPercentage
    from (tblQuizAnswers ta
    inner join tblQuestions tq on ta.QuizID1=tq.QuizID)
    inner join tblQuizResults tqr on tq.QuizID=tqr.QuizID1 "
            conn.Open()
            Dim cmd As New OleDbCommand(selectSql, conn)
            Dim adapter As New OleDbDataAdapter(cmd)
            adapter.Fill(dt)
            DataGridView1.DataSource = dt

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, August 8, 2017 4:17 AM
  • Astrid,

    Be aware that with an SQL string which contains joins you can show what you want as lines in a DataGridView. 

    However, not update it. 

    For real relational presentation in a Data Grid you can use 3th party controls. 

    http://www.bing.com/search?q=windows+forms+3th+party+controls&FORM=HDRSC1


    Success
    Cor

    Tuesday, August 8, 2017 7:44 AM