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.
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.