How to combine two queries to show only one date 2

[netrusher]

I have two queries that I want to combine. See code below:
The first query gives me a total of how many leaks there
were by day.

The second query give me a total of how many WorkUnits there
were by day.

When I combine the two I only want to see the date once and
then the Leak totals and WorkUnit totals. I am thankful for
all advice and help. I am also open to other ways to get
the info I need.

Date LeakTotals WUTotals
05/27/08 6 49

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;

SELECT vTbl.Day, 
     Count(*) AS [WU Totals]
FROM 
     (SELECT DISTINCT Format(TodaysDate,'yyyy-mm-dd') AS [Day], 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.Day;

 

[netrusher]

A better example would be

Date LeakTotals WUTotals
05/27/08 6 49
05/28/08 3 55
05/29/08 10 43

 

[HarleyQuinn]

Something like:

SELECT
     Format(TodaysDate,'yyyy-mm-dd') AS [Date],
     WU.[WU Totals],
     Count(*) AS [Leak Totals]
FROM
     WorkUnitsFaultsMainTBL,
     (
     SELECT vTbl.Day as WUDay,
     Count(*) AS [WU Totals]
FROM
     (SELECT DISTINCT Format(TodaysDate,'yyyy-mm-dd') AS [Day], 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.Day;
     ) WU
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"))
AND   Format(TodaysDate,'yyyy-mm-dd') = WU.WUDay)
GROUP BY
     Format(TodaysDate,'yyyy-mm-dd'), WU.[WU Totals]
ORDER BY
     Format(TodaysDate,'yyyy-mm-dd'), WU.[WU Totals], Count(*) DESC;

Please bear in mind this is untested as I don't have a copy of access at work to try this with.

Hope this points you in the right direction.

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[netrusher]

Harley,

Thanks for the help, that seems to work.

I found out that I need to divde the Leak totals by the
WU totals and get a pct of leaks. I can do that if I
have separate queries. Can you tell me how to add to
this query to accomplish this?

 

[Golom]

SELECT
       Format(TodaysDate,'yyyy-mm-dd') AS [Date],
       WU.[WU Totals],
       Count(*) AS [Leak Totals],
       [red](Count(*) / WU.[WU Totals] * 100.0) As [Percent][/red]
FROM
     WorkUnitsFaultsMainTBL,
     (
       SELECT vTbl.Day as WUDay,
              Count(*) AS [WU Totals]
       FROM
             (SELECT DISTINCT Format(TodaysDate,'yyyy-mm-dd') AS [Day], 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.Day ) WU

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"))
  AND Format(TodaysDate,'yyyy-mm-dd') = WU.WUDay)

GROUP BY Format(TodaysDate,'yyyy-mm-dd'), WU.[WU Totals]

ORDER BY Format(TodaysDate,'yyyy-mm-dd'), WU.[WU Totals], Count(*) DESC;

 

[netrusher]

Thanks Golom, that works great.

I have another question. How can I change the output of the
query to look like this:

Date 2008-05-01 2008-05-05 2008-05-06
WU Totals 47 40 44
Leak Totals 9 3 2
Percent 19.15% 7.50% 4.55%

 

[Golom]

Check out Cross-tab queries

 

[netrusher]

I have been working on that but I can't seem to take
the current query and turn it into a crosstab. Any
suggestions or examples or do I need to create a whole
new query?