Relationship between tables

[Muldoon]

I’m in the process of learning Access and had a question about how to set up a test database. I've been through several tutorials and looked at some different examples, but I'm not sure of where to go next. The attached image shows what I have so far.

What is currently working:
1. Populate the Classes table with data and have it flow into the Sections table
2. Populate the StudentInfo table with data and have it flow into the Student Class Table

What I want is when the StudentInfo table is populated with data that only the information from the Classes and Sections table are available. Since the tables are not related, I can put anything in for the class and section...

[orange]

Why do you have Class within the Section table? I'm not suggesting it's wrong, but I'd like to know why you have it.

Do you have a write up on the rules that this database is supposed to support? If so, please post it.

[Muldoon]

I'm just starting out and trying to learn as I go.

Here are the "rules":
The students should have their own information and be able to see what classes they are taking.
Each class should have its own information (section, session, room)....

Right now, the students have their own info and each class has its own info. Now I'm trying to enroll students in some classes. When the students select a class they should only be able to select a class that already exists....

I have the Classes setup so I can have something like Math, 101, Intro and Math, 201, Trig... Later on I can make sure that I always use "Math" and not "Mathematics".... Without Class in Sections I couldn't relate the Sections back to the specific class. There could be Math 101 and Science 101...

If there is a better way to do to it then I'm definitely listening

[orange]

Can you describe Section, session and room?

It seems you have Classes and Students.
A Student can enroll in several Classes.
A Class can be taught in different Sessions in different Rooms.

But what exactly is a Section?

[Muldoon]

I was able to get it working (though there probably is a better way). I had initially switched some of the values in Indexed switched to Yes (No Duplicates). Even when I switched all them back to No and deleted all of the test data it still wouldn't allow me to make the needed Relationships. I basically started over with the exact same Tables and layout but left the Indexed value to No. After that everything seems a lot better.

The Section are needed when there are multiple Classes with the same Number but when they are at different times or locations. For example, Math 101 (Class and Number) could have 10 different Sections and could be taught in the Spring (Session) or Fall (Session), MWF (Date) or TR(Date), in different Buildings and in different Rooms.... Because of this, the Title should be the same for the Class and Number combination. When there is a different Class/Number combination then there should be a different Title.

If it helps anyone, here is an image of the working Relationships; everything cascades when Updating/Deleting.

[orange]

Glad you have it working.

If your interested in another Student Courses database with lots of descriptive materials related to the processes of building the data base, then see

http://www.rogersaccesslibrary.com/T...nformation.zip


You may also find the info at post#6 about compound PK at
http://www.access-programmers.co.uk/...d+unique+index