[AccessD] A momentary lapse of reason: Part II

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





More information about the AccessD mailing list