Looping across fields to check for null values

[Nicole]

Hi,

I'm using Access 2007 and have a database that contains an account ID number
and 30 fields that each represent a procedure conducted by a healthcare
office (Proc1, Proc2,....,Proc30).

What I need to know is how many records have a non-null value in Proc 2 but
are null in Proc 1. I need to loop this through all procedure fields,
searching for fields where a value exists but the prior field(s) is(are)
null. Also, I eventually need to export a file for each record where this is
true.

I apologize for asking so many questions; I've got a flat-file database but
the only tool they gave me to work with is Access. Can anyone recommend a
good reference for writing loops in Acces SQL? The ones I have aren't
helping much.

Thanks,
Nicole

 

[Nicole]

Hi,

I'm using Access 2007 and have a database that contains an account ID number
and 30 fields that each represent a procedure conducted by a healthcare
office (Proc1, Proc2,....,Proc30).

What I need to know is how many records have a non-null value in Proc 2 but
are null in Proc 1. I need to loop this through all procedure fields,
searching for fields where a value exists but the prior field(s) is(are)
null. Also, I eventually need to export a file for each record where this is
true.

I apologize for asking so many questions; I've got a flat-file database but
the only tool they gave me to work with is Access. Can anyone recommend a
good reference for writing loops in Acces SQL? The ones I have aren't
helping much.

Thanks,
Nicole

PS--I've figured out how to get the count of how many records have a
non-null value in the subsequent columns. Hoping someone can tell me if I'm
on the right track or if there is a simpler way to do this for all 30 fields
at once.

Thanks!

SELECT
COUNT(PROC1) AS CPROC1,
COUNT(PROC2) AS CPROC2,
COUNT(PROC3) AS CPROC3,
COUNT(PROC4) AS CPROC4,
COUNT(PROC5) AS CPROC5,
COUNT(PROC6) AS CPROC6,
..
..
..

COUNT(PROC30) AS CPROC30
FROM [BPH:2006]
WHERE PROC1 IS NULL ;