Im in too far

[sherylleigh]

I working on building a query to make a report for my supervisor that will provide data on anybody who has had a problem UA (urine screen) within a specific time frame.

The query below provides me with that I need, but doesn't sort out just the problem screens, it shows everyone who provided me with a UA.
__________________________________________________
SELECT Client_Case_Information.Client_Last_Name, Client_Case_Information.Client_First_Name, Drug_History.DrugHistoryID, Drug_History.CaseID, Client_Case_Information.Case_Manager, Client_Case_Information.Status, Drug_History.Screen_Flag, IIf([Manual_Flag],IIf([Screen_Flag],"(screen)",""),"") AS ManualScreen, IIf([Manual_Flag],IIf([Screen_Flag],"","(relapse)"),"") AS ManualRelapse, IIf([Screen_Flag],"","R") AS ScreenEntry, Drug_History.Drug_Date, Drug_History.Panel, Drug_History.Time_Collected, Drug_History.CRet_Amount, Drug_History.Positive_Flag, Drug_History.Complete_Flag, Drug_History.NCDR_Flag, Drug_History.Cancel_Flag, Drug_History.Dilute_Flag, Drug_History.Late_Flag, Drug_History.Observe_Flag, Drug_History.Relapse_Flag, Drug_History.Manual_Flag, Drug_History.Comments, IIf(([Positive_Flag] Or [Late_Flag] Or [Dilute_Flag] Or [NCDR_Flag] Or [Relapse_Flag]),IIf(([Resp_Blind_Review] Or [Resp_Discharge_From_Program] Or [Resp_Education] Or [Resp_Incidental] Or [Resp_Increase_Monitoring] Or [Resp_Practice_Restrictions] Or [Resp_Practice_Restrictions] Or [Resp_Referral] Or [Resp_Reported_To_Board] Or [Resp_Treatment]),(IIf([Resp_Increase_Monitoring],"Increase Monitoring, ","")+IIf([Resp_Treatment],"Treatment, ","")+IIf([Resp_Referral],"Referral, ","")+IIf([Resp_Reported_To_Board],"Rpt to Board, ","")+IIf([Resp_Discharge_From_Program],"Disch from Program, ","")+IIf([Resp_Incidental],"Incidental, ","")+IIf([Resp_Practice_Restrictions],"Practice Res, ","")+IIf([Resp_Blind_Review],"Blind Review, ","")+IIf([Resp_Education],"Education, ","")),"** select responses **"),"") AS Response, IIf([Relapse_Flag],IIf(IsNull([Rel_Date]),"","Date: "+CStr([Rel_Date])+" ")+IIf(IsNull([Rel_Duration]),"","Duration: "+CStr([Rel_Duration])+Chr(13)+Chr(10))+IIf(IsNull([Rel_Amount]),"","Amount: "+CStr([Rel_Amount])+Chr(13)+Chr(10))+IIf(IsNull([Rel_Acquired]),"","Acquired: "+CStr([Rel_Acquired])),"") AS RelapseEntry, Drug_History.Resp_Increase_Monitoring, Drug_History.Resp_Treatment, Drug_History.Resp_Referral, Drug_History.Resp_Reported_To_Board, Drug_History.Resp_Discharge_From_Program, Drug_History.Resp_Incidental, Drug_History.Resp_Practice_Restrictions, Drug_History.Resp_Blind_Review, Drug_History.Resp_Education, Drug_History.Rel_Date, Drug_History.Rel_Duration, Drug_History.Rel_Detection, Drug_History.Rel_Reaction, Drug_History.Rel_Amount, Drug_History.Rel_Acquired
FROM Drug_History INNER JOIN Client_Case_Information ON Drug_History.CaseID = Client_Case_Information.CaseID
WHERE (((Client_Case_Information.Status)="PA and MP signed") AND ((Drug_History.Screen_Flag)=True) AND ((Drug_History.Drug_Date)>=[Forms]![Parm_Dates_Two]![txtStartDate] And (Drug_History.Drug_Date)<=[Forms]![Parm_Dates_Two]![txtEndDate]))
ORDER BY Drug_History.Drug_Date DESC , Drug_History.Time_Collected DESC;
________________________________________

Next Problem.

I do, however need to add data from one more table to get the additional information I need in the Report.

When I tried to add the table and run the query, it ends up filtering out only the Positive screens (table name is Drug_History_ Positive. Info I need from it is the Drug, Level, if they Admit or Deny.

Please help if possible.

TIA.
Sheryl

 

[MarkK]

One useful consideration to be aware of is that you can write sub-queries. So, write a query that does some of the work you need done, that use that query as if it was a table, join it to other data and so on, and then save that query. Then use that query as a source, again as if it was a table, so that you essentially query your previous queries.

To restate: Rather than write one sprawling incomprehensibly complicated query, write a series of short, sweet, simple queries that call each other in a chain, each one incrementally advancing the processed data closer to your goal.

Hope this helps,