[AccessD] A momentary lapse of reason: Part II

Johncliviger at aol.com Johncliviger at aol.com
Thu Oct 15 15:28:02 CDT 2009


Hi John C and Gustav
 
I've had a feeling that the Between filter may be causing problems, but I'm 
 stuck with "xxxx-AP99" text format. But I'm unsure wether I'm barking up 
the  right tree. Can I re-state what I'm trying do. It will help me I hope.  
I've got a table in a db that records faults on components. It records  
SupplierID, AP (Accounting Period) and QtyAtFault (Quantity at Fault) in table  
tbl_Rejection. In addition each month I import (from SAP) into Access  a 
list of all components received, eg, SupplierID, AP and QtyReceived and  dump 
them in a table(tbl_PPMSupplier). 
 
>From this I need to calculate a monthly and 6 monthly rolling  
part-per-million calculation by supplier and by AP.
 
Simple I thought. Create a group by query with a Sum on the QtyAtFault. Do  
the same for the imported table except with Sum on the Components Received. 
Then  pull the 2 queries together with a join on the SupplierID and AP. 
This is where  the fan and brown stuff collide.  The problem is that I can have 
missing  rows on either side on the join and where there are missing rows 
it takes out  the other side.
 
So I thought that a union Q would come to the rescue. I'm have me  doubts. 
Maybe I should open the hood on VBA!
 
Anyway It's 21:30 here, my brain hurts and off for a pint of Copper  
Dragon... Well maybe more than one!
 
Thanks for your patience
 
john cliviger
 
 
 



More information about the AccessD mailing list