Query for Report

[Dennis]

I need some help with a query in a report. I have values
that tell what time medication is to be taken. QAM = AM,
QPM=PM, QHS=HS (HS means hour of sleep) BID=ID (That means
both AM and PM) and TID (that means three times a day AM,
PM, & HS). What I am doing now is making a control in my
report for each field (QAM, QPM, QHS, BID & TID) and by
placing the QAM (report control) next to QPM (report
control) next to QHS (report control) I can get the report
to print the time of medication. All three if need be. The
problem is I have two more values BID that reports AM and,
PM, which is more than just AM or PM in one control and
TID that prints all three AM, PM, HS in one control. To
get any and all possible combinations I have to overlay
the controls and of course, they do not line up exactly if
the values are superimposed over each other. The question
is in my query, how can I get an IIf statement or other to
print any or all AM, PM or HS based on values from five
different fields in one control? I tried MED: IIf([AM]
="AM" & [PM]="PM" & [HS]=Null & [BID]=Null & [TID]
=Null,"AM PM");... and so on until all possible
combinations are covered but although I get no errors, I
do not get a solution or result. I think "&" is not
correct in an expression?

I also think I may not be able to add this statement in
the report's query SQL as I already have the following:

SELECT DISTINCT PillLine1qry.CDC_NBR, PillLine1qry.LNAME,
PillLine1qry.UNIT, GetSig([CDC_NBR],"QAM") AS AM, GetSig
([CDC_NBR],"QPM") AS PM, GetSig([CDC_NBR],"QHS") AS HS,
GetSig([CDC_NBR],"BID") AS AMPM, GetSig([CDC_NBR],"TID")
AS AMPMHS, IIf([AMPM]="ID","AM PM") AS BID, IIf([AMPMHS]
="ID","AM, PM, HS") AS TID
FROM PillLine1qry
WHERE (((PillLine1qry.UNIT) Between [Start Building ie
B01] And [Ending Building Plus One]) AND ((Right
([SIG1],3)) In ("QAM","QPM","QHS","BID","TID")))
ORDER BY PillLine1qry.LNAME;

Can I do something like this in the form control and if so
how?

What do you think?

Dennis

 

[Joan Wild]

Try:

MED: (([AM] + " ") & ([PM] + " ") & ([HS] + " ") & ([BID] + " ") & ([TID]))

Using the + concatenation operator will ignore Nulls

Just an aside, I think your database isn't normalized. Those five fields
should actually be in a separate table, but as records not fields.

i.e. You have a medication table, and related to it is a table for the times
it is to be taken

medication time
abc QAM
abc TID
def QPM

etc.

I need some help with a query in a report. I have values
that tell what time medication is to be taken. QAM = AM,
QPM=PM, QHS=HS (HS means hour of sleep) BID=ID (That means
both AM and PM) and TID (that means three times a day AM,
PM, & HS). What I am doing now is making a control in my
report for each field (QAM, QPM, QHS, BID & TID) and by
placing the QAM (report control) next to QPM (report
control) next to QHS (report control) I can get the report
to print the time of medication. All three if need be. The
problem is I have two more values BID that reports AM and,
PM, which is more than just AM or PM in one control and
TID that prints all three AM, PM, HS in one control. To
get any and all possible combinations I have to overlay
the controls and of course, they do not line up exactly if
the values are superimposed over each other. The question
is in my query, how can I get an IIf statement or other to
print any or all AM, PM or HS based on values from five
different fields in one control? I tried MED: IIf([AM]
="AM" & [PM]="PM" & [HS]=Null & [BID]=Null & [TID]
=Null,"AM PM");... and so on until all possible
combinations are covered but although I get no errors, I
do not get a solution or result. I think "&" is not
correct in an expression?

I also think I may not be able to add this statement in
the report's query SQL as I already have the following:

