Stacking Querys returns all subquerys as null if one is null

[dbourdea]

I have an Access reporting database that pulls all of its data from a Remedy database. This part works fine. I create 2 tables. The first is a problem managment table, the second is a service request table (moves, adds, changes). In the Problem Management table we have 5 distinct product types that we need to do reporting on. (products are the types of trouble calls that we recieve; HARDWARE, SOFTWARE, Printer, Telecomm, and NETWORK). In the Service request table all I need is a count of the tickets closed with in a specific period of time. In the Problem Management table I need to know how many of each of the above mentioned 5 product types have been closed during the same time period as the Service Requests. I have written 5 individual querys for each of the Problem Management product types and 1 query for the Service Requests. All of the querys use the Count function to get the number of those type of problem/service calls that have been placed in the system. I call these querys subquerys. I have then created a "Master Query" that displays all of those counts in a single row. I use these numbers to create a pie chart that show the number of each of the product types of tickets that were closed during the specified period of time.

The problem:
If one of the query's returns a count of zero then all of the results in the "Master Query" return zero, and thus no chart.

I have tried to use an iif statement but have been unable to get it to return something if the count=zero, null, or empty.

I need some help. I am willing to forward the database to you for review. Thanks in advance.

 

[KyleS]

I don't think sending the DB will be necesary, but a look at the SQL (for each query and the Master) would help a lot.


Kyle

 

[dbourdea]

Kyle,

Here are those sql statments for the query's:

