Combine two queries into one

[EdAROC]

Situation: Select for vendors who (1) had PO activity in 2006 or (2) vendor record created in 2006.

So far my technique has been to create a Totals query (design view) to get the last PO date and then use that query in another to get the desired records. Here are the SQL Views of the queries:

#1 Query: qryLastPODate
SELECT POHEADER.POHVCODE, Max(POHEADER.POHORDDATE) AS MaxOfPOHORDDATE
FROM POHEADER
GROUP BY POHEADER.POHVCODE;

#2 Query: qryReportData
SELECT VENDOR.VNCODE, VENDOR.VNNAME, qryLastPODate.MaxOfPOHORDDATE, VENDOR.VNTYPE
FROM VENDOR INNER JOIN qryLastPODate ON VENDOR.VNCODE = qryLastPODate.POHVCODE
WHERE (((qryLastPODate.MaxOfPOHORDDATE)>="1/1/2005") AND ((VENDOR.VNTYPE)="A"));

It's time for me to go to a new level. How can these be combined into one query statement?

 

[Golom]

You need a UNION query but you will need to use SQL view. Design view can't handle union queries.

SELECT POHEADER.POHVCODE As [Vendor Code]
     , '?' As [Vendor Name]
     , Max(POHEADER.POHORDDATE) AS [Max Header Date]
     , '?' As [Vendor Type]

FROM POHEADER

GROUP BY POHEADER.POHVCODE, '?', '?'

UNION

SELECT VENDOR.VNCODE
     , VENDOR.VNNAME
     , qryLastPODate.MaxOfPOHORDDATE
     , VENDOR.VNTYPE

FROM VENDOR INNER JOIN qryLastPODate 
     ON VENDOR.VNCODE = qryLastPODate.POHVCODE

WHERE qryLastPODate.MaxOfPOHORDDATE >= #1/1/2005# 
  AND VENDOR.VNTYPE ='A'

You must have the same number of fields in the same order to be able to combine them in a UNION.

 

[PHV]

You wanted this ?
SELECT V.VNCODE, V.VNNAME, L.MaxOfPOHORDDATE, V.VNTYPE
FROM VENDOR V INNER JOIN (
SELECT POHVCODE, Max(POHORDDATE) AS MaxOfPOHORDDATE
FROM POHEADER GROUP BY POHVCODE
) L ON V.VNCODE = L.POHVCODE
WHERE L.MaxOfPOHORDDATE >= #2005-01-01# AND V.VNTYPE = 'A'


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886