SQL syntax

[DrewDog_21]

I need to be able to return with SQL syntax the number of fields within a record that are not null. I can do this with coding with ADO or DAO, but I am using the data environment to create a data report. Thus I need to return the value with SQL. Can someone show me the syntax for this?

Thanks

Andrew

[DrewDog_21]

That's not exactly what I meant. That example returns the total number of recordsin a table where one field is not null. A little clearer explanation:

I have nine fields in the table. Eight are for grades and observations (the client is a school) which are defined in another table as either passing or not passing. The ninth is the number of observations that have a value of 'passing' in the other table. In the data report I need to show the percentage of passing grades received for each student.

For example, student Joe Smith receives 5 grades, of which 3 are passing. Joe Smith's percentage will be the total number of passing grades divided by the total number of fields that are not null. The number of grades will vary for each record, so I need to be able to return, for each student, how many of the 8 grade fields have an entry.

Thanks to all of you guys for the help you have given me in the past. Any help here will be, as always, greatly appreciated!

Chao

Andrew

[JHausmann]

You might want to create a temporary table that has the studentid (or whatever the unique key is) and a grade_count field (small integer).

Then you make 8 passes thru to create/update data.

Step 1 - Insert into tempTBL (select studentid from table)
Step2 - Update tempTBL set grade_count=0
Step3 thru 10 - Update tempTbl set grade_count=grade_count + 1 where studentid in (select keyvalue from table where grade# is not null)

Step 11 - join tempTBL to your existing table for your reports.

[andymac]

SELECT IF(isnull(FLD1), 0, 1)+IF(isnull(FLD2), 0, 1)+IF(isnull(FLDx), 0, 1) as MyCount FROM TABLE

hope this helps

cheers

Andy

P.S. You might need to change the IF(isnull(FIELD), 0, 1) syntax depending on what flavour of SQL you are using

[DrewDog_21]

I'm using Jet 4.0 for Access with DAO 3.6 That particular SQL syntax gives me the error that the function IF is not defined.

[Buzby]

SELECT COUNT(*) FROM [TABLE] WHERE [FIELD] IS NOT NULL

eg;

SELECT COUNT(*) FROM tbl_Customers WHERE PhoneNumber IS NOT NULL



------------------
Mark "Buzby" Beeton
VB Developer
[email protected]

[CMerc]

Try the inline if function IIF(...). It's standard in all the flavors of VB that I know.

Originally posted by DrewDog_21:
I'm using Jet 4.0 for Access with DAO 3.6 That particular SQL syntax gives me the error that the function IF is not defined.