I am new to pivot tables, and not versed in SQL, looking for a straight forward method, preferably in a pivot table. I have data that shows different numbers for the same rooms in a specific building. I need to know the total (sum) of the maximum number of students in a particular room. See the attached file.
Main Table
PIVOT TABLE
Thank you for any help you can provide
Main Table
| BLDG_CODE | ROOM_CODE | MAX_ENROLLED | ACT_ENROLLED | AVAILABLE SEATS |
| AN3 | 213 | 100 | 25 | 75 |
| AN3 | 213 | 100 | 63 | 37 |
| AN3 | 319 | 20 | 5 | 15 |
| AN3 | 319 | 20 | 5 | 15 |
| AN3 | 319 | 20 | 7 | 13 |
| AN3 | 319 | 20 | 5 | 15 |
| ASA | 216 | 20 | 4 | 16 |
| ASA | 312 | 10 | 3 | 7 |
| ASA | 312 | 10 | 4 | 6 |
| ASA | 312 | 10 | 3 | 7 |
PIVOT TABLE
| Row Labels | Max of MAX_ENR | Max of ACT_ENR | Max of AVAIL |
| AN3 | <<NEED TO GET *THE SUM* OF THE MAX BELOW (120)>> *NOT THE MAX (100)* | ||
| 213 | 100 | 63 | 75 |
| 319 | 20 | 7 | 15 |
| ASA | |||
| 216 | 20 | 4 | 16 |
| 312 | 10 | 4 | 7 |
| Grand Total | 100 | 63 | 75 |
Thank you for any help you can provide
