Results 1 to 2 of 2

Thread: Access SQL query failing to pull information on new patients

  1. #1
    Join Date
    Mar 2009

    Access SQL query failing to pull information on new patients

    I maintain a database for a doctors office and recently a query that is used
    to pull information for a report has stopped working. The query will pull the
    information on old patients, but fails to do so on the newer ones (I have
    already checked to see if it is a problem with the magnitude of the patient
    number). Below is my SQL code.

    SELECT DISTINCT i.[PT_Account#], i.DFV, i.Mr_Mrs_Ms, i.Questionnair,
    i.PLname, i.PFname, i.PMI, i.DOB, i.[SS#], i.Sex, i.Street, i.PCity,
    i.PState, i.PZip, i.PHPhone, i.PWPhone, i.P_email, i.Race, i.P_Birth,
    i.Hrs_work, i.Phys_LName, i.Phys_FName, i.Phys_Phone, i.Phys_Address,
    i.Phys_City, i.Phys_ST, i.Phys_Zip, i.Condition1, i.Condition2, i.Condition3,
    i.Condition4, i.Condition5, i.Condition6, i.Med1, i.Dose1, i.Med2, i.Dose2,
    i.Med3, i.Dose3, i.Med4, i.Dose4, i.Med5, i.Dose5, i.Med6, i.Dose6, i.Med7,
    i.Dose7, i.Med8, i.Dose8, i.Med9, i.Dose9, i.Med10, i.Dose10, [Med1] & " " &
    [med2] & " " & [med3] & " " & [med4] & " " & [med5] & " " & [med6] & " " &
    [med6] & " " & [med7] & " " & [med8] & " " & [med9] & " " & [med10] AS Meds,
    [Med1] & [med2] & [med3] & [med4] & [med5] & [med6] & [med6] & [med7] &
    [med8] & [med9] & [med10] AS Meds_cfc, i.Procedure1, i.Pro_Date1,
    i.Procedure2, i.Pro_Date2, i.Procedure3, i.Pro_Date3, i.Procedure4,
    i.Pro_Date4, i.Procedure5, i.Pro_Date5, i.Allergies, i.Allergy_type1,
    i.Allergy_type2, i.Allergy_type3, i.Exercise AS IntakeTBL_Exercise,
    i.Exer_Reason, i.Weight_Gain, i.Diabetes_Education, i.Blood_Glucose,
    i.Blood_Pressure, i.Lipid, i.Other, i.Progress_Report, i.Notification,
    i.Contact_Info, i.Mailing, i.On_research, i.Referred, i.G_Lname, i.G_Fname,
    i.G_MI, i.G_Address, i.G_City, i.G_State, i.G_Zip, i.G_Hphone, i.G_Wphone,
    i.relationship, i.Insulin, i.Med_adjustments, i.Nutrition_Diab,
    i.Carbohydrate, i.Exercise_Diabetes, i.Alcohol, i.Pregnancy, i.Monit_Blood,
    i.Low_High_Sugar, i.Stress, i.Diabetes_complications, i.Sick_days,
    i.Insulin_pump, i.Reviewed, i.Rev_Date, sd.[Survey#], sd.DM_Scale,
    sd.Psychosocial_Scale, sd.Nutritional_Scale, sd.PhysicalActivity_Scale,
    sd.SelfManagement_Scale, sd.[Which Program?], sd.Height_cm, sd.Weight_kg,
    sd.BP, sd.Random_Glucose, [Weight_kg]/([Height_cm]*[Height_cm])*10000 AS BMI,
    rr.[Survey#], rr.DMRisk, rr.Nutritional, rr.Exercise, rr.Psychosocial,
    rr.SelfManagement, rr.ProviderDMRisk, vtq.VDate, vtq.Lab_Findings_Comments,
    vtq.Listed_LT_Comment, vtq.Provider_Comments, vtq.[Visit#]
    FROM (((IntakeTBL AS i INNER JOIN [Visit Tracking Query] AS vtq ON
    i.[PT_Account#]=vtq.[PT_Account#]) INNER JOIN [qry:Risk Results] AS rr ON
    i.[PT_Account#]=rr.[PT_Account #]) INNER JOIN [Lab Tracking] AS lt ON
    i.[PT_Account#]=lt.[PT_Account#]) INNER JOIN [Survey Diabetes] AS sd ON
    i.[PT_Account#]=sd.[PT_Account #]
    WHERE (((sd.[Survey#])="1st Survey") AND ((rr.[Survey#])="1st Survey") AND

    Please let me know if any other information is needed.

  2. #2
    Join Date
    Mar 2006
    Oklahoma City, OK
    t is really had to just look at a query to see an issue unless it is a syntax problem. It could be a data issue.

    Have you made a backup and then compacted the data (back end) lately?

    In what way has it "stopped working"?

    Are you getting an error message?
    Boyd Trimmell aka HiTech Coach
    Microsoft MVP - Access Expert
    Office Programming 25+ years as a Software Developer specializing in:
    Business Process Management
    Accounting/Inventory Control
    Customer Relations Management (CRM)
    Electronic Data Interchange (EDI)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts