I have a problem that I am at a loss to work out...
I have about 600 AutoCAD drawings that have 1 to 50 pieces of
equipment on each drawing.
I need to summarize this equipment by manufacturer and model number so
that I know how many of each piece to order.
Some of the same pieces of equipment will appear on up to 6 different
I am able to extract this equipment with the following attribute
fields into an Excel file that I import (one at a time!) into the
Equipment List database:
System name (Unique to each piece of equipment)
Drawing number (Which drawing this was extracted from)
After I spent a couple of weeks extracting and importing I did a
search for duplicates so that a summary wouldn't result in an
incorrect equipment count.
Of course I found lots of duplicates because many pieces of unique
equipment were(correctly)shown on multiple drawings. These pieces of
equipment were shown on multiple drawings because they have subsystems
that touch equipment on multiple drawings.
I copied the drawing numbers of the duplicates into a single record's
drawing number field (a repeating field) so that searches could be
performed by drawing.
I then erased the all of the duplicate records for the given system
name. There were around 4000 duplicates!
Now I can do summaries and order the correct amount of equipment.
Unfortunately the drawings keep changing. Rather than maintain the
database as the drawings change we want to go through the process of
re-extracting and re-importing, don't ask why! :)
The straightforward way to do this is to search for the drawing # that
has changed and re-import. The problem to begin with is that if a
piece of equipment is found on multiple drawings its record is erased
for all drawings.
Sounds like the repeating fields need to be done away with and the
database be broken up into a relational database. (I think this
anytime that I stoop to using a repeating field!)
The problem is that I see no way to do a summary with multiple
instances of the same piece of equipment even if this was made
Does anyone have any clues that may boot my head into a different way
to approach this problem?