0

I really need your help and it seems from an SQL standpoint that I can't seem to quite think like an SQL programmer with trying to obtain the following result set.

Consider the following table below (which is a snapshot from my MS Access MDB file:

+-----------------+-----------------+-----------------+-----------------+
|  Date Received  |  Request Type   |      Branch     |     Division    |
+-----------------+-----------------+-----------------+-----------------+
|   2016-05-10    | Status Report   |      Alpha      |       AAA       |
|   2016-05-11    | Business Case   |      Bravo      |       BB        |
|   2016-05-12    | Business Plan   |      Charlie    |       C         |
|   2016-05-13    | Project Charter |      Alpha      |       A         |
|   2016-05-14    | Status Report   |      Alpha      |       AAA       |
|   2016-05-15    | Business Plan   |      Charlie    |       CC        |
|   2016-05-16    | Financial Report|      Alpha      |       AAAA      |
|   2016-05-17    | Financial Report|      Alpha      |       AA        |
|   2016-05-18    | Status Report   |      Bravo      |       BBB       |
|   2016-05-19    | Financial Report|      Alpha      |       AAA       |
|   2016-05-20    | Financial Report|      Bravo      |       B         |
+-----------------+-----------------+-----------------+-----------------+

I need your help to basically capture the metrics (occurrence/count of each request type and then sort it by Division), resulting in the new result set below:

+-----------------+-----------------+-----------------+-----------------+
|    Division     |  Status Report  |  Business Case  |  Business Plan  |
+-----------------+-----------------+-----------------+-----------------+
|   A             |        1        |         0       |        0        |
|   AA            |        1        |         0       |        0        |
|   AAA           |        1        |         0       |        0        |
|   B             |        0        |         1       |        0        |
|   BB            |        0        |         1       |        0        |
|   BBB           |        0        |         1       |        0        |
|   C             |        1        |         0       |        1        |
|   CC            |        1        |         0       |        1        |
|   CCC           |        1        |         0       |        1        |
+-----------------+-----------------+-----------------+-----------------+
BobbyJones
  • 1,331
  • 1
  • 26
  • 44
  • 3
    Check out the Crosstab Query Wizard. Find it from the ribbon, Create -> Query Wizard. – HansUp Jun 14 '16 at 14:26
  • `SELECT branch, division, COUNT(CASE WHEN [request type] = 'Status Report' THEN 1 END) AS [Status Report], COUNT(CASE WHEN [request type] = 'Business Case' THEN 1 END) AS [Business Case], COUNT(CASE WHEN [request type] = 'Business Plan' THEN 1 END) AS [Business Plan] FROM yourTable GROUP BY branch, division ORDER BY branch, division` – MatBailie Jun 15 '16 at 13:26

1 Answers1

0

Edit: count the request types for a branch:

DECLARE @count int;
DECLARE @count0 int;
DECLARE @count1 int;


set @count = (select Count(Request Type) from TABLENAME where Request Type = 'Status Report', Division = 'AAA')

set @count0 = (select Count(Request Type) from TABLENAME where Request Type = 'Business Case', Division = 'AAA')

set @count1 = (select Count(Request Type) from TABLENAME where Request Type = 'Business Plan', Division = 'AAA')

Edit: use this to order

order by COLUMNXXX ASC
HumbleWebDev
  • 555
  • 4
  • 20
  • Thanks! so much, this seems like a really long tree to bark up and climb. I didn't see much reference for it on the web, hence, I am taking it up with the pros, I appreciate your response. Cheers – BobbyJones Jun 14 '16 at 14:54
  • I've been poking at it, and it seems you'll need to loop through every row in column Division and select the counts where Division. This is very, very slow.... from what I can see..... Good luck man..... This seems to almost be akward table design imo, as there probably should be multiple tables with foriegn keys, further abstraction would improve the viability of this procedure.... – HumbleWebDev Jun 14 '16 at 15:28
  • You should refactor the tables to be structured more like this: Division Table(Primary Division) <- Branch Table(Pirmary Id, branch, foriegn to Division) <- Request Table (Primary Id, request, foreign to branch, DATE RECEIVED) Edit: If you need clarification, ie, an ERD, let me know Edit2: also, this way you'd use select starting from the request table and linking backwards. – HumbleWebDev Jun 14 '16 at 15:32