Little help getting started...............

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am planning to create a database and need a little guidance on how to get
started. I want to create a dB that tracks students going through our command
and assigns an instructor to each student (sort of like a mentor to the
student). I understand how to create the input form for the students
information and whatnot and have done so. I have also created a table with
all instructors at my command. What I can't comprehend is how would I
automatically assign a next sequential instructor from a table of instructors
when I inputted student information into a record. For example, if I inputted
Joe Shmoe's student info into the database, upon completion of the final
textbox, the next name in an instructor table not assigned to a student would
fill the gap. At the end of the instructor list, it would then begin again
at the top. Any guidance on how to do this would be most appreciated.

Cheers,

Frank
 
Frank

One consideration is that Access tables are unordered buckets-o-data. The
concept of "the next name in an instructor table not assigned..." only makes
sense if you've added some way to order your table of instructors. Once you
have a way to order the instructors table, you'll also need a way to confirm
which are "free" at the moment.

If you have a third table that associates a student with an instructor, that
would be the place to "look" for who is currently busy (a backwards approach
to determining who is NOT busy, but it works automatically, without having
to update a "BusyNow" field).

But if you record StudentA and InstructorB in that third table, how do you
know when they are finished? You can add a StartDate and EndDate to that
third table ... this gives you a way to track who was working with whom, and
when, and identifying instructors who are currently busy (either they don't
have an EndDate, or their EndDate is in the future).

Do that as a query (who is busy?), then use that query back against your
Instructor table to find who is not on that list, and, therefore, is
available.

Now the last step -- sort that query result by your "order", and modify the
query to only show the Top 1.

(I bet you're sorry you asked!)

Good luck!

Jeff Boyce
<Access MVP>
 
(my emailer "croaked" in the middle of my response. I hope this doesn't
duplicate it.)

Frank

One consideration is that Access tables are unordered buckets-o-data. The
concept of "the next name in an instructor table not assigned..." only makes
sense if you've added some way to order your table of instructors. Once you
have a way to order the instructors table, you'll also need a way to confirm
which are "free" at the moment.

If you have a third table that associates a student with an instructor, that
would be the place to "look" for who is currently busy (a backwards approach
to determining who is NOT busy, but it works automatically, without having
to update a "BusyNow" field).

But if you record StudentA and InstructorB in that third table, how do you
know when they are finished? You can add a StartDate and EndDate to that
third table ... this gives you a way to track who was working with whom, and
when, and identifying instructors who are currently busy (either they don't
have an EndDate, or their EndDate is in the future).

Do that as a query (who is busy?), then use that query back against your
Instructor table to find who is not on that list, and, therefore, is
available.

Now the last step -- sort that query result by your "order", and modify the
query to only show the Top 1.

(I bet you're sorry you asked!)

Good luck!

Jeff Boyce
<Access MVP>
 
Back
Top