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