[AccessD] Aggregation Aggravation

[Stuart McLachlan]

Bad approach.  Don't have separate fields for the same data in separate workbooks.

What you should start off with is a summary table containing:
PartNum,BOE,Wt,WorkBookNum as  Table1

Now  create  Table2 using :
Select Distinct PartnNum,BOE,Wt from Table1 
Group by PartNum,BOE,Wt

That will give you a list of all the combinations or BOE and Wt for each partnum.

Now create Table3 using:
Select Distinct PartNum, Count(Partnum) as Variations
>From Table2
Where Count(PartNum) > 1

That will give you a list of all Partnumbers where there are variations.

Now delete all unique records in Table 2
Delete * from table2 where PartNum not in (Select Partnum from Table 3)

Now you can easily run various queries against Table2 to determine the quantity and type of 
discrepancies.
 


On 4 Oct 2016 at 15:01, ewaldt at gdls.com wrote:

> I have about 20 tables in a database, each containing a parts list
> imported from an Excel workbook and having about 25 fields. I am
> interested primarily in 3 fields, a part number field (text), a weight
> field (single), and a BoE field (text field). BoE, BTW, stands for
> "Basis of Estimate" and states whether the weight was attained byType,
> physically weight the part (thus an "Actual"), or from CAD
> ("Released", and others.) or just an estimate ("Preliminary
> Estimate"). Each occurrence of a part number in a work book (and
> therefore a table in this database) SHOULD have the same weight and
> BoE for a part number, but that is not always the case. The analyst
> who created the workbook might not have updated the data, or might
> have his/her own preferred estimate for it. The boss wants to know
> which ones have BoEs or weights that disagree from table to table. I
> have created an additional table with a field for part number, and
> then two fields representing each table (BoE and weight), and have
> filled it with the corresponding data. With over 100,000 records and
> about 45 fields, most of it is empty, since most part numbers occur in
> only one table. About 35,000 part numbers occur more than once, so
> those are the ones I care about. I hope the following simplified
> example illustrating this summary table can help you visualize this
> (01, 02, and 03 being table references):
> 
> PartNumber 01BoE 01Wt 02BoE 02Wt 03BoE 03Wt NumOccurrence
>  ABCD001    Actual    14.2         1
>  ABCD002    Actual     12   Actual    12   2
>  ABCE003                            Releas   11    PrelEst 10 2
> 
> No telling how that will look in your email. BTW, I do have the final
> field in this example, which I've attained via VBA. Basically, I don't
> care about record one in this context; it only shows one occurrence.
> Record 2 has two occurrences, and they agree. Yea! OTOH, record 3's
> occurrences do NOT agree.
> 
> What I need is to be able to find how many multi-occurrence records DO
> agree and how many do NOT agree, and for the latter, how many disagree
> because of BoE, and how many because of weight. 
> 
> I have written code to create the summary table, as well as other code
> to create a table telling me how many occurrences are for each part
> number. If you think either is unneeded, don't hesitate to ignore
> either or both. 
> 
> This is not easy to describe. I'm at a standstill, not knowing which
> way to go from here. Help would be definitely appreciated. 
> 
> Thanks.
> 
> Tom Ewald
> Mass Properties
> General Dynamics Land Systems
> 
> ----------------------------------------------------------------------
> This is an e-mail from General Dynamics Land Systems. It is for the
> intended recipient only and may contain confidential and privileged
> information.  No one else may read, print, store, copy, forward or act
> in reliance on it or its attachments.  If you are not the intended
> recipient, please return this message to the sender and delete the
> message and any attachments from your computer. Your cooperation is
> appreciated. && -- AccessD mailing list AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd Website:
> http://www.databaseadvisors.com
>