ms access - SQL - Count and sorted by one column

Question

Need help on getting the count out for a Table in Access and grouping them by that particular type.

Example, I have this table

I need it to output in a query into this:

Answer 1

You need conditional aggregation as follows:

select itemname, itemtype,
       count(case when building = 'A' then 1 end) as totalA,
       count(case when building = 'B' then 1 end) as totalB,
       count(case when building = 'C' then 1 end) as totalC
  from your_table
group by itemname, itemtype
order by itemname, itemtype, totalA desc, totalB desc, totalC desc