yes/no check boxes on a query

[Nouaman]

hi ya'll,
my table fields are yes/no check boxes and i have a query for this table to calculate how many 'yes' are checked and how many are not.
this is what i did:
on the total row on the design grid of the query i put : COUNT
on the criteria : Yes
the results comes out like this: all the fields have number 1 on them whether they were checked or not!!!
any help please?

[skhanal]

Are you using Access or SQL Server? It is more intuitive write SQL Code

select count(*)
from yourtable
where columnval='Y'

[Nouaman]

i will be using either servers and this is how my SQL code looks:



SELECT Referrals.[Referral Date], Customer.[Customer Name], Count(Referrals.[Defaulted Student Loan]) AS [CountOfDefaulted Student Loan], Count(Referrals.[Legal Service in State]) AS [CountOfLegal Service in State], Count(Referrals.[Legal Service Out of State]) AS [CountOfLegal Service Out of State], Count(Referrals.[Mortgage/LPP Service]) AS [CountOfMortgage/LPP Service], Count(Referrals.[Real Estate Services]) AS [CountOfReal Estate Services], Count(Referrals.[Seniors/Disable Judgment Proof]) AS [CountOfSeniors/Disable Judgment Proof], Count(Referrals.[Vehicle Purchase]) AS [CountOfVehicle Purchase], Customer.[Referred By]
FROM Customer INNER JOIN Referrals ON Customer.[Customer ID] = Referrals.[Customer ID]
WHERE (((Referrals.[Referral Date]) Between [Beginning Date] And [Ending Date]))
GROUP BY Referrals.[Referral Date], Customer.[Customer Name], Customer.[Referred By]
HAVING (((Count(Referrals.[Defaulted Student Loan]))=Yes) AND ((Count(Referrals.[Legal Service in State]))=Yes) AND ((Count(Referrals.[Legal Service Out of State]))=Yes) AND ((Count(Referrals.[Mortgage/LPP Service]))=Yes) AND ((Count(Referrals.[Real Estate Services]))=Yes) AND ((Count(Referrals.[Seniors/Disable Judgment Proof]))=Yes) AND ((Count(Referrals.[Vehicle Purchase]))=Yes));

[skhanal]

For SQL Server, try this

SELECT Referrals.[Referral Date], Customer.[Customer Name],
sum (case (Referrals.[Defaulted Student Loan]) when 'Yes' Then 1 else 0 end) AS [CountOfDefaulted Student Loan],
sum (case (Referrals.[Legal Service in State]) when 'Yes' Then 1 else 0 end) AS [CountOfLegal Service in State],
sum (case (Referrals.[Legal Service Out of State]) when 'Yes' Then 1 else 0 end) AS [CountOfLegal Service Out of State],
sum (case (Referrals.[Mortgage/LPP Service]) when 'Yes' Then 1 else 0 end) AS [CountOfMortgage/LPP Service],
sum (case (Referrals.[Real Estate Services]) when 'Yes' Then 1 else 0 end) AS [CountOfReal Estate Services],
sum (case (Referrals.[Seniors/Disable Judgment Proof]) when 'Yes' Then 1 else 0 end) AS [CountOfSeniors/Disable Judgment Proof],
sum (case (Referrals.[Vehicle Purchase]) when 'Yes' Then 1 else 0 end) AS [CountOfVehicle Purchase], Customer.[Referred By]
FROM Customer INNER JOIN Referrals ON Customer.[Customer ID] = Referrals.[Customer ID]
WHERE (((Referrals.[Referral Date]) Between [Beginning Date] And [Ending Date]))
GROUP BY Referrals.[Referral Date], Customer.[Customer Name], Customer.[Referred By]

[Nouaman]

Thank you so much!!!

[skhanal]

Wow, you looked at it after 7 months

[Nouaman]

hi guys,
first of all i would like to thank SKHANAL and all other members of this forum for helping me.

here is the question in my assignment:
List the most expensive book purchased by customer 1017?
i have these tables: books, customers, orders, orderitems.
my answer was:
SELECT MAX(retail) " Most Expensive Book"
FROM orders NATURAL JOIN orderitems NATURAL JOIN books
where customer#=1017
;
I get the right answer but i can't get both the book title and its price at the same time on the output. i only get this :

Most Expensive Book
--------------------
89.95

So, is there any other way to write the SQL code and get something like this:
Title
------------------------
Handcranked Computeres

Most Expensive Book
-------------------
89.95