SELECT DISTINCT PillLine1qry.CDC_NBR, PillLine1qry.LNAME,
PillLine1qry.UNIT, GetSig([CDC_NBR],"QAM") AS AM, GetSig
([CDC_NBR],"QPM") AS PM, GetSig([CDC_NBR],"QHS") AS HS,
GetSig([CDC_NBR],"BID") AS AMPM, GetSig([CDC_NBR],"TID")
AS AMPMHS, IIf([AMPM]="ID","AM PM") AS BID, IIf([AMPMHS]
="ID","AM, PM, HS") AS TID
FROM PillLine1qry
WHERE (((PillLine1qry.UNIT) Between [Start Building ie
B01] And [Ending Building Plus One]) AND ((Right
([SIG1],3)) In ("QAM","QPM","QHS","BID","TID")))
ORDER BY PillLine1qry.LNAME;

Can I do something like this in the form control and if so
how?

What do you think?

Dennis

 

[Dennis]

Joan,

The core values (SIGCODE) I am working with are in a Med
table as you discribe that is linked to my database form
another program and the values are extracted into a query
which gives all med records based on specific criteria.

We are working with query that consolidates all meds and
schedules for each patient so I do not have up to ten
records per patient. This query converts all schedules
into one patient record. The problem is that many
medications have overlaping times. Example patient takes
one med AM & PM & HS and takes another med AM & PM. What I
want as a result is for only one AM, PM or HS to report.
What I get is AM, PM, HS,AM, PM. and the list is much
longer if the patient takes ten different meds.

This report is for med accountibility to let the staff
know meds must be given at these times and that the staff
must make sure meds are given and checked off the report.
It is not important what meds are to be taken as the staff
actually dispensing the meds have that info. This list is
only a "track down the patient list" and identifies that
patient (A) be given meds at AM or PM or HS or any
combination of the three and list AM, PM and or HS only
once. Can that be incorporated in to your expression??

Thanks,

Dennis

-----Original Message-----
Try:

MED: (([AM] + " ") & ([PM] + " ") & ([HS] + " ") & ([BID] + " ") & ([TID]))

Using the + concatenation operator will ignore Nulls

Just an aside, I think your database isn't normalized. Those five fields
should actually be in a separate table, but as records not fields.

i.e. You have a medication table, and related to it is a table for the times
it is to be taken

medication time
abc QAM
abc TID
def QPM

etc.

I need some help with a query in a report. I have values
that tell what time medication is to be taken. QAM = AM,
QPM=PM, QHS=HS (HS means hour of sleep) BID=ID (That means
both AM and PM) and TID (that means three times a day AM,
PM, & HS). What I am doing now is making a control in my
report for each field (QAM, QPM, QHS, BID & TID) and by
placing the QAM (report control) next to QPM (report
control) next to QHS (report control) I can get the report
to print the time of medication. All three if need be. The
problem is I have two more values BID that reports AM and,
PM, which is more than just AM or PM in one control and
TID that prints all three AM, PM, HS in one control. To
get any and all possible combinations I have to overlay
the controls and of course, they do not line up exactly if
the values are superimposed over each other. The question
is in my query, how can I get an IIf statement or other to
print any or all AM, PM or HS based on values from five
different fields in one control? I tried MED: IIf([AM]
="AM" & [PM]="PM" & [HS]=Null & [BID]=Null & [TID]
=Null,"AM PM");... and so on until all possible
combinations are covered but although I get no errors, I
do not get a solution or result. I think "&" is not
correct in an expression?

I also think I may not be able to add this statement in
the report's query SQL as I already have the following:

SELECT DISTINCT PillLine1qry.CDC_NBR, PillLine1qry.LNAME,
PillLine1qry.UNIT, GetSig([CDC_NBR],"QAM") AS AM, GetSig
([CDC_NBR],"QPM") AS PM, GetSig([CDC_NBR],"QHS") AS HS,
GetSig([CDC_NBR],"BID") AS AMPM, GetSig([CDC_NBR],"TID")
AS AMPMHS, IIf([AMPM]="ID","AM PM") AS BID, IIf([AMPMHS]
="ID","AM, PM, HS") AS TID
FROM PillLine1qry
WHERE (((PillLine1qry.UNIT) Between [Start Building ie
B01] And [Ending Building Plus One]) AND ((Right
([SIG1],3)) In ("QAM","QPM","QHS","BID","TID")))
ORDER BY PillLine1qry.LNAME;

