Simple SQL statement

Question

I have a table that has 2 columns: Location and job title in MS Access

I want to create a query that can find the sum of job titles in each location.

For example:

Location                   Job Title 
----------------------------------------------
Alabama                   Engineer
Orlando                   Teacher 
Alabama                   Teacher 
Los Angeles               Engineer 

The query result to be:

Location     Alabama      Orlando      Los Angeles 
Job Title       
-----------------------------------------------------------------------------
Engineer        1            0              1
Teacher         1            1              0

Thanks and appreciated

Answer 1

using this instruction

select location, job_title, count(job_title)
from table
group by location, job_title

the result will be

Alabama Engineer 1
Alabama Teacher 1
Orlando Teacher 1
Los Angeles Engineer 1

After you got this (columns name are location, job_title, sum, table name is table1) you can use

TRANSFORM FIRST(sum)
SELECT job_title
FROM TABLE1
GROUP BY job_title
PIVOT location

and you will get what you wanted

job_title   Alabama Los Angeles Orlando
Engineer    1       1   
Teacher     1                   1