Query Modification not giving Correct Data

[danbl]

The first query works for the active providers and captures appropriate data. When the same query is modified (Query 2) it produces no data. The second query involves all the same tables with the addition of a query which identifies retired providers. It has the same date parameters, other criteria, but adds a provider ID field.

Can anyone tell me why this is not working??

Query 1
SELECT t_DVT_Data.did, t_DVT_Data.vil, t_Visit.VisitDate, t_Visit.DOB, t_Visit.Physician, t_DVT_Data.[Anesthesia Type], IIf([Anesthesia Type]="General" And [Total Ttime]>="01:00" And [Asprin]<>1,1,0) AS [G>=1], t_DVT_Data.[ASA Class], [t_Temperature _Monitoring].[Anesthesia Start Time], [t_Temperature _Monitoring].[Anesthesia End Time], Format([Anesthesia End Time]-[Anesthesia Start Time],"Short Time") AS [Total Ttime], t_DVT_Data.BMI, IIf([Anesthesia Type]="General" And [Total Ttime]>="01:00" And [History of VTE Prophylaxsis in the Past]=-1,1,0) AS GwVTE, t_DVT_Data.[History of VTE Prophylaxsis in the Past], IIf([Anesthesia Type]="General" And [Total Ttime]>="01:00" And [History of Breast Cancer]=-1,1,0) AS GwBC, t_DVT_Data.[History of Breast Cancer], t_DVT_Data.[Cardiovascular History], t_DVT_Data.[Ca Channel or Beta Blocker Medication], t_DVT_Data.[Antihypertensive Medication], t_DVT_Data.Position, t_DVT_Data.Position_2, t_DVT_Data.Position_3, IIf([G>=1]=1 And [Asprin]=1,1,0) AS GwASA, IIf(IsNull([VTE Prophylaxsis Used_2]) And [VTE Prophylaxsis Used]="Medication (Pre&Post include low dose ASA Regime",1,IIf([VTE Prophylaxsis Used]="Medication (Pre&Post include low dose ASA Regime" And [VTE Prophylaxsis Used_2]<>"Sequentials - L",1,0)) AS Asprin, t_DVT_Data.[VTE Prophylaxsis Used], t_DVT_Data.[VTE Prophylaxsis Used_2], t_DVT_Data.[VTE Prophylaxsis Used_3], t_DVT_Data.[VTE Prophylaxsis Used_4], t_DVT_Data.[VTE Prophylaxsis Used_5], t_DVT_Data.[VTE Prophylaxsis Used_6], t_DVT_Data.[VTE Prophylaxsis Used_7], t_DVT_Data.[Therapy In Place Until LII], t_DVT_Data.[Location of Surgery], t_DVT_Data.[DVT_Comment(s)], t_DVT_Data.[DVT Occurred], t_DVT_Data.[DVT Tx]
FROM (t_Visit INNER JOIN t_DVT_Data ON t_Visit.vid = t_DVT_Data.vil) INNER JOIN [t_Temperature _Monitoring] ON t_Visit.vid = [t_Temperature _Monitoring].vil
WHERE (((t_Visit.VisitDate) Between [Forms]![Date Range Form]![Start Date] And [Forms]![Date Range Form]![End Date]) AND ((t_DVT_Data.[Anesthesia Type])="General") AND ((Format([Anesthesia End Time]-[Anesthesia Start Time],"Short Time"))>="01:00") AND ((t_DVT_Data.[VTE Prophylaxsis Used])<>"Medication (Pre&Post include low dose ASA Regime" And (t_DVT_Data.[VTE Prophylaxsis Used])<>"Medication")) OR (((t_Visit.VisitDate) Between [Forms]![Date Range Form]![Start Date] And [Forms]![Date Range Form]![End Date]) AND ((t_DVT_Data.[Anesthesia Type])="General") AND ((Format([Anesthesia End Time]-[Anesthesia Start Time],"Short Time"))>="01:00") AND ((t_DVT_Data.[VTE Prophylaxsis Used_2])<>"Medication (Pre&Post include low dose ASA Regime" And (t_DVT_Data.[VTE Prophylaxsis Used_2])<>"Medication")) OR (((t_Visit.VisitDate) Between [Forms]![Date Range Form]![Start Date] And [Forms]![Date Range Form]![End Date]) AND ((t_DVT_Data.[Anesthesia Type])="General") AND ((Format([Anesthesia End Time]-[Anesthesia Start Time],"Short Time"))>="01:00") AND ((t_DVT_Data.[VTE Prophylaxsis Used_3])<>"Medication (Pre&Post include low dose ASA Regime" And (t_DVT_Data.[VTE Prophylaxsis Used_3])<>"Medication"));

