[AccessD] Aggregation Aggravation

[ewaldt at gdls.com]

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 by 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.
&&