[AccessD] Aggregation Aggravation

James Button jamesbutton at blueyonder.co.uk
Tue Oct 4 14:38:31 CDT 2016


Thoughts - 
A table with all the part numbers and any one of the entries from your aggregate

That will give you a start set of 'all reported parts' that have data against
the part number) 
A separate table may be of use to indicate parts with no data entry 

In turn, match that against each of the source sets generating ( inner/outer?
Whatever) 
a table of 'parts @ source workbook' where it differs from the value in the
generated 'all reported parts' entry in the start 

You can use those reports individually to advise people of the need for
checking/correction
And you can then re-do an aggregation to report all parts where there are
multiple differing values.

I suspect the actual technique to use will depend on what you want to do with
the results. 

Also - as you have the aggregate table, you could just process that to generate
multiple result tables, and then aggregate them as you want.

JimB


-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
ewaldt at gdls.com
Sent: Tuesday, October 4, 2016 8:02 PM
To: accessd at databaseadvisors.com
Subject: [AccessD] Aggregation Aggravation

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.
&&
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



More information about the AccessD mailing list