Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Posts
    126

    Unanswered: Database Design Help for Simple Scheduling

    I am transitioning a simple application from Excel to be part of an Access ADP, and it is proving to be WAY more complicated than I first thought.

    Current functionality: The user maintains a list of open orders (rows in excel, each with an order number). The user assigns a date and time that order is scheduled to be picked up. Then the user manually updates a schedule that is formatted like a weekly calendar, entering the order number to be picked up in the appropriate time slot (and I think carrier and destination too)

    Now, these two "functions" are obviously just two different "views" of the same data, so i thought this would be easy, but it isn't.

    I relatively easily replaced the first function, the list of open orders. Then there is a field for scheduled date and one for scheduled time and this can replace that Excel spreadsheet.

    My troubles arise from creating any sort of schedule report. The load times are scheduled at 45 minute intervals from 6:30 am to 6:15 pm, and are standard every day. How can I easily show a schedule that takes into account any empty loading slots? Do i need to create a table that simply lists all the possible load times and then do an outer join between that table and my scheduled orders table to see what is scheduled to ship when and what's still available?

    Also, my interface right now is just entering a date and time in the list type view (it's a little more complicated if you look at my other recent posts), are there any suggestions on entering the data in a format similar to the weekly calendar view?

    i am honestly at a complete loss, and as usual, this needs to be done...yesterday.

    thanks all for your help.

  2. #2
    Join Date
    Feb 2004
    Posts
    126
    Yeah, that's what I thought, this is hard.

    It looks like I'm going to polish up the first half of this project (the orders list) and they can generate the schedule by hand the way they're doing it now until we figure out a better solution.

  3. #3
    Join Date
    Sep 2003
    Location
    Washington, DC
    Posts
    116
    I feel for ya...

    Is the excel spreadsheet linked to the db, or did you move the data into the db?
    If you moved excel to db, how did you set this up? 1 table for orders and 1 for scheduled shipments, or something else?

  4. #4
    Join Date
    Feb 2004
    Posts
    126
    We maintain orders in our ERP/Accting. system database. This whole Excel thing was being maintained by duplicating the entry in Excel and e-mailing them to a remote office where he added them to his Excel spreadsheet.

    What I am doing in Access is just pulling the list of open orders directly from the ERP system (which is fortunately all on SQL Server) and then, since we can't modify our ERP systems table structure, adding any additional fields to our own SQL table that has a 1 to 1 relationship to the orders in the ERP system, kinda like a vertical partition.

    So we don't really ahve to move the data in the Excel to a database because it's already there. We jsut have to pull the open orders from the ERP system, that part is done.

    Now what I am trying to do is replicate the functionality of the weekly schedule. I am having a tough time visualizing how to enter data and create a report that looks like a weekly schedule. So in the meantime, I think it might be best if they maintain the weekly schedule by hand in Excel still.

    The flexibility of Excel is both a gift to the user and a curse on anyone trying to create any sort of structured system.

Posting Permissions

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