Query failing to pull results

  • Thread starter Thread starter Takari
  • Start date Start date
T

Takari

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)
 
Post the SQL of your query. Open in design view, click on VIEW - SQL View,
highlight all, copy, and paste in a post.
 
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.
 
Try changing WHERE to this --
WHERE ((([Survey Diabetes].[Survey#])="1st Survey") AND (([qry:Risk
Results].[Survey#])="1st Survey") AND
(([Visit Tracking Query].[Visit#])="Visit1"));

If that is not it then try changing FROM to this --
FROM (((IntakeTBL AS i INNER JOIN [Visit Tracking Query] AS vtq ON
i.[PT_Account#]=vtq.[PT_Account#]) LEFT JOIN [qry:Risk Results] AS rr ON
i.[PT_Account#]=rr.[PT_Account #]) LEFT JOIN [Lab Tracking] AS lt ON
i.[PT_Account#]=lt.[PT_Account#]) LEFT JOIN [Survey Diabetes] AS sd ON
i.[PT_Account#]=sd.[PT_Account #]
WHERE ((([Survey Diabetes].[Survey#])="1st Survey") AND (([qry:Risk
Results].[Survey#])="1st Survey") AND
(([Visit Tracking Query].[Visit#])="Visit1"));

--
KARL DEWEY
Build a little - Test a little


Takari said:
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.
Click to expand...
 
Both of these solutions made two more pop ups appear for user input when the
query was run("qry:Risk" and "Visit Tracking Query.Visit#"), there should
only be one (for the patient account number).

:-S

KARL DEWEY said:
Try changing WHERE to this --
WHERE ((([Survey Diabetes].[Survey#])="1st Survey") AND (([qry:Risk
Results].[Survey#])="1st Survey") AND
(([Visit Tracking Query].[Visit#])="Visit1"));

If that is not it then try changing FROM to this --
FROM (((IntakeTBL AS i INNER JOIN [Visit Tracking Query] AS vtq ON
i.[PT_Account#]=vtq.[PT_Account#]) LEFT JOIN [qry:Risk Results] AS rr ON
i.[PT_Account#]=rr.[PT_Account #]) LEFT JOIN [Lab Tracking] AS lt ON
i.[PT_Account#]=lt.[PT_Account#]) LEFT JOIN [Survey Diabetes] AS sd ON
i.[PT_Account#]=sd.[PT_Account #]
WHERE ((([Survey Diabetes].[Survey#])="1st Survey") AND (([qry:Risk
Results].[Survey#])="1st Survey") AND
(([Visit Tracking Query].[Visit#])="Visit1"));

--
KARL DEWEY
Build a little - Test a little


Takari said:
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.
Click to expand...
Click to expand...
 
Sometimes copying a post and pasting adds hard returns that should not be
there.
"qry:Risk" should be like this --
(([qry:Risk Results].[Survey#])=
or
LEFT JOIN [qry:Risk Results] AS

"Visit Tracking Query.Visit#" should be like this --
(([Visit Tracking Query].[Visit#])=
or
[Visit Tracking Query] AS
based on where in the query you are looking.

--
KARL DEWEY
Build a little - Test a little


Takari said:
Both of these solutions made two more pop ups appear for user input when the
query was run("qry:Risk" and "Visit Tracking Query.Visit#"), there should
only be one (for the patient account number).

:-S

KARL DEWEY said:
Try changing WHERE to this --
WHERE ((([Survey Diabetes].[Survey#])="1st Survey") AND (([qry:Risk
Results].[Survey#])="1st Survey") AND
(([Visit Tracking Query].[Visit#])="Visit1"));

If that is not it then try changing FROM to this --
FROM (((IntakeTBL AS i INNER JOIN [Visit Tracking Query] AS vtq ON
i.[PT_Account#]=vtq.[PT_Account#]) LEFT JOIN [qry:Risk Results] AS rr ON
i.[PT_Account#]=rr.[PT_Account #]) LEFT JOIN [Lab Tracking] AS lt ON
i.[PT_Account#]=lt.[PT_Account#]) LEFT JOIN [Survey Diabetes] AS sd ON
i.[PT_Account#]=sd.[PT_Account #]
WHERE ((([Survey Diabetes].[Survey#])="1st Survey") AND (([qry:Risk
Results].[Survey#])="1st Survey") AND
(([Visit Tracking Query].[Visit#])="Visit1"));

--
KARL DEWEY
Build a little - Test a little


Takari said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Yes, when I pasted it into access they had this format :(

KARL DEWEY said:
Sometimes copying a post and pasting adds hard returns that should not be
there.
"qry:Risk" should be like this --
(([qry:Risk Results].[Survey#])=
or
LEFT JOIN [qry:Risk Results] AS

"Visit Tracking Query.Visit#" should be like this --
(([Visit Tracking Query].[Visit#])=
or
[Visit Tracking Query] AS
based on where in the query you are looking.

--
KARL DEWEY
Build a little - Test a little


Takari said:
Both of these solutions made two more pop ups appear for user input when the
query was run("qry:Risk" and "Visit Tracking Query.Visit#"), there should
only be one (for the patient account number).

:-S

KARL DEWEY said:
Try changing WHERE to this --
WHERE ((([Survey Diabetes].[Survey#])="1st Survey") AND (([qry:Risk
Results].[Survey#])="1st Survey") AND
(([Visit Tracking Query].[Visit#])="Visit1"));

If that is not it then try changing FROM to this --
FROM (((IntakeTBL AS i INNER JOIN [Visit Tracking Query] AS vtq ON
i.[PT_Account#]=vtq.[PT_Account#]) LEFT JOIN [qry:Risk Results] AS rr ON
i.[PT_Account#]=rr.[PT_Account #]) LEFT JOIN [Lab Tracking] AS lt ON
i.[PT_Account#]=lt.[PT_Account#]) LEFT JOIN [Survey Diabetes] AS sd ON
i.[PT_Account#]=sd.[PT_Account #]
WHERE ((([Survey Diabetes].[Survey#])="1st Survey") AND (([qry:Risk
Results].[Survey#])="1st Survey") AND
(([Visit Tracking Query].[Visit#])="Visit1"));

--
KARL DEWEY
Build a little - Test a little


:

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.
Click to expand...
Click to expand...
Click to expand...
 
To help locate where the syntax is in error try entering an X one time and
look over the results and then a 9 the next time.
--
KARL DEWEY
Build a little - Test a little


Takari said:
Yes, when I pasted it into access they had this format :(

KARL DEWEY said:
Sometimes copying a post and pasting adds hard returns that should not be
there.
"qry:Risk" should be like this --
(([qry:Risk Results].[Survey#])=
or
LEFT JOIN [qry:Risk Results] AS

"Visit Tracking Query.Visit#" should be like this --
(([Visit Tracking Query].[Visit#])=
or
[Visit Tracking Query] AS
based on where in the query you are looking.

--
KARL DEWEY
Build a little - Test a little


Takari said:
Both of these solutions made two more pop ups appear for user input when the
query was run("qry:Risk" and "Visit Tracking Query.Visit#"), there should
only be one (for the patient account number).

:-S

:

Try changing WHERE to this --
WHERE ((([Survey Diabetes].[Survey#])="1st Survey") AND (([qry:Risk
Results].[Survey#])="1st Survey") AND
(([Visit Tracking Query].[Visit#])="Visit1"));

If that is not it then try changing FROM to this --
FROM (((IntakeTBL AS i INNER JOIN [Visit Tracking Query] AS vtq ON
i.[PT_Account#]=vtq.[PT_Account#]) LEFT JOIN [qry:Risk Results] AS rr ON
i.[PT_Account#]=rr.[PT_Account #]) LEFT JOIN [Lab Tracking] AS lt ON
i.[PT_Account#]=lt.[PT_Account#]) LEFT JOIN [Survey Diabetes] AS sd ON
i.[PT_Account#]=sd.[PT_Account #]
WHERE ((([Survey Diabetes].[Survey#])="1st Survey") AND (([qry:Risk
Results].[Survey#])="1st Survey") AND
(([Visit Tracking Query].[Visit#])="Visit1"));

--
KARL DEWEY
Build a little - Test a little


:

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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top