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)))