1. IMAC Query
PARAMETERS [Forms]![frmPrintStatisticalReports]![txtFrom] DateTime, [Forms]![frmPrintStatisticalReports]![txtTo] DateTime;
SELECT Count([tbl_Make-APL_Service_Request].Status) AS IMAC
FROM [tbl_Make-APL_Service_Request]
WHERE ((([tbl_Make-APL_Service_Request].Date_Time_Solved) Between [Forms]![frmPrintStatisticalReports]![txtFrom] And [Forms]![frmPrintStatisticalReports]![txtTo]))
GROUP BY [tbl_Make-APL_Service_Request].Status
HAVING ((([tbl_Make-APL_Service_Request].Status)="Closed&quot);

2. HARDWARE QUERY
PARAMETERS [Forms]![frmPrintStatisticalReports]![txtFrom] DateTime, [Forms]![frmPrintStatisticalReports]![txtTo] DateTime;
SELECT Count(tbl_Make_PM_TroubleTicket.Product) AS [#], tbl_Make_PM_TroubleTicket.Product AS HARDWARE
FROM tbl_Make_PM_TroubleTicket
WHERE (((tbl_Make_PM_TroubleTicket.Date_Time_Solved) Between [Forms]![frmPrintStatisticalReports]![txtFrom] And [Forms]![frmPrintStatisticalReports]![txtTo]))
GROUP BY tbl_Make_PM_TroubleTicket.Product, tbl_Make_PM_TroubleTicket.Status
HAVING (((tbl_Make_PM_TroubleTicket.Product) Like "HARDWARE&quot AND ((tbl_Make_PM_TroubleTicket.Status)="Closed&quot)
ORDER BY Count(tbl_Make_PM_TroubleTicket.Product) DESC;

3. NETWORK
PARAMETERS [Forms]![frmPrintStatisticalReports]![txtFrom] DateTime, [Forms]![frmPrintStatisticalReports]![txtTo] DateTime;
SELECT Count(tbl_Make_PM_TroubleTicket.Product) AS [#], tbl_Make_PM_TroubleTicket.Product AS Network
FROM tbl_Make_PM_TroubleTicket
WHERE (((tbl_Make_PM_TroubleTicket.Date_Time_Solved) Between [Forms]![frmPrintStatisticalReports]![txtFrom] And [Forms]![frmPrintStatisticalReports]![txtTo]))
GROUP BY tbl_Make_PM_TroubleTicket.Product, tbl_Make_PM_TroubleTicket.Status
HAVING (((tbl_Make_PM_TroubleTicket.Product) Like "network&quot AND ((tbl_Make_PM_TroubleTicket.Status)="Closed&quot)
ORDER BY Count(tbl_Make_PM_TroubleTicket.Product) DESC;

4. Printer
PARAMETERS [Forms]![frmPrintStatisticalReports]![txtFrom] DateTime, [Forms]![frmPrintStatisticalReports]![txtTo] DateTime;
SELECT Count(tbl_Make_PM_TroubleTicket.Product) AS [#], tbl_Make_PM_TroubleTicket.Product AS Printer
FROM tbl_Make_PM_TroubleTicket
WHERE (((tbl_Make_PM_TroubleTicket.Date_Time_Solved) Between [Forms]![frmPrintStatisticalReports]![txtFrom] And [Forms]![frmPrintStatisticalReports]![txtTo]))
GROUP BY tbl_Make_PM_TroubleTicket.Product, tbl_Make_PM_TroubleTicket.Status
HAVING (((tbl_Make_PM_TroubleTicket.Product) Like "Printer&quot AND ((tbl_Make_PM_TroubleTicket.Status)="Closed&quot)
ORDER BY Count(tbl_Make_PM_TroubleTicket.Product) DESC;

5. SOFTWARE
PARAMETERS [Forms]![frmPrintStatisticalReports]![txtFrom] DateTime, [Forms]![frmPrintStatisticalReports]![txtTo] DateTime;
SELECT Count(tbl_Make_PM_TroubleTicket.Product) AS [#], tbl_Make_PM_TroubleTicket.Product AS SOFTWARE
FROM tbl_Make_PM_TroubleTicket
WHERE (((tbl_Make_PM_TroubleTicket.Date_Time_Solved) Between [Forms]![frmPrintStatisticalReports]![txtFrom] And [Forms]![frmPrintStatisticalReports]![txtTo]))
GROUP BY tbl_Make_PM_TroubleTicket.Product, tbl_Make_PM_TroubleTicket.Status
HAVING (((tbl_Make_PM_TroubleTicket.Product) Like "SOFTWARE&quot AND ((tbl_Make_PM_TroubleTicket.Status)="Closed&quot)
ORDER BY Count(tbl_Make_PM_TroubleTicket.Product) DESC;

6. Telecomm
PARAMETERS [Forms]![frmPrintStatisticalReports]![txtFrom] DateTime, [Forms]![frmPrintStatisticalReports]![txtTo] DateTime;
SELECT Count(tbl_Make_PM_TroubleTicket.Product) AS [#], tbl_Make_PM_TroubleTicket.Product AS Telecomm
FROM tbl_Make_PM_TroubleTicket
WHERE (((tbl_Make_PM_TroubleTicket.Date_Time_Solved) Between [Forms]![frmPrintStatisticalReports]![txtFrom] And [Forms]![frmPrintStatisticalReports]![txtTo]))
GROUP BY tbl_Make_PM_TroubleTicket.Product, tbl_Make_PM_TroubleTicket.Status
HAVING (((tbl_Make_PM_TroubleTicket.Product) Like "Telecomm&quot AND ((tbl_Make_PM_TroubleTicket.Status)="Closed&quot)
ORDER BY Count(tbl_Make_PM_TroubleTicket.Product) DESC;

7. Master Query
SELECT [subqryTicket-IMAC].IMAC, [subqryTickets-SOFTWARE].[#] AS SOFTWARE, [subqryTickets-HARDWARE].[#] AS HARDWARE, [subqryTickets-Printer].[#] AS Printer, [subqryTickets-NETWORK].[#] AS NETWORK, [subqryTickets-Telecomm].[#] AS Telecomm
FROM [subqryTickets-HARDWARE], [subqryTickets-NETWORK], [subqryTickets-SOFTWARE], [subqryTicket-IMAC], [subqryTickets-Printer], [subqryTickets-Telecomm];


Thanks,

David Bourdeau