joylene6
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_D ATE] AS START_DATE_YMD, PHM_MAC_CHRT_TRNS.CHARTED_ DATE, PHM_MAC_CHRT_TRNS.CHARTED_ TIME, Mid$(convdate(1,10,[PHM_MA C_CHRT_TRN S]![CHARTE D_DATE]),1 ,10) & " at " & Mid$([PHM_MAC_CHRT_TRNS]![ CHARTED_TI ME],1,5) AS [Charted at], IIf([PHM_MAC_CHRT_TRNS]![L OCATION] Is Null,"NONE",[PHM_MAC_CHRT_ TRNS]![LOC ATION]) AS NRS_STATION, Left(Right([PHM_MAC_CHRT_T RNS]![PTBE D],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_METHO D, PHM_MAC_CHRT_TRNS.IV_METHO D_CODE, PHM_MAC_CHRT_TRNS.PMP, "¿" AS Removed, "¿" AS [Power Line], "¿" AS [Tip Location], PHM_MAC_CHRT_TRNS.TRANS_TY PE, PHM_MAC_CHRT_TRNS.ADMIN_ST ATUS, 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]![DR UG_INFO_1] ) Like "*ALTEPL*")
AND (([PHM_MAC_CHRT_TRNS]![OCC _DATE]) Between DATERELTODAY(-7) And DATERELTODAY(0))
AND ((PHM_MAC_CHRT_TRNS.IV_MET HOD_CODE)= "0")
AND ((PHM_MAC_CHRT_TRNS.TRANS_ TYPE)="C")
AND ((PHM_MAC_CHRT_TRNS.ADMIN_ STATUS)="A DMIN")
AND ((PHM_ORDERS.INGR_LEVEL_NU M)<"20"));
UNION SELECT PHHST_GENNAME_IDX.GENERIC_ NAME, PHHST_GENNAME_IDX.START_DA TE_YMD, PHM_MAC_CHRT_HST.CHARTED_D ATE, PHM_MAC_CHRT_HST.CHARTED_T IME, Mid$(convdate(1,10,[PHM_MA C_CHRT_HST ]![CHARTED _DATE]),1, 10) & " at " & Mid$([PHM_MAC_CHRT_HST]![C HARTED_TIM E],1,5) AS [Charted at], IIf([PHM_MAC_CHRT_HST]![LO CATION] Is Null,"NONE",[PHM_MAC_CHRT_ HST]![LOCA TION]) AS NRS_STATION, Left(Right([PHM_MAC_CHRT_H ST]![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_TYP E, PHM_MAC_CHRT_HST.ADMIN_STA TUS, "" 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.GENER IC_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_METH OD_CODE)=" 0")
AND ((PHM_MAC_CHRT_HST.TRANS_T YPE)="F")
AND ((PHM_MAC_CHRT_HST.ADMIN_S TATUS)="AD MIN"));
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_
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
WHERE ((([PHM_MAC_CHRT_TRNS]![DR
AND (([PHM_MAC_CHRT_TRNS]![OCC
AND ((PHM_MAC_CHRT_TRNS.IV_MET
AND ((PHM_MAC_CHRT_TRNS.TRANS_
AND ((PHM_MAC_CHRT_TRNS.ADMIN_
AND ((PHM_ORDERS.INGR_LEVEL_NU
UNION SELECT PHHST_GENNAME_IDX.GENERIC_
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
WHERE (((PHHST_GENNAME_IDX.GENER
AND ((PHHST_GENNAME_IDX.START_
AND ((PHM_MAC_CHRT_HST.CHARTED
AND ((PHM_MAC_CHRT_HST.IV_METH
AND ((PHM_MAC_CHRT_HST.TRANS_T
AND ((PHM_MAC_CHRT_HST.ADMIN_S
ASKER
No the field does not exsist. They have to be equal right? So How do I balance it out?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you. That worked.
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