[AccessD] A momentary lapse of reason

You need a list of *all* suppliers and Accounting Periods which you can then Left Join the 
two tables to.  You can do this with a UNION query on the two tables.

Create a query:

Select SupplierID,AP from PartsReceived
UNION 
Select SupplierID,AP from PartsRejected;

Save this query as qryAllSupplierAPs
(By default, a UNION query selects DISTINCT so this will give you a unique list of all 
SupplierID/AP pairs)

Then create a second query which left joins both tables to this query:

SELECT qryAllSupplierAPs.AP, qryAllSupplierAPs.SupplierID, PartsReceived.Qty, 
PartsRejected.Qty
FROM (qryAllSupplierAPs 
   LEFT JOIN PartsReceived 
         ON (qryAllSupplierAPs.AP = PartsReceived.AP) 
         AND (qryAllSupplierAPs.SupplierID = PartsReceived.SupplierID)) 
   LEFT JOIN PartsRejected 
         ON (qryAllSupplierAPs.AP = PartsRejected.AP) 
         AND (qryAllSupplierAPs.SupplierID = PartsRejected.SupplierID);


On 14 Oct 2009 at 6:05, Johncliviger at aol.com wrote:

>  
> Good morning all
> Please forgive the title but Pink Floyd's cd is in front of me and I  
> thought the title quite apt to my mental processes this morning.  But...  
>  
>  
> I have problem: I have 2 tables. One holds Parts Rejected and the  other 
> holds Parts Received. Each table has 2 common fields, SupplierID and AP  
> (Accounting Period).  In  PartsRejects table I have total parts rejected by 
> supplierID and AP. In  PartsReceived I have total parts received by supplierID 
> and AP.  
> When I run a query linking these tables have missing rows in both. While  
> this many appear to be the classical relationship of  many-to-many, but is 
> it?  The PartsReceived table is imported to  Access once each accounting 
> period from accounts software.  
> A question!  I need a query to show All rows from both  tabels, however 
> when I use either a Left Join or Inner Join I get all rows from  the left sided 
> table but loose rows from the right side where there is no match  or rows 
> are absent on the Left sided table.   
> Creating a Link or Junction table causes problems because I have to  
> populate this table each accounting period. 
> Anyone done this type of thing before? 
> TIA 
> johnc
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com