Need query to return even when result is 0 2

[mbthaz]

I have 4 managers I need to pull sales numbers on for various packages. The problem I have is that when a manager doesn't sell one of the packages, they dissappear from the report altogether.
I have multiple queries to build the data set as I couldn't feasibly bring the numbers together with out running queries on queries.

I have a managers table with each of the managers. I have an orders table with multiple columns, the important one being "packages". In the package column 6 different packages are tracked. I need to pull tallies for each individual package out of that column.

Any help is appreciated.
TIA

 

[joshuar]

Are you using outer joins to show all the rows from your managers table?

 

[mbthaz]

Here is the table set oversimplified.

Managers Table
manager1
manager2
manager3
manager4

Employees Table
employee1 salescode1 manager
employee2 salescode2 manager

Orders Table
order# Package salescode

Here is the query as it sits now.

SELECT [Sales Managers].[Sales Manager], Count([Package 1])+Count([Package 2]) AS Tppkgs, Orders.ApplicationDate
FROM (Employees RIGHT JOIN [Sales Managers] ON Employees.[Sales Manager] = [Sales Managers].[Sales Manager]) LEFT JOIN Orders ON Employees.[Sales Code] = Orders.[Sales Code]
WHERE (((Orders.[Package 1])="Total Package&quot) OR (((Orders.[Package 2])="Total Package&quot)
GROUP BY [Sales Managers].[Sales Manager], Orders.ApplicationDate
HAVING (((Orders.ApplicationDate)=[Date]));

Every way I try to write this I get ambiguous outer join errors.

Thoughts?
And thanks again.

 

[joshuar]

I think I've solved this problem in the past by using two queries. The first would the employees table joined to the orders table grouped on employees with a count on package. The second would be the managers table left joined to the first query grouped on managers summing the counts from the first query.

I don't have Access on this computer, so this is only an approximation of the code, but it might work:

First Query (qryEmpOrders):
SELECT Employees.[Sales Manager], Count([Package 1])+Count([Package 2]) AS Tppkgs, Orders.ApplicationDate
FROM Employees LEFT JOIN Orders ON Employees.[Sales Code] = Orders.[Sales Code]
WHERE (((Orders.[Package 1])="Total Package&quot) OR (((Orders.[Package 2])="Total Package&quot)
GROUP BY Employees.[Sales Manager], Orders.ApplicationDate
HAVING (((Orders.ApplicationDate)=[Date]));

Second query:
SELECT [Sales Managers].[Sales Manager], Sum([Tppkgs]) AS Totalpkgs, qryEmpOrders.ApplicationDate
FROM qryEmpOrders RIGHT JOIN [Sales Managers] ON qryEmpOrders.[Sales Manager] = [Sales Managers].[Sales Manager]
GROUP BY [Sales Managers].[Sales Manager], qryEmpOrders.ApplicationDate;

 

[mbthaz]

joshuar- That worked great..kinda messy that access requires so much effort to achieve but, it works. Thanks again!!

 

[mbthaz]

Ok, I lied. It does work great, but now I need to carry that to the larger query, and it is being dropped again.

I am assuming because it still contains a null value. I have a hard time wrapping my head around the right and left joins. Here is the next part of my problem.

The query is below, and terribly long. In a nut shell, it pulls from about 6 queries just like the one above, but puts all the data into a usable format for reporting. When I run this query, the coach with no sales in the one item falls off again. The query above is [Team by Consultant Booked Packages -TP Part2] in the query below.
Hoping for ideas-
Mike


SELECT
[Team by Consultant Booked Packages - Custom Choice].ApplicationDate,
[Sales Managers].[Sales Manager],
[Team by Consultant Booked Packages - Custom Choice].ccpkgs,
[pcpkgs]+[cccpkgs] AS cccpc,
[Team by Consultant Booked Packages -TP Part2].Totalpkgs AS Tppkgs,
[Team by Consultant Booked Packages - 2lccc].[2lccpkgs],
[Team by Coach Strategic Products Booked AppDate].BB,
[Team by Coach Strategic Products Booked AppDate].NB,
[Team by Coach Strategic Products Booked AppDate].pcscnt,
[Team by Coach Strategic Products Booked AppDate].CountOfFeaturedProduct AS feat,
[Team by Coach Strategic Products Booked AppDate].Nsr,
[Team by Consultant Booked DSL].DSLCnt

FROM
((((Employees
INNER JOIN (Orders
INNER JOIN (((([Team by Consultant Booked Packages - Custom Choice]
INNER JOIN [Sales Managers]
ON [Team by Consultant Booked Packages - Custom Choice].[Sales Manager] = [Sales Managers].[Sales Manager])
INNER JOIN [Team by Consultant Booked Packages - Custom Choice Complete]
ON [Sales Managers].[Sales Manager] = [Team by Consultant Booked Packages - Custom Choice Complete].[Sales Manager])
INNER JOIN [Team by Consultant Booked Packages - Other Packages]
ON [Sales Managers].[Sales Manager] = [Team by Consultant Booked Packages - Other Packages].[Sales Manager])
INNER JOIN [Team by Consultant Booked Packages - Popular Choice]
ON [Sales Managers].[Sales Manager] = [Team by Consultant Booked Packages - Popular Choice].[Sales Manager])
ON (Orders.ApplicationDate = [Team by Consultant Booked Packages - Custom Choice].ApplicationDate)
AND (Orders.ApplicationDate = [Team by Consultant Booked Packages - Custom Choice Complete].ApplicationDate)
AND (Orders.ApplicationDate = [Team by Consultant Booked Packages - Other Packages].ApplicationDate)
AND (Orders.ApplicationDate = [Team by Consultant Booked Packages - Popular Choice].ApplicationDate))
ON (Employees.[Sales Manager] = [Sales Managers].[Sales Manager])
AND (Employees.[Sales Code] = Orders.[Sales Code]))
INNER JOIN [Team by Consultant Booked Packages - 2lccc]
ON ([Sales Managers].[Sales Manager] = [Team by Consultant Booked Packages - 2lccc].[Sales Manager])
AND (Orders.ApplicationDate = [Team by Consultant Booked Packages - 2lccc].ApplicationDate))
INNER JOIN [Team by Coach Strategic Products Booked AppDate]
ON ([Sales Managers].[Sales Manager] = [Team by Coach Strategic Products Booked AppDate].[Sales Manager])
AND (Orders.ApplicationDate = [Team by Coach Strategic Products Booked AppDate].ApplicationDate))
INNER JOIN [Team by Consultant Booked DSL]
ON ([Sales Managers].[Sales Manager] = [Team by Consultant Booked DSL].[Sales Manager])
AND (Orders.ApplicationDate = [Team by Consultant Booked DSL].ApplicationDate))
INNER JOIN [Team by Consultant Booked Packages -TP Part2]
ON ([Team by Consultant Booked Packages -TP Part2].ApplicationDate = Orders.ApplicationDate)
AND ([Sales Managers].[Sales Manager] = [Team by Consultant Booked Packages -TP Part2].[Sales Manager])
GROUP BY
[Team by Consultant Booked Packages - Custom Choice].
ApplicationDate, [Sales Managers].[Sales Manager],
[Team by Consultant Booked Packages - Custom Choice].ccpkgs,
[pcpkgs]+[cccpkgs], [Team by Consultant Booked Packages -TP Part2].Totalpkgs,
[Team by Consultant Booked Packages - 2lccc].[2lccpkgs],
[Team by Coach Strategic Products Booked AppDate].BB,
[Team by Coach Strategic Products Booked AppDate].NB,
[Team by Coach Strategic Products Booked AppDate].pcscnt,
[Team by Coach Strategic Products Booked AppDate].CountOfFeaturedProduct,
[Team by Coach Strategic Products Booked AppDate].Nsr,
[Team by Consultant Booked DSL].DSLCnt
HAVING ((([Team by Consultant Booked Packages - Custom Choice].ApplicationDate)=[Date]));

 

[mbthaz]

Perhaps the easiest solution is to conditionaly return 0. Something in the query like If (TotalPkgs<>Null) Return TotalPkgs Else Return 0; Not sure how to do that in access, as I'm neither an access guru or VB programmer.. Ideas?

 

[cmmrfrds]

The nz function does that, when null either the field or what you want in this case 0.

nz(TotalPkgs,0)

 

[mbthaz]

Thanks again, total problem solved ;-)