Can I do something like this in the form control and if so
how?

What do you think?

Dennis

--
Joan Wild
Microsoft Access MVP


.

 

[Joan Wild]

Perhaps I'm not following, but can't you just modify it to
MED: (([AM] + " ") & ([PM] + " ") & ([HS] ))

Or perhaps one of the functions at
http://www.mvps.org/access/modules/mdl0004.htm
or
http://www.mvps.org/access/modules/mdl0008.htm
would do what you want.

Joan,

The core values (SIGCODE) I am working with are in a Med
table as you discribe that is linked to my database form
another program and the values are extracted into a query
which gives all med records based on specific criteria.

We are working with query that consolidates all meds and
schedules for each patient so I do not have up to ten
records per patient. This query converts all schedules
into one patient record. The problem is that many
medications have overlaping times. Example patient takes
one med AM & PM & HS and takes another med AM & PM. What I
want as a result is for only one AM, PM or HS to report.
What I get is AM, PM, HS,AM, PM. and the list is much
longer if the patient takes ten different meds.

This report is for med accountibility to let the staff
know meds must be given at these times and that the staff
must make sure meds are given and checked off the report.
It is not important what meds are to be taken as the staff
actually dispensing the meds have that info. This list is
only a "track down the patient list" and identifies that
patient (A) be given meds at AM or PM or HS or any
combination of the three and list AM, PM and or HS only
once. Can that be incorporated in to your expression??

Thanks,

Dennis

-----Original Message-----
Try:

MED: (([AM] + " ") & ([PM] + " ") & ([HS] + " ") & ([BID] + " ") &
([TID]))

Using the + concatenation operator will ignore Nulls

Just an aside, I think your database isn't normalized. Those five
fields should actually be in a separate table, but as records not
fields.

i.e. You have a medication table, and related to it is a table for
the times it is to be taken

medication time
abc QAM
abc TID
def QPM

etc.

I need some help with a query in a report. I have values
that tell what time medication is to be taken. QAM = AM,
QPM=PM, QHS=HS (HS means hour of sleep) BID=ID (That means
both AM and PM) and TID (that means three times a day AM,
PM, & HS). What I am doing now is making a control in my
report for each field (QAM, QPM, QHS, BID & TID) and by
placing the QAM (report control) next to QPM (report
control) next to QHS (report control) I can get the report
to print the time of medication. All three if need be. The
problem is I have two more values BID that reports AM and,
PM, which is more than just AM or PM in one control and
TID that prints all three AM, PM, HS in one control. To
get any and all possible combinations I have to overlay
the controls and of course, they do not line up exactly if
the values are superimposed over each other. The question
is in my query, how can I get an IIf statement or other to
print any or all AM, PM or HS based on values from five
different fields in one control? I tried MED: IIf([AM]
="AM" & [PM]="PM" & [HS]=Null & [BID]=Null & [TID]
=Null,"AM PM");... and so on until all possible
combinations are covered but although I get no errors, I
do not get a solution or result. I think "&" is not
correct in an expression?

I also think I may not be able to add this statement in
the report's query SQL as I already have the following:

SELECT DISTINCT PillLine1qry.CDC_NBR, PillLine1qry.LNAME,
PillLine1qry.UNIT, GetSig([CDC_NBR],"QAM") AS AM, GetSig
([CDC_NBR],"QPM") AS PM, GetSig([CDC_NBR],"QHS") AS HS,
GetSig([CDC_NBR],"BID") AS AMPM, GetSig([CDC_NBR],"TID")
AS AMPMHS, IIf([AMPM]="ID","AM PM") AS BID, IIf([AMPMHS]
="ID","AM, PM, HS") AS TID
FROM PillLine1qry
WHERE (((PillLine1qry.UNIT) Between [Start Building ie
B01] And [Ending Building Plus One]) AND ((Right
([SIG1],3)) In ("QAM","QPM","QHS","BID","TID")))
ORDER BY PillLine1qry.LNAME;

Can I do something like this in the form control and if so
how?

What do you think?

Dennis

--
Joan Wild
Microsoft Access MVP


.