Compare teachers and their courses

[cmyers]

Hey all!



I've created a Database to reference teachers with the courses they've taught. The last names are pulled from a different sheet which has contact information. Since a course is unique to the code in the first column, I've used it as the Key in the format (TTTT ####). This means that over time, a course may remain active, but it will switch between teachers. Because of this, I've allowed multiple selections in the drop-down of the instructor(s) column. What I want to create is a subdata sheet that displays the courses a teacher has taught, but access won't allow that on the contact sheet since Instructor(s) allows multiple choice. Can I use a form/query to pull this data? Thanks for any help!

[ssanfu]

It looks to me like the structure is incorrect. I don't know how you would get the info you want without several queries and/or a lot of VBA code.

(try this in a copy of your dB first)
I would have fields: (NO look-up fields)

Field Name........... Field Type
-------------------------------
CourseID_PK.........Autonumber
Instructor_FK..... ..Long Integer (to a look up TABLE)
SemesterYr_FK......Long Integer (to a look up TABLE)
CourseName.........Text



Now finding out which courses a teacher has taught is a simple query.

[June7]

Data structure is troublesome at best. The checkbox fields are for semesters? Multiple similar name fields for same data is an indication of non-normalized structure and can cause serious issues with searching and filtering, even more difficult because they are yes/no fields.

To get the output you want from current structure requires a query that expands the multi-value Instructor(s) field to individual records and apply filter/sort criteria http://office.microsoft.com/en-us/ac...010149297.aspx. I NEVER use multi-value fields.

Advise no spaces or special characters/punctuation (underscore is exception) in naming convention. The parens in Instructor(s) is bad.

[orange]

cmyers,

I agree with June7 and Steve that your structure is not supporting your requirements very well. I have a suspicion that you have an Excel/spreadsheet background, and may make learning Access more difficult than no Excel background.

For database, in my view, you really need a good description of WHAT you are trying to do. With Teachers and Courses there is often Student, Semester, Course, Class, Grade, Roster....

It seems you are working with a small corner of this. Perhaps you could tell us more about WHAT you are trying to do. You have shown us how you have tried to do something, but we don't know the "business need".

[cmyers]

Thanks for all the help guys!

Orange had the correct suspicion, I'm a big excel guy working on a project to bring information into a database. There isn't data beyond this as far as students, etc, but the other datasheet contains information about the professors. The main goals of the database are to 1) store the information about when a course was offered, last approved, and taught by who so that we can 2) analyze the data to track which courses need to be re-reviewed, see why a course may have stopped being offered, or try to answer other questions of the data.

These courses operate through a relatively new department (center for public service) but new procedures as far as reviewing courses for integrity means that the paper/excel records need to go to something more powerful. Thanks again for the tips and hopefully this info helps!

Edit: Forgot to mention that while I'm creating this database, it serves to help people who aren't terribly knowledgeable about Excel, Access, etc. I plan on then creating forms/queries for them to access the info and add data, and then passing it on to them for their use.