将以下SQL保存为新查询， qryBaseCounts :

Save the following SQL as a new query, qryBaseCounts:

SELECT
    sub.color,
    sub.light_10,
    sub.dark_20,
    light_10+dark_20 AS light_plus_dark
FROM [
        SELECT
            color,
            Sum(IIf(lightness=10,1,0)) AS light_10,
            Sum(IIf(darkness=20,1,0)) AS dark_20
        FROM colortable
        WHERE
            cid In (5,8)
            AND (lightness=10
            OR darkness=20)
        GROUP BY color
    ]. AS sub;

然后，您可以在UNION查询中使用qryBaseCounts:

Then you can use qryBaseCounts in a UNION query:

SELECT
    q1.color,
    q1.light_10 AS lightness,
    q1.dark_20 AS darkness,
    q1.light_plus_dark AS [Total]
FROM qryBaseCounts AS q1
UNION ALL
SELECT
    "Total",
    Sum(q2.light_10)
    Sum(q2.dark_20)
    Sum(q2.light_plus_dark)
FROM qryBaseCounts AS q2;

这是第二次查询的Access 2007输出，使用您的 colortable 示例数据:

This is the Access 2007 output from that second query using your sample data for colortable:

color lightness darkness Total
green         4        1     5
red           2        2     4
Total         6        3     9