jwcolby
jwcolby at colbyconsulting.com
Thu Oct 15 17:19:15 CDT 2009
John, One thing to do is simply build a temp table and append each query into that table. Doing this would allow you to see the resulting records from each query that you wanted to union. check that each query returns the records you expect, and if not find out why not. Look at the total in the temp table. See if the results are what you expect, and if not discover why not. Go back to the union and see if the results match what is in the temp table, if not discover why. Breaking the problem down like this might allow you to discover where your expectations are not being met, and maybe even why your expectations are not being met. John W. Colby www.ColbyConsulting.com Johncliviger at aol.com wrote: > 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 > > >