Thread: Counts

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


"Matt Dawson" <MattDawson@discussions.microsoft.com> wrote in message
news:8EF420AF-E923-408F-8DE2-CBB44BC2F21F@microsoft.com...
>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

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


"Matt Dawson" <MattDawson@discussions.microsoft.com> wrote in message
news:8EF420AF-E923-408F-8DE2-CBB44BC2F21F@microsoft.com...
>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

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

"Matt Dawson" wrote:

> 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