How can you have two fields with multiple data linked to each other?

[StarScream]

How can you have two fields with multiple data linked to each other?

If I have two fields: skills and classes. I want to be able to list all the classes that fall under a certain skill as well as match all the skills that fall under a certain class. How is the best way to link these two? (the numbers for each may vary and I may add more classes or skills over time)

Example:
Class 1 = Skills 1, 4, 8, 13, 19
and
Skill 1 = Classes 1, 3, 5, 12, 13, 19

Thanks.
PJ

 

[WildHare]

Read this and then let us know if you need more assistantce. It's just a matter of designing the correct kind of left and right join(s)...

http://www.jmhare.com/joins.htm

Ex-JimAtTheFAA
78.5% of all statistics are made up on the spot.
Another free Access forum:

http://www.forumco.com/jmhare

More Neat Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[StarScream]

Okay, I understood that. But still am lost about what fields to have in which tables. Here's my example:

Students (Table)
StudentID (Primary Key)
First Name (Field)
Last Name (Field)
Major (Field)
Classes (Field) <-- Do I have this in this table?

Classes (Table)
ClassID (Primary Key)
Class Name (Field)
Instructor (Field)
Students (Field) <-- Do I have this in this table?

OR
Do I have a third table:
Schedule (Table)
ClassID
StudentID

Please help. Thanks.

PJ

 

[WildHare]

Yes, you want a third table - it's the &quot;merge&quot; table of CLASSES as taken by STUDENTS - so it will have the ClassID and the StudentID as keys. If a student can take a particular class more than once, then you'll need some kind of class-start date field as well. Here's a sample table:


*CourseID <-- FK from the Classes Table
*StudentID <-- FK from the Students table
*ClassDate <-- if necessary
Grade

Every time a STUDENT takes a CLASS, a record will be created here.

With the proper joins, you'll be able to report

1) which classes any particular student has taken
2) Which students have taken any particular class
3) which classes have NEVER been taken
4) which STUDENTS have never taken a class

and so on.

Get the picture?

Ex-JimAtTheFAA
78.5% of all statistics are made up on the spot.
Another free Access forum:

http://www.forumco.com/jmhare

More Neat Access stuff at

http://www.jmhare.com/wildhare2.htm