Sorting query results

[lroy1978]

Hi

I have a problem sorting some query data and I wonder if someone could help point me in the right direction.

I have a query with two columns, in the first column is a number (either 6 or 7) and the second is the name of a business. Eg

Column 1 Column 2
6 Pizzas & Co
6 Pizzas & Co
7 Pizzas & Co
6 IPM Ltd
7 IPM Ltd
6 Computer Sales Ltd
6 Computer Sales Ltd
6 Computer Sales Ltd
7 Computer Sales Ltd
7 Computer Sales Ltd
7 Computer Sales Ltd
7 Computer Sales Ltd
7 Computer Sales Ltd

And I want to produce some another query so that the output which looks like:

Column 1 Column 2 (6's) Column 3 (7's)
Pizzas & Co 2 1
IPM Ltd 1 1
Computer Sales Ltd 3 5

So for each company name, I am essentially doing a frequency count for all the 6's and 7's.

I think this must be relatively easy to do, but I am getting it wrapped around my head!

Many Thanks,
Lee

 

[Kelemit]

{code}
SELECT test.Company, Count(test.Number) AS CountOfNumber
FROM test
WHERE (((test.Number)=7))
GROUP BY test.Company;
{/code}


This will return the number of "7s" for each company. To get the 6's, change the WHERE portion to =6.

This is of course 2 queries, but it does get you what you want.

Kelemit

 

[lroy1978]

Thanks Kelemit!