Need help creating a complicated DB (1 Viewer)

[lacey]

OK, this is a project I was working on earlier this year and came here for help, so if this sounds familiar to some of you, bear with me...

I work at a community college. I am creating a DB to hold all the information for registering kids' in our summer Kid's College program. That information includes their personal information (name, address, etc.), session number, period number , and class title.

• There are three sessions total, each one week long
• There are four periods in one day
• Each class may appear more than once per session, and in more than one period

I need to be able to record the student's information, their session number, and what class they are enrolled in in each period.

Here is what I have so far: View attachment 39749

From what I can see, everything is going fine and dandy until I open the Session Query to select which class they are enrolled in. I am able to check which classes they want, but when I go to enter a new record, it keeps the same classes checked as the last student and I can't enter a new record on that part.

Does this make sense? Any ideas? Let me know if I can clarify anything. Also, try to use lamens terms as I am nowhere near an expert with this program.

 

[jdraw]

Some of us don't have acc2007 or 2010 and can not open a accdb.
You might try posting mdb version to reach a broader audience.

You might wish to post a jpg of your table relationships.

 

[lacey]

Access 2000 DB: View attachment 39750

Table Relationships: View attachment 39752

 

[odin1701]

Your query only looks at one table, so it's just going to update that table - that won't let you select what classes someone is in. It's simply setting a check box to true/false in the Classes table.

 

[lacey]

What would be the best way to have the classes set up, then?

 

[odin1701]

Ahh...there's lots of issues. First, it looks like your Classes table doesn't have any foriegn keys setup, the StudentID is an AutoNumber in the Student table, but elsewhere it's Text - that creates errors and you can't generate the necessary queries to do your updates.

 

[odin1701]

Classes should have:

ClassID (Can be autonumber if you wish)
SessionID (This should match the ID from your Session table)
PeriodID (This should match the ID from your Period table)
ClassName

I don't even know what the Yes/No is there for.

To assign a student to a class you would need a table for that which had:

StudentID
ClassID
DateRegistered (or other stuff you want to track)

You need a form to edit the class assignments which should have a drop down to select the student, class, etc. Or you can do a subform in tabs or whatever.

I don't know what the StudentSessions table is for. By assigning a student to a Class you know what session that class is in, at least how you are designed now.

In the same vein I don't know what SessionPeriod or SessionPeriodClass are for - looks like attempted normalization but it isn't setup right.

The other issue is that your Classes table is not normalized as it is now. There should only be one instance of a class name in that table, with a ClassID. You don't need session or period, though for your purposes it probably won't matter.

 

[lacey]

Okay... I'm trying to follow everything you said.

I changed the Classes table to reflect what you advised. I created a table with StudentID and ClassID.

The form to edit which student goes in which class (I'm assuming that's what you meant by "assignments"?) is confusing. How would I use a drop-down to select a student? Don't I need a text box so the user can enter the student's name? Please clarify.

As I stated in my OP, one class may appear in multiple sessions. Therefore I don't necessarily know which session the student is in just based on the class title. I'm guessing that's why I have StudentSession there.

Lastly, how should the table relationships reflect these changes?

 

[odin1701]

Well you should have something like a registration table. It depends on how your data is, but the way it is now - yes based on ClassID you would know which Session and which Period it is for - that isn't necessarily the best way to do it. Each ClassID should only appear once in a normalized table. So there should be no duplicated names. It's the same class I'm assuming - just offered in different sessions/periods.

In order to identify a registration you need the StudentID, ClassID, PeriodID and SessionID. You need a table that combines all that - or you need to create an offering of a class since the specific offering of a class is unique, then you add a registration based on that offering.

Take a look at the DB that I tossed together. It's a bit inelegant, but it would get the job done. You will have to modify things but maybe this will get you started on the right direction.

Take note of how the primary keys are done in order to enforce the rules that a class can be offered in multiple sessions or period, but can't be offered in the same session and period more than one time. Similarly a student can be registered in one and only one class offering, which relates to a specific class/period/session.

(Updated DB to add a report and query for the report so you can see how everything ties together.)

(2nd Update - Showing two different ways to add a registration - the Alternate way requires a bit of VBA and could be done much better - i.e. only enabling the next combo box after the first is updated, etc.)

 

[lacey]

Thank you so much so far! Your info has been helpful.

Can you tell me how one would go about using the DB you made? I don't understand how it could be used to enter new classes that correspond with the correct session/period. When I add a new class in tblClass it does not reflect in the Add Class Offering form.

 

[odin1701]

Thank you so much so far! Your info has been helpful.

Can you tell me how one would go about using the DB you made? I don't understand how it could be used to enter new classes that correspond with the correct session/period. When I add a new class in tblClass it does not reflect in the Add Class Offering form.

You wouldn't ever interact with a table directly. You would create a form to add classes. You also would have to add a class offering. A class and a class offering are different, IIRC.

 

[odin1701]

Yup, went back and looked. You need to add a class to the tblClass table, but you should use a form to do that.

Once there, you can add a class offering. There is an error on the Add Registration Alternate form, I accidentally left the tblClassOffering linked in the query for the Class Combo Box. Just need to delete that table in the query for the row source and it should pull up just fine.

 

[lacey]

What is the purpose of the Add Registration Alternate form?

 

[odin1701]

What is the purpose of the Add Registration Alternate form?

The add registration and add registration alternate simply show two different ways to do the same thing. The alternate form requires some VBA for it to work properly and is a bit more advanced.

 

[lacey]

Hmmm okay. I think I am getting the hang of this.

About the Alternate Registration--Can you explain how to use it? I would like to use that one instead because the other people in my department who will be registering students are NOT computer literate at all and I would like to make it as easy as possible for them to use.

How does the ClassOfferingID box work? When I select a student, class, session, and period, sometimes the ClassOfferingID box will be empty and thus not let me save the record.

 

[lacey]

I guess, from what I can see, not all classes I enter in the Add Class Option form get a ClassOptionID specific to the session/period. Why would that be?

 

[odin1701]

Because I was doing that very quickly and messed up the query. I've attached one that works properly.

Each combo box you select allows a query to be run on the successive box using the value(s) from the previous. In the case of the final combo box, for period, I didn't have it limit the choices on both the SessionID AND ClassID, so some Periods could show that were not really available.

After each combo box is updated, it does a requery on the next combo box in VBA so that the proper results show.

As mentioned on the form, you can hide the OfferingID combo box because nobody needs to see that when they use the form.

You shouldn't really use these forms as is because the "add" forms should really only let you add more records, not view existing ones, as well as other design issues. This was thrown together in a very short period of time to simply illustrate how the table design should work. Also you should enable/disable combo boxes on the registration alternate form so that the user is forced to start with the first and then proceed, and if they change a previous one, it should clear the values and if they change the first one it should clear the values from both the following as well as disable the last one again.

 

[lacey]

Gotchya. Thank you so so much for your help! I was going crazy trying to figure this out. I'll let you know if I have more questions

 

[lacey]

So, when I delete all of the dummy names that are in the DB and dummy classes, it wants me to enter a parameter value when I try to enter in new students and new classes. This has got to be simple, but can you tell me how to get around that?

 

[odin1701]

Where are you entering the new students and classes? Did you make a form? That's what you should do. What object is asking for a parameter?

Also, you can't just delete the names and classes because they are foreign keys in the registration and classoffering table.