Stuart McLachlan
stuart at lexacorp.com.pg
Thu Oct 15 16:02:44 CDT 2009
John, Take a look again at my and Gustav's original replies. You are trying to this in one pass. You need to do it in two stages. 1. Use a Union query (without All) to get a list of all SupplierIDs and AP_Nos in both tables. Do NOTt include quantities in this query. 2. Left join both tables onto the Union query to get the quantities involved. The way you are combining rows including quantities will *always* give you either more or less than the desired rows depending on whether or not you are using the ALL qualifier. -- Stuart On 15 Oct 2009 at 7:25, Johncliviger at aol.com wrote: > > Hi Guys > Following on from my recent post the Union query works. That is it returns > 24498 rows. The problem is the tbl_PPMSupplier contains 23320 rows and > tbl_Rejection has 100 rows. I expected the result view to have total of the 2 > tables eg 23420 rows. the Union Q is returning 1078 rows more that > expected. Any suggesstions? > TIA > johnc > SELECT Distinctrow tbl_PPMSupplier.AP_No, tbl_PPMSupplier.SupplierID, > tbl_PPMSupplier.GRQ > FROM tbl_PPMSupplier > WHERE ((tbl_PPMSupplier.AP_No) Between "2009-ap04" And "2009-ap09") > order by tbl_PPMSupplier.AP_No > UNION ALL SELECT tbl_Rejection.AP,tbl_Rejection.SupplierID, > tbl_Rejection.QuantityAtFault > FROM tbl_Rejection; > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com