SQL QUERY ow to achieve it? (short paths)

February 2, 2010 3:05 PM

I am trying to create a SQL Query for Access2003 that will basically show me by employee ALL potential classes they can take and sorted together with the most recent date of a class they may have taken. Basically this is used as a worksheet to plan training for the people who may have some of the required training but not others. (see inside

Basically i need to see for each person an entry for each class that we offer a detail of the class name and if they have taken it, the most recent date they took the class and score. My ACCESS2003 has the following tables:

I have 3 tables tblClass (classnick, classname, classtype)
tblClassDetail(ClassNick,EmpId, ClassDate, Score, ...)
tblEmp (EmpID, fname, lname, enddate)

the relationships are tblClass.classnick/tblClassDetail.ClassNick
tblClass.Detail.EmpID/tblEmp.EmpID


so say we only have i classes in tblClass: blueprint1, 2, corestack, color copier, and bar cutter... the query output I am looking for has an entry per person of even the classes that have not been taken.
with no date. then I can link this with word and create the training
page for each person. <- preferably, the null would not be there.



fname --lname--classnick---classdate-----score

laura le blueprint1 10/1/2009 100
laura le blueprint2 NULL NULL
laura le corestack 1/1/2009 100
laura le color copier 3/1/2009 85
laura le bar cutter NULL NULL
joe blo blueprint1 10/1/2009 100
joe blo blueprint2 11/12/2009 85
joe blo corestack NULL NULL
joe blo color copier 3/1/2009 85
joe blo bar cutter 10/15/2009 100


I've been looking at the sites and trying to follow the examples, and
i do "get" what outer join does, and inner join... left and right,
etc. but I don't "get" how to do the query in such a way that the short path records are also included.

I have easily achieved this in the past using IBI's WebFocus MATCH FILE logic, however, I can not for the life of me figure it out the SQL way. And sadly, no WebFOCUS is available for me here.

Does anyone out there know how to achieve this? I will be porting the records to msWord to print one page per person of their training history and requirements. THANK YOU SO MUCH for your time.

posted by wildpetals to Computers & Internet (3 answers total)

You'll need to full join tblEmp to tblClass with no join criteria, then left join tblClassDetail onto both of those matching empID and classNick.


select * from
tblEmp a full join tblClass b on 1=1
left join tblClassDetail c on a.empID = c.empID and b.classNick = c.classNick

If you have any questions mefi mail me I guess.

The trick to this is the full join. Try just doing

select * from
tblEmp a full join tblClass b on 1=1

to see what it is doing.
posted by spatula at 3:48 PM on February 2, 2010

Best answer: So I came up with the same thing as spatula (well very similar) - but then it turns out access is stupid and can't left join to a query with more than one table. To get it to work, create it in two parts - the first is a query to select all combinations of classes and employees:

SELECT tblClass.*, tblEmp.* FROM tblClass, tblEmp ORDER BY tblEmp.lname, tblEmp.fname;

save this query as emp_classes then create a second query:

SELECT emp_classes.*, tblClassDetail.*
FROM emp_classes LEFT JOIN tblClassDetail ON (emp_classes.EmpID = tblClassDetail.EmpID) AND (emp_classes.classnick = tblClassDetail.ClassNick);

which should work fine.

Not relevant to your question but I strongly recommend switching to Microsoft SQL Server Express or another Real Database, unless you're stuck with Access for legacy reasons.
posted by samj at 4:06 PM on February 2, 2010

Response by poster: to everyone who responded... thank you so much!! I've learned a lot for sure. and samj, if I could use SQL Server... would I ever! :)
posted by wildpetals at 5:54 AM on February 3, 2010