Access SQL Summarize Data

Question

I can get the output that I want from the data my query provides in Excel, but it would be nice to be able to do it in Access directly.

I return a table like this:

Date          kName   NumKits   ActKits  dName  NumDoses   ActDoses
---------------------------------------------------------------------
11/03/2018    AA      5         1000     BB     12         3400
12/03/2018    CC      7         1100     AA     10         4120
etc..

Any name could appear in either column, maybe both columns at the same time. The criteria for the data as provided is a date range.

I want this information summarized, as follows:

     (Sum)     (Sum)        (Count)     (Sum)      (Sum)         (Count)
Name KitMade   KitActivity  KitSession  DosesMade  DoseActivity  DoseSession
----------------------------------------------------------------------------
AA   26         42015       3           40         35420         4
BB (etc...)

retaining the date filter.

My initial thought is to do the query twice, separating out Kits and Doses then combining them back together; but I'm sure that there must be a simpler way?

Using Access 2016, data has all been changed from actual information.

Answer 1

You seem to want something like this:

select name, sum(numkits) as numkits, sum(actkits) as actkits, sum(kitcnt) as numkits,
       sum(numdoses) as numdoses, sum(actdoses) as actdoses, sum(dosecnt) as dosecnt
from (select kname as name, 1 as kitcnt, numkits, actkits, 0 as dosecnt, 0 as numdoses, 0 as actdoses
      from t
      union all
      select kname, 0 as kitcnt, 0 as numkits, 0 as actkits, 1 as dosecnt, numdoses, actdoses
      from t
     ) as t
group by name;

I think some older versions of MS Access do not support union all in the FROM clause. If that is a problem, you can get around it by creating a view.