Is this possible??

[Mike]

We have patients at our clinic and some are on medication
and some are not... In the table that tracks there
monthly session attendance, there is a check box that
also track whether or not they receved medication for
that month (if a client gets medication one month they
should get it every month there after)...
I have been using this database for about a year and a
half now and now I would like to go back and see if any
data is missing from the patients that receive
medication...

not sure how to do this because I seem to only be able to
get results on all or none of the patients who receive
medication (i.g. Yes or No)... I want a list of ONLY
patients who have ever recevied medication but I want all
records regardless of whether the "got meds this month"
box is checked or unchecked.
I know its sort of paradoxical to ask a single yes/no
field to report back all of the "unchecked" key records
that ever had had a "check" in another different months
chek box field

table is as follows:
Key= SessionID - (Autonumber)
ForignKey=AdmissionID
Field= PrimaryCounselor
Field= Month
Field= Year
Field= 1on1 session (NUMBER)
Field= Group session (NUMBER)
Field= Medication (YES/NO)


any help is appreciated...

Kind Regards

Mike K

 

[Sharyn]

One possibility, if this is a one-time thing you want to
do, might be to make a copy of the table and then make a
delete query to delete out all NO records in the copied
table. If you think you might want to do this on a
regular basis, copy the table with a make-table query so
it is easier to do in the future.

Sharyn

 

[itareus]

Anything is possible....and, although I'm not 100 percent
sure of your requirement it could be done through VBA
coding (I can't think of an easy way offhand) but as
Sharyn says - is this a one time thing? If it is then
the coding effort won't be justified.

 

[John Vinson]

not sure how to do this because I seem to only be able to
get results on all or none of the patients who receive
medication (i.g. Yes or No)... I want a list of ONLY
patients who have ever recevied medication but I want all
records regardless of whether the "got meds this month"
box is checked or unchecked.
I know its sort of paradoxical to ask a single yes/no
field to report back all of the "unchecked" key records
that ever had had a "check" in another different months
chek box field

An IN clause will do the trick here. Create a query joining the two
tables, with a date criterion to select just the month's data (e.g.
BETWEEN DateSerial(Year(Date()), Month(Date()), 1) AND
DateSerial(Year(Date()), Month(Date()) + 1, 0) to get the current
month's data). As a criterion on ClientID put

IN(SELECT ClientID FROM Medications WHERE GotMeds = True)

to select only those clientID's who have *ever* gotten medication.