Counts

[Matt Dawson]

I have designed a query where i want to sum how many quotes have been either
sent, not sent or rejected by country.
I have used this as my SQL but it brings up the number of overall records
for each country not the number of those sent or rejected etc.

This is my SQL:

SELECT Acceptance.Country, Count(Acceptance.[Quote Sent]) AS [CountOfQuote
Sent], Count(Acceptance.[Quote Rejected]) AS [CountOfQuote Rejected],
Count(Acceptance.[Quote Not Sent]) AS [CountOfQuote Not Sent]
FROM Acceptance
GROUP BY Acceptance.Country
HAVING (((Count(Acceptance.[Quote Sent]))=Yes)) OR
(((Count(Acceptance.[Quote Rejected]))=Yes)) OR (((Count(Acceptance.[Quote
Not Sent]))=Yes));


What is wrong with this?
Many Thanks,
Matt

 

[Duane Hookom]

Count will count all valued fields whether they are true or false.

If your fields are Yes/No, then try this SQL:

SELECT Acceptance.Country,
Sum(Abs([Quote Sent])) AS [CountOfQuote Sent],
Sum(Abs([Quote Rejected])) AS [CountOfQuote Rejected],
Sum(Abs([Quote Not Sent])) AS [CountOfQuote Not Sent]
FROM Acceptance
GROUP BY Acceptance.Country;


--
Duane Hookom
MS Access MVP

I have designed a query where i want to sum how many quotes have been
either
sent, not sent or rejected by country.
I have used this as my SQL but it brings up the number of overall records
for each country not the number of those sent or rejected etc.

This is my SQL:

SELECT Acceptance.Country, Count(Acceptance.[Quote Sent]) AS [CountOfQuote
Sent], Count(Acceptance.[Quote Rejected]) AS [CountOfQuote Rejected],
Count(Acceptance.[Quote Not Sent]) AS [CountOfQuote Not Sent]
FROM Acceptance
GROUP BY Acceptance.Country
HAVING (((Count(Acceptance.[Quote Sent]))=Yes)) OR
(((Count(Acceptance.[Quote Rejected]))=Yes)) OR (((Count(Acceptance.[Quote
Not Sent]))=Yes));


What is wrong with this?
Many Thanks,
Matt

 

[geebee]

hi,

Looks like you just need to use more GROUP BY clauses, and if desired, take
out the Group by COUNTRY. You need to GROUP BY [CountOfQuote Rejected], and
so forth.

Hope this helps,
geebee

I have designed a query where i want to sum how many quotes have been either
sent, not sent or rejected by country.
I have used this as my SQL but it brings up the number of overall records
for each country not the number of those sent or rejected etc.

This is my SQL:

SELECT Acceptance.Country, Count(Acceptance.[Quote Sent]) AS [CountOfQuote
Sent], Count(Acceptance.[Quote Rejected]) AS [CountOfQuote Rejected],
Count(Acceptance.[Quote Not Sent]) AS [CountOfQuote Not Sent]
FROM Acceptance
GROUP BY Acceptance.Country
HAVING (((Count(Acceptance.[Quote Sent]))=Yes)) OR
(((Count(Acceptance.[Quote Rejected]))=Yes)) OR (((Count(Acceptance.[Quote
Not Sent]))=Yes));


What is wrong with this?
Many Thanks,
Matt