Blank Record Count

  • Thread starter Thread starter Michael N
  • Start date Start date
M

Michael N

I am strugling creating a module that I can call as a
function to count blank fields in a table.

Table:
id, Name, date, location, Diagnosis.

In the table, that is a linked table generated by another
program, if a person has more than one diagnosis but the
other fields are the same they are left blank.

example
1 Dave Smith 09/01/2004 ER broken leg
2 Broken Foot
3 Broken Toe

I need the function to count that 2 rows are empty for any
given field. so Name for ID 2 and 3 should be Dave smith
but it is null.

I have tried many differnt looping functions, but can not
seem to get it to return a number I can use (2 in the
above example)

Any help would be appreciated.
 
I need the function to count that 2 rows are empty for any
given field. so Name for ID 2 and 3 should be Dave smith
but it is null.
Click to expand...

Proper way:

SELECT COUNT(*) FROM MyTable
WHERE GivenField IS NULL


Shortcut way (actually the same)

wEmptyRows = DCount("*", "MyTable", "GivenField IS NULL")

Hope that helps



Tim F
 
Tim

That is a nice function but I am really trying to get the
blank count for each person in the table. so for dave
smith with 3 records, the admit date for record 2 and 3
are blank.

Mark johnson has 5 records and after the first one the
next 4 have blank admit dats. I would like a function
that basically counts for each line the number of lines
until they reach a row where the dat is not blank.

so david smith would have the folling counts
first row =0
second row =1
third row = 2

does this help explain it?
 
Mark johnson has 5 records and after the first one the
next 4 have blank admit dats.
Click to expand...

This sounds awfully like a Major Design Problem. In tables, there are no
"first" or "next" records -- there are only records and sorting orders.
Looking back at your original posts, I see that this hunch is confirmed.
From that posting, it looks like you should really have a set up something
like:

People(*PtID, FName, LName, etc)
DiagnosticCodes(*Code, Description)
Diagnoses(*PtID, *DiagnssCode)
Visits(*PtID, *VisitDate, Location, AdmittedYesOrNo, etc)


I can't see any way of getting reliable counts unless you have a decent
table design in the first place... :-(

Hope that helps


Tim F
 
Back
Top