尝试对多个实例的选定行求和

Question

我有一个查询设置，如下所示：

  PEmpID  /  PEmplye  /  PPay  /  PTip  /  BEmpID  /  BEmplye  /  BPay  /  BTip
    17    /    Desi   /  4.80  /  0.00  /    06    /   Mike    /  5.10  /  0.00
    01    /    Sally  /  2.40  /  0.80  /    06    /   Mike    /  2.55  /  0.85
    01    /    Sally  /  2.80  /  0.80  /    01    /   Sally   /  2.98  /  0.85
    17    /    Desi   /  2.40  /  0.80  /    06    /   Mike    /  2.55  /  0.85

我需要以下结果：

  ID   /  NAME  /  PPay  /  PTip  /  BPay  /  BTip
  01   /  Sally /  5.20  /  1.60  /  2.98  /  0.85
  06   /  Mike  /  0.00  /  0.00  /  10.20 /  1.70
  17   /  Desi  /  7.20  /  0.80  /  0.00  /  0.00

我尝试的不起作用：

        EDITED & UPDATED

提出了使用 UNION 查询的建议。我有4个选择查询：qryTESTppay，qryTESTbpay，qryTESTdpay和qryTESTgpay。其中每个都返回所需的确切数字，如下所示：

  ID  /  NAME  /  PPay  /  PTip  /  BPay  /  BTip
  01  /  Sally /  5.20  /  1.60  /  Rand  /  Rand
  06  /  Mike  /  0.00  /  0.00  /  Rand  /  Rand
  17  /  Desi  /  2.40  /  0.80  /  Rand  /  Rand

和

  ID  /  NAME  /  PPay  /  PTip  /  BPay  /  BTip  
  01  /  Sally /  Rand  /  Rand  /  2.98  /  0.85
  06  /  Mike  /  Rand  /  Rand  /  10.20 /  1.70
  17  /  Desi  /  Rand  /  Rand  /  0.00  /  0.00

其余 2 个查询以此类推，其中 Rand=Random Numbers。

我的 UNION 查询如下所示：

SELECT DISTINCTROW qryAllEmployee.ID, 
                   qryAllEmployee.NAME, 
                   Sum(qryTESTpayroll.PPay) AS Weekly_PPay, 
                   Sum(qryTESTpayroll.PTip) AS Weekly_PTip, 
                   0 AS Weekly_BPay, 
                   0 AS Weekly_BTip, 
                   0 AS Weekly_DPay, 
                   0 AS Weekly_DTip, 
                   0 AS Weekly_GPay, 
                   0 AS Weekly_GTip
FROM qryAllEmployee LEFT JOIN qryTESTpayroll ON qryAllEmployee.ID = qryTESTpayroll.PEmpID
GROUP BY qryAllEmployee.ID, qryAllEmployee.NAME
UNION
SELECT DISTINCTROW qryAllEmployee.ID, 
                   qryAllEmployee.NAME, 
                   0 AS Weekly_PPay, 
                   0 AS Weekly_PTip, 
                   Sum(qryTESTpayroll.BPay) AS Weekly_BPay, 
                   Sum(qryTESTpayroll.BTip) AS Weekly_BTip, 
                   0 AS Weekly_DPay, 
                   0 AS Weekly_DTip, 
                   0 AS Weekly_GPay, 
                   0 AS Weekly_GTip
FROM qryAllEmployee LEFT JOIN qryTESTpayroll ON qryAllEmployee.ID = qryTESTpayroll.BEmpID
GROUP BY qryAllEmployee.ID, qryAllEmployee.NAME
UNION
SELECT DISTINCTROW qryAllEmployee.ID, 
                   qryAllEmployee.NAME, 
                   0 AS Weekly_PPay, 
                   0 AS Weekly_PTip, 
                   0 AS Weekly_BPay, 
                   0 AS Weekly_BTip, 
                   Sum(qryTESTpayroll.DPay) AS Weekly_DPay, 
                   Sum(qryTESTpayroll.DTip) AS Weekly_DTip, 
                   0 AS Weekly_GPay, 
                   0 AS Weekly_GTip
FROM qryAllEmployee LEFT JOIN qryTESTpayroll ON qryAllEmployee.ID = qryTESTpayroll.DEmpID
GROUP BY qryAllEmployee.ID, qryAllEmployee.NAME
UNION
SELECT DISTINCTROW qryAllEmployee.ID, 
                   qryAllEmployee.NAME, 
                   0 AS Weekly_PPay, 
                   0 AS Weekly_PTip, 
                   0 AS Weekly_BPay, 
                   0 AS Weekly_BTip, 
                   0 AS Weekly_DPay, 
                   0 AS Weekly_DTip, 
                   Sum(qryTESTpayroll.GPay) AS Weekly_GPay, 
                   Sum(qryTESTpayroll.GTip) AS Weekly_GTip
FROM qryAllEmployee LEFT JOIN qryTESTpayroll ON qryAllEmployee.ID = qryTESTpayroll.GEmpID
GROUP BY qryAllEmployee.ID, qryAllEmployee.NAME;

我正在像这样将结果堆叠在一起：

  ID  /  NAME  /  PPay  /  PTip  /  BPay  /  BTip  /  Dpay  /  DTip  /  GPay  /  GTip
  01  /  Sally /  0.00  /  0.00  /  0.00  /  0.00  /  0.00  /  0.00  /**63.70*/**33.51**
  01  /  Sally /  0.00  /  0.00  /  0.00  /  0.00  /**9.00**/**2.16**/  0.00  /  0.00
  01  /  Sally /  0.00  /  0.00  /**3.40**/**0.76**/  0.00  /  0.00  /  0.00  /  0.00
  01  /  Sally /**26.00*/**5.00**/  0.00  /  0.00  /  0.00  /  0.00  /  0.00  /  0.00
  06  /  Mike  /  0.00  /  0.00  /  0.00  /  0.00  /  0.00  /  0.00  /  0.00  /  0.00
  06  /  Mike  /  0.00  /  0.00  /  0.00  /  0.00  /  0.00  /  0.00  /  0.00  /  0.00
  06  /  Mike  /  0.00  /  0.00  /  0.00  /  0.00  /**25.65*/**4.82**/  0.00  /  0.00
  06  /  Mike  /  0.00  /  0.00  /**42.51*/**6.70**/  0.00  /  0.00  /  0.00  /  0.00
  17  /  Desi  /  0.00  /  0.00  /  0.00  /  0.00  /  0.00  /  0.00  /  0.00  /  0.00
  17  /  Desi  /  0.00  /  0.00  /  0.00  /  0.00  /  0.00  /  0.00  /**68.61*/**31.65**
  17  /  Desi  /  0.00  /  0.00  /  0.00  /  0.00  /**13.95*/**0.90**/  0.00  /  0.00
  17  /  Desi  /**17.20*/**2.00**/  0.00  /  0.00  /  0.00  /  0.00  /  0.00  /  0.00

数字都是正确的。但是我需要它们每行 1 行。

我还缺少什么来完成这项工作？？

Answer 1

使用联合查询：

Select 
    PEmpID As ID,
    PEmplye As [Name],
    PPay,
    PTip,
    0 As BPay,
    0 As BTip
From
    YourQuery
Union All
Select 
    BEmpID As ID,
    BEmplye As [Name],
    0 As PPay,
    0 As PTip,
    BPay,
    BTip
From
    YourQuery

保存此查询并将其用作另一个查询中的源：

Select 
    ID, [Name], Sum(PPay), Sum(PTip), Sum(BPay), Sum(BTip)
From
    Query1
Group By
    ID, [Name]