Union query with left join

Hello all and please excuse me for the long post.
I have the following tables in my databases:
[Daily Test Statistics TP],[Daily Test Statistics IV],[Daily Test
Statistics W/S], [fail result],[pass result] with a common field name [Part
No]
I want to have a query which will gives me the number of [Part No] which
are [NG Batch] and [OK Batch] for a given period.
e.g:
[Part No] [NG Batch] [OK Batch]
ABC 2 6
XYZ 1 13

This query works:
SELECT [Daily Test Statistics TP].[Part No], [fail result].[NG Batch],
[pass result].[OK Batch]
FROM ([Daily Test Statistics TP] LEFT JOIN [pass result] ON [Daily Test
Statistics TP].[Part No]=[pass result].[Part No]) LEFT JOIN [fail result]
ON [Daily Test Statistics TP].[Part No]=[fail result].[Part No]
WHERE ((([Daily Test Statistics TP].[Test Date]) Between DateAdd("d",-3-
WeekDay(Date()),Date()) And DateAdd("d",Weekday(Date()),Date())))
GROUP BY [Daily Test Statistics TP].[Part No], [fail result].[NG Batch],
[pass result].[OK Batch];

I don't know how to make the union works for the following please help:
SELECT [Daily Test Statistics TP].[Part No], Count(*) AS [NG Batch]
FROM
[Daily Test Statistics TP]
WHERE ((([Daily Test Statistics TP].[Result])="fail"))
GROUP BY [Daily Test Statistics TP].[Part No]
union
SELECT [Daily Test Statistics IV].[Part No], Count(*) AS [NG Batch]
FROM
[Daily Test Statistics IV]
WHERE ((([Daily Test Statistics IV].[Result])="fail"))
GROUP BY [Daily Test Statistics IV].[Part No]

UNION SELECT [Daily Test Statistics W/S].[Part No], Count(*) AS [NG Batch]
FROM
[Daily Test Statistics W/S]
WHERE ((([Daily Test Statistics W/S].[Result])="fail"))
GROUP BY [Daily Test Statistics W/S].[Part No];

SELECT [Daily Test Statistics TP].[Part No], Count(*) AS [OK Batch]
FROM
[Daily Test Statistics TP]
WHERE ((([Daily Test Statistics TP].[Result])="Pass"))
GROUP BY [Daily Test Statistics TP].[Part No]
union
SELECT [Daily Test Statistics IV].[Part No], Count(*) AS [OK Batch]
FROM
[Daily Test Statistics IV]
WHERE ((([Daily Test Statistics IV].[Result])="Pass"))
GROUP BY [Daily Test Statistics IV].[Part No]

UNION SELECT [Daily Test Statistics W/S].[Part No], Count(*) AS [OK Batch]
FROM
[Daily Test Statistics W/S]
WHERE ((([Daily Test Statistics W/S].[Result])="Pass"))
GROUP BY [Daily Test Statistics W/S].[Part No];

With many thanks for your time .
Zaky.

--
Message posted via http://www.accessmonster.com

Problem solved.
I created a new query containing all the [part no] from the 3 tables and
then make the left join queries.

--
Message posted via http://www.accessmonster.com

Test

x with a line over it,, to dednote "mean of a sample".

Anyone kindly tell me how to do this?

In Access? You can't, unless you're using an RTF control.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"Mathsidiot" wrote in message
...
x with a line over it,, to dednote "mean of a sample".

Anyone kindly tell me how to do this?