Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2010
    Posts
    10

    Unanswered: Making a Scheduling Database

    I wanna make a simple scheduling database:
    There are teachers who can only work certain times.
    There are classes which are generally the same times each week.
    There are VIP students who request/deny particular teachers and times.
    The VIP students are the crux:

    What I figure is make one table for teachers, one for weekly classes and one for VIPs and then get the relations from there, BUT!
    I realized that the VIP times may not be continugous (and are different for each day), and same goes for the teachers, which means I have to answer the question: How do I get non-contiguous times into a table easily?
    Ie. John can only teach 9-10 and 5-9 on Tuesday (but maybe 9-9pm on Wednesday)
    and Jim can only come to class 3-5 on Monday or between 2 and five on tuesday, etc, etc.

    I saw somewhere that Access 2007 lets you put in more than one unit of data per field (which is actually a violation of those crazy normalization rules or whatnot, except that really it does behind the scenes work to make it normalized, but "user friendly" - do I understand that right?) I don't wanna use Access crutches - how should I structure my data?

    Should I make one table per-teacher so that I can have hour by hour booleans of when they can work or something like that? What is the best way to deal with time anyhow? I'm a little confused about this part - any suggetions?

    How many hours do you think this crazy scheduling project is gonna take? (especially for an ub3rN008 like me?)

    Thanks bundles

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    Call normalisation rules "crazy" around here will make you no friends!

    I would suggest that you think about the smallest unit of time by which you will be dividing records - most likely hour or half-hour. Then build a table that holds each of these for each day:
    DayName---Period---StartTime---EndTime
    Mon---1---07:00---07:29
    Mon---2---07:30---07:59
    ...etc.

    You then build a couple of intersection tables - one is the intersection of teachers and teaching periods; the other is the intersection of pupils and teaching periods. Once you've populated these tables, you'll be able to see when peoples' schedules line up.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Nov 2010
    Posts
    10
    I deeply appreciate your response, but hurt by the results!
    Why's it gotta be so MUCH data?

    I guess the next question is this:
    Are there any hand scripts or something that I can make so that the user of this table (not me) can simply input a range of hours and then have the table automatically filled out for him?

    Can this be done using the database? Should I have a "Entry" table which relates to the data by inputting to the (excel spreadsheets) rather than pulling data from them? At first I thought that I would just have the user put the data into the spreadsheets directly, but now I think that won't do...

    Sorry for my newbdome

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    Well, once you've created the table to hold the scheduling slots, you can create the data to fill it in Excel, using copy & paste/copy down/fill down/whichever method you like. Then you can copy and paste append it into the Access table. Alternatively, you can write a VBA sub to create the records using some form of looping process.

    I'm not what you mean by, "have the table automatically filled out". Which table? Do the records magically appear out of the aether? (I'm sure that some of my users think that, but that's a whole other rant.) If your scheduling data is coming in from a spreadsheet, then yes - you can write processes to pick that up and use it to create records in Access tables. It's how I populate a couple of databases.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  5. #5
    Join Date
    Nov 2010
    Posts
    10
    What I meant was:
    User inputs 7-13:30 in a box (which means 7 am to 1:30pm) and then the table is filled out for the user (so she doesn't have to mark all those boxes).
    ^^This can be accomplished with VB?
    But yea... now that you mentin it - fill down is pretty handy - still all those boxes might scare them.

    Thanks Sorry for my hand wave wording - not real clear on the lingo etc.

  6. #6
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    Right, yes. That you can do, although I'd probably use combo boxes to make sure that what the user entered matches up with what's in the scheduling slots table.

    If you design a user interface with forms and reports (much to be encouraged), you can hide the scary tables from the users, and stop them from changing important stuff and breaking the database.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •