MS Access SQL求行与列SUM总和问题求助

Hi there! Let's break down how to add row totals (E_Total) and column totals (D_Total) to your Access query, plus explore the VB.NET DataGridView alternative you asked about.

1. Pure MS Access SQL Implementation #

First, we'll modify your existing query to include the row-level E_Total, then union in a summary row for D_Total. We'll use Nz() to handle null values (since some dates might have no data for an employee).

Modified Query with Totals #

-- Base query with E_Total column
SELECT
  CStr(es.EmpID) AS ID,
  es.FirstName AS Name,
  Nz(Sum(Switch(es.DateS = #5/1/2018#, es.Amount)), 0) AS [5/1/2018],
  Nz(Sum(Switch(es.DateS = #5/2/2018#, es.Amount)), 0) AS [5/2/2018],
  Nz(Sum(Switch(es.DateS = #5/3/2018#, es.Amount)), 0) AS [5/3/2018],
  (Max(IIf(es.DateS = #5/1/2018#, 1, 0)) + Max(IIf(es.DateS = #5/2/2018#, 1, 0)) + Max(IIf(es.DateS = #5/3/2018#, 1, 0))) AS Count,
  Nz(Sum(Switch(es.DateS = #5/1/2018#, es.Amount)), 0) + Nz(Sum(Switch(es.DateS = #5/2/2018#, es.Amount)), 0) + Nz(Sum(Switch(es.DateS = #5/3/2018#, es.Amount)), 0) AS E_Total
FROM (
  SELECT e.EmpID, e.FirstName, s.DateS, s.Amount
  FROM Employee AS e
  INNER JOIN Sale AS s ON (s.EmployeeID = e.EmpID AND s.Amount IS NOT NULL)
  WHERE s.DateS BETWEEN #5/1/2018# AND #5/3/2018#
  UNION ALL
  SELECT e1.EmpID, e1.FirstName, s1.DateS, s1.Amount
  FROM Employee1 AS e1
  INNER JOIN Sale1 AS s1 ON (s1.EmployeeID = e1.EmpID AND s1.Amount IS NOT NULL)
  WHERE s1.DateS BETWEEN #5/1/2018# AND #5/3/2018#
) AS es
GROUP BY es.EmpID, es.FirstName

-- Union in the D_Total summary row
UNION ALL

SELECT
  'D_Total' AS ID,
  '' AS Name,
  Sum(Nz(Switch(es.DateS = #5/1/2018#, es.Amount), 0)) AS [5/1/2018],
  Sum(Nz(Switch(es.DateS = #5/2/2018#, es.Amount), 0)) AS [5/2/2018],
  Sum(Nz(Switch(es.DateS = #5/3/2018#, es.Amount), 0)) AS [5/3/2018],
  Sum(Max(IIf(es.DateS = #5/1/2018#, 1, 0)) + Max(IIf(es.DateS = #5/2/2018#, 1, 0)) + Max(IIf(es.DateS = #5/3/2018#, 1, 0))) AS Count,
  Sum(Nz(Sum(Switch(es.DateS = #5/1/2018#, es.Amount), 0) + Nz(Sum(Switch(es.DateS = #5/2/2018#, es.Amount), 0) + Nz(Sum(Switch(es.DateS = #5/3/2018#, es.Amount), 0)))) AS E_Total
FROM (
  SELECT e.EmpID, e.FirstName, s.DateS, s.Amount
  FROM Employee AS e
  INNER JOIN Sale AS s ON (s.EmployeeID = e.EmpID AND s.Amount IS NOT NULL)
  WHERE s.DateS BETWEEN #5/1/2018# AND #5/3/2018#
  UNION ALL
  SELECT e1.EmpID, e1.FirstName, s1.DateS, s1.Amount
  FROM Employee1 AS e1
  INNER JOIN Sale1 AS s1 ON (s1.EmployeeID = e1.EmpID AND s1.Amount IS NOT NULL)
  WHERE s1.DateS BETWEEN #5/1/2018# AND #5/3/2018#
) AS es
GROUP BY 1, 2
ORDER BY 
  CASE WHEN ID = 'D_Total' THEN 1 ELSE 0 END, -- Push D_Total to the bottom
  ID;

Notes:

• We convert EmpID to a string with CStr() so the D_Total text value matches the column type.
• Nz() ensures nulls (no sales on a date) are treated as 0 for accurate sums.
• The final ORDER BY keeps employee rows first, then the D_Total row at the bottom.

2. VB.NET DataGridView Alternative #

Absolutely, you can shift this logic to VB.NET's DataGridView—it's especially useful if your date range might change dynamically (so you don't have to rewrite SQL every time). Here's a step-by-step approach:

Step 1: Load Data into a DataTable #

First, execute your original SQL query and fill a DataTable:

Imports System.Data.OleDb

' Replace with your Access connection string
Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\YourDatabase.accdb;"
Dim originalSql As String = "SELECT es.EmpID, es.FirstName, sum(switch(es.DateS = #5/1/2018#, es.Amount)) AS [5/1/2018], sum(switch(es.DateS = #5/2/2018#, es.Amount)) AS [5/2/2018], sum(switch(es.DateS = #5/3/2018#, es.Amount)) AS [5/3/2018], (max(iif(es.DateS = #5/1/2018#, 1, 0)) + max(iif(es.DateS = #5/2/2018#, 1, 0)) + max(iif(es.DateS = #5/3/2018#, 1, 0)) ) as Count from ( select e.EmpID, e.FirstName, s.DateS, s.Amount from Employee as e inner join Sale as s on ( s.EmployeeID = e.EmpID AND s.Amount IS NOT NULL) where s.DateS between #5/1/2018# and #5/3/2018# union all select e1.EmpID, e1.FirstName, s1.DateS, s1.Amount from Employee1 as e1 inner join Sale1 as s1 on ( s1.EmployeeID = e1.EmpID and s1.Amount IS NOT NULL) where s1.DateS between #5/1/2018# and #5/3/2018# ) as es group by es.EmpID, es.FirstName order by es.EmpID;"

Dim dt As New DataTable()
Using conn As New OleDbConnection(connString)
    Using cmd As New OleDbCommand(originalSql, conn)
        conn.Open()
        dt.Load(cmd.ExecuteReader())
    End Using
End Using

Step 2: Add E_Total Column to DataTable #

Calculate row totals and add them to a new column:

' Add E_Total column
dt.Columns.Add("E_Total", GetType(Integer))

For Each row As DataRow In dt.Rows
    Dim total As Integer = 0
    ' Sum the date columns (adjust column names if needed)
    total += If(row("[5/1/2018]") Is DBNull.Value, 0, CInt(row("[5/1/2018]")))
    total += If(row("[5/2/2018]") Is DBNull.Value, 0, CInt(row("[5/2/2018]")))
    total += If(row("[5/3/2018]") Is DBNull.Value, 0, CInt(row("[5/3/2018]")))
    row("E_Total") = total
Next

Step 3: Add D_Total Summary Row #

Compute column totals and add a summary row:

Dim summaryRow As DataRow = dt.NewRow()
summaryRow("EmpID") = "D_Total"
summaryRow("FirstName") = ""

' Calculate column sums
summaryRow("[5/1/2018]") = dt.Compute("Sum([5/1/2018])", "")
summaryRow("[5/2/2018]") = dt.Compute("Sum([5/2/2018])", "")
summaryRow("[5/3/2018]") = dt.Compute("Sum([5/3/2018])", "")
summaryRow("Count") = dt.Compute("Sum(Count)", "")
summaryRow("E_Total") = dt.Compute("Sum(E_Total)", "")

dt.Rows.Add(summaryRow)

Step 4: Bind to DataGridView and Style #

Finally, bind the DataTable to your DataGridView and format the summary row:

dataGridView1.DataSource = dt

' Style the D_Total row (make text bold)
For Each row As DataGridViewRow In dataGridView1.Rows
    If row.Cells("EmpID").Value.ToString() = "D_Total" Then
        row.DefaultCellStyle.Font = New Font(dataGridView1.Font, FontStyle.Bold)
    End If
Next

Which Option Should You Choose? #

• Use SQL if your date range is fixed and you want the totals calculated directly in the database.
• Use VB.NET DataGridView if you need dynamic date ranges, plan to add more formatting, or want to keep database queries simpler. It's more flexible for UI-level adjustments.

内容的提问来源于stack exchange，提问作者Tony Nguyen