Union Query not showing all columns 1

[netrusher]

Below is the code that I have for a query. It is a Union query. I am try to display two three columns.
Date, Leak Totals, WU Totals.

The code below is showing two columns.
Date, Leak Totals
It is giving me the WU Totals but in the same column as the Leak Totals. What am I doing wrong?

SELECT 
     Format(TodaysDate,'yyyy-mm-dd') AS [Date], 
     Count(*) AS [Leak Totals]
FROM 
     WorkUnitsFaultsMainTBL
WHERE 
     (((WorkUnitsFaultsMainTBL.Problem) Like "*leak*") AND ((WorkUnitsFaultsMainTBL.TodaysDate) Between [Forms]![Queries_ReportsFRM]![StartDateTxt] And [Forms]![Queries_ReportsFRM]![EndDateTxt]) AND ((WorkUnitsFaultsMainTBL.BuildID) In ("E010","C809","F001","C810","F187","A910","M173","M174")))
GROUP BY 
     Format(TodaysDate,'yyyy-mm-dd')
ORDER BY 
     Format(TodaysDate,'yyyy-mm-dd'), Count(*) DESC
UNION ALL
SELECT 
     vTbl.Month, Count(*) AS [WU Totals]
FROM 
     (SELECT DISTINCT Format(TodaysDate,'yyyy-mm-dd') AS [Month], WorkUnit
FROM 
     WorkUnitsFaultsMainTBL
WHERE 
     BuildID IN ('E010','C809','F001','C810','F187','A910','M173','M174')
AND 
     PossibleCause <> 'Out of Stock'
AND 
     (TodaysDate BETWEEN [Forms]![Queries_ReportsFRM]![StartDateTxt] And [Forms]![Queries_ReportsFRM]![EndDateTxt])) AS vTbl
GROUP BY vTbl.Month;

 

[Golom]

A UNION matches columns by position. If you want three columns then you need to define 3 columns in every select.

SELECT 
Format(TodaysDate,'yyyy-mm-dd') AS [Date], 
Count(*) AS [Leak Totals][red], 0 As [WU Totals][/red]
FROM 
WorkUnitsFaultsMainTBL
WHERE 
(((WorkUnitsFaultsMainTBL.Problem) Like "*leak*") AND ((WorkUnitsFaultsMainTBL.TodaysDate) Between [Forms]![Queries_ReportsFRM]![StartDateTxt] And [Forms]![Queries_ReportsFRM]![EndDateTxt]) AND ((WorkUnitsFaultsMainTBL.BuildID) In ("E010","C809","F001","C810","F187","A910","M173","M174")))
GROUP BY 
Format(TodaysDate,'yyyy-mm-dd')
ORDER BY 
Format(TodaysDate,'yyyy-mm-dd'), Count(*) DESC
UNION ALL
SELECT 
vTbl.Month, [red]0, [/red]Count(*) AS [WU Totals]
FROM 
(SELECT DISTINCT Format(TodaysDate,'yyyy-mm-dd') AS [Month], WorkUnit
FROM 
WorkUnitsFaultsMainTBL
WHERE 
BuildID IN ('E010','C809','F001','C810','F187','A910','M173','M174')
AND 
PossibleCause <> 'Out of Stock'
AND 
(TodaysDate BETWEEN [Forms]![Queries_ReportsFRM]![StartDateTxt] And [Forms]![Queries_ReportsFRM]![EndDateTxt])) AS vTbl
GROUP BY vTbl.Month;

 

[netrusher]

Thanks Golom,

Now that code give me 3 columns but in the date column
it gives me each date twice.

Date Leak Totals WU Totals
06/16/08 3 0
06/17/08 1 0
06/18/08 2 0
06/19/08 5 0
06/16/08 0 30
06/17/08 0 45
06/18/08 0 47
06/19/08 0 24

I would rather just have the date once and the two totals
and skip the 0's

 

[Golom]

OK. That's how a UNION works but you have the basics to get what you want

SELECT [Date], 
       MAX([Leak Totals]) As [Max Leak Totals],
       MAX([WU Totals])   As [Max WU Totals]

From
  (
    [red]Your big UNION Query[/red]
  ) As X

Group By [Date]

Order By [Date]

 

[netrusher]

Golom,

Thanks, that is working fine. How would I include the
following so I can get a pct.

I would like to divide the Max Leak Totals by the
Max WU Totals.

