Access Query help for unnormalized table

[cdborg]

Greetings folks,
I have an application with one table, and I don't have time to normalize it, but asked to work with what is in place to produce some queries. In the query I have a field called Whopays which has the following data:

Whopays
INAC
Myself
Band Council
Band Council
Myself
Other
INAC
Myself
Other
Myself
Myself
Myself
Myself; other
Other; disability
Other; medical services
Other; parents

My client want me to produce a count of the following:

Band Council
INAC
Myself
Other
(Anything with Other in it)

So the output would look like

WhoPays Count
Band Council 2
INAC 2
Myself 7
Other 5

Is this possible?

 

[PHV]

Something like this ?
SELECT Left(WhoPays, InStr(WhoPays & ";", ";")-1) As [Who pays], Count(*) As [Count]
FROM yourTable
GROUP BY Left(WhoPays, InStr(WhoPays & ";", ";")-1);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244