Count "like" values from field

[stretch66]

Hi,

Brand new to this! And have been trying all morning!

Have a field in a table called Job_Status which can have three values:
Permanent = 1
Temporary = 2
Probationary = 3

All I wanted to do was create a query that showd me the totals of all employees plus the number of perm,temp and probationary staff.

Have tried select query and can only get it to display one value at a time and got completely thrown with the crosstab queries.

Can anyone help walk me through the process?

 

[Rich]

Use a totals query

 

[stretch66]

Use a totals query

Yep tried that......but my knowledge of Access is even less basic and I cannot seem to grasp building the query properly.

Here is the SQL that doesn't work:

SELECT tblEmployees.Job_Status, Sum(tblEmployees.Job_Status) AS SumOfJob_Status, Sum(tblEmployees.Job_Status) AS SumOfJob_Status1, Sum(tblEmployees.Job_Status) AS SumOfJob_Status2
FROM tblEmployees
GROUP BY tblEmployees.Job_Status
HAVING (((Sum(tblEmployees.Job_Status)) Like "1" And (Sum(tblEmployees.Job_Status)) Like "2" And (Sum(tblEmployees.Job_Status)) Like "3"));

 

[stretch66]

Searched again and found a wsite with screenshots and explanation which was quite useful:

http://www.meadinkent.co.uk/aquerychoose.htm

Didn't realise the count actually seperated the different values and gave a total of each. I was trying to seperate them myself.

Well it's taken me since 7am this morning but it's a start.

 

[FoFa]

SELECT tblEmployees.Job_Status, count(tblEmployees.Job_Status)
FROM tblEmployees
GROUP BY tblEmployees.Job_Status

 

[stretch66]

Thanks FoFa managed to get the answer from website posted previously and which is nearly the same.

 

[Pat Hartman]

When running count queries, I always use * instead of the field name. The difference is that when using *, your total will sum to the number of rows in the domain. When using the field name, any row with a null (and possibly ZLS) field is ignored.

SELECT tblEmployees.Job_Status, count(*) As StatusCount
FROM tblEmployees
GROUP BY tblEmployees.Job_Status;

 

[stretch66]

Point taken.........changed it !

Thankyou.