Query Joins

[AC5FF]

I for the life of me cannot figure out the 'join properties' and figuring out which means what. To compound that I've read through here before that certain 'joins' cannot be done in Query Design but instead must be done in SQL? Is that true?

Here is my problem. I have a fairly complex query that used to work fine until I had to add a couple new colums and two new tables to pull data from (There's 8 tables related to each other in this query). I'm terrible at reading/writing SQL but since that's the only way to really post what I have it's posted below.

The first problem is that if WORK!DOC does not have a match in D23!DOC it is not listed. That makes sense, but I need to show the WORK!DOC in the query results. I've tried the three join options in design view, but never get the results I need.

The other problem in this query I've not figured out where/why/how what is going on. But where I would expect this query to return 270 lines I am only getting 200. I can account for maybe 5 of these missing lines due to the WORK!DOC not being in the D23!DOC table. The rest is a mystery as to why they are dropping... If something jumps out at anyone as to what could be the problem there I'd love to hear about it!

Thanks for looking!!!

SELECT lru.Team, Status.[Code Order], Status.Code, Work.Doc, IIf(Work!Status<>2,Work!Awp,WPDays(Work![AWP Start],Work!Awp)) AS Awp, IIf(Work!Status<>3,Work!Dwp,WPDays(Work![DWP Start],Work!Dwp)) AS Dwp, Work.ISU, IIf(IsNull(MD5TeamProductionQTYAWFAWP!CountOfDoc)," ","x" & MD5TeamProductionQTYAWFAWP!CountOfDoc & " ") & lru!Abbr AS ABBR, IIf(Work![Shop Unit]=False,lru![Maint Std],0) AS [Maint Std], Int([lru]![CND Rate]*100) & "%" AS [CND Rate], Work.[S/N], Work.Maint, IIf([Work]![Shop Unit]=False,IIf([Work]![MICAP]=True,"MICAP",[D23]![MP]),[Work]![Shop Comment]) AS Priority, CStr(IIf(Work!Repeat>0,Work!Repeat,"")) AS Repeat, CStr(IIf(Work![Shop Unit]=False,IIf(Work!MICAP=True,2,1),0)) AS Sort1, IIf([Work]![shop unit]=False And ([enterprise repair status]![contracted]-[enterprise repair status]![repaired])>0,1,0) AS Sort2, CStr(IIf([Work]![Status]<2,[MP]![Priority],[lru]![Abbr])) AS Sort3, IIf(lru!DIG,"{","") & Area!Name & IIf(lru!DIG,"}","") AS Name, lru.DIG, Area.Element
FROM ((d23 INNER JOIN MP ON d23.MP = MP.MP) INNER JOIN (Status INNER JOIN ((Area INNER JOIN lru ON Area.ID = lru.Team) INNER JOIN ([Work] LEFT JOIN MD5TeamProductionQTYAWFAWP ON (Work.Status = MD5TeamProductionQTYAWFAWP.Status) AND (Work.[LRU ID] = MD5TeamProductionQTYAWFAWP.ID)) ON lru.ID = Work.[LRU ID]) ON Status.ID = Work.Status) ON d23.DOC = Work.Doc) INNER JOIN [Enterprise Repair Status] ON lru.Abbr = [Enterprise Repair Status].LRU
WHERE (((Work.Status)<4))
GROUP BY lru.Team, Status.[Code Order], Status.Code, Work.Doc, IIf(Work!Status<>2,Work!Awp,WPDays(Work![AWP Start],Work!Awp)), IIf(Work!Status<>3,Work!Dwp,WPDays(Work![DWP Start],Work!Dwp)), Work.ISU, IIf(IsNull(MD5TeamProductionQTYAWFAWP!CountOfDoc)," ","x" & MD5TeamProductionQTYAWFAWP!CountOfDoc & " ") & lru!Abbr, IIf(Work![Shop Unit]=False,lru![Maint Std],0), Int([lru]![CND Rate]*100) & "%", Work.[S/N], Work.Maint, IIf([Work]![Shop Unit]=False,IIf([Work]![MICAP]=True,"MICAP",[D23]![MP]),[Work]![Shop Comment]), CStr(IIf(Work!Repeat>0,Work!Repeat,"")), CStr(IIf(Work![Shop Unit]=False,IIf(Work!MICAP=True,2,1),0)), IIf([Work]![shop unit]=False And ([enterprise repair status]![contracted]-[enterprise repair status]![repaired])>0,1,0), CStr(IIf([Work]![Status]<2,[MP]![Priority],[lru]![Abbr])), IIf(lru!DIG,"{","") & Area!Name & IIf(lru!DIG,"}",""), lru.DIG, Area.Element;

 

[AC5FF]

Hmmm.. No one willing to tackle this one? I was really hoping to come in to work this morning and find some help or a few good ideas..

Maybe just have to go home early today and leave the problem be until Monday...

 

[anthonys]

You need to look up the concept of Left and Right Outer joins, for example

d23 INNER JOIN MP ON d23.MP = MP.MP

This query will only return records from d23 where there is a related record in MP. If you have 270 records in d23 and only 200 have a matching record in MP you will only return 200 records.

If you change that to d23 LEFT OUTER JOIN MP ON d23.MP = MP.MP
you will return everything from d23 (the left hand side table) and only records from MP where they match. If you have 270 records in d23 and only 200 have a matching record in MP you will return all 270 with blanks.


You will need to address all the tables that have a join to ensure the correct number are returned. You might try changing them all to Left joins and see what is returned.

Ant

 

[AC5FF]

Ant
I'd been looking at the D23 table as my fault point... I hadn't considered the records w/out a MP match... There are quite a few of those. I'll have to go check that out..
Thanks