count

[at51178]

hey guys


need to find out how to create a count on a column

I have done it before but this time it is not working


I created a query and in one of the columns I have yes and no answers I would like to first count all the columns but for some reason I get a blank text box as if there was nothing to count

and for some reason I do get it to work I would like to also have access count only the yes answers

PLease let me know what I am doing wrong this is what I have been typing



count=(field name)

thanks in advance

 

[ErnNJ]

Go into SQL view on your query and try this to get the count of rows that have a value:

SELECT Count(*) AS [Field Name]
FROM Table_Name
WHERE [Table_Name].[Field Name]is not null;

Then to get specific values try:

SELECT Count(*) AS [Field Name]
FROM Table_Name
WHERE [Table_Name].[Field Name] like "Value_to_search_for";

Hope this was what you needed.

 

[at51178]

I tried doing it the way you told me through sql but I don't too much about sql as of yet

is there any way to do it through the expression builder

I get an error message when I do it though sql
error message is "characters found after sql statement"

 

[ErnNJ]

"characters found after sql statement" means that you entered something after the ; which is to be the last character of the SQL statement. But to to it with the expression builder...

in the first column, instead of selecting a field from the table list, enter
Count_Of_Fiels: Count(*)
(Count_Of_Fields is the label that will appear at the top of the result query in that column.)

in the next column select the field you want the count of and in the criteria bar enter 'is not null' for the count of all or like "the value you want to find" for the count of a specific value

Be sure and uncheck the show box in the column with the field name or you will get an aggregate function error.
Hope that works for you.

 

[scriverb]

SELECT Count(FieldName) AS Count
FROM tblYourTable
WHERE (((FieldName)=Yes));

This query SQL will count the yes answers in your FieldName.

"I would like to first count all the columns. . ."
I don't understand what you are asking for when you say your want to count all of the columns. If they each have different values what are you counting. The above can be tailored to count a different column for a different value and you can run them seperately but counting them at the same time will give you bizarre results.

Bob Scriver