issue with Join

[will1128]

I'm having issues where this query returns all the service offices I need (26):

SELECT McQuayInstalledBaseDetailExport.[SERVICE OFFICE], Count(McQuayInstalledBaseDetailExport.[SERVICE OFFICE]) AS [CountOfSERVICE OFFICE]
FROM McQuayInstalledBaseDetailExport
GROUP BY McQuayInstalledBaseDetailExport.[SERVICE OFFICE];

However, this query:

SELECT [No Start Date].[SERVICE OFFICE], Totals![CountOfSERVICE OFFICE]-[No Start Date]![CountOfSERVICE OFFICE] AS [Number Started], Totals.[CountOfSERVICE OFFICE] AS [Total Units], ([Totals]![CountOfSERVICE OFFICE]-[No Start Date]![CountOfSERVICE OFFICE])/[Totals]![CountOfSERVICE OFFICE] AS [% of Total Started], [IsStarted&HasDNet].[Total with D-Net], ([Totals]![CountOfSERVICE OFFICE]-[No Start Date]![CountOfSERVICE OFFICE])/[IsStarted&HasDNet]![Total with D-Net] AS [% Started With D-Net]
FROM ([No Start Date] INNER JOIN Totals ON [No Start Date].[SERVICE OFFICE] = Totals.[SERVICE OFFICE]) LEFT JOIN [IsStarted&HasDNet] ON Totals.[SERVICE OFFICE] = [IsStarted&HasDNet].[SERVICE OFFICE];

Isn't return all my rows because some exist in the first, but don't exist in the 2nd. How do I get it that even if it exists in the first and not in the second it is still returned?

Thanks for your help.

 

[WayneRyan]

Will,

The Inner Join is restricting the rows, change it to a Left Join and be aware that the
"missing" rows will be filled with Nulls.

SELECT [No Start Date].[SERVICE OFFICE], 
             Totals![CountOfSERVICE OFFICE] - [No Start Date]![CountOfSERVICE OFFICE] AS [Number Started],
             Totals.[CountOfSERVICE OFFICE] AS [Total Units], 
            ([Totals]![CountOfSERVICE OFFICE] - [No Start Date]![CountOfSERVICE OFFICE]) / [Totals]![CountOfSERVICE OFFICE] AS [% of Total Started], 
             [IsStarted&HasDNet].[Total with D-Net], 
            ([Totals]![CountOfSERVICE OFFICE] - [No Start Date]![CountOfSERVICE OFFICE]) / [IsStarted&HasDNet]![Total with D-Net] AS [% Started With D-Net]
FROM ([No Start Date] Left JOIN Totals ON 
               [No Start Date].[SERVICE OFFICE] = Totals.[SERVICE OFFICE]) LEFT JOIN [IsStarted&HasDNet] ON 
                    Totals.[SERVICE OFFICE] = [IsStarted&HasDNet].[SERVICE OFFICE];

Wayne

 

[will1128]

I guess I'm having a heck of time with this.

This query, which counts the number of services offices where the UNIT start date is NULL returns 24 records.

SELECT McQuayInstalledBaseDetailExport.[SERVICE OFFICE], Count(McQuayInstalledBaseDetailExport.[SERVICE OFFICE]) AS [CountOfSERVICE OFFICE]
FROM McQuayInstalledBaseDetailExport
WHERE (((IsNull([McQuayInstalledBaseDetailExport].[UNIT START DATE]))=-1))
GROUP BY McQuayInstalledBaseDetailExport.[SERVICE OFFICE];
[code]
 
This query returns 27 records. This query returns ALL of my offices and the number of units there.
[code]
SELECT McQuayInstalledBaseDetailExport.[SERVICE OFFICE], Count(McQuayInstalledBaseDetailExport.[SERVICE OFFICE]) AS [CountOfSERVICE OFFICE]
FROM McQuayInstalledBaseDetailExport
GROUP BY McQuayInstalledBaseDetailExport.[SERVICE OFFICE];

This query returns 24 records, but I need it to return 27 (the number offices).

SELECT [No Start Date].[SERVICE OFFICE], 
             Totals![CountOfSERVICE OFFICE] - [No Start Date]![CountOfSERVICE OFFICE] AS [Number Started],
             Totals.[CountOfSERVICE OFFICE] AS [Total Units], 
            ([Totals]![CountOfSERVICE OFFICE] - [No Start Date]![CountOfSERVICE OFFICE]) / [Totals]![CountOfSERVICE OFFICE] AS [% of Total Started], 
             [IsStarted&HasDNet].[Total with D-Net], 
            ([Totals]![CountOfSERVICE OFFICE] - [No Start Date]![CountOfSERVICE OFFICE]) / [IsStarted&HasDNet]![Total with D-Net] AS [% Started With D-Net]
FROM ([No Start Date] Left JOIN Totals ON 
               [No Start Date].[SERVICE OFFICE] = Totals.[SERVICE OFFICE]) LEFT JOIN [IsStarted&HasDNet] ON 
                    Totals.[SERVICE OFFICE] = [IsStarted&HasDNet].[SERVICE OFFICE];
[code]

 
I don't know if this helps as to what I'm trying to do, but I hope it does because I'm stuck. :confused:

 

[jal]

If you want to return all the offices, the table that has ALL the offices must be in the FROM clause

FROM tblAllOffices

Generally, the rest of the tables should be left joined (thanks for pointing that out Wayne) to prevent the join from eliminating some of the offices. One approach would be the following:

SELECT *
FROM (((
(
SELECT DISTINCT [Service OFFICE]
FROM tblAllOffices
) as AllOffices
LEFT JOIN [No Start Date] AS N ON N.[ServiceOffice] = AllOffices.[Service Office])
LEFT JOIN [TOTALS] as T ON T.[Service Office] = AllOffices.[Service Office])
LEFT JOIN [IsStarted&HasDNet] as I ON I.[SERVICE OFFICE] =AllOffices.[Service office])

That's just a sketch to get you started.