none
VB.NET/Access - Query RRS feed

  • Question

  • I got a query from a question I asked earlier.  It worked perfectly.  I wanted to count the total number of entries for each student and display their total in a datagridview.

    Here is the query that worked.

    SELECT 
       FirstName AS [First Name],
      LastName AS [Last Name], 
      TrainDate AS [Training Date], Count(*) AS TotalCount
    FROM 
       ACJATTENDANCE
    GROUP BY FirstName, LastName, TrainDate, TrainDate;

    Here is my modification that doesn't work.  It displays every entry for every user.

    ID                  LastName       FirstName        TrainDate        Classes
    3                    Smith             Joe                  3/1/2020         1
    3                    Smith             Joe                  3/1/2020         1
    3                    Harris             Jason               3/15/2020       1

    Instead of...

    ID                  LastName           FirstName             TrainDate        Classes
    3                   Smith                  Joe                       3/1/2020        2
    1                   Harris                  Jason                    3/15/2020      1

                             "SELECT
                                  ID AS [STUDENT ID],
                                  FirstName AS [First Name],
                                  LastName AS [Last Name],
                                  TrainDate AS [Training Date], Count(*) AS TotalCount  
                             FROM 
                                   ACJATTENDANCE
                             GROUP BY ID, LastName, FirstName, TrainDate, TrainDate;"


    • Edited by Chet.Horton Wednesday, March 25, 2020 5:30 PM left out field in 2nd query
    Wednesday, March 25, 2020 5:28 PM

All replies

  • Hi Chet.Horton,

    Please try this way to populate your DataGridView

    Imports System.Data.OleDb
    Imports System.Text
    
    Public Class Form1
    
        Private sql As String
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles Me.Load
            Using connection As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\Database1.accdb")
                sql = " SELECT ID , FirstName  AS [First Name],LastName AS [Last Name], FROM ACJATTENDANCE GROUP BY ID, FirstName, LastName, TrainDate"
                Dim adapter As OleDbDataAdapter = New OleDbDataAdapter(sql, connection)
                Dim studentds As DataSet = New DataSet()
                adapter.Fill(studentds, "tblstudents")
                DataGridView1.DataSource = studentds.Tables("tblstudents")
            End Using
        End Sub
    End Class

    This is the result after the form has been loaded


    Please remember to mark the replies as answers if they helped you :) ~

    Wednesday, March 25, 2020 9:06 PM
  • "I wanted to count the total number of entries for each student and display their total in a datagridview."

    Hi,

    Which column do you want to make a total count ????? ... Do you need to count the rows of the Datagridview Or you need to get the "SUM" of specific column 

    So of you need to count the ROWS you will need to do something like that after you fill it by the code of "REBIN QADIR"

    ===> (DataGridView1.RowCount -1) 

    And if you need to get the SUM of  any column you will need this code , Also after you fill it by the code of "REBIN QADIR"

    Dim sum As Double = 0
    For Each dgr As DataGridViewRow In DataGridView1.Rows
           sum = sum + dgr.Cells(3).Value.ToString     '' number 3 represents your column number 
    Next
     txtTotElect.Text = sum 

    =========

    DataGridView Columns count begin by 0,1,2,3,4..... and so on ,So be careful when count your columns

    You can control the sum of the columns or rows count for each student by filter the query of filling the DGV ...


    Regards From Egypt




    • Edited by Amr_Aly Wednesday, March 25, 2020 10:17 PM
    Wednesday, March 25, 2020 10:10 PM
  • Maybe ID is not a “Student ID” in this table?

    Thursday, March 26, 2020 3:44 PM