Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    bbrazeau is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Sep 2011
    Posts
    101

    Scheduling Presses

    Hi, I'm creating a DB and in it I would like to have a scheduling aspect. I would like to track the schedule for 30 presses.
    Information:
    1).Presses get jobs scheduled to run in them that vary in length of time.


    (Nominally this time could be calculated by multiplying cycle time by qty. req. in run plus setup and breakdown time) however, this would not account for mid-run problems or press adjustments, but an estimate would suffice. ie. I can be off by an hour or so and it would be no sweat.

    2). The schedule is usually followed, but sometimes a job will be pushed out to get a higher priority job completed first. (So it should be changable)

    3). The number of presses is something that may change, as more are bought, but will never get that large. ( maybe 40 or 50 max)

    I have a table to track production runs.( part# and qty. req.)
    I have a table with Press info.
    Does anyone know of a way to do this or something similar??

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    So do you want something like a Gantt chart? http://www.brighthub.com/computing/w...les/57933.aspx
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    bbrazeau is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Sep 2011
    Posts
    101

    Scheduling Presses

    Hi June7, I looked over the link you sent and... I don't believe a gantt chart is what I need. From what I saw and read a gantt chart would be for tracking the steps in a project. If my data were a gantt chart the project would only have one step...Is it complete or not? What I am striving for is a record of the daily utilization of 30 presses. For example:

    Press 1 is scheduled to run Job-x from 10/27/11 to 11/01/11, then Job-y from 11/01/11 to 11/09/11, and so on.

    Press 2 is scheduled to run Job-z from 10/27/11 to 11/07/11, then Job-a from 11/07/11 to 12/01/11, and so on.

    Press 3 is scheduled to run Job-y from 10/27/11 to 10/31/11 then Job-b from 11/01/11 to 01/03/12, and so on.

    and if I need to I can go in and create a new record to put Job-c in Press 2 in between Job-z and Job-a and have it change the dates for Job-a either automatically, or by pressing a command button.
    "Presses" and "Jobs" are tables in my DB. If "Press_Schedule" were also a table with Press_ID, Job_ID, and start/end dates could I have a command button that would query records for that specific press and update the dates of future Jobs based on the new job being inserted after an existing job? In other words: If I made a new record for Press 2 and put Job-c in it with a start date of 11/07/11 and end date of 11/14/11 it would change the dates for Job-a to 11/14/11 to 11/22/11. Likewise, if I go in and delete Job-c it would move the dates back to where they were.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Automatically change start or end date of a record based on another record's range or deletion of a record? Not code I would want to tackle but suppose could be done.

    As far as presenting the data so it can be easily analysed, I still think something like Gantt is applicable. It's just a timeline chart. Worked with couple posters who used this visual presentation to track periods by categories, one was for employee attendance (meeting, training, absence).
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    bbrazeau is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Sep 2011
    Posts
    101

    Scheduling Presses

    Hi June7, Can you direct me to the post where this was discussed? Could you provide a simple example of it's implimentation? Thanks for your replies June7. You have been a big help.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    The attachment on the busdriver3 post has been removed (post was years old) so here's another copy:

    BusDrive3.zip

  8. #8
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by rpeare View Post
    The attachment on the busdriver3 post has been removed (post was years old)...
    I was noticing older posts are missing their attachments. I guess the server was purged, bummer.

  9. #9
    Broes is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jan 2017
    Posts
    10

    Problems with "0" in the date

    Thanks to this thread I managed to produce a schedule for my projects.
    Unfortunately something goes wrong when I insert dates with a "0" in the day in my schedule table like 10-11-2017 and 3-12-2017 (dd-mm-yyyy).
    When I fill in "0" days I get no week nr or the wrong week nr back.
    So I assume I have to change the second row of the code, I have tried several options but no result.
    Hopefully someone can point me in the right direction.

    Code:
    Do While dCurrDate <= dEndDate    sSQL = "INSERT INTO tblReportSetup (Nr, OffTimeID, DateOff) "
        sSQL = sSQL & "SELECT [tblProjecten]![Nr], iif(#" & dCurrDate & "# between [startdate] and [enddate], [offtimeid],null) as Expr2, '" & Right("0" & DatePart("ww", dCurrDate, 2, 2), 2) & DatePart("yyyy", dCurrDate, 2, 2) & "' AS DateOff "
        sSQL = sSQL & "FROM tblProjecten "
        sSQL = sSQL & "RIGHT JOIN tblSchedule ON tblProjecten.Nr = tblSchedule.Nr"
      
        Debug.Print sSQL
        db.Execute sSQL
            
        dCurrDate = DateAdd("ww", 1, dCurrDate)
    Loop

  10. #10
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Have you got an example database to play with, hard to know what to tell you when I don't know the data stucture/etc

    this is assuming you're adapting my busdriver database linked above.

  11. #11
    Broes is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jan 2017
    Posts
    10
    Thanks for your fast response it's indeed based on your busdriver database.
    I created an example database with two projects.
    For the first project I used the "0" dates.
    Attached Files Attached Files

  12. #12
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    First, you are going to have trouble with this database, for instance in your table tblProjecten you have a primary key field (ID) but you are storing the Nr field as your foreign key on your tblSchedule. You should be storing the tblProjecten primary key field value in tblSchedule.

    Second, my example has static column headers, your crosstab query does not. Every new date range you put in alters the column header names which in turn invalidates your fields on your report.

    Your options are to
    1. Create a crosstab that has static column header names
    2. Create a report that is capable of reading a crosstab and correctly altering control sources to reflect the columns of the report.

  13. #13
    Broes is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jan 2017
    Posts
    10
    You are absolutely right about the wrong foreign key in the tblSchedule I was aware of that and was going to change this after solving the problem.

    I am not sure about what you are trying to explain me for option 2 (that's the road I want to take).
    What I see is that the problem appears already in the tblReportSchedule and the query and the report are showing exactly what's in this table.
    So my assumption is that I have to alter the code that's generating the data for tblReportSchedule (but I have no clue how to do this).

    But maybe I have overlooked something?

  14. #14
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I have some experience with equipment reliability and maintenance scheduling, so I'm not sure what you mean by "track the schedule" but I take it you're not looking for a way to announce due dates for maintenance or record details of it. What about an Access based Outlook type of calendar? I have a copy of one which I think I got from this forum but I haven't played with it much. I know it can hold more than one appointment on the same day, but not sure it would hold 50 if you're looking to do that for a number of machines.

    As for post #9 where you mention a zero problem, I'd say it's because you're trying to mix text ("0") with numbers in your use of DatePart.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  15. #15
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Just for the sake of etiquette....

    @Broes,
    What you have done by posting in this thread is called "Hi-jacking a thread".
    This is considered bad for two reasons:
    1) You are asking a thread in someone else's thread.... and this thread is almost 2 years old!
    2) You will get more views and possible responses if it is your own thread and not someone else's

    It is better to start a new thread and reference a thread that is close to your question.
    Something like:
    "I have a problem that is something like this thread https://www.accessforums.net/showthread.php?t=18459&p=345889#post345889

    How can I .........."



    Because it is a new thread, you get more reads than hi-jacking a thread - especially if the old thread is marked solved.. (I know this thread was not marked as such)


    Waiting to see what solution you get....

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. scheduling appointments
    By mikejames in forum Programming
    Replies: 1
    Last Post: 09-30-2011, 04:45 PM
  2. Replies: 3
    Last Post: 04-28-2011, 10:46 PM
  3. Scheduling Problem
    By Marie in forum Queries
    Replies: 2
    Last Post: 01-19-2011, 09:10 PM
  4. scheduling db
    By JSHEL in forum Database Design
    Replies: 1
    Last Post: 12-12-2010, 09:28 AM
  5. Scheduling database in Access
    By ACS Newbie in forum Access
    Replies: 4
    Last Post: 01-21-2010, 10:32 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums