My problem is as follows: I am trying to pull data from four tables where a one table may not have data. How do I write the query that retrieves all the data from the ETS_STUDENT, ETS_ENROLLMENT, ETS_SCHOOL_DATA tables reguardless of the data in ETS_INTERNAL. ie... If the there is data in ETS_INTERNAL include it, else not. But still print the data in the other three tables.
This is my current code.
SELECT ETS_STUDENT.LAST_NAME, ETS_STUDENT.FIRST_NAME, ETS_STUDENT.ETS_SCHOOL, ETS_STUDENT.ETS_GRADE, ETS_SCHOOL_DATA.ETS_SCHEDULE_BLOCK, ETS_SCHOOL_DATA.ETS_SCHEDULE_TEACHER, ETS_SCHOOL_DATA.ETS_SCHEDULE_ROOM, ETS_ENROLLMENT.ETS_PROGRAM_STATUS, ETS_INTERNAL.OTHER_DESC
FROM ((ETS_STUDENT INNER JOIN ETS_ENROLLMENT ON ETS_STUDENT.ETS_ID = ETS_ENROLLMENT.ETS_ID) INNER JOIN ETS_INTERNAL ON ETS_STUDENT.ETS_ID = ETS_INTERNAL.ETS_ID) INNER JOIN ETS_SCHOOL_DATA ON ETS_STUDENT.ETS_ID = ETS_SCHOOL_DATA.ETS_ID
WHERE (((ETS_STUDENT.ETS_ID)=[ETS_ENROLLMENT]![ETS_ID] And (ETS_STUDENT.ETS_ID)=[ETS_SCHOOL_DATA]![ETS_ID] And (ETS_STUDENT.ETS_ID)=[ETS_INTERNAL]![ETS_ID]));
Thank you for your time.
MikeI figured it out. I created a sub query as follows then called the sub query from the main query.
Sub:
SELECT ETS_STUDENT.ETS_ID, ETS_INTERNAL.OTHER_DESC
FROM ETS_STUDENT LEFT JOIN ETS_INTERNAL ON ETS_STUDENT.ETS_ID = ETS_INTERNAL.ETS_ID;
Main:
SELECT ETS_STUDENT.LAST_NAME, ETS_STUDENT.FIRST_NAME, ETS_STUDENT.ETS_SCHOOL, ETS_STUDENT.ETS_GRADE, ETS_SCHOOL_DATA.ETS_SCHEDULE_BLOCK, ETS_SCHOOL_DATA.ETS_SCHEDULE_TEACHER, ETS_SCHOOL_DATA.ETS_SCHEDULE_ROOM, ETS_ENROLLMENT.ETS_PROGRAM_STATUS, [MKsPassesQuery(sub)].OTHER_DESC
FROM ((ETS_STUDENT INNER JOIN [MKsPassesQuery(sub)] ON (ETS_STUDENT.ETS_ID = [MKsPassesQuery(sub)].ETS_ID) AND (ETS_STUDENT.ETS_ID = [MKsPassesQuery(sub)].ETS_ID)) INNER JOIN ETS_ENROLLMENT ON ETS_STUDENT.ETS_ID = ETS_ENROLLMENT.ETS_ID) INNER JOIN ETS_SCHOOL_DATA ON ETS_STUDENT.ETS_ID = ETS_SCHOOL_DATA.ETS_ID
WHERE (((ETS_SCHOOL_DATA.ETS_ID)=[ETS_STUDENT]![ETS_ID]) AND ((ETS_ENROLLMENT.ETS_ID)=[ETS_STUDENT]![ETS_ID]));
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment