I am trying to count the number of fields containing data in Access query

0
votes

My Table : Table1

 ID  Field1 Field2  Field4
  1     4       2   
  2     5       
  3     6       7

I want the Field4 to show the count of Field1 and Field2. In the SQL view I tried to Count(*) From Table1. But it is showing syntax error in FROM clause.

The total program I have typed is

Select[Table1].Field1,[Table1].Field2,Count(*) From Table1 AS Field4
From[Table1];

Any suggestion will be very helpful

I want to get the count of the data in Field1 and Field2. In row 1 Field1 and Field2 both has data in it, so Field4 will show 2,in row 2 as only Field1 has data so Field4 will show 1.It is like the count function in excel. The Field4 will show like this. The answer of Raging Bull is Ok, but I want a little smaller program.

 Field4
  2
  1
  2
2
sqlms-accessms-access-2010
I'm not sure what you mean by "count of field1 and field2"... what would be the desired result in field4 for your sample data? – Joachim Isaksson
Are you perhaps asking for select [field1] + [field2] as [field4]? – crthompson
Please edit your question with your desired results. – Gordon Linoff

2 Answers

0
votes

Your query should be something like:

UPDATE `Table1` SET Field4 = Field1+Field1
0
votes

I think this is what you are after (gives the number of non-null values in the same record):

SELECT ID,Field1,Field2,
       (CASE WHEN Field1 IS NOT NULL THEN 1 ELSE 0 END)+
       (CASE WHEN Field2 IS NOT NULL THEN 1 ELSE 0 END) as Field4
FROM TableName

Result:

ID  FIELD1  FIELD2  FIELD4
1   4       2       2
2   5       (null)  1
3   6       7       2