Counting criteria

[morlan]

I am trying to count certain criteria in a column.
The table looks like thisio:

ID ------- NAME ------- DECISION

1 -------- John ------- APP
2 -------- John ------- APP
3 -------- John ------- APP
4 -------- John ------- DEC
5 -------- John ------- DEC
6 -------- John ------- APP
7 -------- John ------- APP

Query result should be:

Name --- APP --- DEC

John ----- 5 ------ 2

Any help appreciated!

 

[Mile-O]

A crosstab query:

TRANSFORM Count(MyTable.ID) AS CountOfID
SELECT Count(MyTable.ID) AS [TotalOfID], MyTable.Name
FROM MyTable
GROUP BY MyTable.Name
PIVOT MyTable.Decision;

 

[morlan]

A crosstab query:

TRANSFORM Count(MyTable.ID) AS CountOfID
SELECT Count(MyTable.ID) AS [TotalOfID], MyTable.Name
FROM MyTable
GROUP BY MyTable.Name
PIVOT MyTable.Decision;

Cheers mile, however, this is for SQL! I should'v posted in SQL forum not unless you have an answer

 

[Mile-O]

Er, it's SQL and works fine in Access.

 

[morlan]

Er, it's SQL and works fine in Access.

But I have a backend SQL server and AFAIK is doesn't support it..

 

[Mile-O]

I've never worked with SQL Server ( ) so I don't know the differences, nuances, etc

I would have thought that because the tables were linked into Access, the query grid would treat them as Access tables and the operation would be performable.

 

[MrTibbs]

Simple Crosstab queries work against an Oracle SQL back-end for me.
Some other options:
Do a select query against the SQL data and build the crosstab against the select query.
Export your data to Excel and use Excels Countif function.
Write a report and use count on the report footers.

 

[morlan]

Thanks for the help.

I finally just changed the table to:

ID ------- NAME ------- APP --- DEC

1 -------- John ------- 1 ------- 0
2 -------- John ------- 1 ------- 0
3 -------- John ------- 1 ------- 0
4 -------- John ------- 0 ------- 1
5 -------- John ------- 0 ------- 1
6 -------- John ------- 1 ------- 0
7 -------- John ------- 1 ------- 0

This makes it much easier to count.