counting and group by date

[ferrisj]

I have two fields. One is a date field the other is a text field. I want to count all of the YES values in the one field and group them by date:

I have written this query,
SELECT Contact.HVDate, Count(Contact.[Recieve Ballotx]) AS [CountOfRecieve Ballotx]
FROM Contact
GROUP BY Contact.HVDate
HAVING (((Contact.[Recieve Ballotx])="YES"));

I keep getting an aggregate function error. Can you help me with this

 

[bernie10]

Try this:

SELECT Contact.HVDate, Count(Contact.[Recieve Ballotx]) AS [CountOfRecieve Ballotx]
FROM Contact
GROUP BY Contact.HVDate, Contact.[Recieve Ballotx]
HAVING (((Contact.[Recieve Ballotx])="Yes"));

Hope this helps,
Collen

 

[dhookom]

Try changing the Having to Where:
[tt][blue]
SELECT Contact.HVDate, Count(Contact.[Recieve Ballotx]) AS [CountOfRecieve Ballotx]
FROM Contact
WHERE [Recieve Ballotx]="YES"
GROUP BY Contact.HVDate;
[/blue][/tt]


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PHV]

And if you want a little more stats info:
SELECT HVDate, Sum(IIf([Recieve Ballotx]="YES",1,0)) AS [CountOfRecieve Ballotx=YES], Sum(IIf([Recieve Ballotx]="YES",0,1)) AS [CountOfRecieve Ballotx NOT=YES]
FROM Contact
GROUP BY HVDate;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244