Total table from existing table from

[HectorH]

I want to make a table that has just the totals from another table.

Example source table:
equip color size
x red S
y green M
z red L
f red L

Output table
[Equipment total] [red] [green] S M L


4 3 1 1 1 2

Is there a simple way to do this? They only way I know how is to make separate queries and combine them into one later

[rpeare]

Yes there is, but it's clumsy and likely will not fit your need for large datasets.


For each field you wanted a total for on this combined query you'd have to have something like

TotalRed: iif(color = 'red', 1, 0)
TotalGreen: iif(color = 'green', 1, 0)

then add a summation line (click the sigma button in your query design) and sum the totalred, totalgreen and so on fields.

[HectorH]

Perfect. Exatly what I needed.