Join problems

  • Thread starter Thread starter Biss
  • Start date Start date
B

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 Icssa-110 GISILLA
FALSE FALSE TRUE SF Icssa-110 Montane
FALSE FALSE TRUE SF Icssa-110 Nirvanna
FALSE FALSE TRUE SF LATA-010 ALEXEA
FALSE FALSE TRUE SF LATA-010 Amanna
FALSE FALSE TRUE SF LATA-010 Anyta Paz
FALSE FALSE TRUE SF LATA-010 Fanni
FALSE FALSE TRUE SF LATA-010 Lella
FALSE FALSE TRUE SF LATA-010 Natica
FALSE FALSE TRUE SF LATA-040 Angeleca
FALSE FALSE TRUE SF LATA-040 Carle
FALSE FALSE TRUE SF LATA-040 Christani
FALSE FALSE TRUE SF LATA-040 Ema
FALSE FALSE TRUE SF LATA-040 Johhanna
FALSE FALSE TRUE SF LATA-040 Lizz
FALSE FALSE TRUE SF LATA-040 Nidia
FALSE FALSE TRUE SF LATA-040 Sammey
FALSE FALSE TRUE SF LATA-040 Scarlata
FALSE FALSE TRUE SF LATA-040 Varanika


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
IndexID TotMin PVT
FALSE FALSE TRUE SF Icssa-110 GISILLA
FALSE FALSE TRUE SF Icssa-110 Montane
FALSE FALSE TRUE SF Icssa-110 Nirvanna
FALSE FALSE TRUE SF LATA-010 ALEXEA
FALSE FALSE TRUE SF LATA-010 Amanna
FALSE FALSE TRUE SF LATA-010 Anyta Paz
FALSE FALSE TRUE SF LATA-010 Fanni
FALSE FALSE TRUE SF LATA-010 Lella
FALSE FALSE TRUE SF LATA-010 Natica
FALSE FALSE TRUE SF LATA-040 Angeleca
FALSE FALSE TRUE SF LATA-040 Carle
FALSE FALSE TRUE SF LATA-040 Christani 5 32 0
FALSE FALSE TRUE SF LATA-040 Ema
FALSE FALSE TRUE SF LATA-040 Johhanna
FALSE FALSE TRUE SF LATA-040 Lizz
FALSE FALSE TRUE SF LATA-040 Nidia
FALSE FALSE TRUE SF LATA-040 Sammey
FALSE FALSE TRUE SF LATA-040 Scarlata
FALSE FALSE TRUE SF LATA-040 Varanika
 
What are you doing to "combine the two together"?

Regards

Jeff Boyce
Microsoft Access MVP
 
I am selecting a join in the query template and changing to "Include ALL
records from 'tblRegModels' and only those records from 'tblRegModelSite'
where the joined fields are egual..

this damnd thing works in other quieres I have and for the life of me I cant
understand why it doesnot work here..

Thanks


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;
 
I cant understand why it doesnot work here..
What is it not doing that you expect? What is it doing that it should not
do?
 
Thanks for the clarification.

When I run into a query that isn't performing as I would expect/hope, I
generally start over again at the beginning, adding one aspect at a time
until it breaks (or works).

Have you tried starting over?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
Jeff I have started from the beginning I have separated the two queries and
the bottom line is no matter how I join them it always works out the same

I am basicly generating a list o names of people that are supposed to be
working with the left query.

Then I am trying to match the data from a list of peop who actually worked.

The idea is to have a list of people that are no working with no data and
the ones that are working with data.
 
Carl It is returning this
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


When it should return this
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
 
When I looked back over your SQL statement joining the two queries, it
looked like you're joining the two tables, not the queries.

Consider creating a new query, adding the two existing queries, and joining
them with that LEFT JOIN.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
Back
Top