Structural question - URGENT, as I guess they all are :)

[marika1981]

Hello!

I have a structural question which I suspect is at the root of some problems
I'm having in a database I'm designing. I'll get to the actual problems
if/when I receive a response. Here's the structural question:

My database consists of three main tables: 1) a list of student names and
various personal data - one record per student. 2) a list of potential
colleges and universities for them to attend and various data on each school
- also, one record per school, per year. 3) A table which consists of only 3
columns - year of data, student name, and school name. Here, each student
has several (i.e. 30 or 40) records each, one record for each potential
school he/she has been matched to. The data in this table is populated
through a Data Entry form using Add New Record functions. The user of the
database is someone who matches students with schools and wishes to build new
lists of schools for students to consider.

My main question: is this table structure not optimal? Could this structure
be a source of foreseeable problems in your opinion?

Less importantly, there is a fourth table called tblMeetingNotes which also
has multiple records per student - one record consisting of a notes field for
each dated meeting had with the student.

The problems I'm having have to do with populating and growing those tables
with multiple records per student (3rd table above, and tblMeetingNotes) when
a new student is added to the database and doesn't already exist in those two
tables. I would like that the student's name (and the year of the data) be
automatically the default value when the forms are opened to populated those
tables. Instead I get #NAME, since the forms where the new data is inputted
are linking to the tables where the data (i.e student) doesn't yet exist. I
tried linking to a query instead of the simple tables, but still had
problems, I think, because one of the forms is a Data Entry table. Can you
Autofill fields in Data Entry style forms with values from other open forms?

Thank you SOOOO very much for your time. I'm pretty new at Access, and any
timely assistance would be incredibly appreciated.

Very best,
Marika1981

 

[Marshall Barton]

I have a structural question which I suspect is at the root of some problems
I'm having in a database I'm designing. I'll get to the actual problems
if/when I receive a response. Here's the structural question:

My database consists of three main tables: 1) a list of student names and
various personal data - one record per student. 2) a list of potential
colleges and universities for them to attend and various data on each school
- also, one record per school, per year. 3) A table which consists of only 3
columns - year of data, student name, and school name. Here, each student
has several (i.e. 30 or 40) records each, one record for each potential
school he/she has been matched to. The data in this table is populated
through a Data Entry form using Add New Record functions. The user of the
database is someone who matches students with schools and wishes to build new
lists of schools for students to consider.

My main question: is this table structure not optimal? Could this structure
be a source of foreseeable problems in your opinion?

Less importantly, there is a fourth table called tblMeetingNotes which also
has multiple records per student - one record consisting of a notes field for
each dated meeting had with the student.

The problems I'm having have to do with populating and growing those tables
with multiple records per student (3rd table above, and tblMeetingNotes) when
a new student is added to the database and doesn't already exist in those two
tables. I would like that the student's name (and the year of the data) be
automatically the default value when the forms are opened to populated those
tables. Instead I get #NAME, since the forms where the new data is inputted
are linking to the tables where the data (i.e student) doesn't yet exist. I
tried linking to a query instead of the simple tables, but still had
problems, I think, because one of the forms is a Data Entry table. Can you
Autofill fields in Data Entry style forms with values from other open forms?

That is the proper way to model a many to many relationship.

A popular UI (because it is easiest) for that is to use a
form bound to the student table and a subform bound to the
student-school-year table. Then subform would have text
boxes for the year, ets. and a combo box dor selecting the
school. Set the link master/child properties to the student
field and that field will be filled in automatically when
new records are added to the subform. (You can set the year
text box's DefaultValue property to Year(Date()) to get the
current year automatically, but type over it if you want a
different year.)

 

[Beetle]

From your post it *sounds* like you might have a fcouple of flaws in your
table structure. You don't say how your relationships are set up but,
generally speaking, anytime you have multiple records that repeat the much of
the same information (Student Name & Address, School Name & Address, etc.),
then you have a design flaw.

For example, you said your Colleges table has one record per school *per
year*. Is this because certain information about a school can change from
year to year and you need to keep the historical data? If so, then that
information should be in another table. Also, it sounds like you have a
many-to-many relationship between the student and the school, in which case
you need a 3rd table to define the relationship (it's possible that table # 3
you describe in your post was intended to serve this purpose, but it normally
shouldn't be storing student name and school name - just the ID numbers)
Below is a rough idea of how the table structure might look based on what I
can gather from your post.

tblStudents
********
StudentID (Autonumber Primary Key)
LastName
FirstName
Other fields related specifically to the student

(Note: In my experience College aged students tend to move a lot - on
campus, off campus, back to parents house, crashing at friends parents house,
etc - so you may want to keep address information in a separate table related
by StudentID with a fiel to indicate which adress is current. That is *if*
you store that type of info about a student)

tblSchools
********
SchoolID (AutoNumber PK)
SchoolName
other *non-variable* fields related specifically to the school

tblStudentSchools (junction table)
*************
SSID (AutoNumber PK)
StudentID (Foreign Key to tblStudents)
SchoolID (FK to tblSchools)
MatchDate (if you need to track the date a student was matched with a school)

tblSchoolVariables
**************
VariableID (AutoNumber PK)
SchoolID (FK to tblSchools)
SchoolYear
other fields for *variable* school information

tblStudentMeetings
**************
meetingID (AutoNumber PK)
StudentID (FK to tblStudents)
MeetingNotes (memo field)

In regards to your other questions;

1) You can't create records for a student that doesn't yet exist in your db,
but there are ways - like the NotInList event of a combo box - to make it
relatively easy for your users to add a student to the db, and then continue
adding the other data related to the student.

2) As far as displaying data from other tables (tables that aren't the
recordsource of the form you are working in), yes - this is commonplace in
Access. The data is not stored redundantly, it is just displayed when you
need it.

HTH