[AccessD] Aggregation Aggravation

[David Emerson]

Hi Tom,

If I understand you correctly, another way of approaching it is to have a
single table with the three fields and put all the records into the table
(you might want to include a 4th field for the original table the data came
from).  Then create a Totals query that groups the fields and also includes
a count.  The results will tell you how many of each combination you have.
Then you could create another Totals query on the first query to count how
many parts have multiple records.  Using filtering you should be able to
come up with your two lists.

Regards

David Emerson
Dalyn Software Ltd
Wellington, New Zealand



-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
ewaldt at gdls.com
Sent: Wednesday, 5 October 2016 8:02 a.m.
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