storing data in a table

[robin a]

I am using access 2007. This is a training database in which classes are assigned to employees by their job title. I have the Class_Catalog table which stores all class information, including a job_title field. My problem is, now that I am importing data, I've found that since up to everyone in the company (450 people) are required to take many of these classes. I don't want to add the same class 450 times with each applicable job title because I don't think that's the smart way to do it and I don't want to bloat the database. I can't think of any other way to make sure classes get assigned to the proper job_titles.

I would appreciate any suggestions.

[robin a]

I wanted to add that there are only 38 job titles. would making job titles a mutli-valued field work or would it complicate all of my queries?

[Seth Schrock]

From what I'm reading, I believe that you would want a table of job titles and a table of classes. Also, since each class could be assigned to multiple job titles and I'm assuming that each job title could be required to take multiple classes, you have a many to many relationship which requires a joining table. This would consist of the primary keys of the two tables. You then set the combination of those two fields as the primary key of the joining table. This would allow you to make all the assignments needed.

[zmbd]

Seth is certainly on the right track here with normalizing the database. I've sent you a PM with several links that might prove helpfull

[Seth Schrock]

I wouldn't do a multi-valued field as it would really complicate your queries.

[robin a]

Seth,
Are you saying that after creating the join table, I would still add a lot of records to the class_catalog table? e.g. with a multivalued field it would look like this:

Class_ID Job_Title Class_Name
1 machine operator, handpacker, coach first aid

if it isn't multivalued it would look like this:

Class_ID Job_Title Class_Name
1 machine operator first aid
2 handpacker first aid
3 coach first aid

[Seth Schrock]

That is correct. The problem with the multivalued field is that if you would ever want to query the database to see what classes a certain job title, it would require a lot of coding. Even if you don't need that ability now, it is always best to design a database according to normalization rules. This allows you to add to your database without having to make major design changes to the existing database. Also, because the join table is just those two fields (occasionally there are more, but in this case there are only two) you don't have to worry about bloating the database. To save you from having to type everything, you can create a form based on the join table and use combo boxes to populate the table.

I believe I might have forgotten to mention adding the relationships between the three tables. Classes table related to the join table as one-to-many and job titles table related to the join table the same way.

Here is something else you can do to make it easier to add all of those combinations. Say you have a form for your classes based on the classes table and a form for your job titles based on the job titles table. We will name them frmClasses and frmJobTitles respectively. You would then create a form in datasheet view based on the join table. We will call it frmJoin. You would then put frmJoin on frmClasses as a subform. You can also do this on frmJobTitles. Because of the relationships, access will be able to automatically find the relationship between the main form and the subform form. This will make it so that you only have to add information to the one field instead of both.

[TheSmileyCoder]

What Seth and zmbd are saying is the "best practice" approach to the problem. People tend to think that the multivalued fields are nice to use, but if you look around a bit on the net, you will see that almost all seasoned access developers will tell you not to use that feature.

[robin a]

Thank you everyone. I now know NOT to use the multivalue data type. Turns out my existing design included a join table and it works well. I just don't like having to put in a class up to 40 times BUT, I do have a selection for ALL that assigns to every employee. Everyone's advice was very clear and helpful. Thank you everyone.