Query not pulling data anymore, do you have any suggestions?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

This query here isn't pulling current data from the tables any more and I
can't figure out why:

SELECT Adverse_events.Date_Of_Entry, Adverse_events.Date_Of_Onset,
Adverse_events.Date_Of_Resolution, Adverse_events.Severity,
Adverse_events.Adverse_Event, Adverse_events.Outcome,
Adverse_events.Reviewed_With_PI, Adverse_events.Description_Of_Event,
Adverse_events.Event_Type, [project coordinator].[date consent return],
[project coordinator].randomization, TableRegistry.first, TableRegistry.last,
TableRegistry.birthDate, TableRegistry.gender, TableRegistry.race,
TableRegistry.ethnicity, Adverse_events.interviewid,
Adverse_events.Visit_Num, Adverse_events.eventId, Adverse_events.Interviewer,
QueryBaselineSessions.date
FROM ((TableRegistry INNER JOIN TableStudyIdSSN ON TableRegistry.SSN =
TableStudyIdSSN.ssn) INNER JOIN (Adverse_events INNER JOIN [project
coordinator] ON Adverse_events.interviewid = [project
coordinator].InterviewID) ON TableStudyIdSSN.studyid = [project
coordinator].InterviewID) INNER JOIN QueryBaselineSessions ON
Adverse_events.interviewid = QueryBaselineSessions.interviewId;



Also here is the query QueryBaselineSessions
SELECT TableSessionsCompleted.*
FROM TableSessionsCompleted
WHERE (((TableSessionsCompleted.visit)=0));
 
pokdbz said:
This query here isn't pulling current data from the tables any more and I
can't figure out why:

SELECT Adverse_events.Date_Of_Entry, Adverse_events.Date_Of_Onset,
Adverse_events.Date_Of_Resolution, Adverse_events.Severity,
Adverse_events.Adverse_Event, Adverse_events.Outcome,
Adverse_events.Reviewed_With_PI, Adverse_events.Description_Of_Event,
Adverse_events.Event_Type, [project coordinator].[date consent return],
[project coordinator].randomization, TableRegistry.first, TableRegistry.last,
TableRegistry.birthDate, TableRegistry.gender, TableRegistry.race,
TableRegistry.ethnicity, Adverse_events.interviewid,
Adverse_events.Visit_Num, Adverse_events.eventId, Adverse_events.Interviewer,
QueryBaselineSessions.date
FROM ((TableRegistry INNER JOIN TableStudyIdSSN ON TableRegistry.SSN =
TableStudyIdSSN.ssn) INNER JOIN (Adverse_events INNER JOIN [project
coordinator] ON Adverse_events.interviewid = [project
coordinator].InterviewID) ON TableStudyIdSSN.studyid = [project
coordinator].InterviewID) INNER JOIN QueryBaselineSessions ON
Adverse_events.interviewid = QueryBaselineSessions.interviewId;

Also here is the query QueryBaselineSessions
SELECT TableSessionsCompleted.*
FROM TableSessionsCompleted
WHERE (((TableSessionsCompleted.visit)=0));
Click to expand...


I can't figure it out either.

What data is it getting?

Are you sure **all** those INNER JOIN tables actually have
matching records for **all** the records you expect to find?

Are you sure that the expected records have their Visit
field set to zero?
 
It is not pulling anything new in the query. It pulls the old data but not
the new. Yea, pretty sure that all the inner joins are correct.

Why do the expected records for the visit have to be set to 0?

Thank you


Marshall Barton said:
pokdbz said:
This query here isn't pulling current data from the tables any more and I
can't figure out why:

SELECT Adverse_events.Date_Of_Entry, Adverse_events.Date_Of_Onset,
Adverse_events.Date_Of_Resolution, Adverse_events.Severity,
Adverse_events.Adverse_Event, Adverse_events.Outcome,
Adverse_events.Reviewed_With_PI, Adverse_events.Description_Of_Event,
Adverse_events.Event_Type, [project coordinator].[date consent return],
[project coordinator].randomization, TableRegistry.first, TableRegistry.last,
TableRegistry.birthDate, TableRegistry.gender, TableRegistry.race,
TableRegistry.ethnicity, Adverse_events.interviewid,
Adverse_events.Visit_Num, Adverse_events.eventId, Adverse_events.Interviewer,
QueryBaselineSessions.date
FROM ((TableRegistry INNER JOIN TableStudyIdSSN ON TableRegistry.SSN =
TableStudyIdSSN.ssn) INNER JOIN (Adverse_events INNER JOIN [project
coordinator] ON Adverse_events.interviewid = [project
coordinator].InterviewID) ON TableStudyIdSSN.studyid = [project
coordinator].InterviewID) INNER JOIN QueryBaselineSessions ON
Adverse_events.interviewid = QueryBaselineSessions.interviewId;

Also here is the query QueryBaselineSessions
SELECT TableSessionsCompleted.*
FROM TableSessionsCompleted
WHERE (((TableSessionsCompleted.visit)=0));
Click to expand...


I can't figure it out either.

What data is it getting?

Are you sure **all** those INNER JOIN tables actually have
matching records for **all** the records you expect to find?

Are you sure that the expected records have their Visit
field set to zero?
Click to expand...
 
The WHERE clause in QueryBaselineSessions only includes
records where Visit = 0.

I think this is really a debugging issue. Try using a copy
of the big query and remove the Joined tables and their
fields and see what you get. If it's correct, add the Joins
back in one at a time until you spot the issue.
--
Marsh
MVP [MS Access]

It is not pulling anything new in the query. It pulls the old data but not
the new. Yea, pretty sure that all the inner joins are correct.

Why do the expected records for the visit have to be set to 0?

pokdbz said:
This query here isn't pulling current data from the tables any more and I
can't figure out why:

SELECT Adverse_events.Date_Of_Entry, Adverse_events.Date_Of_Onset,
Adverse_events.Date_Of_Resolution, Adverse_events.Severity,
Adverse_events.Adverse_Event, Adverse_events.Outcome,
Adverse_events.Reviewed_With_PI, Adverse_events.Description_Of_Event,
Adverse_events.Event_Type, [project coordinator].[date consent return],
[project coordinator].randomization, TableRegistry.first, TableRegistry.last,
TableRegistry.birthDate, TableRegistry.gender, TableRegistry.race,
TableRegistry.ethnicity, Adverse_events.interviewid,
Adverse_events.Visit_Num, Adverse_events.eventId, Adverse_events.Interviewer,
QueryBaselineSessions.date
FROM ((TableRegistry INNER JOIN TableStudyIdSSN ON TableRegistry.SSN =
TableStudyIdSSN.ssn) INNER JOIN (Adverse_events INNER JOIN [project
coordinator] ON Adverse_events.interviewid = [project
coordinator].InterviewID) ON TableStudyIdSSN.studyid = [project
coordinator].InterviewID) INNER JOIN QueryBaselineSessions ON
Adverse_events.interviewid = QueryBaselineSessions.interviewId;

Also here is the query QueryBaselineSessions
SELECT TableSessionsCompleted.*
FROM TableSessionsCompleted
WHERE (((TableSessionsCompleted.visit)=0));
Click to expand...
Click to expand...
Marshall Barton said:
I can't figure it out either.

What data is it getting?

Are you sure **all** those INNER JOIN tables actually have
matching records for **all** the records you expect to find?

Are you sure that the expected records have their Visit
field set to zero?
Click to expand...
Click to expand...
 
Back
Top