[AccessD] Wrong number of records returned in Union query

I've replied to this post before...but I have not seen it show up...

this is from the help file:"By default, no duplicate records are returned 
when you use a UNION operation; however, you can include the ALL predicate 
to ensure that all records are returned. This also makes the query run 
faster."

Good Luck,

Mark


>From: "Jim Hewson" <JHewson at karta.com>
>Reply-To: Access Developers discussion and problem 
>solving<accessd at databaseadvisors.com>
>To: "Access Developers discussion and problem 
>solving"<accessd at databaseadvisors.com>
>Subject: Re: [AccessD] Wrong number of records returned in Union query
>Date: Mon, 9 Apr 2007 12:39:29 -0500
>
>Jim,
>I would think that taking out "DISTINCT" in the first query would work.
>Another option - one that I use - is to create each query separately and 
>insure the results of each query are what is needed.
>Then use a simple union between them.
>For example:
>If your first query was qryPlanCapex and your second was qryYTD_PlanCapex 
>the your union query would be:
>
>SELECT *
> >From qryPlanCapex
>Union select *
> >From qryYTD_PlanCapex
>
>As long as each query has the same number of fields and are in the same 
>order it should work.
>
>HTH
>
>Jim
>jhewson at karta.com
>
>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com 
>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Hale, Jim
>Sent: Monday, April 09, 2007 11:39 AM
>To: accessd at databaseadvisors.com
>Subject: [AccessD] Wrong number of records returned in Union query
>
>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;
>
>***********************************************************************
>The information transmitted is intended solely for the individual or
>entity to which it is addressed and may contain confidential and/or
>privileged material. Any review, retransmission, dissemination or
>other use of or taking action in reliance upon this information by
>persons or entities other than the intended recipient is prohibited.
>If you have received this email in error please contact the sender and
>delete the material from any computer. As a recipient of this email,
>you are responsible for screening its contents and the contents of any
>attachments for the presence of viruses. No liability is accepted for
>any damages caused by any virus transmitted by this email.
>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
>
>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com

_________________________________________________________________
Mortgage refinance is Hot. *Terms. Get a 5.375%* fix rate. Check savings 
https://www2.nextag.com/goto.jsp?product=100000035&url=%2fst.jsp&tm=y&search=mortgage_text_links_88_h2bbb&disc=y&vers=925&s=4056&p=5117