Query 2
SELECT t_DVT_Data.did, t_DVT_Data.vil, t_Visit.VisitDate, t_Visit.DOB, qryResignedMD.DrID, t_Visit.Physician, t_DVT_Data.[Anesthesia Type], IIf([Anesthesia Type]="General" And [Total Ttime]>="01:00" And [Asprin]<>1,1,0) AS [G>=1], t_DVT_Data.[ASA Class], [t_Temperature _Monitoring].[Anesthesia Start Time], [t_Temperature _Monitoring].[Anesthesia End Time], Format([Anesthesia End Time]-[Anesthesia Start Time],"Short Time") AS [Total Ttime], t_DVT_Data.BMI, IIf([Anesthesia Type]="General" And [Total Ttime]>="01:00" And [History of VTE Prophylaxsis in the Past]=-1,1,0) AS GwVTE, t_DVT_Data.[History of VTE Prophylaxsis in the Past], IIf([Anesthesia Type]="General" And [Total Ttime]>="01:00" And [History of Breast Cancer]=-1,1,0) AS GwBC, t_DVT_Data.[History of Breast Cancer], t_DVT_Data.[Cardiovascular History], t_DVT_Data.[Ca Channel or Beta Blocker Medication], t_DVT_Data.[Antihypertensive Medication], t_DVT_Data.Position, t_DVT_Data.Position_2, t_DVT_Data.Position_3, IIf([G>=1]=1 And [Asprin]=1,1,0) AS GwASA, IIf(IsNull([VTE Prophylaxsis Used_2]) And [VTE Prophylaxsis Used]="Medication (Pre&Post include low dose ASA Regime",1,IIf([VTE Prophylaxsis Used]="Medication (Pre&Post include low dose ASA Regime" And [VTE Prophylaxsis Used_2]<>"Sequentials - L",1,0)) AS Asprin, t_DVT_Data.[VTE Prophylaxsis Used], t_DVT_Data.[VTE Prophylaxsis Used_2], t_DVT_Data.[VTE Prophylaxsis Used_3], t_DVT_Data.[VTE Prophylaxsis Used_4], t_DVT_Data.[VTE Prophylaxsis Used_5], t_DVT_Data.[VTE Prophylaxsis Used_6], t_DVT_Data.[VTE Prophylaxsis Used_7], t_DVT_Data.[Therapy In Place Until LII], t_DVT_Data.[Location of Surgery], t_DVT_Data.[DVT_Comment(s)], t_DVT_Data.[DVT Occurred], t_DVT_Data.[DVT Tx]
FROM qryResignedMD INNER JOIN ((t_Visit INNER JOIN t_DVT_Data ON t_Visit.vid = t_DVT_Data.vil) INNER JOIN [t_Temperature _Monitoring] ON t_Visit.vid = [t_Temperature _Monitoring].vil) ON qryResignedMD.DrID = t_Visit.Physician
WHERE (((t_Visit.VisitDate) Between [Forms]![Date Range Form3]![Start Date] And [Forms]![Date Range Form3]![End Date]) AND ((qryResignedMD.DrID)=[Forms]![Date Range Form3]![Physician ID]) AND ((t_DVT_Data.[Anesthesia Type])="General") AND ((Format([Anesthesia End Time]-[Anesthesia Start Time],"Short Time"))>="01:00") AND ((t_DVT_Data.[VTE Prophylaxsis Used])<>"Medication (Pre&Post include low dose ASA Regime" And (t_DVT_Data.[VTE Prophylaxsis Used])<>"Medication")) OR (((t_Visit.VisitDate) Between [Forms]![Date Range Form3]![Start Date] And [Forms]![Date Range Form3]![End Date]) AND ((qryResignedMD.DrID)=[Forms]![Date Range Form3]![Physician ID]) AND ((t_DVT_Data.[Anesthesia Type])="General") AND ((Format([Anesthesia End Time]-[Anesthesia Start Time],"Short Time"))>="01:00") AND ((t_DVT_Data.[VTE Prophylaxsis Used_2])<>"Medication (Pre&Post include low dose ASA Regime" And (t_DVT_Data.[VTE Prophylaxsis Used_2])<>"Medication")) OR (((t_Visit.VisitDate) Between [Forms]![Date Range Form3]![Start Date] And [Forms]![Date Range Form3]![End Date]) AND ((qryResignedMD.DrID)=[Forms]![Date Range Form3]![Physician ID]) AND ((t_DVT_Data.[Anesthesia Type])="General") AND ((Format([Anesthesia End Time]-[Anesthesia Start Time],"Short Time"))>="01:00") AND ((t_DVT_Data.[VTE Prophylaxsis Used_3])<>"Medication (Pre&Post include low dose ASA Regime" And (t_DVT_Data.[VTE Prophylaxsis Used_3])<>"Medication"));

 

