Query - Help

  • Thread starter Thread starter kClark
  • Start date Start date
K

kClark

I have a report that I am working. This is my problem. I
have 4 categories. One is if the employee is under 40
then they fall into two categories Panel 8 and 9. If the
employee has not had a chest x-ray then they are in panel
9. If the employee is 40 and older they fall into the
other two categories Panel 10 and 11. If they do not have
an chest x-ray then they are in Panel 11. This is my
problem I have to take the total number of employees that
fall in panel 9 and panel 11 and take 10% of that total
and mark the report that they need a chest x-ray. I would
appreciate any help.
 
If you are continually shrinking your pool of employees the 10% value should
get progressively smaller. Zeno & the Hare! Wouldn't you be better off
settling on a smaller fraction of the whole pool of employees so that your
list of X-ray candidates will be more stable?

With all of the details you've given we still don't know what data is
actually in your tables so couldn't construct a complete algorithm for you.
If you used the concept above then you should be able to run your query to
list just the TOP N (integer representing the percentage of the whole pool.
As more people have their X-rays the list will eventually fall to a number
less than N. No problem, it will just list the remainder. Be aware that
you have to enter the TOP N statement in SQL view of the Query wizard. Look
in Help for TOP.

HTH
 
Thanks Larry.

The report starts by pulling all the employees that work
at the facility where the mobile lab testing is being
performed. The query for the cxr is as following:

StrSQL = "Select CXR from Nurse Where [SSNO] = '" & Me!
SSNO & "'"
Set myCXR = dbsMR.OpenRecordset(StrSQL)
If myCXR.RecordCount <> 0 Then
myCXR.MoveFirst
Do Until myCXR.EOF
If CXRValue = False Then
If myCXR!CXR = "N" Or IsNull(myCXR!CXR) Then
CXRValue = True
End If
End If
myCXR.MoveNext
Loop
Else
CXRValue = True
End If

The physician would like only 10% of the total number of
people to have chest x-rays. Would I still use Top N?
 
Back
Top