Multivalued Field or Sub-Table? (1 Viewer)

[Opcode]

I am working on my final project for school, and attempting to fulfill the criteria that states, "Use as many Access tools as possible in your database."

I created a table for employees of a fictional airline. I want to track their certifications. Of course, employees could have many roles, and many certifications: college degree, airport certification, safety and security, pilot licenses, etc. I thought this would be a good time to add a multivalue field to my tblEmployee table. I also decided to be adventurous, and use more than 1 column, even though I don't know if that actually works in this context and it isn't in my textbook. Now, I have 4 columns and 18 fields, and I'm wondering if this is a good idea? I'm especially wondering as the next window in the Lookup Wizard asks me which column I want to use that uniquely IDs the row, so it can store that value in my database. On one hand, a sub-table might make more sense. On the other, my grade depends on using as many Access tools as I can.

Any suggestions?

 

[wmphoto]

I would create a table. What you're doing when you type in the lookup values yourself is creating a domain which isn't a table (or at least not one you can view and edit in the same way as other tables). So really you're not using a new feature, just be sure to explain your knowledge of this in your work. If you want your domain to have multiple columns, it is much easier if it exists as a table, really I would only use a look up field where you type the values yourself for really simple domains (such as M or F) for gender.

 

[Opcode]

I suspected that Lookup Fields should be limited to simple tables with only a few options. But, how do I link a tblCertifications table to tblEmployee? I think I want to use the certifications to determine who is qualified to do various jobs for the airline, so I would probably want to do lookups that say something like, "all employees who have a private pilots license and CPR training." I'm trying to figure how that link would work in the tblEmployee table.

 

[wmphoto]

You would create a many-to-many relationship, as one employee can have many certifications and one certification can be held by many employees.

You create a 3rd table to link the employees and their certifications. You could call it something tblEmployeeCertifications so that you know that it links one table to another (that's the format I follow in my databases but it doesn't have to be like that)

The 3rd table only has to have 3 fields: its own Primary Key; a lookup field that selects a record from employees and one that selects a record from certifications (the foreign keys). And then that's it, if records from this table are created as a subform or subreport in a form or report about employees then it will list their certifications and vice versa in a form/report about certifications.

I think that the way to have a query which finds employees with more than one specified certification is to create one query (based on the new 3rd table) to find employees with certification A, and then another similar query to find employees with certification B and then a final 3rd query based on the employees table which only returns employees who appear in both the other queries, though I've never done exactly that thing before.

Although your new table, tblEmployeeCertifications only needs those 3 key fields, if you wanted to say, have a field showing what grade that employee got, then that would be the table to add it in. fields for additional comments with the data type 'memo' are also good practice.

EDIT: I just checked and that query is what you do. the SQL for my quick query I tested it with was:

SELECT Employees.Employeename, Employees.ID
FROM (Employees INNER JOIN Firstaid ON Employees.ID = Firstaid.Employeename) INNER JOIN [pilots licence] ON Firstaid.Employeename = [pilots licence].Employeename;

In my example I called the two earlier queries 'pilots licence' and 'firstaid' (I didn't take the time to give things proper names) in the query designer this shows the employee table, joined to pilots licence with the option selected to only return records where both fields are equal and then pilots licence joined to first aid in the same way,.

 

[Opcode]

Wow, thanks wmphoto! I appreciate all that input!