Problems with a join

[Biss]

Access 2007

I am trying to produce a simple query or at least I think that it should be simple. I apologize in advance as to how I am going to explain but could not think of any other way to express what I am trying to do..

First part of the query returns this

qryTestSfActive
tblRegStudio.NotActive tblRegModels.NotActive Active PageName StudioID Stage
FALSE FALSE TRUE SF LATA-040 Christani
FALSE FALSE TRUE SF LATA-040 Ema
FALSE FALSE TRUE SF LATA-040 Johhanna

This is the sql for the above

SELECT tblRegStudio.NotActive, tblRegModels.NotActive, tblRegModelSite.Active, tblRegModelSite.PageName, tblRegModelSite.StudioID, tblRegModelSite.Stage
FROM (tblRegStudio INNER JOIN tblRegModels ON tblRegStudio.StudioID = tblRegModels.StudioID) INNER JOIN tblRegModelSite ON tblRegModels.ModelID = tblRegModelSite.ModelID
WHERE (((tblRegStudio.NotActive)=No) AND ((tblRegModels.NotActive)=No) AND ((tblRegModelSite.Active)=Yes) AND ((tblRegModelSite.PageName)="SF"))
ORDER BY tblRegModelSite.StudioID, tblRegModelSite.Stage;

The second part of the query returns this

qryTestSfWklyData
IndexID NIT TotMin PVT Conv
5 Christani 32 0 0.00%


This is the sql for the above

SELECT IndexData.IndexID, tblSfData.NIT, tblSfData.TotMin, tblSfData.PVT, tblSfData.Conv
FROM IndexData INNER JOIN tblSfData ON IndexData.IndexID = tblSfData.IndexID
WHERE (((IndexData.IndexID)=5));

When I combine the two together this is what is returned

qryTestSfActive Combined Wkly Data
tblRegStudio.NotActive tblRegModels.NotActive Active PageName StudioID Stage IndexID TotMin PVT
FALSE FALSE TRUE SF LATA-040 Christani 5 32 0


This is the sql for the above

SELECT tblRegStudio.NotActive, tblRegModels.NotActive, tblRegModelSite.Active, tblRegModelSite.PageName, tblRegModelSite.StudioID, tblRegModelSite.Stage, tblSfData.IndexID, tblSfData.TotMin, tblSfData.PVT
FROM ((tblRegStudio INNER JOIN tblRegModels ON tblRegStudio.StudioID = tblRegModels.StudioID) INNER JOIN tblRegModelSite ON tblRegModels.ModelID = tblRegModelSite.ModelID) LEFT JOIN tblSfData ON tblRegModelSite.Stage = tblSfData.NIT
WHERE (((tblRegStudio.NotActive)=No) AND ((tblRegModels.NotActive)=No) AND ((tblRegModelSite.Active)=Yes) AND ((tblRegModelSite.PageName)="SF") AND ((tblSfData.IndexID)=5))
ORDER BY tblRegModelSite.StudioID, tblRegModelSite.Stage;

This is what I am trying to achieve. What in the &(*&^*&^9 am I doing wrong. the join that I have between Stage and NIT is correct to the best of my understaning.

Hoped For Results

tblRegStudio.NotActive tblRegModels.NotActive Active PageName StudioID Stage TotMin PVT
FALSE FALSE TRUE SF LATA-040 Christani 32 0
FALSE FALSE TRUE SF LATA-040 Ema
FALSE FALSE TRUE SF LATA-040 Johhanna
FALSE FALSE TRUE SF LATA-040 Lizz

 

[Daryl S]

Biss -

You have criteria that IndexData.IndexID = 5, and this will limit which
records you can see, even though it is an LEFT JOIN. You need to allow this
to be null also, so the record can show up. Change the criteria to

WHERE (((IndexData.IndexID = 5) OR (IndexData.IndexID is Null)))

 

[KenSheridan via AccessMonster.com]

Daryl:

That would only apply if the desired result was to return all rows from the
left side of the outer join where there is no match in the table to the right
of the join, plus all rows where the IndexID value in the table to the right
of the join is 5. If in the example given Emma, Johhanna or Lizz have
matching rows in tblSfData with IndexID values other than 5 then the query
would still return no rows for them.

A solution would be, employing a LEFT OUTER JOIN on the Stage and NIT columns,
to join a query which excludes the tblSfData table to another query which
returns all rows from tblSfData where IndexID = 5.

So the new query would be:

SELECT *
FROM tblSfData
WHERE IndexID = 5;

and the final query would be:

SELECT qryTestSfActive.*,
NewQuery.TotMin, NewQuery.PVT
FROM qryTestSfActive LEFT JOIN NewQuery
ON qryTestSfActive.Stage = NewQuery.NIT;

Ken Sheridan
Stafford, England

Biss -

You have criteria that IndexData.IndexID = 5, and this will limit which
records you can see, even though it is an LEFT JOIN. You need to allow this
to be null also, so the record can show up. Change the criteria to

WHERE (((IndexData.IndexID = 5) OR (IndexData.IndexID is Null)))

Access 2007

[quoted text clipped - 71 lines]

FALSE FALSE TRUE SF LATA-040 Johhanna
FALSE FALSE TRUE SF LATA-040 Lizz