Max function between 2 tables

[fawnlemur]

Hi



I have 3 tables,

1 will hold the courses that are completed by student.

table1 - Student name - student id - course id - course name - course completion date
table2 - course id - course name
table3 - course name - course id1 - course id2


so I pull the data of completed courses and add them to table1

table2 will have the list of courses - each course has 2 version (Audio and written) they each have a different course ID

on table 3 I want the course name and audio and written ID's


and on the query I want to pull the student name with the course completion date (I want it to compare the completion date of the audio and written course and pull the Max date between them)


I know how to do this in excel but I've new to access, can someone please explain to how I can do this.

sorry if didn't explain it properly

[ArviLaanemets]

tblStudents: StudentID, ForeName, LastName, ...;
tblCourses: CourseID, CourseStartYear/CourseStartDate, CourseName, CourseVersion (Version values: 1 - audio, 2 - written, with same name and CourseStartYear/CourseStartDate can be 2 different versions);
tblStudentCourses: SCID, StudentID, CourseID, CompletedDate.

The query will be something like (I'm written this on fly and I am not very comfortable with Access query syntax and weird usage of brackets in it, as lately I use SQL Server Databases as backend exclusively)

SELECT
std.StudentID, std.ForeName, std.LastName, crs1.CourseName, crs1.CourseStartYear, stc1.CompletedDate AS [AudioCompleted], stc2.CompletedDate AS [WrittenCompleted]
FROM tblStudents std 
LEFT JOIN (((tblStudentCourses stc1) ON stc1.StudentID = std.StudentID LEFT JOIN tblCOurses crs1 ON crs1.CourseID = stc1.CourseID)
LEFT JOIN tblStudentCourses stc2 ON stc2.StudentID = std.StudentID LEFT JOIN tblCOurses crs2 ON crs2.CourseID = stc2.CourseID AND crs2.CourseStartYear = crs1.CourseStartyear AND crs2.CourseName = crs1.CourseName)
WHERE 
 crs1.COurseVersion = 1 AND crs2.COurseVersion = 2

[fawnlemur]

tblStudents: StudentID, ForeName, LastName, ...;
tblCourses: CourseID, CourseStartYear/CourseStartDate, CourseName, CourseVersion (Version values: 1 - audio, 2 - written, with same name and CourseStartYear/CourseStartDate can be 2 different versions);
tblStudentCourses: SCID, StudentID, CourseID, CompletedDate.

SELECT

FROM ((tblStudents std) LEFT JOIN tblStudentCourses stc1 ON stc.StudentID = std.StudentID) LEFT JOIN tblStudentCourses stc2 ON stc2,StudentID = std,studentID
WHERE stc1.CourseVersion =1 AND stc2.CourseVersion = 2 AND stc2.CourseStartYear = stc1.CourseStartYear

Thank you for your reply. but can explain how this works to me please, i'm new to access and sql

[ArviLaanemets]

As forum saved my post accidently when I was writing it jet, I edited it a lot later.

I'm not best person to explain here, as syntax SQL Server uses for linking tables in queries is entirely different. I'm even not entirely sure the query I posted will work in Access without serious editing (I searched google a bit, and tried to get something together based on what I read) - it is here to give you a general direction only.

[ArviLaanemets]

In attachment is an working example with dummy dates in tables. The final query which probably returns what you want is qStudentCourseStatus.

Maybe it is possible to reduce the number of saved queries, but I don't have time to spend more on it.

[fawnlemur]

Thanks for this