[AccessD] Wrong number of records returned in Union query

If you want the duplicates, why are you using the "DISTINCT" keyword, 
which tells Access that you don't want them? If you want the duplicates, 
don't use either of the distinct or distinctrow keywords.

Sorry if this is too simple, but your SQL code doesn't seem to match what 
you say you're looking for.

Thomas F. Ewald
Stryker Mass Properties
General Dynamics Land Systems


Date: Mon, 9 Apr 2007 11:39:02 -0500 
From: "Hale, Jim" <Jim.Hale at FleetPride.com>
Subject: [AccessD] Wrong number of records returned in Union query
To: accessd at databaseadvisors.com
Message-ID:
 <FBEA4C39D7934A47A057CDFD3CF070EF12AAD6 at corp-es01.fleetpride.local>
Content-Type: text/plain

I have a simple select  query that returns a different number of records
when it is run by itself than when it is run inside a union query. I 
believe
it is because duplicate records (which I need) are being dropped.  I have
tried the distinct and distinctrow key words in the union query to no 
avail.
Can someone tell me how to fix this? Thanks

The SQL for the union query is as follows ( the first query is the broken
one:

SELECT  DISTINCT qryPlanCapex_sub.fldCOMPY, qryPlanCapex_sub.fldDEPT,
qryPlanCapex_sub.Name, qryPlanCapex_sub.fldProjectNo,
qryPlanCapex_sub.fldDescr, qryPlanCapex_sub.fldYr, 
qryPlanCapex_sub.fldQtr,
qryPlanCapex_sub.fldAmt, qryPlanCapex_sub.Kind, 
qryPlanCapex_sub.fldRegion,
qryPlanCapex_sub.PlanPrNO, qryPlanCapex_sub.fldArea FROM qryPlanCapex_sub;
Union
SELECT qryYTD_Capex_ApprovedAmt_sub.fldCOMPY,
qryYTD_Capex_ApprovedAmt_sub.fldDEPT, qryYTD_Capex_ApprovedAmt_sub.Name,
qryYTD_Capex_ApprovedAmt_sub.CAR_Number,
qryYTD_Capex_ApprovedAmt_sub.Description, qryYTD_Capex_ApprovedAmt_sub.Yr,
qryYTD_Capex_ApprovedAmt_sub.Qtr, qryYTD_Capex_ApprovedAmt_sub.CAR_Amount,
qryYTD_Capex_ApprovedAmt_sub.Kind, qryYTD_Capex_ApprovedAmt_sub.fldRegion,
qryYTD_Capex_ApprovedAmt_sub.Internal_Asset_ID,
qryYTD_Capex_ApprovedAmt_sub.GLMBRANCHES.fldArea
FROM qryYTD_Capex_ApprovedAmt_sub;
UNION
SELECT qryYTD_Capex_sub.fldCOMPY, qryYTD_Capex_sub.fldDEPT,
qryYTD_Capex_sub.Name, qryYTD_Capex_sub.CAR_Number,
qryYTD_Capex_sub.Description, qryYTD_Capex_sub.Yr, qryYTD_Capex_sub.Qtr,
qryYTD_Capex_sub.Acquired_Value, qryYTD_Capex_sub.Kind,
qryYTD_Capex_sub.fldRegion,
qryYTD_Capex_sub.Internal_Asset_ID,qryYTD_Capex_sub.GLMBRANCHES.fldArea
FROM qryYTD_Capex_sub;


This is an e-mail from General Dynamics Land Systems. It is for the intended recipient only and may contain confidential and privileged information.  No one else may read, print, store, copy, forward or act in reliance on it or its attachments.  If you are not the intended recipient, please return this message to the sender and delete the message and any attachments from your computer. Your cooperation is appreciated.