If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
vendor enviromental database design
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) |
#2
|
|||
|
|||
vendor enviromental database design
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 specific questions. More general descriptions usually result in more general suggestions. Regards Jeff Boyce Microsoft Office/Access MVP "nirie1" u46772@uwe wrote in message news:8b5ce664ffea0@uwe... 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) |
#3
|
|||
|
|||
vendor enviromental database design
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 |
#4
|
|||
|
|||
vendor enviromental database design
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 wrote: 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 |
#5
|
|||
|
|||
vendor enviromental database design
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. |
Thread Tools | |
Display Modes | |
|
|