Query failing to pull results

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)
 
K

KARL DEWEY

Post the SQL of your query. Open in design view, click on VIEW - SQL View,
highlight all, copy, and paste in a post.
 
T

Takari

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.
 
K

KARL DEWEY

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...
 
T

Takari

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...
 
K

KARL DEWEY

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...
 
T

Takari

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...
 
K

KARL DEWEY

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Date query from datasheet in subform 1
Query shows only one record 3
query to pull between two dates 4
Query vs Report 2
Multi-Value Field Query 0
Query to pull all records for those missing records 5
Query with <= Parameter 2
Query to pull most recent data 3

Top