If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Problems with a join
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 |
#2
|
|||
|
|||
Problems with a join
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))) -- Daryl S "Biss" wrote: 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 |
#3
|
|||
|
|||
Problems with a join
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 Daryl S wrote: 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 -- Message posted via http://www.accessmonster.com |
Thread Tools | |
Display Modes | |
|
|