[plog]

First, make it easy on the people you want help from to help you. You can't just throw a wall of text in your post and expect people to dig through it to help you. CODE tags would help, but indentation, spacing and line breaks are what you really need:

SELECT on one line
FROM on another
WHERE on another and if any clause is so large its going to wrap
   you should break it yourself and indent it.

I did skim the code and see you've used nothing but INNER JOINs in your FROM. Those effectively act as criteria. You must have a matching record in every datasource you INNER JOIN for records to be produced. in the final result:

FROM A INNER JOIN B....INNER JOIN C

A match must be in all 3 tables for it to be included. Now let's say you add one more table to the mix:

FROM A INNER JOIN B....INNER JOIN C...INNER JOIN D

To make it through to the final output every record must have a match in every table. My guess that's what's happend to yours.

 

[jdraw]

That's a lot of SQL for the reader to consume without some background info.
Here's query1 that has been formatted with Poor SQL

SELECT t_DVT_Data.did
    ,t_DVT_Data.vil
    ,t_Visit.VisitDate
    ,t_Visit.DOB
    ,t_Visit.Physician
    ,t_DVT_Data.[Anesthesia Type]
    ,IIf([Anesthesia Type] = "General"
        AND [Total Ttime] >= "01:00"
        AND [Asprin] <> 1, 1, 0) AS [G>=1]
    ,t_DVT_Data.[ASA Class]
    ,[t_Temperature _Monitoring].[Anesthesia Start Time]
    ,[t_Temperature _Monitoring].[Anesthesia End Time]
    ,Format([Anesthesia End Time] - [Anesthesia Start Time], "Short Time") AS [Total Ttime]
    ,t_DVT_Data.BMI
    ,IIf([Anesthesia Type] = "General"
        AND [Total Ttime] >= "01:00"
        AND [History of VTE Prophylaxsis in the Past] = - 1, 1, 0) AS GwVTE
    ,t_DVT_Data.[History of VTE Prophylaxsis in the Past]
    ,IIf([Anesthesia Type] = "General"
        AND [Total Ttime] >= "01:00"
        AND [History of Breast Cancer] = - 1, 1, 0) AS GwBC
    ,t_DVT_Data.[History of Breast Cancer]
    ,t_DVT_Data.[Cardiovascular History]
    ,t_DVT_Data.[Ca Channel or Beta Blocker Medication]
    ,t_DVT_Data.[Antihypertensive Medication]
    ,t_DVT_Data.Position
    ,t_DVT_Data.Position_2
    ,t_DVT_Data.Position_3
    ,IIf([G>=1] = 1
        AND [Asprin] = 1, 1, 0) AS GwASA
    ,IIf(IsNull([VTE Prophylaxsis Used_2])
        AND [VTE Prophylaxsis Used] = "Medication (Pre&Post include low dose ASA Regime", 1, IIf([VTE Prophylaxsis Used] = "Medication (Pre&Post include low dose ASA Regime"
            AND [VTE Prophylaxsis Used_2] <> "Sequentials - L", 1, 0)) AS Asprin
    ,t_DVT_Data.[VTE Prophylaxsis Used]
    ,t_DVT_Data.[VTE Prophylaxsis Used_2]
    ,t_DVT_Data.[VTE Prophylaxsis Used_3]
    ,t_DVT_Data.[VTE Prophylaxsis Used_4]
    ,t_DVT_Data.[VTE Prophylaxsis Used_5]
    ,t_DVT_Data.[VTE Prophylaxsis Used_6]
    ,t_DVT_Data.[VTE Prophylaxsis Used_7]
    ,t_DVT_Data.[Therapy In Place Until LII]
    ,t_DVT_Data.[Location of Surgery]
    ,t_DVT_Data.[DVT_Comment(s)]
    ,t_DVT_Data.[DVT Occurred]
    ,t_DVT_Data.[DVT Tx]
