SQL for Confused Join

[gazzieh]

I am writing myself into circles here and maybe someone can help me.

I have two tables: tbl_course_units and tbl_units



The key fields of interest are:

tbl_course_units: course_id, unit_id
tbl_units: unit_id, unit_no, unit_title, int_level

I want to list all records in tbl_units where:

• int_level = 3
• the unit_id does not have a corresponding record in tbl_course_units where course_id=2

I can easily get the first and I can easily join:

• SELECT unit_id, unit_no, unit_title FROM tbl_units WHERE int_level=3
• SELECT unit_id, unit_no, unit_title FROM tbl_units LEFT JOIN tbl_course_units ON tbl_units.unit_id=tbl_units.unit_id WHERE int_level=3

However, if I add to the WHERE such that it reads 'AND NOT course_id=2' then that simply lists all units where there are records existing in both tables but not those records where course_id=2.

I believe I want to subtract two queries: one that lists all units and one that lists all units that have a course_id=3

• SELECT unit_id, unit_no, unit_title FROM tbl_units WHERE int_level=3
• SELECT unit_id FROM tbl_course_units WHERE course_id=2

And that is where I grind to a halt because I cannot for the life of me work out how to subtract the second set from the first.

I then need to shift this to VBA:

• set rst = currentdb.recordset('...')

How would I subtract the results from two recordset variables into a third? Or am I talking drivel again?

[orange]

It would be helpful to readers, and to you, if you would describe what you are trying to do in plain English. Don't confuse WHAT you're doing with HOW you think it might be done.

What is a Course? Unit? CourseUnit? Level?

Some context.............

[gazzieh]

DOH! Yes, I am sorry; I was in fact in bed and packing up after a long day - brain failed.

So, I have a course (Computing F435, for example) that is made up of units (Unit 1: Computer Systems); hence the tbl_course_units and tbl_units (the table tbl_course is not part of this problem but exists).

I have a listbox that lists all units whilst I have another listbox that lists the units assigned to this course. I want the former list NOT to show:

• units that are NOT set at Level 3 (the difficulty level, if you will)
• units that are already in the assigned list

There will be other courses that have the very same units assigned to them; therefore a NULL test is not suitable. I suppose the way I could put this is:

I want to SELECT ALL units from the UNITS where the LEVEL = 3 AND NOT where the course F435 has the same units

I do have a sort of solution (from writing the above) which generates what I want but three times! I believe this is because currently I have 3 units assigned to the course of interest (part of the tests).

I hope this is clearer.

[gazzieh]

I HAVE NOT SOLVED THIS! I say this because I just posted I had. Then I looked at the logic and realised I had fallen into the trap I keep falling in to.

My solution excludes ALL assigned units and not simply those assigned to a particular course.

My 'solution' is below. I will keep experimenting.

SELECT tbl_units.unit_id, tbl_units.unit_title, tbl_units.unit_no FROM tbl_units, tbl_course_units WHERE tbl_units.int_level=3 AND tbl_units.unit_id<>tbl_course_units.unit_id GROUP BY tbl_units.unit_id,  tbl_units.unit_title, tbl_units.unit_no

[gazzieh]

I have a solution, of kinds.

SELECT unit_id, unit_title, unit_no FROM qry_test LEFT JOIN qry_test2 ON qry_test.unit_id = qry_test2.unit_id WHERE qry_test2.unit_id Is Null

This uses the results of two other queries and subtracts the results of the one from the other (a MINUS query, if you will).

The following is the SQL of the query called qry_test

SELECT unit_id, unit_title, unit_no FROM tbl_units WHERE int_level)=3 GROUP BY unit_id, unit_title, unit_no

The following is the SQL from qry_test2

SELECT course_id, unit_id FROM tbl_course_units WHERE course_id=2

The results are correct but I now need to either convert this into one recordset command (set rst=...) or work out how to combine the results of two recordsets into a third, or run the final query within VBA, still being able to use the returned values inside the code.

Would it be as simple as:

Set rst = CurrentDb.OpenRecordset("SELECT unit_id, unit_title, unit_no FROM qry_test LEFT JOIN qry_test2 ON qry_test.unit_id = qry_test2.unit_id WHERE qry_test2.unit_id Is Null")

???

[gazzieh]

So, I have a solution of sorts:

Set rst = CurrentDb.OpenRecordset("SELECT qry_test.unit_id, qry_test.unit_title, qry_test.unit_no FROM qry_test LEFT JOIN qry_test2 ON qry_test.unit_id = qry_test2.unit_id WHERE qry_test2.unit_id Is Null")

The two queries are given in the earlier post and, if the values are hardwired (WHERE int_level=3 and course_id=2) then all is fine.

Now I need qry_test and qry_test2 to respond to changes in the form.

• I have a dropdown with the name txt_course
• this dropdown has three columns
  
  • course_id
  • an amalgation of several fields
  • int_level
  
  
• The value of this dropdown is set to be course_id
• I have used the tag property of this dropdown to take the value of int_level (column 2, accepting a zero column)
• In qry_test I have replaced the 3 (the int_level value) with int_level=forms!frm_groups!txt_course - where frm_groups is the name of the form all of this is meant to relate to
• In qry_test2 I have replaced the 2 (the course_id value) with course_id=forms!frm_groups!txt_course.tag

If I run the unmatched query without the form being active then I obviously get asked for the values, I enter these and everything works.

If I run the unmatched query with the form then everything works.

If I run the SQL of the unmatched query within VBA then I get an error:

VBA and SQL:

Set rst = CurrentDb.OpenRecordset("SELECT qry_test.unit_id, qry_test.unit_no, qry_test.unit_title FROM qry_test LEFT JOIN qry_test2 ON qry_test.[unit_id] = qry_test2.[unit_id] WHERE (((qry_test2.unit_id) Is Null));")

ERROR: Run-time error '3061': Too few parameters. Expected 2.

So, it dislikes my parameters in the two earlier queries but why and how do I resolve this?

[gazzieh]

Okay, have now solved this. I recreated the two queries within two separate recordsets and simply comparing through two loops each record with those I wish to exclude.

Probably not the most efficient method but since the dataset is very small and usage is minimal the time issue is negligible.