new data base and video professor was a deadend, please help

[Guest]

i am trying to create a data base to manage our msds sheets for a new
factory. hazardous ingredient cas numbers is my only sticking point. i want
to use a separate cas number table. individual msds can have zero to 20 cas
numbers that need to be assigned when i attempted to set it up, i can only
get one cas number per msds into the database. when it is on the data entry
form i would also like it to open a drop down window that adjusts in length
as more cas numbers are added for a particular msds.

any suggestions would be greatly appreciated

 

[Ed Warren]

msds have many cas numbers:
each cas number can be related to many msds (assuming there is 20 allowable
cas numbers)
so you have a many:many relationship.
so you should have

msds table: list of msds (whatever they are)

cas table : list of cas numbers and some meaningful text

msds_cas table: a relationship between msds and 0 to all the cas

you will need the following forms

main msds form (enter the msds stuff)
main cas number form (enter the cas information)
msds_cas form to use as a subform in the Main msds from (to enter the cas
values related to tha specific msds)
it will consist of a textbox to hold the msdsKey field and a dropdownbox
to capture the casKey field. and will be linked to the main form through the
msdsKey field.

Hope this helps some.

Ed Warren