-
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
((vtq.[Visit#])="Visit1"));
Please let me know if any other information is needed.
-
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
[SIGPIC][/SIGPIC]
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
-
Forum Rules
|
|