Count of times "A" appears in query

[Elmobram22]

Hi,

I have a query that gives a value in a column either "A","V","L","H","P","S" or blank.

I want to be able to count the amount of occurunces of each letter and hold that number in a column. Is it easy to do?

Cheers,

Paul

 

[pr2-eugin]

You mean a GROUP BY Query?

SELECT theColumnName, Count(thecolumnName) As NoOfOcc
FROM theQuery
GROUP BY theColumnName;

 

[Elmobram22]

I kinda want a query like this...

Id Name A V L H P S
---------------------------------------------------------------------------------------------------------
01 PAUL 2 0 0 1 0 1
02 JOHN 3 1 1 0 0 0

The ID would be from TblStaff and the occurances are from a query QryDeductionsandSleep Ins

 

[pr2-eugin]

How about a CrossTab Query then?

 

[namliam]

Yes crosstab, except in this case that wouldnt show P

perhaps....

Select ID, sum(IIF(Column= "A",1,0)) as A
, sum(IIF(Column= "B",1,0)) as B
,...
group by ID

 

[Elmobram22]

Crosstab works beautifully thanks a lot!