MS Access SQL Query - Probably simple, but I'm rusty

[swerenfl]

Hello all

This is my first post, so please be kind. I work with MS products, java, MySQL, PHP, etc all the time, but for the life of me I cannot figure out this semi-basic query. Maybe because it's Monday, maybe because I haven't had enough coffee, or maybe its because I haven't touched SQL in over 6 months. Whatever the case, I need your help. Here is the setup...

I have a form that people can use to send emails to our clients. The user has an option to select between two canned messages (Message "1" or Message "2"). Behind the scenes, every time they hit the "SEND" button it logs into a "RECORDS" table (so long as it makes it through the error handlers).

Let's pretend the RECORDS table with 2 columns:

    CUST_ID, EMAIL_NUM
    0000, 1
    0000, 2
    0000, 1
    0000, 1
    0001, 2
    0002, 1
    0002, 1
    0003, 2
    0003, 2
    0003, 2

I need a query that counts the ones and two for each CUST_ID. So the result set should look something like this:

    CUST_ID, EMAIL_1_COUNT, EMAIL_2_COUNT
    0000, 3, 1
    0001, 0, 1
    0002, 2, 0
    0003, 0, 3

I've used count, group bys, havings, while, union, nested selects, but like I said, I'm probably over complicating something that is relatively easy.

Please help. Thank you!

[swerenfl]

Answered my own question

select
CUST_ID,
sum(iif(EMAIL_NUM = 1, 1, 0)) as EMAIL_1_COUNT,
sum(iif(EMAIL_NUM = 2, 1, 0)) as EMAIL_2_COUNT
from
RECORDS
group by
CUST_ID

[RuralGuy]

Thanks for posting back with your success and solution.