Count Function

[Mickster]

Hi there,

I have a field called Unit in my table. That field references another table which has about 5 entries. The user can select one on the form.

What I want to do is count how many times each one has been selected so that i can create a chart. The unit names are: North, West, East, South and Main.

Any help would be appreciated.

Thanks

 

[Studentos]

SELECT Unit, Count(Unit) FROM your_table GROUP BY Unit

 

[Mickster]

Thanks Studento
However, right now they are being displayed in multiple rows. I want them in colums

For example this is how it looks now

North 12
South 15
East 14


I want it like this

North South East
12 15 14

Any thoughts?

 

[Studentos]

TRANSFORM First(your_field)
SELECT your_field
FROM your_table
GROUP BY your_field
PIVOT Unit

 

[Mickster]

Thank you so much that worked.

however where in that query would i put the Count?

Thanks

 

[Studentos]

oops =) try this

TRANSFORM Count(Unit)
SELECT "result is..."
FROM Table2
GROUP BY "result is..."
PIVOT Unit

 

[Mickster]

Excellent

Thanks so much that worked

Your help is much appreciated.