Calculate the physicians that are compliant with their Tetnus shot

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I work for a hospital and assist in the Employee Health department.
I have an Access data base that consists with the ID number, the physician
name,DOB, PPD dates. tetnus date, tetnus decline date.
I need to know how to calculate the physicians that are compliant and that
are ot compliant.
Help!!!!
 
How do you know from the data that a physician is compliant?



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
Bam said:
I work for a hospital and assist in the Employee Health department.
I have an Access data base that consists with the ID number, the physician
name,DOB, PPD dates. tetnus date, tetnus decline date.
I need to know how to calculate the physicians that are compliant and that
are ot compliant.
Help!!!!
Click to expand...

Assuming your data is in a table named TblPhysician and if a physician has a
Tetnus Date he is compliant and if he has a Tetnus Decline Date he is
non-compliant -----

Create a query based on TblPhysician and sort Physician Name ascending. In
the first blank field at the right in your query, type in:
Tetnus Compliant::IIF([Tetnus Date] Is Not Null,"Yes",IIF([Tetnus Decline
Date] Is Not Null,"No","UnKnown"))

Now use the form wizard to create a continuous form based on the query.
Include the fields Physician Name and Tetnus Compliant. When you open the
form you will see a list of physicians and if they are tetnus compliant.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)
 
If the physician has a Tetnus Date he is compliant, and if he has a Tetnus
Decline Date, he is non-compliant.
I need to somehow count the # of physicians in the database, and calculate
how many are and how many are not compliant.
 
Thank you, Steve........but I'm getting an error message back : Undefined
function':IIF'in expression.

I actually used the medical term on the physician Tbl and query. Instead of
Tetunus Date, my table and query have Tdap_Vaccine_Date, and
Tdap_Decline_Date, which is exactly what I used in the string thinking that's
the exact fields the query would read. Right?

The reason I use the underscore is because of an ex-employee that used the
underscores as a standard, and she initially set up the whole database.
Why?? I don't know/remember. Could that have ANYTHING to do with my error
message??

As I look at the string you had me put in, it makes since logically to me.
I don't know why the error...

Help


Steve said:
Bam said:
I work for a hospital and assist in the Employee Health department.
I have an Access data base that consists with the ID number, the physician
name,DOB, PPD dates. tetnus date, tetnus decline date.
I need to know how to calculate the physicians that are compliant and that
are ot compliant.
Help!!!!
Click to expand...

Assuming your data is in a table named TblPhysician and if a physician has a
Tetnus Date he is compliant and if he has a Tetnus Decline Date he is
non-compliant -----

Create a query based on TblPhysician and sort Physician Name ascending. In
the first blank field at the right in your query, type in:
Tetnus Compliant::IIF([Tetnus Date] Is Not Null,"Yes",IIF([Tetnus Decline
Date] Is Not Null,"No","UnKnown"))

Now use the form wizard to create a continuous form based on the query.
Include the fields Physician Name and Tetnus Compliant. When you open the
form you will see a list of physicians and if they are tetnus compliant.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)
Click to expand...
 
Look at the punctuation in your expression. Note that there are square
brackets and parantheses.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)



Bam said:
Thank you, Steve........but I'm getting an error message back : Undefined
function':IIF'in expression.

I actually used the medical term on the physician Tbl and query. Instead
of
Tetunus Date, my table and query have Tdap_Vaccine_Date, and
Tdap_Decline_Date, which is exactly what I used in the string thinking
that's
the exact fields the query would read. Right?

The reason I use the underscore is because of an ex-employee that used the
underscores as a standard, and she initially set up the whole database.
Why?? I don't know/remember. Could that have ANYTHING to do with my error
message??

As I look at the string you had me put in, it makes since logically to me.
I don't know why the error...

Help


Steve said:
Bam said:
I work for a hospital and assist in the Employee Health department.
I have an Access data base that consists with the ID number, the
physician
name,DOB, PPD dates. tetnus date, tetnus decline date.
I need to know how to calculate the physicians that are compliant and
that
are ot compliant.
Help!!!!
Click to expand...

Assuming your data is in a table named TblPhysician and if a physician
has a
Tetnus Date he is compliant and if he has a Tetnus Decline Date he is
non-compliant -----

Create a query based on TblPhysician and sort Physician Name ascending.
In
the first blank field at the right in your query, type in:
Tetnus Compliant::IIF([Tetnus Date] Is Not Null,"Yes",IIF([Tetnus Decline
Date] Is Not Null,"No","UnKnown"))

Now use the form wizard to create a continuous form based on the query.
Include the fields Physician Name and Tetnus Compliant. When you open the
form you will see a list of physicians and if they are tetnus compliant.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)
Click to expand...
Click to expand...
 
If the physician has a Tetnus Date he is compliant, and if he has a Tetnus
Decline Date, he is non-compliant.
Click to expand...

Ouch. I see a potential data normalization problem here.

What if he has BOTH dates entered? What if she has NEITHER?

As structured, both of these outcomes are possible. How would you evaluate
compliance - the later of the dates? or would this be a data entry error?
I need to somehow count the # of physicians in the database, and calculate
how many are and how many are not compliant.
Click to expand...

You should be able to use two calculated fields (if the above concern is moot;
create a Query based on your table and enter into a vacant Field cell in the
query grid:

CompliantCount: IIf(IsNull(Tdap_Vaccine_Date), 0, 1)

and

NonCompliantCount: IIF(IsNull(Tdap_Decline_Date), 0, 1)

You can then change the query to a Totals query; include some other field (the
PhysicianID or primary key field is handy) to count all physicians, and Sum
these fields to count compliant and non-compliant records.

You might want to consider a different design: two fields, a Yes/No field
TdapVaccine (checked Yes if the physician accepted, No if declined), and a
TdapDate for the date of that decision).

John W. Vinson [MVP]
 
Back
Top