Achieving an OUTER join in Access

[stevenblanc]

Hey folks,

So access doesn't take outer joins I take it.

I have three tables.

tblGuidelines which contains the primary key pedGuide

tblCourseRecords which contains a list of courses with the primary key courseRecordID

and

tblCourseRecordPedMarking which is related to tblCourseRecords through a junction table and has both courseRecord and pedGuide as primary Keys.

I am trying to build a query which lists all pedGuide contained in tblGuidelines that are do not currently exist for the particular courseRecordID on tblCourseRecordPedMarking.

I attempted to use a LEFT OUTER JOIN to do this but access does not recognize the join. I then attempted to use the query builder, but the three join types allowed do not seem to provide what I need.

SELECT tblGuidelines.pedGuide, tblGuidelines.pedTitle FROM tblGuidelines LEFT OUTER JOIN tblCourseRecordPedMarking ON tblGuidelines.pedGuide WHERE tblGuidelines.pedCategory ="Marking" AND tblCourseRecordPedMarking.coureRecordID = 1 AND tblCourseRecordPedMarking.pedGuide IS null ;

Any ideas how I can achieve this?

 

[stevenblanc]

Until someone corrects me I will handle this with WHERE:

SELECT tblGuidelines.pedGuide, tblGuidelines.pedTitle 
FROM tblGuidelines, tblCourseRecordPedMarking
WHERE tblGuidelines.pedGuide=[tblCourseRecordPedMarking].[pedGuide] AND tblCourseRecordPedMarking.CourseRecordID = 1;

EDIT: Wait... this is exactly what I don't want...

EDIT: If I do this however I get a decent outer join, however I can't outerjoin for the particular record. I can however outerjoin the particular record if I stick another query in between to first filter the record. This sounds like a lot of query creation... Is there an easier way?

SELECT tblGuidelines.pedGuide, tblGuidelines.pedTitle, tblCourseRecordPedMarking.pedGuide
FROM tblGuidelines LEFT JOIN tblCourseRecordPedMarking ON tblGuidelines.pedGuide = tblCourseRecordPedMarking.pedGuide WHERE tblCourseRecordPedMarking.CourseRecordID = 1 AND tblCourseRecordPedMarking.pedGuide IS NULL;

 

[SBWolfe54]

The following Query is a left outer join. It is telling us to return all the records from the table on the left and only those in the right tables where the joined fields are equal. I am able to return ALL the records from the left because I have included an expression that says if you don't find the joined field on the right, Yes I am not inactive, else if found, I am:

SELECT HydraulicHoseDataCollectionOutput.DataCollectionRowNumber, HydraulicHoseDataCollectionOutput.AssemblyMaterialNumber, HydraulicHoseDataCollectionOutput.HoseAssemblyNumber, IIf(IsNull([007 00 Inactive Components List].[MARC_MATNR]),"N","Y") AS HoseAssemblyNumberInactive
FROM HydraulicHoseDataCollectionOutput LEFT JOIN [007 00 Inactive Components List] ON HydraulicHoseDataCollectionOutput.HoseAssemblyNumber = [007 00 Inactive Components List].MARC_MATNR;

Is this what you are looking for?

 

[stevenblanc]

SBWolfe54,

Thanks. After much battling, I got it sorted out. I don't why I try to manually code these things, Access design mode makes my life so much easier!

 

[speakers_86]

The qbe makes writing sql much easier. The visual alone is enough to tell you what output to expect. I usually dont try to write sql from scratch.

 

[stevenblanc]

I almost always do. So I understand whats going on. But as of yesterday I started just building everything as a query and copy and pasting the sql it produces when Im ready.

 

[stevenblanc]

Bob,

Or something. My brain has a funny way of focusing on old habits and forgetting new pearls until I've used them a couple times!

 

[SBWolfe54]

Even though I am very familar with writing SQL from scratch, when I am working in Access, I don't usually build the query myself using SQL, I let Query build it for me. I just wanted to show Steven that the left outer join was possible and how to do it. Glad everything worked out well.