MS Access. 3 identical tables, get DISTINCT listing

PasturesNew

I have three tables (for simplicity, I'll call these students as this makes it easier to understand).

Table 1: a snapshot of students in 2007, called "students 2007"
Table 2: a snapshot of students in 2008, called "students 2008"
Table 3: a snapshot of students in 2009, called "students 2009"

I wish to do a query to find out how many students there were in total and how many left in each year.

e.g.
In 2007, Alan, Bob and Charles were enrolled. Charles left.
In 2008, Alan and Bob were still enrolled, so was Dave. Bob left.
In 2009, Alan, Dave and Eric and Frank were enrolled. Alan and Dave left.

If I just ran the report: "How many students were there - and how many left" per year, I'd get
2007: 3 students, 1 left
2008: 3 students, 1 left
2009: 4 students, 2 left

Total these and there were 10 students, 4 left.

In reality there were only 6 students from 2007-2009 (Alan, Bob, Charles, Dave, Eric and Frank) and 4 left. By reporting separately it inflates the number of students, skewing the final value that shows 4/6 students leave (66%) not 4/10 (40%).

I hope that makes sense.... the real report is on entirely different data, and for a wider span of years, but I did this format as people can understand students being enrolled and leaving in different years.

Thanks.

How can I join the three tables together, to give a distinct list of students?

If you want to go the whole hog:
Table names: "students 2007", "students 2008", "students 2009"
Field names: StudentID, StudentEndDate, ClassDate
(ClassDate would be an entry per date that each student turns up and is not required in the end report)

End report should look like:

StudentID, StudentEndDate

Cheers

RobTang

Awful query but I can't think of anything better atm, table structure doesn't really help.

SELECT t.studentid , t.studentenddate as StudentLeft
FROM (SELECT studentid, studentenddate from students2007 union SELECT studentid, studentenddate from students2008 union SELECT studentid, studentenddate from students2009 ) as t
where t.studentenddate <> NULL
union
SELECT s.studentid , NULL as StudentLeft
FROM (SELECT studentid, studentenddate from students2007 union SELECT studentid, studentenddate from students2008 union SELECT studentid, studentenddate from students2009 ) as s
group by s.studentid
having count(s.studentenddate) = 0

PasturesNew

Cheers, that's a pig of a query isn't it
That's enough to get me started.
Thanks a lot!

It'd have taken me about 3 hours from new/scratch I figure
And 6 cigs.

Mobeer

RobTang's query seems to work ok if students only do one course.

But if for example student ID=1 has left according to the [students 2007] table, then comes back to do another course in [students 2009], then the output doesn't exactly give unique students:


studentid StudentLeft 1 01/01/2008 1 01/01/2010 2 01/01/2009 3 01/01/2008 4 01/01/2010 5
6


Note student 1 appears twice


If you create one query [q_students_all_years] to merge all the tables together:

SELECT * FROM [students 2007]
UNION SELECT * FROM [students 2008]
UNION SELECT * FROM [students 2009];

Then another query based upon this:
SELECT StudentID, Max(StudentEndDate) AS last_end_date
FROM q_students_all_years
GROUP BY StudentID
ORDER BY StudentID;

gives distinct rows for student IDs with the last date a student left any course:

StudentID last_end_date 1 01/01/2010 2 01/01/2009 3 01/01/2008 4 01/01/2010 5
6

DVardysShadow

Your database model looks wrong. You have a many to many relationship between Students and YearGroups.

You need a data table which contains all Students and you need a data entity called Enrollment which relates 1 Student to 1 YearGroup. The list of Enrollments is effectively a merge of your 3 tables. The problem with the 3 distinct tables is that there is no data integrity between tables - so Charles on one could be described as Charlie on another - and there is no guarantee that Charles on one is definitely the Charles on another.

Or something like that. I never did like databases.