vendor enviromental database design

[nirie1]

I am creating an environmental database for my products. I have planned it
out as follows but am not sure how to input or connect several items. The
information will be originally imported from surveys and updated manually.
Can someone please help?

I am sending surveys to vendors to get back info about chemical content.
Survey will include a list of chemicals and they will enter the qty as w/w
(weighttoweight). Will use querys to calculate if our parts are compliant to
the various standards. Should

Will setup switchboard with user able to enter part number to display if PFOS,
RoHS and other standards are met for part number entered. Other switchboard
screens will give details about the contents of the part in context to each
reg such as PFOS, RoHS, etc. For example the RoHS screen will show how much
of the five chemicals are in the product.

tblProduct: PartNo, Rev, Description, weight (I want to let the PartNo be the
primary key, problem is that there will be duplicate part numbers with
different revs not sure how to address this. The other issue is that some of
our parts are made from subparts. The subparts all have part numbers as well
and we will need the same info for the subparts. Would I set up a components
table which will using a query will tally the components and calculate
compliance?

tblDatasheets: ChemSpec, ChemSpecRecd(date), SDS, SDSRecd(date), VendorPartNo
(as foreign key) (each vendor will indicate if they are sending in a
datasheet for their individual parts)

tblRoHS: vendorpartno (foreign key) autonumber(primary key) there will be
three yes/no columns (compliant, exempt, not compliant). If its compliant 5
of the chemicals on the tblchemicals should be 0. If exempt then will need to
input info in tblRoHSExemptions (there maybe multiple exemptions for a part
although unlikely)

tblRoHSExemptions: it has 30 yes/no categories which will tie to each vendors
part number. Should the primary key be autonmbered?

tblChemicals: there are 25 chemicals which will be tied to each vendor part
no as the foreign key. Again not sure if primary key should be autonumbered.

tblVendor: VendorName, VendorNo(primary key), Fax, Email, Contact, Vendor
PartNo is foreign key.

tblVendorParts: Vendor Part No, DateContacted, Notes(using this table as a
bridge for many to many relationships, not sure if anything else is needed in
this table besides an autonumber primary key and VendorNo as foreign key)

 

[Jeff Boyce]

That's quite a bit of information, but I'm not sure what specific question
you are asking.

These newsgroups and the folks who volunteer here are great at
answer/suggesting ideas re: specific questions.

More general descriptions usually result in more general suggestions.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Fred]

Echoing what Jaff said plus.....

You are describing whole universe of work there. A sound baby step would
be to start building and loading your "your products" table (tblProduct).
If the part number (PartNo) repeats it (alone) would not be a suitable PK.
Ditto for the real life situation. If the product is going to vary (with
revs) in a way that is relevant to your mission, then it (alone) is also
unsuitable as a identifying name. A couple ideas would be to add rev #'s as
a suffix to the part number in a consistent manner, or make a 2 field PK
(PartNo and RevNo)

Fred

 

[nirie1]

Fred, your 2 field PK worked. It was really simple!!! Thanks.

I am going to follow what Jaff said and let the other fields autonumber.Sorry
about giving so much info. I tend to ramble and think outloud. LOL

Still trying to figure out what I am going to do about component parts.
Thinking to create two tables one tblBOM (autoPK) and another tblComponent
(2fieldPK) with tblBOM connecting tblProducts and tblComponents. Sometimes
just one hint helps it all come together. Thanks

 

[Fred]

Your info was all good. It's just that you were describing an overall
project rather than a particular question.

I run a manufacturing company (Access and forums are my "hobby") that uses
multi-level BOMS, and so an item which is an assembly might, a few "minutes"
later become a mere part (sub assembly) for a larger assemble. For this and
other reasons, every part, assembly, sub assembly etc. is just an item, all
of them in the same table.

BOMS are created in a BOMItems table (which has a record for each instance
of use of a part to build something) where both the part number of the
assembly and the part number's of the components are linked to the same
previously mentioned table.

(of course, shorten my lengthy field names!!!!!!!!)

Essential Fields Are

PartNumberOfTheAssembly
PartNumberOfItemBeingUsed
QuantityofTheItemBeingUsed

We also include (mostly for engineering documentation purposes to tie in
with schematics etc.) )

BomItemID (PK, autonumber)
NotesOnThisInstanceOfUsageOfAnItem
DesignationOfThisUsageOfAnItem

For example, if we used two IC's of the same part number to build something,
we might enter two records with a quantity of one in each and

Designation Notes
IC1 Input Buffer
IC2 Output buffer


Incidentially, this BOM structure can also contain 1 item BOMS which record
substitutions or production stages of an item.