SELECT [Date], 
       MAX([Leak Totals]) As [Max Leak Totals],
       MAX([WU Totals])   As [Max WU Totals]

From
  (
SELECT 
Format(TodaysDate,'yyyy-mm-dd') AS [Date], 
Count(*) AS [Leak Totals], 0 As [WU Totals]
FROM 
WorkUnitsFaultsMainTBL
WHERE 
(((WorkUnitsFaultsMainTBL.Problem) Like "*leak*") AND ((WorkUnitsFaultsMainTBL.TodaysDate) Between [Forms]![Queries_ReportsFRM]![StartDateTxt] And [Forms]![Queries_ReportsFRM]![EndDateTxt]) AND ((WorkUnitsFaultsMainTBL.BuildID) In ("E010","C809","F001","C810","F187","A910","M173","M174")))
GROUP BY 
Format(TodaysDate,'yyyy-mm-dd')
ORDER BY 
Format(TodaysDate,'yyyy-mm-dd'), Count(*) DESC
UNION ALL
SELECT 
vTbl.Month, 0, Count(*) AS [WU Totals]
FROM 
(SELECT DISTINCT Format(TodaysDate,'yyyy-mm-dd') AS [Month], WorkUnit
FROM 
WorkUnitsFaultsMainTBL
WHERE 
BuildID IN ('E010','C809','F001','C810','F187','A910','M173','M174')
AND 
PossibleCause <> 'Out of Stock'
AND 
(TodaysDate BETWEEN [Forms]![Queries_ReportsFRM]![StartDateTxt] And [Forms]![Queries_ReportsFRM]![EndDateTxt])) AS vTbl
GROUP BY vTbl.Month
  ) As X

Group By [Date]

Order By [Date]

 

[Golom]

SELECT [Date], 
       MAX([Leak Totals]) As [Max Leak Totals],
       MAX([WU Totals])   As [Max WU Totals],
       (MAX([Leak Totals]) / MAX([WU Totals]) * 100) As [Percent]

 

[netrusher]

Golom,

Is there any way for me to use this query or a number of
queries to come up with the following?:

I would like for the dates to go across as columns.

I would like for the Leak totals to be a row under
the date columns.

I would like for the WU totals to be a row under that.

I would like for the Pct totals to be a row under that.

I have tried cross tab but cannot get it figure out.

Any help will be appreciated.

SELECT X.Date, Max(X.[Leak Totals]) AS [Max Leak Totals], Max(X.[WU Totals]) AS [Max WU Totals], (Max([Leak Totals])/Max([WU Totals])*1) AS [Percent]
FROM [SELECT 
Format(TodaysDate,'yyyy-mm-dd') AS [Date], 
Count(*) AS [Leak Totals], 0 As [WU Totals]
FROM 
WorkUnitsFaultsMainTBL
WHERE 
(((WorkUnitsFaultsMainTBL.Problem) Like "*leak*") AND ((WorkUnitsFaultsMainTBL.TodaysDate) Between [Forms]![Queries_ReportsFRM]![StartDateTxt] And [Forms]![Queries_ReportsFRM]![EndDateTxt]) AND ((WorkUnitsFaultsMainTBL.BuildID) In ("E010","C809","F001","C810","F187","A910","M173","M174")))
GROUP BY 
Format(TodaysDate,'yyyy-mm-dd')
ORDER BY 
Format(TodaysDate,'yyyy-mm-dd'), Count(*) DESC
UNION ALL
SELECT 
vTbl.Month, 0, Count(*) AS [WU Totals]
FROM 
(SELECT DISTINCT Format(TodaysDate,'yyyy-mm-dd') AS [Month], WorkUnit
FROM 
WorkUnitsFaultsMainTBL
WHERE 
BuildID IN ('E010','C809','F001','C810','F187','A910','M173','M174')
AND 
PossibleCause <> 'Out of Stock'
AND 
(TodaysDate BETWEEN [Forms]![Queries_ReportsFRM]![StartDateTxt] And [Forms]![Queries_ReportsFRM]![EndDateTxt])) AS vTbl
GROUP BY vTbl.Month
  ]. AS X
GROUP BY X.Date
ORDER BY X.Date;

 

[Golom]

I would need to resort to code for something like that.

SQL is designed to retrieve and manipulate data and, as such, it is constrained to limitations on how columns and rows are defined and constructed.

This is more of a reporting challenge and I would probably go with Excel or Crystal Reports that can handle this sort of variability.