Count Query Help

D

Daveo

Hi there,

I have a table with the following fields:

id :Autonumber
name :Text
1-1 :Y/N
1-2 :Y/N
1-3 :Y/N
1-4 :Y/N
1-5 :Y/N
2-1 :Y/N
2-2 :Y/N
2-3 :Y/N
2-4 :Y/N
2-5 :Y/N

I need to be able to count how many times 1-1 is True, then 1-2 then
1-3 and so on. Is there another way of doing this apart from making a
count query for each and then combining them?

Many thanks - David
 
J

John Spencer

Is that your table structure?

If so, you can write a query that uses the following expression for each
field
Field: Count1_1: Abs(Sum([1-1]))

In an SQL statement that would look like

SELECT Abs(Sum([1-1])) as Count1_1
, Abs(Sum([1-2])) as Count1_2
, Abs(Sum([1-3])) as Count1_3
, ...
FROM YourTable
 
D

Daveo

Hi John,

Thankyou very much for the response. That works great.

However, how do I add criteria to the query e.g. name = "Dave" ? When I
try, I get an error saying:

"You tried to execute a query that does not include the specified
expression 'name' as part of an aggregate function"

Thanks again - David


John said:
Is that your table structure?

If so, you can write a query that uses the following expression for each
field
Field: Count1_1: Abs(Sum([1-1]))

In an SQL statement that would look like

SELECT Abs(Sum([1-1])) as Count1_1
, Abs(Sum([1-2])) as Count1_2
, Abs(Sum([1-3])) as Count1_3
, ...
FROM YourTable


Daveo said:
Hi there,

I have a table with the following fields:

id :Autonumber
name :Text
1-1 :Y/N
1-2 :Y/N
1-3 :Y/N
1-4 :Y/N
1-5 :Y/N
2-1 :Y/N
2-2 :Y/N
2-3 :Y/N
2-4 :Y/N
2-5 :Y/N

I need to be able to count how many times 1-1 is True, then 1-2 then
1-3 and so on. Is there another way of doing this apart from making a
count query for each and then combining them?

Many thanks - David
Click to expand...
Click to expand...
 
J

John Spencer

To just limit the results and not show the Name
SELECT Abs(Sum([1-1])) as Count1_1
, Abs(Sum([1-2])) as Count1_2
, Abs(Sum([1-3])) as Count1_3
, ...
FROM YourTable
WHERE [Name] = "Dave"

OR
To show the name and the results
SELECT [Name], Abs(Sum([1-1])) as Count1_1
, Abs(Sum([1-2])) as Count1_2
, Abs(Sum([1-3])) as Count1_3
, ...
FROM YourTable
GROUP BY [Name]
HAVING [Name] = "Dave"

OR to show the name and the results and be a bit faster
SELECT [Name], Abs(Sum([1-1])) as Count1_1
, Abs(Sum([1-2])) as Count1_2
, Abs(Sum([1-3])) as Count1_3
, ...
FROM YourTable
WHERE [Name] = "Dave"
GROUP BY [Name]



Daveo said:
Hi John,

Thankyou very much for the response. That works great.

However, how do I add criteria to the query e.g. name = "Dave" ? When I
try, I get an error saying:

"You tried to execute a query that does not include the specified
expression 'name' as part of an aggregate function"

Thanks again - David


John said:
Is that your table structure?

If so, you can write a query that uses the following expression for each
field
Field: Count1_1: Abs(Sum([1-1]))

In an SQL statement that would look like

SELECT Abs(Sum([1-1])) as Count1_1
, Abs(Sum([1-2])) as Count1_2
, Abs(Sum([1-3])) as Count1_3
, ...
FROM YourTable


Daveo said:
Hi there,

I have a table with the following fields:

id :Autonumber
name :Text
1-1 :Y/N
1-2 :Y/N
1-3 :Y/N
1-4 :Y/N
1-5 :Y/N
2-1 :Y/N
2-2 :Y/N
2-3 :Y/N
2-4 :Y/N
2-5 :Y/N

I need to be able to count how many times 1-1 is True, then 1-2 then
1-3 and so on. Is there another way of doing this apart from making a
count query for each and then combining them?

Many thanks - David
Click to expand...
Click to expand...
Click to expand...
 
D

Daveo

That's brilliant John - Thanks Again!


John said:
To just limit the results and not show the Name
SELECT Abs(Sum([1-1])) as Count1_1
, Abs(Sum([1-2])) as Count1_2
, Abs(Sum([1-3])) as Count1_3
, ...
FROM YourTable
WHERE [Name] = "Dave"

OR
To show the name and the results
SELECT [Name], Abs(Sum([1-1])) as Count1_1
, Abs(Sum([1-2])) as Count1_2
, Abs(Sum([1-3])) as Count1_3
, ...
FROM YourTable
GROUP BY [Name]
HAVING [Name] = "Dave"

OR to show the name and the results and be a bit faster
SELECT [Name], Abs(Sum([1-1])) as Count1_1
, Abs(Sum([1-2])) as Count1_2
, Abs(Sum([1-3])) as Count1_3
, ...
FROM YourTable
WHERE [Name] = "Dave"
GROUP BY [Name]



Daveo said:
Hi John,

Thankyou very much for the response. That works great.

However, how do I add criteria to the query e.g. name = "Dave" ? When I
try, I get an error saying:

"You tried to execute a query that does not include the specified
expression 'name' as part of an aggregate function"

Thanks again - David


John said:
Is that your table structure?

If so, you can write a query that uses the following expression for each
field
Field: Count1_1: Abs(Sum([1-1]))

In an SQL statement that would look like

SELECT Abs(Sum([1-1])) as Count1_1
, Abs(Sum([1-2])) as Count1_2
, Abs(Sum([1-3])) as Count1_3
, ...
FROM YourTable


Hi there,

I have a table with the following fields:

id :Autonumber
name :Text
1-1 :Y/N
1-2 :Y/N
1-3 :Y/N
1-4 :Y/N
1-5 :Y/N
2-1 :Y/N
2-2 :Y/N
2-3 :Y/N
2-4 :Y/N
2-5 :Y/N

I need to be able to count how many times 1-1 is True, then 1-2 then
1-3 and so on. Is there another way of doing this apart from making a
count query for each and then combining them?

Many thanks - David
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Scrabble Value calculation for Welsh words 0
Conditional Count Column in Query 1
count three values in multiple fields 2
"Stacking" Identical Tables 6
formula need a fix. HELP! 1
count & sum 3
League Table 1
DCount function for filtered data in table 7

Top