count Iif is not null

[Mary]

Hello All .
I really need some help.
I have two formulas in excel that I am trying to mirror in my access
database.
Here are the Excel formula:

=IF(COUNTA(A11)=0,"No Data",SUM(E1:H1)) This formula check to see if
column A1 to D1 =0 and return “No Data†IF they are not 0 it sum Row E1 to H1.

I n my access database Column A is English, B is French, C is Art and D is
Math. I would like an access formula that will evaluate column English,
French, Art and Math. If they are null cross then “No data†else
SUM([English]+[French]+[Art]+[Math])

The second formula is
IF(COUNTA(A11)<4,"Only "&COUNTA(A11)&" Stat(s) Available, Please Review" )

In my access database I would like to evaluate the count of Is Not Null
column for the 4 columns, English, French, Art and Math . if the total count
of Is not Null column is less than 4 the “Only “ & Count the total of is
not null column for [English], [French]+[Art] and [Math]

Basically, I if the count of the is not null column(English, French, Art and
Math) is less than 4 then show the count of columns with data.

Thanks in advance.

 

[Duane Hookom]

Since you are using a relational database, you should not have fields with
names of subjects. Each score should create a new record. You can then easily
query your table with a totals query.

I would first normalize the table and if that isn't possible, create a union
query that does the normalization:

SELECT IDField, [English] As Score, "English" as Subject
FROM tblSpreadsheetLike
UNION ALL
SELECT IDField, [French], "French"
FROM tblSpreadsheetLike
UNION ALL
SELECT IDField, [Art], "Art"
FROM tblSpreadsheetLike
UNION ALL
SELECT IDField, [Math], "Math"
FROM tblSpreadsheetLike;

You can then query the union query to count the number of non-nulls by
IDField.