Can I use same field twice in a table

[alexthefourth]

I have a courses table which has information about courses. It has the following Fields: courseID, Professor, Units.

There is another table called prereq which has prereq id as the primary key. courseID is a foreign key in the prereq table because one course can have many prereqs. The prereq table also has a field called prereqname which simply gives the name of the course that is a prereq.

So right now if i want to find all of the prereqs of a specific course I only get the name.



Is there a way to use the courseID a second time in the prereq table so that when i want to find the prereqs of a specific course I can get all of the course data of the prereqs?

[June7]

Can use the same data in multiple fields. Consider:

tblPreReqs
PreReqID (autonumber, this field might not be needed)
PrimaryCourseID (foreign key for the course)
PreReqCourseID (foreign key for the course)

Set the two CourseID fields as compound primary key and duplicate pairs won't be allowed.

Not every course will be in this table. Only need to have in PrimaryCourseID field if course has prerequisites that will be listed in PreRegCourseID. If a course has no prerequisites then no record required. Many courses will be in both fields.

[alexthefourth]

Thank You,

and when i run a query to display each courses prereqs how can i use PreReqCourseID to grab the CourseName, Professor, and units which is located in the Course table?

[June7]

Include Course table in the query twice, one to each of the CourseID fields.