FROM (
    t_Visit INNER JOIN t_DVT_Data
        ON t_Visit.vid = t_DVT_Data.vil
    )
INNER JOIN [t_Temperature _Monitoring]
    ON t_Visit.vid = [t_Temperature _Monitoring].vil
WHERE (
        (
            (t_Visit.VisitDate) BETWEEN [Forms] ! [Date Range Form] ! [Start Date]
                AND [Forms] ! [Date Range Form] ! [End Date]
            )
        AND ((t_DVT_Data.[Anesthesia Type]) = "General")
        AND ((Format([Anesthesia End Time] - [Anesthesia Start Time], "Short Time")) >= "01:00")
        AND (
            (t_DVT_Data.[VTE Prophylaxsis Used]) <> "Medication (Pre&Post include low dose ASA Regime"
            AND (t_DVT_Data.[VTE Prophylaxsis Used]) <> "Medication"
            )
        )
    OR (
        (
            (t_Visit.VisitDate) BETWEEN [Forms] ! [Date Range Form] ! [Start Date]
                AND [Forms] ! [Date Range Form] ! [End Date]
            )
        AND ((t_DVT_Data.[Anesthesia Type]) = "General")
        AND ((Format([Anesthesia End Time] - [Anesthesia Start Time], "Short Time")) >= "01:00")
        AND (
            (t_DVT_Data.[VTE Prophylaxsis Used_2]) <> "Medication (Pre&Post include low dose ASA Regime"
            AND (t_DVT_Data.[VTE Prophylaxsis Used_2]) <> "Medication"
            )
        )
    OR (
        (
            (t_Visit.VisitDate) BETWEEN [Forms] ! [Date Range Form] ! [Start Date]
                AND [Forms] ! [Date Range Form] ! [End Date]
            )
        AND ((t_DVT_Data.[Anesthesia Type]) = "General")
        AND ((Format([Anesthesia End Time] - [Anesthesia Start Time], "Short Time")) >= "01:00")
        AND (
            (t_DVT_Data.[VTE Prophylaxsis Used_3]) <> "Medication (Pre&Post include low dose ASA Regime"
            AND (t_DVT_Data.[VTE Prophylaxsis Used_3]) <> "Medication"
            )
        );

OOoops: I see plog has responded while I was typing... totally agree with using code tags

 

[danbl]

I will try to refine ……