Setting up tables

[Gash13]

Need some advice on how to get started. I have three variables. "Instructor","Student",and "Time" The instructors and students can/will change. The times will remain the same. This is my setup now. I have a table that has the first field as "instructor" the rest are "M-3:00", and "M-3:00i", "M-4:00, M-4:00i" etc.. There are 40 of them. (The times can only contain 2 students thus the "i&quot The form I built pulls the students name from a student table(contains names and info) and puts it in the correct Time field in the 1st table. It works great for keeping the schedule and printing the reports. Here is my problem... I need to be able to display the Times on the student Information Report. (The times are fields right now so I can't)..

Any idea how I could set it up differently... I'm using Access 97

 

[dawnd3]

Are you saying that the field names are M-3:00, M-3:00i? If this is so, this isn't a very good way to set it up. Your table is not "normalized". Just from your example I would have 1 table for Teachers and have any information pertaiing to the teacher, [TeacherId], [name], etc. Then, the easiest way, would be to have another table (time table)with the fields, [Teacher ID], [Student], and [time]. Since there are only 2 students you can create a combo box in the table with a value list of the two names to choose from. And then the time field you can do the same and have the values 3:00, 4:00 and so on in the list to choose from. Then you would link the two tables by the teacher ID, (one teacher can have many student/time combinations). If you had more students I would say that a student table would be better. Does this sound like it will work? Am I explaining this ok? The combo box I am referring to can be found in the properties of the table design. You can even create a combo box for the teacher (in the time table) and pull the name from the teacher table. Or you can wait and do that in the form. Let me know if I can explain further. Dawn

http://www.calliopeconsulting.net

 

[Gash13]

I need to be able to see the whole schedule (instructors and open and occupied time slots by day) when assigning students their time slot. I have roughly 10 instructors and around 100+ students. Only two students can be with an instructor at one time. I know it is a mess. It is hard to explain without actually showing it.
The database I put together does everything I need it to except put the times with the students.

In access 97 can you put entries in one field? (if not that is why I have M-3:00 as one field and M-3:00i as a second field.--I need to be able to see open time slots)

Thanks Any ideas would greatly be appreciated..

 

[Gash13]

The last part didn't come out right. Can you put multiple entries in one field on a single row.

 

[dawnd3]

It isn't a good idea to have multible entries in one field. That is part of the reason you are having issues. It also isn't wise to have many feilds with the same type of data, ie: M-3:00, M-3:30, etc. From what you are telling me now, you should have Teachers, Students in 2 different tables. And then in a third table have Student ID, Teacher ID and then a time feild. If you want to be able to show slots where there isn't anything scheduled, then you would just have a record for each time slot and change the teacher and student as necesary. You can then lay this out on a form to view as you need to, using combo boxes to select the student name and teacher name for each time slot.

Like so:

Time slot Teacher Student
3:00 John Smith David Jones

Remember to divide names up into first and last name. If you want more information on setting up tables look up "normalizing data" or "5 normal forms" in help or here on Tek-tips.

Your table structure is definitly not going to work the way you have it. If you want to do it the way you have it, maybe you should just use excel.

let me know how it goes, if you give me your email address, I will send you a quick sample of how I think you should have this.

Dawn

http://www.calliopeconsulting.net

 

[Gash13]

I would greatly appreciate the examples. My e-mail is appleton@odsy.net I do understand how you are explaining I should set it up. I did start that way, but I ran into some problems, so I switched to the way I have it now. If you will send your e-mail address I could send you my database so you can see exactly what I'm trying to do. (It is real hard to explain in a message.) I can zip it down to around 600kb

I appreciate your help..

 

[dawnd3]

Can you tell me the kinds of problems you were having when you tried to set it up the way I described?

Dawn

http://www.calliopeconsulting.net