Link to home
Start Free TrialLog in
Avatar of joylene6
joylene6Flag for United States of America

asked on

Union Query asking for a parameter value

I have been asked to fix this query which I did not write. When I run this I get
Enter Parameter Value boxes in this order:
PHM_MAC_CHRT_HST.PTMEDREC
PHHST_GENNAME_IDX.PTMEDREC
PHHST_ORDERS.PTMEDREC


Here is the union query below.  Please help me rewrite this so it does not ask for the parameter values.

SELECT [PHM_MAC_CHRT_TRNS]![DRUG_INFO_1] AS GENERIC_NAME, [PHM_MAC_CHRT_TRNS]![OCC_DATE] AS START_DATE_YMD, PHM_MAC_CHRT_TRNS.CHARTED_DATE, PHM_MAC_CHRT_TRNS.CHARTED_TIME, Mid$(convdate(1,10,[PHM_MAC_CHRT_TRNS]![CHARTED_DATE]),1,10) & " at " & Mid$([PHM_MAC_CHRT_TRNS]![CHARTED_TIME],1,5) AS [Charted at], IIf([PHM_MAC_CHRT_TRNS]![LOCATION] Is Null,"NONE",[PHM_MAC_CHRT_TRNS]![LOCATION]) AS NRS_STATION, Left(Right([PHM_MAC_CHRT_TRNS]![PTBED],5),3) & "-" & Right([PHM_MAC_CHRT_TRNS]![PTBED],2) AS BED, PHM_MAC_CHRT_TRNS.PTNAME, PHM_MAC_CHRT_TRNS.PAT_NUM, PHM_MAC_USERS.USER_NAME, PHM_MAC_CHRT_TRNS.ROUTE, PHM_ORDERS.DOSE, PHM_MAC_CHRT_TRNS.ADM_DOSE_OVR, PHM_MAC_CHRT_TRNS.IV_METHOD, PHM_MAC_CHRT_TRNS.IV_METHOD_CODE, PHM_MAC_CHRT_TRNS.PMP, "¿" AS Removed, "¿" AS [Power Line], "¿" AS [Tip Location], PHM_MAC_CHRT_TRNS.TRANS_TYPE, PHM_MAC_CHRT_TRNS.ADMIN_STATUS, PHM_ORDERS.INGR_LEVEL_NUM

FROM (USERS INNER JOIN (PHM_MAC_CHRT_TRNS INNER JOIN PHM_ORDERS ON (PHM_MAC_CHRT_TRNS.PMP = PHM_ORDERS.PMP) AND (PHM_MAC_CHRT_TRNS.ITN = PHM_ORDERS.ITN)) ON USERS.USER_INITIALS = PHM_MAC_CHRT_TRNS.NURSE_ID) INNER JOIN PHM_MAC_USERS ON USERS.ACCESS_KEY = PHM_MAC_USERS.USER_ID

WHERE ((([PHM_MAC_CHRT_TRNS]![DRUG_INFO_1]) Like "*ALTEPL*")
 AND (([PHM_MAC_CHRT_TRNS]![OCC_DATE]) Between DATERELTODAY(-7) And DATERELTODAY(0))
 AND ((PHM_MAC_CHRT_TRNS.IV_METHOD_CODE)="0")
AND ((PHM_MAC_CHRT_TRNS.TRANS_TYPE)="C")
 AND ((PHM_MAC_CHRT_TRNS.ADMIN_STATUS)="ADMIN")
 AND ((PHM_ORDERS.INGR_LEVEL_NUM)<"20"));


UNION SELECT PHHST_GENNAME_IDX.GENERIC_NAME, PHHST_GENNAME_IDX.START_DATE_YMD, PHM_MAC_CHRT_HST.CHARTED_DATE, PHM_MAC_CHRT_HST.CHARTED_TIME, Mid$(convdate(1,10,[PHM_MAC_CHRT_HST]![CHARTED_DATE]),1,10) & " at " & Mid$([PHM_MAC_CHRT_HST]![CHARTED_TIME],1,5) AS [Charted at], IIf([PHM_MAC_CHRT_HST]![LOCATION] Is Null,"NONE",[PHM_MAC_CHRT_HST]![LOCATION]) AS NRS_STATION, Left(Right([PHM_MAC_CHRT_HST]![PTBED],5),3) & "-" & Right([PHM_MAC_CHRT_HST]![PTBED],2) AS BED, PHM_MAC_CHRT_HST.PTNAME, Left(Right([PHHST_GENNAME_IDX]![PAT_NUM],10),5) & "-" & Right([PHHST_GENNAME_IDX]![PAT_NUM],5) AS PAT_NUM, [qry_NW Master List of Nursing Users].USER_NAME, PHM_MAC_CHRT_HST.ROUTE, PHHST_ORDERS.DOSE, PHM_MAC_CHRT_HST.ADM_DOSE_OVR, PHM_MAC_CHRT_HST.IV_METHOD, PHM_MAC_CHRT_HST.IV_METHOD_CODE, PHM_MAC_CHRT_HST.PMP, "¿" AS Removed, "¿" AS [Power Line], "¿" AS [Tip Location], PHM_MAC_CHRT_HST.TRANS_TYPE, PHM_MAC_CHRT_HST.ADMIN_STATUS, "" AS INGR_LEVEL_NUM

FROM ([qry_NW Master List of Nursing Users] INNER JOIN (PHM_MAC_CHRT_HST INNER JOIN PHHST_GENNAME_IDX ON (PHM_MAC_CHRT_HST.PTMEDREC = PHHST_GENNAME_IDX.PTMEDREC) AND (PHM_MAC_CHRT_HST.PAT_NUM = PHHST_GENNAME_IDX.PAT_NUM) AND (PHM_MAC_CHRT_HST.PMP = PHHST_GENNAME_IDX.PMP)) ON [qry_NW Master List of Nursing Users].USER_INITIALS = PHM_MAC_CHRT_HST.NURSE_ID) INNER JOIN PHHST_ORDERS ON (PHM_MAC_CHRT_HST.PMP = PHHST_ORDERS.PMP) AND (PHM_MAC_CHRT_HST.PAT_NUM = PHHST_ORDERS.PAT_NUM) AND (PHM_MAC_CHRT_HST.PTMEDREC = PHHST_ORDERS.PTMEDREC)

WHERE (((PHHST_GENNAME_IDX.GENERIC_NAME) Like "*ALTEPL*")
 AND ((PHHST_GENNAME_IDX.START_DATE_YMD) Between DATERELTODAY(-60) And DATERELTODAY(0))
 AND ((PHM_MAC_CHRT_HST.CHARTED_DATE) Between DATERELTODAY(-7) And DATERELTODAY(0))
AND ((PHM_MAC_CHRT_HST.IV_METHOD_CODE)="0")
 AND ((PHM_MAC_CHRT_HST.TRANS_TYPE)="F")
AND ((PHM_MAC_CHRT_HST.ADMIN_STATUS)="ADMIN"));
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

open tables
PHM_MAC_CHRT_HST
PHHST_GENNAME_IDX
PHHST_ORDERS

in design view and check that the field "PTMEDREC" exists in the three tables
check spelling too
Avatar of joylene6

ASKER

No the field does not exsist. They have to be equal right?  So How do I balance it out?
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you. That worked.