please help with query logic

[nemo700]

Access is making my brain hurt - can anybody please help?

Here's what I have and what I am trying to get it to do...

I have a table, maba_registrations, into which students are entered according to which course they are doing in which semester - eg -

autonumber  student_id   course_code   semester   year
1           1            A123          1          2006
2           2            A124          1          2006
3           2            A124          2          2006
4           2            A126          1          2006
5           3            A132          1          2006

What I need to do is to pull four subsets of data out of this table -

1) A unique list of courses that student x is taking in semester 1 (only) of a given year
2) A unique list of courses that student x is taking in semester 2 (only) of a given year
3) A unique list of courses that student x is taking over 2 semesters (both semester 1 and semester 2 of a given year)
4) A unique list of courses that student x is taking over 2 semesters (semester 2 of a given year and semester 1 of the following year)

Obviously there should be no overlap... and that's where I get a bit stuck

I can craft a simple query that will show me the courses a student is taking in semester 1 of a given year, but I cannot figure out how to get it to exclude those courses which are also still being taken in semester 2. In the above example, the query pulls out records 2 and 4 for student 2; I only want record 4.

I think what I need is to have 2 queries -

a) pulls out all courses for student x in semester 1 for the current year
b) pulls out all courses for student x in semester 2 for the current year

- and then subtract the results of query (b) from query (a) (ie remove the rows that are matched). Is there any way to do something like that?

Or is there a simple part of query syntax that I can use to make a query that just says "pull out all the courses for student x where semester=1 and there is no row for this course and this student where semester=2"?

Any ideas would be hugely appreciated!

 

[ByteMyzer]

Try these:

1)

SELECT M1.student_id, M1.course_code, M1.semester, M1.year
FROM maba_registrations AS M1
WHERE M1.semester=1
AND NOT EXISTS
 (SELECT M2.*
  FROM maba_registrations AS M2
  WHERE M2.student_id=M1.student_id
  AND M2.course_code=M1.course_code
  AND (M2.semester<>M1.semester OR M2.year<>M1.year)
 );

2)

SELECT M1.student_id, M1.course_code, M1.semester, M1.year
FROM maba_registrations AS M1
WHERE M1.semester=2
AND NOT EXISTS
 (SELECT M2.*
  FROM maba_registrations AS M2
  WHERE M2.student_id=M1.student_id
  AND M2.course_code=M1.course_code
  AND (M2.semester<>M1.semester OR M2.year<>M1.year)
 );

3)

SELECT M1.student_id, M1.course_code, M1.year
FROM maba_registrations AS M1
WHERE M1.semester=1
AND EXISTS
 (SELECT M2.*
  FROM maba_registrations AS M2
  WHERE M2.student_id=M1.student_id
  AND M2.course_code=M1.course_code
  AND M2.semester=2
  AND M2.year=M1.year
 );

4)

SELECT M1.student_id, M1.course_code, M1.year
FROM maba_registrations AS M1
WHERE M1.semester=2
AND EXISTS
 (SELECT M2.*
  FROM maba_registrations AS M2
  WHERE M2.student_id=M1.student_id
  AND M2.course_code=M1.course_code
  AND M2.semester=1
  AND M2.year=M1.year+1
 );

 

[nemo700]

Fantastic!

That's exactly what I've spent the last 6 hours trying to achieve.

You are my personal hero for the day. Thank you very much!

 

[ByteMyzer]

You're very welcome; glad I could help.

 

[nemo700]

Just to revisit this one, if I may...

The above solution works fine, so obviously now I've been trying to do something a little more clever with it.

I have successfully linked the tables underlying the queries, so that they can look up some course code information in another table. What I am trying to do now is to put an unbound "query year" text box onto a form, so that a user can enter a year, press a button, and run the 4 queries for that year... the idea being that you will want to look at a different set of semesters for a given student for 2006, 2007, etc

The control is passed to the queries via [Forms]![people_maba]![qyear], and all is well and good. It's the query logic that's making me stumble again... I have the following:

1)

SELECT M1.person, M1.module, M1.semester, M1.year, maba_modules.angliacode, maba_modules.title
FROM maba_modules INNER JOIN maba_registrations AS M1 ON maba_modules.id = M1.module
WHERE (((M1.semester)=1) AND ((M1.year)=[Forms]![people_maba]![qyear]) AND ((Exists (SELECT M2.* FROM maba_registrations AS M2 WHERE M2.person=M1.person AND M2.module=M1.module AND M2.year=[Forms]![people_maba]![qyear] AND (M2.semester<>M1.semester OR M2.year<>M1.year)  ))=False));

2)

SELECT M1.person, M1.module, M1.semester, M1.year, maba_modules.angliacode, maba_modules.title
FROM maba_modules INNER JOIN maba_registrations AS M1 ON maba_modules.id = M1.module
WHERE (((M1.semester)=2) AND ((M1.year)=[Forms]![people_maba]![qyear]) AND ((Exists (SELECT M2.* FROM maba_registrations AS M2 WHERE M2.person=M1.person AND M2.module=M1.module AND M2.year=[Forms]![people_maba]![qyear] AND (M2.semester<>M1.semester OR M2.year<>M1.year)  ))=False));

3)

SELECT M1.person, M1.module, M1.year, maba_modules.angliacode, maba_modules.title
FROM maba_modules INNER JOIN maba_registrations AS M1 ON maba_modules.id = M1.module
WHERE (((M1.year)=[Forms]![people_maba]![qyear]) AND ((M1.semester)=1) AND ((Exists (SELECT M2.* FROM maba_registrations AS M2 WHERE M2.person=M1.person AND M2.module=M1.module AND M2.semester=2 AND M2.year=[Forms]![people_maba]![qyear]))<>False));

4)

SELECT M1.person, M1.module, M1.year, maba_modules.angliacode, maba_modules.title
FROM maba_modules INNER JOIN maba_registrations AS M1 ON maba_modules.id = M1.module
WHERE (((M1.year)=[Forms]![people_maba]![qyear]) AND ((M1.semester)=2) AND ((Exists (SELECT M2.* FROM maba_registrations AS M2 WHERE M2.person=M1.person AND M2.module=M1.module AND M2.semester=1 AND M2.year=[Forms]![people_maba]![qyear]+1 ))<>False));

It looks like 3 and 4 are behaving as I would expect, but certainly 2 isn't (although 1 seems to be, even though the two queries are fundamentally the same); query 2 seems to pick up stuff that also (rightly) appears under query 3.

I'm sure it's something simple that I've got wrong, but you know how tunnel vision sets in if you stare at a problem for too long. Can anyone help me spot the "deliberate" mistake?